github-starred/open-webui
2
0
Fork 0
mirror of https://github.com/open-webui/open-webui.git synced 2026-07-30 06:00:43 -05:00
Code Issues 2.4k Packages Projects Releases 129 Wiki Activity

[bug/perf] large db, slow query to /chats/ #1577

New Issue
Closed
opened 2025-11-11 14:47:42 -06:00 by GiteaMirror · 4 comments
GiteaMirror commented 2025-11-11 14:47:42 -06:00
Owner
Copy Link
Copy Source

Originally created by @thearyadev on GitHub (Jul 22, 2024).

Bug Report

Description

Bug Summary:
i've been having this issue where the /chats/ endpoint takes like 30+ seconds to respond with the chats list.

i did some poking, and queried the database using sqlite3. the select * from chats query takes forever to complete, with a huge amount of encoded file data dumped.

i'm assuming this is image/file data. When i upload an image (in this case, multiple images) the size of the database increases, and the /chats/ endpoint takes longer to resolve.

image

Deleting all chats resolves the slow downs.

the query used by the backend loads the entire table for a single user, which in my case, contained hundreds of images.

SELECT chat.id AS chat_id, chat.user_id AS chat_user_id, chat.title AS chat_title, chat.chat AS chat_chat, chat.created_at AS chat_created_at, chat.updated_at AS chat_updated_at, chat.share_id AS chat_share_id, chat.archived AS chat_archived FROM chat WHERE chat.user_id = ? AND chat.archived = 0

result from sqlite3 query:
image

Steps to Reproduce:

  1. Upload large files (images) to the chat
  2. repeat step one until your database is massive
  3. open devtools/network
  4. refresh open-webui, and see the duration of the /chats request.

Expected Behavior:
the query should be quick. In a separate environment, with ~100 chats, the query time is like 1/100th of a second.

Actual Behavior:
the query took 30 seconds.

Environment

  • Open WebUI Version: v0.3.10
  • Operating System: ubuntu 24 -> docker

possible solutions

(just thinking out loud)

  • paginate chat list
  • load files elsewhere at query time and inject them into the chat/completion call, to avoid database bloat ( unrelated but would help with db bloat)
  • exclude the chat contents from the query/create a new query specifically for the chat list which does not include the entire chat contents; as this data isnt (?) required to display the sidebar
Originally created by @thearyadev on GitHub (Jul 22, 2024). # Bug Report ## Description **Bug Summary:** i've been having this issue where the `/chats/` endpoint takes like 30+ seconds to respond with the chats list. i did some poking, and queried the database using `sqlite3`. the `select * from chats` query takes forever to complete, with a huge amount of encoded file data dumped. i'm assuming this is image/file data. When i upload an image (in this case, multiple images) the size of the database increases, and the `/chats/` endpoint takes longer to resolve. ![image](https://github.com/user-attachments/assets/ded3f8f9-f0d6-4e9e-b5c7-da0d9edb11c9) Deleting all chats resolves the slow downs. the query used by the backend loads the entire table for a single user, which in my case, contained hundreds of images. ```sql SELECT chat.id AS chat_id, chat.user_id AS chat_user_id, chat.title AS chat_title, chat.chat AS chat_chat, chat.created_at AS chat_created_at, chat.updated_at AS chat_updated_at, chat.share_id AS chat_share_id, chat.archived AS chat_archived FROM chat WHERE chat.user_id = ? AND chat.archived = 0 ``` result from sqlite3 query: ![image](https://github.com/user-attachments/assets/0da8188e-cff2-4a3d-b50c-c0ba94b66be8) **Steps to Reproduce:** 1. Upload large files (images) to the chat 2. repeat step one until your database is massive 3. open devtools/network 4. refresh open-webui, and see the duration of the `/chats` request. **Expected Behavior:** the query should be quick. In a separate environment, with ~100 chats, the query time is like 1/100th of a second. **Actual Behavior:** the query took 30 seconds. ## Environment - **Open WebUI Version:** v0.3.10 - **Operating System:** ubuntu 24 -> docker # possible solutions (just thinking out loud) - paginate chat list - load files elsewhere at query time and inject them into the chat/completion call, to avoid database bloat ( unrelated but would help with db bloat) - exclude the chat contents from the query/create a new query specifically for the chat list which does not include the entire chat contents; as this data isnt (?) required to display the sidebar
GiteaMirror commented 2025-11-11 14:47:42 -06:00
Author
Owner
Copy Link
Copy Source

@Yanyutin753 commented on GitHub (Jul 22, 2024):

