[PR #23758] [CLOSED] perf: optimize SQLite WAL connection pragmas #27357

New Issue

GiteaMirror · 2026-04-20T07:01:40-05:00

GiteaMirror commented

2026-04-20 07:01:40 -05:00

📋 Pull Request Information

Original PR: https://github.com/open-webui/open-webui/pull/23758
Author: @Algorithm5838
Created: 4/15/2026
Status: ❌ Closed

Base: dev ← Head: perf/sqlite-wal-pragmas

📝 Commits (1)

26469a8 perf: optimize SQLite WAL connection pragmas

📊 Changes

1 file changed (+19 additions, -19 deletions)

View changed files

📝 backend/open_webui/internal/db.py (+19 -19)

📄 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.

This is to ensure large feature PRs are discussed with the community first, before starting work on it. If the community does not want this feature or it is not relevant for Open WebUI as a project, it can be identified in the discussion before working on the feature and submitting the PR.

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 following:
- BREAKING CHANGE: Significant changes that may affect compatibility
- build: Changes that affect the build system or external dependencies
- ci: Changes to our continuous integration processes or workflows
- chore: Refactor, cleanup, or other non-functional code changes
- docs: Documentation update or addition
- feat: Introduces a new feature or enhancement to the codebase
- fix: Bug fix or error correction
- i18n: Internationalization or localization changes
- perf: Performance improvement
- refactor: Code restructuring for better maintainability, readability, or scalability
- style: Changes that do not affect the meaning of the code (white space, formatting, missing semi-colons, etc.)
- test: Adding missing tests or correcting existing tests
- WIP: Work in progress, a temporary label for incomplete or ongoing work

Changelog Entry

Description

SQLite's default connection settings are tuned for embedded use, not a web server. When WAL is enabled, only journal_mode was being set; other impactful PRAGMAs were left at their conservative defaults. This meant unnecessary fsyncs on every write (synchronous=FULL), no retry on lock contention (causing spurious database is locked errors under concurrent load), and no query planner stats being maintained.

This PR sets the right PRAGMAs on every connection.

Also fixes a bug: the async engine had no else branch, so DATABASE_ENABLE_SQLITE_WAL=false was silently ignored for async connections.

Added

[List any new features, functionalities, or additions]

Changed

Merged sync/async on_connect handlers into shared _sqlite_on_connect, registered on both engines; cursor cleanup is exception-safe via contextlib.closing().
When WAL is enabled, sets per connection:
- synchronous=NORMAL (removes per-write fsync; safe on app crash, may lose last transactions on power loss)
- journal_size_limit=67108864 (64 MB WAL cap)
- cache_size=-32768 (32 MB page cache, per connection)
- temp_store=MEMORY
- busy_timeout=5000 (5 s retry on lock contention)

Deprecated

[List any deprecated functionality or features that have been removed]

Removed

[List any removed features, files, or functionalities]

Fixed

Async engine now sets journal_mode=DELETE when WAL is disabled, matching sync engine behavior.

Security

[List any new or updated security-related changes, including vulnerability fixes]

Breaking Changes

BREAKING CHANGE: [List any breaking changes affecting compatibility or functionality]

Additional Information

Manually tested on a running container. All PRAGMAs apply correctly, integrity_check passes, and no lock errors appear in logs.

Screenshots or Videos

[Attach any relevant screenshots or videos demonstrating the changes]

Contributor License Agreement

By submitting this pull request, I confirm that I have read and fully agree to the Contributor License Agreement (CLA), 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.

_{🔄 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/23758 **Author:** [@Algorithm5838](https://github.com/Algorithm5838) **Created:** 4/15/2026 **Status:** ❌ Closed **Base:** `dev` ← **Head:** `perf/sqlite-wal-pragmas` --- ### 📝 Commits (1) - [`26469a8`](https://github.com/open-webui/open-webui/commit/26469a8f229059609a91e8f30504d3f3dfa28410) perf: optimize SQLite WAL connection pragmas ### 📊 Changes **1 file changed** (+19 additions, -19 deletions) <details> <summary>View changed files</summary> 📝 `backend/open_webui/internal/db.py` (+19 -19) </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. This is to ensure large feature PRs are discussed with the community first, before starting work on it. If the community does not want this feature or it is not relevant for Open WebUI as a project, it can be identified in the discussion before working on the feature and submitting the PR. **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 following: - **BREAKING CHANGE**: Significant changes that may affect compatibility - **build**: Changes that affect the build system or external dependencies - **ci**: Changes to our continuous integration processes or workflows - **chore**: Refactor, cleanup, or other non-functional code changes - **docs**: Documentation update or addition - **feat**: Introduces a new feature or enhancement to the codebase - **fix**: Bug fix or error correction - **i18n**: Internationalization or localization changes - **perf**: Performance improvement - **refactor**: Code restructuring for better maintainability, readability, or scalability - **style**: Changes that do not affect the meaning of the code (white space, formatting, missing semi-colons, etc.) - **test**: Adding missing tests or correcting existing tests - **WIP**: Work in progress, a temporary label for incomplete or ongoing work # Changelog Entry ### Description SQLite's default connection settings are tuned for embedded use, not a web server. When WAL is enabled, only `journal_mode` was being set; other impactful PRAGMAs were left at their conservative defaults. This meant unnecessary fsyncs on every write (`synchronous=FULL`), no retry on lock contention (causing spurious `database is locked` errors under concurrent load), and no query planner stats being maintained. This PR sets the right PRAGMAs on every connection. Also fixes a bug: the async engine had no `else` branch, so `DATABASE_ENABLE_SQLITE_WAL=false` was silently ignored for async connections. ### Added - [List any new features, functionalities, or additions] ### Changed - Merged sync/async `on_connect` handlers into shared `_sqlite_on_connect`, registered on both engines; cursor cleanup is exception-safe via `contextlib.closing()`. - When WAL is enabled, sets per connection: - `synchronous=NORMAL` (removes per-write fsync; safe on app crash, may lose last transactions on power loss) - `journal_size_limit=67108864` (64 MB WAL cap) - `cache_size=-32768` (32 MB page cache, per connection) - `temp_store=MEMORY` - `busy_timeout=5000` (5 s retry on lock contention) ### Deprecated - [List any deprecated functionality or features that have been removed] ### Removed - [List any removed features, files, or functionalities] ### Fixed - Async engine now sets `journal_mode=DELETE` when WAL is disabled, matching sync engine behavior. ### Security - [List any new or updated security-related changes, including vulnerability fixes] ### Breaking Changes - **BREAKING CHANGE**: [List any breaking changes affecting compatibility or functionality] --- ### Additional Information - Manually tested on a running container. All PRAGMAs apply correctly, `integrity_check` passes, and no lock errors appear in logs. ### Screenshots or Videos - [Attach any relevant screenshots or videos demonstrating the changes] ### Contributor License Agreement  - [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-20 07:01:40 -05:00

GiteaMirror closed this issue

2026-04-20 07:01:41 -05:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/open-webui#27357