[PR #16584] [CLOSED] feat: PostgreSQL JSONB Support and Optimization for Chat Operations #79050

New Issue

GiteaMirror · 2026-05-13T12:48:49-05:00

GiteaMirror commented

2026-05-13 12:48:49 -05:00

📋 Pull Request Information

Original PR: https://github.com/open-webui/open-webui/pull/16584
Author: @PVBLIC-F
Created: 8/13/2025
Status: ❌ Closed

Base: main ← Head: feat/jsonb-detection-support-2025-08-12

📝 Commits (10+)

c09a4a2 feat: add dynamic JSONB detection and optimization support
97b9e0e refactor: enhance JSONB detection with enterprise-grade optimizations
0a6726e feat: enhance JSONB detection and database optimization
241833c feat: refactor JSONB auto-detection with thread safety and migration
1362eb6 style: format files with Black
0fe2356 docs: add comprehensive professional comments to chats.py
c971f43 prod: enhance production readiness with improved logging and validation
50a7180 resolve: merge conflict in chats.py - combine main branch indexes with JSONB features
fe4b137 Merge feat/jsonb-detection-support-2025-08-12: Add JSONB optimization and thread-safe database operations
72d36c4 fix: correct JSONB indexes migration syntax

📊 Changes

2 files changed (+968 additions, -62 deletions)

View changed files

➕ backend/open_webui/migrations/versions/544ba9a2b077_add_jsonb_indexes.py (+157 -0)
📝 backend/open_webui/models/chats.py (+811 -62)

📄 Description

Pull Request Checklist

Note to first-time contributors: Please open a discussion post in Discussions and describe your changes before submitting a pull request.

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

Target branch: Please verify that the pull request targets the dev branch.
Description: Provide a concise description of the changes made in this pull request.
Changelog: Ensure a changelog entry following the format of Keep a Changelog is added at the bottom of the PR description.
Documentation: Have you updated relevant documentation Open WebUI Docs, or other documentation sources?
Dependencies: Are there any new dependencies? Have you updated the dependency versions in the documentation?
Testing: Have you written and run sufficient tests to validate the changes?
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?
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

This PR introduces comprehensive PostgreSQL JSONB support to the chat system, providing significant performance improvements for JSON operations while maintaining backward compatibility with existing SQLite and standard JSON implementations.

Added

1. Intelligent JSONB Detection and Adaptation
  Dynamic Column Type Detection: Automatically detects whether chat and meta columns use JSONB or JSON types
  Runtime Capability Caching: Caches database capabilities per connection to avoid repeated schema queries
  Graceful Fallback: Seamlessly falls back to standard JSON operations when JSONB is not available
1. Optimized Query Performance
  JSONB Containment Operators: Uses PostgreSQL's native @> operator for efficient tag filtering with AND operations
  Function Selection: Automatically chooses between jsonb_array_elements and json_array_elements based on actual column types
  Optimized Tag Queries: Implements specialized query builders for single and multi-tag operations
1. Advanced Indexing Support
  GIN Index Creation: Automatically creates optimized GIN indexes for JSONB columns:
  idx_chat_meta_tags_gin - For tag-based queries
  idx_chat_meta_gin - For general meta field queries
  idx_chat_messages_gin - For message content searches
  Smart Index Management: Only creates indexes on actual JSONB columns, skips creation on non-PostgreSQL databases
1. Enhanced Search Capabilities
  Efficient Tag Filtering: Supports complex tag operations (tag:name, tag:none) with optimal query patterns
  Content Search Optimization: Uses appropriate JSON functions based on column types for message content searches
  Multi-criteria Search: Handles combined searches across title, content, tags, folders, and metadata
1. Complete Migration Script
  -- Open Web UI PostgreSQL JSONB Migration
  -- Run this script to convert existing JSON columns to JSONB for better performance

BEGIN;

-- Step 1: Convert chat column to JSONB
ALTER TABLE chat ALTER COLUMN chat TYPE JSONB USING chat::JSONB;

-- Step 2: Convert meta column to JSONB
ALTER TABLE chat ALTER COLUMN meta TYPE JSONB USING meta::JSONB;

-- Step 3: Create optimized GIN indexes
CREATE INDEX IF NOT EXISTS idx_chat_meta_tags_gin ON chat USING GIN ((meta->'tags'));
CREATE INDEX IF NOT EXISTS idx_chat_meta_gin ON chat USING GIN (meta);
CREATE INDEX IF NOT EXISTS idx_chat_messages_gin ON chat USING GIN ((chat->'history'->'messages'));

-- Step 4: Update statistics
ANALYZE chat;

COMMIT;

-- Verify the changes
SELECT
column_name,
data_type
FROM information_schema.columns
WHERE table_name = 'chat'
AND column_name IN ('chat', 'meta');

Changed

[List any changes, updates, refactorings, or optimizations]

Deprecated

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

Removed

[List any removed features, files, or functionalities]

Fixed

[List any fixes, corrections, or bug fixes]

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

[Insert any additional context, notes, or explanations for the changes]
- [Reference any related issues, commits, or other relevant information]

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.

_{🔄 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/16584 **Author:** [@PVBLIC-F](https://github.com/PVBLIC-F) **Created:** 8/13/2025 **Status:** ❌ Closed **Base:** `main` ← **Head:** `feat/jsonb-detection-support-2025-08-12` --- ### 📝 Commits (10+) - [`c09a4a2`](https://github.com/open-webui/open-webui/commit/c09a4a2782cc91da6748eb1832940e061dae5944) feat: add dynamic JSONB detection and optimization support - [`97b9e0e`](https://github.com/open-webui/open-webui/commit/97b9e0ee0d3adc362c5fa7c7b219dac2bca97bae) refactor: enhance JSONB detection with enterprise-grade optimizations - [`0a6726e`](https://github.com/open-webui/open-webui/commit/0a6726e325e2618a0c23258e59465370bedfa4cb) feat: enhance JSONB detection and database optimization - [`241833c`](https://github.com/open-webui/open-webui/commit/241833c36a9938c8d060005665bf30cff859bf32) feat: refactor JSONB auto-detection with thread safety and migration - [`1362eb6`](https://github.com/open-webui/open-webui/commit/1362eb64370b820888050887e287a499180f915d) style: format files with Black - [`0fe2356`](https://github.com/open-webui/open-webui/commit/0fe235677b2712d56853358fa9cacb885b1e1c2d) docs: add comprehensive professional comments to chats.py - [`c971f43`](https://github.com/open-webui/open-webui/commit/c971f437cdaf2a670846422a88afb908ee66549f) prod: enhance production readiness with improved logging and validation - [`50a7180`](https://github.com/open-webui/open-webui/commit/50a718033df2264a3d38a00948a6dd02e40abb87) resolve: merge conflict in chats.py - combine main branch indexes with JSONB features - [`fe4b137`](https://github.com/open-webui/open-webui/commit/fe4b1377f491ac7e578df5a8d150adddcfb8c891) Merge feat/jsonb-detection-support-2025-08-12: Add JSONB optimization and thread-safe database operations - [`72d36c4`](https://github.com/open-webui/open-webui/commit/72d36c4c9e6dc945677204803c2b5f562c221c68) fix: correct JSONB indexes migration syntax ### 📊 Changes **2 files changed** (+968 additions, -62 deletions) <details> <summary>View changed files</summary> ➕ `backend/open_webui/migrations/versions/544ba9a2b077_add_jsonb_indexes.py` (+157 -0) 📝 `backend/open_webui/models/chats.py` (+811 -62) </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) and describe your changes before submitting a pull request. **Before submitting, make sure you've checked the following:** - [ ] **Target branch:** Please verify that the pull request targets the `dev` branch. - [ ] **Description:** Provide a concise description of the changes made in this pull request. - [ ] **Changelog:** Ensure a changelog entry following the format of [Keep a Changelog](https://keepachangelog.com/) is added at the bottom of the PR description. - [ ] **Documentation:** Have you updated relevant documentation [Open WebUI Docs](https://github.com/open-webui/docs), or other documentation sources? - [ ] **Dependencies:** Are there any new dependencies? Have you updated the dependency versions in the documentation? - [ ] **Testing:** Have you written and run sufficient tests to validate the changes? - [ ] **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? - [ ] **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 - This PR introduces comprehensive PostgreSQL JSONB support to the chat system, providing significant performance improvements for JSON operations while maintaining backward compatibility with existing SQLite and standard JSON implementations. ### Added - 1. Intelligent JSONB Detection and Adaptation Dynamic Column Type Detection: Automatically detects whether chat and meta columns use JSONB or JSON types Runtime Capability Caching: Caches database capabilities per connection to avoid repeated schema queries Graceful Fallback: Seamlessly falls back to standard JSON operations when JSONB is not available - 2. Optimized Query Performance JSONB Containment Operators: Uses PostgreSQL's native @> operator for efficient tag filtering with AND operations Function Selection: Automatically chooses between jsonb_array_elements and json_array_elements based on actual column types Optimized Tag Queries: Implements specialized query builders for single and multi-tag operations - 3. Advanced Indexing Support GIN Index Creation: Automatically creates optimized GIN indexes for JSONB columns: idx_chat_meta_tags_gin - For tag-based queries idx_chat_meta_gin - For general meta field queries idx_chat_messages_gin - For message content searches Smart Index Management: Only creates indexes on actual JSONB columns, skips creation on non-PostgreSQL databases - 4. Enhanced Search Capabilities Efficient Tag Filtering: Supports complex tag operations (tag:name, tag:none) with optimal query patterns Content Search Optimization: Uses appropriate JSON functions based on column types for message content searches Multi-criteria Search: Handles combined searches across title, content, tags, folders, and metadata - 5. Complete Migration Script -- Open Web UI PostgreSQL JSONB Migration -- Run this script to convert existing JSON columns to JSONB for better performance BEGIN; -- Step 1: Convert chat column to JSONB ALTER TABLE chat ALTER COLUMN chat TYPE JSONB USING chat::JSONB; -- Step 2: Convert meta column to JSONB ALTER TABLE chat ALTER COLUMN meta TYPE JSONB USING meta::JSONB; -- Step 3: Create optimized GIN indexes CREATE INDEX IF NOT EXISTS idx_chat_meta_tags_gin ON chat USING GIN ((meta->'tags')); CREATE INDEX IF NOT EXISTS idx_chat_meta_gin ON chat USING GIN (meta); CREATE INDEX IF NOT EXISTS idx_chat_messages_gin ON chat USING GIN ((chat->'history'->'messages')); -- Step 4: Update statistics ANALYZE chat; COMMIT; -- Verify the changes SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'chat' AND column_name IN ('chat', 'meta'); ### Changed - [List any changes, updates, refactorings, or optimizations] ### Deprecated - [List any deprecated functionality or features that have been removed] ### Removed - [List any removed features, files, or functionalities] ### Fixed - [List any fixes, corrections, or bug fixes] ### 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 - [Insert any additional context, notes, or explanations for the changes] - [Reference any related issues, commits, or other relevant information] ### 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)](/CONTRIBUTOR_LICENSE_AGREEMENT), and I am providing my contributions under its terms. --- <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-05-13 12:48:49 -05:00

GiteaMirror closed this issue

2026-05-13 12:48:52 -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#79050