I also believe that file information should not be stored in a database, but the URL should be stored, providing a bold hypothesis that we can consider accessing oss or local file urls to solve this problem

@Yanyutin753 commented on GitHub (Jul 22, 2024): I also believe that file information should not be stored in a database, but the URL should be stored, providing a bold hypothesis that we can consider accessing oss or local file urls to solve this problem
GiteaMirror commented 2025-11-11 14:47:43 -06:00
Author
Owner
Copy Link
Copy Source

@thearyadev commented on GitHub (Jul 22, 2024):

I'm working on a solution for this right now.

I think dealing with the database bloat is important, but not directly related to this issue.
Even in the case that the database is not massive, there is a lot of excess data that is being loaded into memory, when all that is required is the title and id of the chat.

Pagination is also a valid solution, but a single query for chats is not very large. The data is fairly small, and the benefit gained from scroll-pagination in the sidebar is minimal in comparison to the complexity it adds (i tried experimenting with this, was not fun)

my proposed solution

    def get_chat_list_by_user_id(
        self,
        user_id: str,
        include_archived: bool = False,
        skip: int = 0,
        limit: int = 50,
    ) -> List[ChatTitleIdResponse]:
        with get_db() as db:
            query = db.query(Chat).filter_by(user_id=user_id)
            if not include_archived:
                query = query.filter_by(archived=False)
            all_chats = (
                query.order_by(Chat.updated_at.desc())
                .with_entities(Chat.id, Chat.title, Chat.updated_at, Chat.created_at) # Only get columns we actually need
                .all()
            )
            return [ChatTitleIdResponse.model_validate(
                {
                    "id": chat[0],
                    "title": chat[1],
                    "updated_at": chat[2],
                    "created_at": chat[3],
                }
            ) for chat in all_chats]

with_entities ensures the sql query only requests the data it needs, and not all of the chat data. I'm not sure where else this function is used, so i think it may be best to add another function specifically returning the ChatTitleIdResponse dataclass.

I can open a PR for this later today.

@thearyadev commented on GitHub (Jul 22, 2024): I'm working on a solution for this right now. I think dealing with the database bloat is important, but not directly related to this issue. Even in the case that the database is not massive, there is a lot of excess data that is being loaded into memory, when all that is required is the `title` and `id` of the chat. Pagination is also a valid solution, but a single query for chats is not very large. The data is fairly small, and the benefit gained from scroll-pagination in the sidebar is minimal in comparison to the complexity it adds (i tried experimenting with this, was not fun) # my proposed solution ```python def get_chat_list_by_user_id( self, user_id: str, include_archived: bool = False, skip: int = 0, limit: int = 50, ) -> List[ChatTitleIdResponse]: with get_db() as db: query = db.query(Chat).filter_by(user_id=user_id) if not include_archived: query = query.filter_by(archived=False) all_chats = ( query.order_by(Chat.updated_at.desc()) .with_entities(Chat.id, Chat.title, Chat.updated_at, Chat.created_at) # Only get columns we actually need .all() ) return [ChatTitleIdResponse.model_validate( { "id": chat[0], "title": chat[1], "updated_at": chat[2], "created_at": chat[3], } ) for chat in all_chats] ``` `with_entities` ensures the sql query only requests the data it needs, and not all of the chat data. I'm not sure where else this function is used, so i think it may be best to add another function specifically returning the `ChatTitleIdResponse` dataclass. I can open a PR for this later today.
GiteaMirror commented 2025-11-11 14:47:43 -06:00
Author
Owner
Copy Link
Copy Source

@tjbck commented on GitHub (Jul 24, 2024):

Related: https://github.com/open-webui/open-webui/issues/2078

@tjbck commented on GitHub (Jul 24, 2024): Related: https://github.com/open-webui/open-webui/issues/2078
GiteaMirror commented 2025-11-11 14:47:43 -06:00
Author
Owner
Copy Link
Copy Source

@Classic298 commented on GitHub (Aug 22, 2025):

Hey everyone, this PR might be interesting to you:

https://github.com/open-webui/open-webui/pull/16520
Testing wanted

@Classic298 commented on GitHub (Aug 22, 2025): Hey everyone, this PR might be interesting to you: https://github.com/open-webui/open-webui/pull/16520 Testing wanted
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
bug
confirmed
confirmed issue
core
documentation
enhancement
good first issue
help wanted
non-core
pull-request
Mirrored from GitHub Pull Request
 python
question
testing wanted
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
GiteaMirror ninjasurge (Kaehlen Rog)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/open-webui#1577