github-starred/open-webui
2
0
Fork 0
mirror of https://github.com/open-webui/open-webui.git synced 2026-05-06 02:48:13 -05:00
Code Issues 1.1k Packages Projects Releases 126 Wiki Activity

[PR #24247] perf: Add SQLite index for default chat sidebar pagination #50561

New Issue
Open
opened 2026-04-30 03:20:39 -05:00 by GiteaMirror · 0 comments
GiteaMirror commented 2026-04-30 03:20:39 -05:00
Owner
Copy Link

📋 Pull Request Information

Original PR: https://github.com/open-webui/open-webui/pull/24247
Author: @PhilosophiMoonbeam
Created: 4/29/2026
Status: 🔄 Open

Base: dev ← Head: optimize-sqlite-chat-sidebar-index

📝 Commits (10+)

📊 Changes

2 files changed (+39 additions, -0 deletions)

View changed files

➕ backend/open_webui/migrations/versions/6f0d1234abcd_add_chat_sidebar_default_index.py (+30 -0)
📝 backend/open_webui/models/chats.py (+9 -0)

📄 Description

Pull Request Checklist

Note to first-time contributors: Please open a discussion post in Discussions to discuss your idea/fix with the community before creating a pull request, and describe your changes before submitting a pull request.

Discussion created after the PR was opened: https://github.com/open-webui/open-webui/discussions/24248

Before submitting, make sure you've checked the following:

  • Target branch: Verify that the pull request targets the dev branch. PRs targeting main will be immediately closed.
  • Description: Provide a concise description of the changes made in this pull request down below.
  • Changelog: Ensure a changelog entry following the format of Keep a Changelog is added at the bottom of the PR description.
  • Documentation: Add docs in Open WebUI Docs Repository. Document user-facing behavior, environment variables, public APIs/interfaces, or deployment steps.
  • Dependencies: Are there any new or upgraded dependencies? If so, explain why, update the changelog/docs, and include any compatibility notes. Actually run the code/function that uses updated library to ensure it doesn't crash.
  • Testing: Perform manual tests to verify the implemented fix/feature works as intended AND does not break any other functionality. Include reproducible steps to demonstrate the issue before the fix. Test edge cases (URL encoding, HTML entities, types). Take this as an opportunity to make screenshots of the feature/fix and include them in the PR description.
  • Agentic AI Code: Confirm this Pull Request is not written by any AI Agent or has at least gone through additional human review AND manual testing. If any AI Agent is the co-author of this PR, it may lead to immediate closure of the PR.
  • Code review: Have you performed a self-review of your code, addressing any coding standard issues and ensuring adherence to the project's coding standards?
  • Design & Architecture: Prefer smart defaults over adding new settings; use local state for ephemeral UI logic. Open a Discussion for major architectural or UX changes.
  • Git Hygiene: Keep PRs atomic (one logical change). Clean up commits and rebase on dev to ensure no unrelated commits (e.g. from main) are included. Push updates to the existing PR branch instead of closing and reopening.
  • Title Prefix: To clearly categorize this pull request, prefix the pull request title using one of the listed prefixes.

Changelog Entry

Description

  • Adds a SQLite partial index for the default chat sidebar pagination query so SQLite can satisfy ORDER BY updated_at DESC, id without building a temporary sort for the common non-folder, non-archived, non-pinned chat list.

Added

  • Added chat_sidebar_default_idx on (user_id, updated_at DESC, id) with a SQLite partial predicate matching the default chat sidebar query.
  • Added an Alembic migration for the new index.

Changed

  • Updated Chat.__table_args__ metadata to include the new SQLite index.

Deprecated

  • None.

Removed

  • None.

Fixed

  • Improves SQLite chat history/sidebar pagination performance on larger local databases where the previous query plan filtered by an index but still used a temporary B-tree for sorting.

Security

  • None.

Breaking Changes

  • BREAKING CHANGE: None.

Additional Information

  • This was observed on a low-resource VPS with 2 vCPU and 4 GB RAM.
  • The affected deployment used SQLite with a local Docker volume, WAL enabled, current Alembic migrations, and a roughly 6.2 GB database with about 4.1k chats.
  • Before this index, chat history loading and subsequent pagination could take 1+ minute.
  • After applying the same index locally, the affected deployment loaded chat history in under 5 seconds.
  • The pre-change SQLite plan used an existing filter index but still reported USE TEMP B-TREE FOR ORDER BY for the default sidebar query.
  • The post-change plan uses chat_sidebar_default_idx directly for the default query.
  • This PR was prepared by Codex acting as an agent for the affected deployment operator, then manually validated against the affected deployment. The operator confirmed the improvement after deployment.

Validation performed:

CREATE INDEX chat_sidebar_default_idx ON chat (user_id, updated_at DESC, id) WHERE folder_id IS NULL AND archived = 0 AND (pinned = 0 OR pinned IS NULL)
  • Verified the migration compiles to the expected SQLite DDL above.
  • Verified SQLite EXPLAIN QUERY PLAN uses chat_sidebar_default_idx for the default sidebar query without a temp sort.
  • Ran Python syntax checks for the touched files.
  • Verified the same index on the affected production-like SQLite database reduced observed chat history load time from 1+ minute to under 5 seconds.

Related Discussion: https://github.com/open-webui/open-webui/discussions/24248

Screenshots or Videos

  • Not applicable; this is a backend SQLite query-planning/index change. Performance was validated with SQLite query plans and observed application load time.

Contributor License Agreement

Note

Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in.

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/open-webui/open-webui/pull/24247 **Author:** [@PhilosophiMoonbeam](https://github.com/PhilosophiMoonbeam) **Created:** 4/29/2026 **Status:** 🔄 Open **Base:** `dev` ← **Head:** `optimize-sqlite-chat-sidebar-index` --- ### 📝 Commits (10+) - [`fe6783c`](https://github.com/open-webui/open-webui/commit/fe6783c16699911c7be17392596d579333fb110c) Merge pull request #19030 from open-webui/dev - [`fc05e0a`](https://github.com/open-webui/open-webui/commit/fc05e0a6c5d39da60b603b4d520f800d6e36f748) Merge pull request #19405 from open-webui/dev - [`e3faec6`](https://github.com/open-webui/open-webui/commit/e3faec62c58e3a83d89aa3df539feacefa125e0c) Merge pull request #19416 from open-webui/dev - [`9899293`](https://github.com/open-webui/open-webui/commit/9899293f050ad50ae12024cbebee7e018acd851e) Merge pull request #19448 from open-webui/dev - [`140605e`](https://github.com/open-webui/open-webui/commit/140605e660b8186a7d5c79fb3be6ffb147a2f498) Merge pull request #19462 from open-webui/dev - [`6f1486f`](https://github.com/open-webui/open-webui/commit/6f1486ffd0cb288d0e21f41845361924e0d742b3) Merge pull request #19466 from open-webui/dev - [`d95f533`](https://github.com/open-webui/open-webui/commit/d95f533214e3fe5beb5e41ec1f349940bc4c7043) Merge pull request #19729 from open-webui/dev - [`a727153`](https://github.com/open-webui/open-webui/commit/a7271532f8a38da46785afcaa7e65f9a45e7d753) 0.6.43 (#20093) - [`6adde20`](https://github.com/open-webui/open-webui/commit/6adde203cd292a9e3af9c64a2ae36b603fed096a) Merge pull request #20394 from open-webui/dev - [`f9b0534`](https://github.com/open-webui/open-webui/commit/f9b0534e0c442631d1cb7205169588b9b6204179) Merge pull request #20522 from open-webui/dev ### 📊 Changes **2 files changed** (+39 additions, -0 deletions) <details> <summary>View changed files</summary> ➕ `backend/open_webui/migrations/versions/6f0d1234abcd_add_chat_sidebar_default_index.py` (+30 -0) 📝 `backend/open_webui/models/chats.py` (+9 -0) </details> ### 📄 Description # Pull Request Checklist ### Note to first-time contributors: Please open a discussion post in [Discussions](https://github.com/open-webui/open-webui/discussions) to discuss your idea/fix with the community before creating a pull request, and describe your changes before submitting a pull request. Discussion created after the PR was opened: https://github.com/open-webui/open-webui/discussions/24248 **Before submitting, make sure you've checked the following:** - [x] **Target branch:** Verify that the pull request targets the `dev` branch. **PRs targeting `main` will be immediately closed.** - [x] **Description:** Provide a concise description of the changes made in this pull request down below. - [x] **Changelog:** Ensure a changelog entry following the format of [Keep a Changelog](https://keepachangelog.com/) is added at the bottom of the PR description. - [x] **Documentation:** Add docs in [Open WebUI Docs Repository](https://github.com/open-webui/docs). Document user-facing behavior, environment variables, public APIs/interfaces, or deployment steps. - [x] **Dependencies:** Are there any new or upgraded dependencies? If so, explain why, update the changelog/docs, and include any compatibility notes. Actually run the code/function that uses updated library to ensure it doesn't crash. - [x] **Testing:** Perform manual tests to **verify the implemented fix/feature works as intended AND does not break any other functionality**. Include reproducible steps to demonstrate the issue before the fix. Test edge cases (URL encoding, HTML entities, types). Take this as an opportunity to **make screenshots of the feature/fix and include them in the PR description**. - [x] **Agentic AI Code:** Confirm this Pull Request is **not written by any AI Agent** or has at least **gone through additional human review AND manual testing**. If any AI Agent is the co-author of this PR, it may lead to immediate closure of the PR. - [x] **Code review:** Have you performed a self-review of your code, addressing any coding standard issues and ensuring adherence to the project's coding standards? - [x] **Design & Architecture:** Prefer smart defaults over adding new settings; use local state for ephemeral UI logic. Open a Discussion for major architectural or UX changes. - [x] **Git Hygiene:** Keep PRs atomic (one logical change). Clean up commits and rebase on `dev` to ensure no unrelated commits (e.g. from `main`) are included. Push updates to the existing PR branch instead of closing and reopening. - [x] **Title Prefix:** To clearly categorize this pull request, prefix the pull request title using one of the listed prefixes. # Changelog Entry ### Description - Adds a SQLite partial index for the default chat sidebar pagination query so SQLite can satisfy `ORDER BY updated_at DESC, id` without building a temporary sort for the common non-folder, non-archived, non-pinned chat list. ### Added - Added `chat_sidebar_default_idx` on `(user_id, updated_at DESC, id)` with a SQLite partial predicate matching the default chat sidebar query. - Added an Alembic migration for the new index. ### Changed - Updated `Chat.__table_args__` metadata to include the new SQLite index. ### Deprecated - None. ### Removed - None. ### Fixed - Improves SQLite chat history/sidebar pagination performance on larger local databases where the previous query plan filtered by an index but still used a temporary B-tree for sorting. ### Security - None. ### Breaking Changes - **BREAKING CHANGE**: None. --- ### Additional Information - This was observed on a low-resource VPS with 2 vCPU and 4 GB RAM. - The affected deployment used SQLite with a local Docker volume, WAL enabled, current Alembic migrations, and a roughly 6.2 GB database with about 4.1k chats. - Before this index, chat history loading and subsequent pagination could take 1+ minute. - After applying the same index locally, the affected deployment loaded chat history in under 5 seconds. - The pre-change SQLite plan used an existing filter index but still reported `USE TEMP B-TREE FOR ORDER BY` for the default sidebar query. - The post-change plan uses `chat_sidebar_default_idx` directly for the default query. - This PR was prepared by Codex acting as an agent for the affected deployment operator, then manually validated against the affected deployment. The operator confirmed the improvement after deployment. Validation performed: ```text CREATE INDEX chat_sidebar_default_idx ON chat (user_id, updated_at DESC, id) WHERE folder_id IS NULL AND archived = 0 AND (pinned = 0 OR pinned IS NULL) ``` - Verified the migration compiles to the expected SQLite DDL above. - Verified SQLite `EXPLAIN QUERY PLAN` uses `chat_sidebar_default_idx` for the default sidebar query without a temp sort. - Ran Python syntax checks for the touched files. - Verified the same index on the affected production-like SQLite database reduced observed chat history load time from 1+ minute to under 5 seconds. Related Discussion: https://github.com/open-webui/open-webui/discussions/24248 ### Screenshots or Videos - Not applicable; this is a backend SQLite query-planning/index change. Performance was validated with SQLite query plans and observed application load time. ### Contributor License Agreement <!-- 🚨 DO NOT DELETE THE TEXT BELOW 🚨 Keep the "Contributor License Agreement" confirmation text intact. Deleting it will trigger the CLA-Bot to INVALIDATE your PR. Your PR will NOT be reviewed or merged until you check the box below confirming that you have read and agree to the terms of the CLA. --> - [x] By submitting this pull request, I confirm that I have read and fully agree to the [Contributor License Agreement (CLA)](https://github.com/open-webui/open-webui/blob/main/CONTRIBUTOR_LICENSE_AGREEMENT), and I am providing my contributions under its terms. > [!NOTE] > Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
GiteaMirror added the pull-request label 2026-04-30 03:20:39 -05:00
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 Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/open-webui#50561
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?