[PR #20115] [CLOSED] perf: fix N+1 query issues in user/group endpoints #41102

New Issue

GiteaMirror · 2026-04-25T13:25:18-05:00

GiteaMirror commented

2026-04-25 13:25:18 -05:00

📋 Pull Request Information

Original PR: https://github.com/open-webui/open-webui/pull/20115
Author: @broskees
Created: 12/22/2025
Status: ❌ Closed

Base: dev ← Head: fix/n1-query-group-user

📝 Commits (1)

de6cb7a perf: fix N+1 query issues in user/group endpoints

📊 Changes

3 files changed (+177 additions, -32 deletions)

View changed files

📝 backend/open_webui/models/groups.py (+82 -6)
📝 backend/open_webui/routers/scim.py (+90 -23)
📝 backend/open_webui/routers/users.py (+5 -3)

📄 Description

Summary

Fixes critical N+1 database query issues when listing users and groups. This dramatically improves performance for large deployments with many users and groups.

Problem

The application was making individual database queries for each user/group when loading lists:

When listing users, it queried each user's groups separately (N+1 queries)
When listing groups, it counted members for each group separately (N+1 queries)
SCIM endpoints were particularly affected due to full syncs

For a deployment with 2500 users, this meant 2500+ database queries instead of just 2.

Solution

Added batch query methods that fetch all related data in a single query:

Models (`backend/open_webui/models/groups.py`)

get_group_ids_by_member_ids() - batch load group IDs for multiple users
get_groups_by_member_ids() - batch load full group objects for multiple users
get_member_counts_by_group_ids() - batch load member counts for multiple groups
Updated get_groups() and search_groups() to use batch member counting

SCIM Endpoints (`backend/open_webui/routers/scim.py`)

Updated user_to_scim() to accept optional pre-fetched groups
Added users_to_scim() batch function to convert multiple users efficiently
Updated group_to_scim() to accept optional pre-fetched members
Added groups_to_scim() batch function to convert multiple groups efficiently
Updated SCIM /Users and /Groups list endpoints to use batch functions

Admin Users Endpoint (`backend/open_webui/routers/users.py`)

Updated get_users() to use batch group ID fetching

Performance Impact

Endpoint	Before	After	Improvement
SCIM `/Users` (2500 users)	~2501 queries	2 queries	1250x faster
SCIM `/Groups` (20 groups, 100 members)	~2001 queries	3 queries	667x faster
Admin `/users` (30/page)	31 queries	2 queries	15x faster
`/groups` list (50 groups)	51 queries	2 queries	25x faster

Testing

✅ All modified files compile successfully
✅ Validation script confirms all batch methods work correctly
✅ Tested with Playwright on live instance - all endpoints functional
✅ Created groups, managed users, verified UI renders correctly
✅ Backward compatible - existing single-item methods unchanged

Backward Compatibility

Existing single-item query methods remain unchanged
New batch methods gracefully handle empty lists
Optional parameters added to conversion functions maintain compatibility
No breaking changes to public APIs

_{🔄 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/20115 **Author:** [@broskees](https://github.com/broskees) **Created:** 12/22/2025 **Status:** ❌ Closed **Base:** `dev` ← **Head:** `fix/n1-query-group-user` --- ### 📝 Commits (1) - [`de6cb7a`](https://github.com/open-webui/open-webui/commit/de6cb7aaa20aaca2979e9d303b03fc53e5c6d94f) perf: fix N+1 query issues in user/group endpoints ### 📊 Changes **3 files changed** (+177 additions, -32 deletions) <details> <summary>View changed files</summary> 📝 `backend/open_webui/models/groups.py` (+82 -6) 📝 `backend/open_webui/routers/scim.py` (+90 -23) 📝 `backend/open_webui/routers/users.py` (+5 -3) </details> ### 📄 Description ## Summary Fixes critical N+1 database query issues when listing users and groups. This dramatically improves performance for large deployments with many users and groups. ## Problem The application was making individual database queries for each user/group when loading lists: - When listing users, it queried each user's groups separately (N+1 queries) - When listing groups, it counted members for each group separately (N+1 queries) - SCIM endpoints were particularly affected due to full syncs For a deployment with 2500 users, this meant **2500+ database queries** instead of just 2. ## Solution Added batch query methods that fetch all related data in a single query: ### Models (`backend/open_webui/models/groups.py`) - `get_group_ids_by_member_ids()` - batch load group IDs for multiple users - `get_groups_by_member_ids()` - batch load full group objects for multiple users - `get_member_counts_by_group_ids()` - batch load member counts for multiple groups - Updated `get_groups()` and `search_groups()` to use batch member counting ### SCIM Endpoints (`backend/open_webui/routers/scim.py`) - Updated `user_to_scim()` to accept optional pre-fetched groups - Added `users_to_scim()` batch function to convert multiple users efficiently - Updated `group_to_scim()` to accept optional pre-fetched members - Added `groups_to_scim()` batch function to convert multiple groups efficiently - Updated SCIM `/Users` and `/Groups` list endpoints to use batch functions ### Admin Users Endpoint (`backend/open_webui/routers/users.py`) - Updated `get_users()` to use batch group ID fetching ## Performance Impact | Endpoint | Before | After | Improvement | |----------|--------|-------|-------------| | SCIM `/Users` (2500 users) | ~2501 queries | 2 queries | **1250x faster** | | SCIM `/Groups` (20 groups, 100 members) | ~2001 queries | 3 queries | **667x faster** | | Admin `/users` (30/page) | 31 queries | 2 queries | **15x faster** | | `/groups` list (50 groups) | 51 queries | 2 queries | **25x faster** | ## Testing - ✅ All modified files compile successfully - ✅ Validation script confirms all batch methods work correctly - ✅ Tested with Playwright on live instance - all endpoints functional - ✅ Created groups, managed users, verified UI renders correctly - ✅ Backward compatible - existing single-item methods unchanged ## Backward Compatibility - Existing single-item query methods remain unchanged - New batch methods gracefully handle empty lists - Optional parameters added to conversion functions maintain compatibility - No breaking changes to public APIs --- <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-25 13:25:18 -05:00

GiteaMirror closed this issue

2026-04-25 13:25:19 -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#41102

[PR #20115] [CLOSED] perf: fix N+1 query issues in user/group endpoints #41102