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
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>
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
📋 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)
de6cb7aperf: 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:
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 usersget_groups_by_member_ids()- batch load full group objects for multiple usersget_member_counts_by_group_ids()- batch load member counts for multiple groupsget_groups()andsearch_groups()to use batch member countingSCIM Endpoints (
backend/open_webui/routers/scim.py)user_to_scim()to accept optional pre-fetched groupsusers_to_scim()batch function to convert multiple users efficientlygroup_to_scim()to accept optional pre-fetched membersgroups_to_scim()batch function to convert multiple groups efficiently/Usersand/Groupslist endpoints to use batch functionsAdmin Users Endpoint (
backend/open_webui/routers/users.py)get_users()to use batch group ID fetchingPerformance Impact
/Users(2500 users)/Groups(20 groups, 100 members)/users(30/page)/groupslist (50 groups)Testing
Backward Compatibility
🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.