[GH-ISSUE #23793] feat: cache get_user_by_id — 25% of all DB queries with zero caching #107069

New Issue

GiteaMirror · 2026-05-18T05:36:35-05:00

GiteaMirror commented

2026-05-18 05:36:35 -05:00

Originally created by @ashm-dev on GitHub (Apr 16, 2026).
Original GitHub issue: https://github.com/open-webui/open-webui/issues/23793

Check Existing Issues

I have searched for all existing open AND closed issues and discussions for similar requests. I have found none that is comparable to my request.

Verify Feature Scope

I have read through and understood the scope definition for feature requests in the Issues section. I believe my feature request meets the definition and belongs in the Issues section instead of the Discussions.

Problem Description

Users.get_user_by_id() generates a full SELECT of all 21 columns from the user table on every authenticated request. There is no caching at any level. This single query accounts for ~25% of all database queries by count and is the heaviest by cumulative time.

SELECT user.id, user.email, user.username, user.role, user.name,
  user.profile_image_url, user.profile_banner_image_url, user.bio,
  user.gender, user.date_of_birth, user.timezone, user.presence_state,
  user.status_emoji, user.status_message, user.status_expires_at,
  user.info, user.settings, user.oauth, user.scim,
  user.last_active_at, user.updated_at, user.created_at
FROM user WHERE user.id = ? LIMIT 1 OFFSET 0

Why it's so frequent:

Auth dependency (utils/auth.py:356): get_current_user() calls get_user_by_id() on every request. 372 endpoints across 28 router files depend on it via Depends(get_current_user|get_verified_user|get_admin_user).
Double-fetch in handlers: ~15 endpoints receive user from auth dependency, then call get_user_by_id(user.id) again (e.g. routers/users.py:279,342,369,387,406).
WebSocket events (socket/main.py:354,382,429,451): Each event re-parses JWT and re-fetches user from DB instead of using the already-populated SESSION_POOL.
N+1 in message/channel listing (models/messages.py:220, routers/channels.py:931,1190,1274): Fetches user per message individually — 50 messages = 50 user SELECTs.
Tools/Functions (models/tools.py:238,256, models/functions.py:351,369, tools/builtin.py:476,2534,2610,2685,2745): Each load/execution re-fetches user.

Current state:

DB backend is configurable: SQLite (default), PostgreSQL, SQLCipher — via DATABASE_URL
Redis is optional — REDIS_URL env var, available as app.state.redis (can be None)
Redis already used for: token revocation, websocket pub/sub, tool server cache, task management
Multiple workers possible (gunicorn/uvicorn) — in-process state is not shared across workers

Desired Solution you'd like

Tier 1 — no new dependencies, works on any DB/deploy config:

Request-scoped cache: Store user on request.state after first fetch in get_current_user(). Eliminates all double-fetches within the same HTTP request. Zero cost.
In-process TTL cache for get_user_by_id(): Simple dict with expiration (30-60s), keyed by user ID. Invalidate on update_user_* / delete_user_* within the same process. Per-worker — each worker maintains its own cache. Stale window = TTL duration, acceptable since id/role/email rarely change.
Column projection for auth path: get_current_user() only needs id, role, email, name. Use SQLAlchemy load_only() — skip JSON blobs (settings, oauth, scim, info). Full model loaded only when needed. DB-agnostic.
Batch user loading for message lists: Replace N individual get_user_by_id() calls with one WHERE user.id IN (...) for channels/threads. SQLAlchemy in_() works on all backends.
Use SESSION_POOL in socket handlers: user-join, join-channels, join-note — user data already in SESSION_POOL[sid] after connect. No DB round-trip needed.

Tier 2 — when Redis is available (`REDIS_URL` is set):

Redis-backed shared cache: When app.state.redis is not None, use Redis as shared user cache across workers. Same TTL. Invalidate on user mutation. Falls back to Tier 1 in-process cache when Redis unavailable. Infrastructure already exists (utils/redis.py, get_redis_client(), REDIS_KEY_PREFIX).

Expected impact

Optimization	Query reduction	Scope
Request-scoped cache	~15% of calls (double-fetches)	Per request
In-process TTL cache	~80-90% of remaining	Per worker
Column projection	Reduces per-query cost (skip JSON blobs)	All remaining queries
Batch loading	O(N) → O(1) for message/channel views	Channel pages
SESSION_POOL reuse	3 unnecessary DB calls per socket session	WebSocket
Redis shared cache (opt.)	Shared warm cache across workers	Multi-worker deploys

Alternatives Considered

Embedding user data in JWT: Would eliminate DB lookups entirely for auth, but makes token revocation harder and increases token size. Current revocation logic (is_valid_token) depends on checking Redis per-request anyway, so JWT-only auth doesn't fully eliminate the round-trip.
SQLAlchemy identity map / session-level caching: Doesn't help here because sessions are short-lived (get_async_db_context creates a new context per call by design — see comment at utils/auth.py:302-305).

Additional Context

No changes to JWT structure or DB schema required
Tier 1 requires no new dependencies
Redis remains optional — Tier 2 is an enhancement, not a requirement

Originally created by @ashm-dev on GitHub (Apr 16, 2026). Original GitHub issue: https://github.com/open-webui/open-webui/issues/23793 ### Check Existing Issues - [X] I have searched for all existing **open AND closed** issues and discussions for similar requests. I have found none that is comparable to my request. ### Verify Feature Scope - [X] I have read through and understood the scope definition for feature requests in the Issues section. I believe my feature request meets the definition and belongs in the Issues section instead of the Discussions. ### Problem Description `Users.get_user_by_id()` generates a full `SELECT` of all 21 columns from the `user` table on every authenticated request. There is no caching at any level. This single query accounts for ~25% of all database queries by count and is the heaviest by cumulative time. ```sql SELECT user.id, user.email, user.username, user.role, user.name, user.profile_image_url, user.profile_banner_image_url, user.bio, user.gender, user.date_of_birth, user.timezone, user.presence_state, user.status_emoji, user.status_message, user.status_expires_at, user.info, user.settings, user.oauth, user.scim, user.last_active_at, user.updated_at, user.created_at FROM user WHERE user.id = ? LIMIT 1 OFFSET 0 ``` **Why it's so frequent:** 1. **Auth dependency** (`utils/auth.py:356`): `get_current_user()` calls `get_user_by_id()` on every request. 372 endpoints across 28 router files depend on it via `Depends(get_current_user|get_verified_user|get_admin_user)`. 2. **Double-fetch in handlers**: ~15 endpoints receive `user` from auth dependency, then call `get_user_by_id(user.id)` again (e.g. `routers/users.py:279,342,369,387,406`). 3. **WebSocket events** (`socket/main.py:354,382,429,451`): Each event re-parses JWT and re-fetches user from DB instead of using the already-populated `SESSION_POOL`. 4. **N+1 in message/channel listing** (`models/messages.py:220`, `routers/channels.py:931,1190,1274`): Fetches user per message individually — 50 messages = 50 user SELECTs. 5. **Tools/Functions** (`models/tools.py:238,256`, `models/functions.py:351,369`, `tools/builtin.py:476,2534,2610,2685,2745`): Each load/execution re-fetches user. **Current state:** - DB backend is configurable: SQLite (default), PostgreSQL, SQLCipher — via `DATABASE_URL` - Redis is optional — `REDIS_URL` env var, available as `app.state.redis` (can be `None`) - Redis already used for: token revocation, websocket pub/sub, tool server cache, task management - Multiple workers possible (gunicorn/uvicorn) — in-process state is not shared across workers ### Desired Solution you'd like #### Tier 1 — no new dependencies, works on any DB/deploy config: 1. **Request-scoped cache**: Store user on `request.state` after first fetch in `get_current_user()`. Eliminates all double-fetches within the same HTTP request. Zero cost. 2. **In-process TTL cache** for `get_user_by_id()`: Simple dict with expiration (30-60s), keyed by user ID. Invalidate on `update_user_*` / `delete_user_*` within the same process. Per-worker — each worker maintains its own cache. Stale window = TTL duration, acceptable since `id`/`role`/`email` rarely change. 3. **Column projection for auth path**: `get_current_user()` only needs `id`, `role`, `email`, `name`. Use SQLAlchemy `load_only()` — skip JSON blobs (`settings`, `oauth`, `scim`, `info`). Full model loaded only when needed. DB-agnostic. 4. **Batch user loading for message lists**: Replace N individual `get_user_by_id()` calls with one `WHERE user.id IN (...)` for channels/threads. SQLAlchemy `in_()` works on all backends. 5. **Use `SESSION_POOL` in socket handlers**: `user-join`, `join-channels`, `join-note` — user data already in `SESSION_POOL[sid]` after `connect`. No DB round-trip needed. #### Tier 2 — when Redis is available (`REDIS_URL` is set): 6. **Redis-backed shared cache**: When `app.state.redis is not None`, use Redis as shared user cache across workers. Same TTL. Invalidate on user mutation. Falls back to Tier 1 in-process cache when Redis unavailable. Infrastructure already exists (`utils/redis.py`, `get_redis_client()`, `REDIS_KEY_PREFIX`). #### Expected impact | Optimization | Query reduction | Scope | |---|---|---| | Request-scoped cache | ~15% of calls (double-fetches) | Per request | | In-process TTL cache | ~80-90% of remaining | Per worker | | Column projection | Reduces per-query cost (skip JSON blobs) | All remaining queries | | Batch loading | O(N) → O(1) for message/channel views | Channel pages | | SESSION_POOL reuse | 3 unnecessary DB calls per socket session | WebSocket | | Redis shared cache (opt.) | Shared warm cache across workers | Multi-worker deploys | ### Alternatives Considered - **Embedding user data in JWT**: Would eliminate DB lookups entirely for auth, but makes token revocation harder and increases token size. Current revocation logic (`is_valid_token`) depends on checking Redis per-request anyway, so JWT-only auth doesn't fully eliminate the round-trip. - **SQLAlchemy identity map / session-level caching**: Doesn't help here because sessions are short-lived (`get_async_db_context` creates a new context per call by design — see comment at `utils/auth.py:302-305`). ### Additional Context - No changes to JWT structure or DB schema required - Tier 1 requires no new dependencies - Redis remains optional — Tier 2 is an enhancement, not a requirement

GiteaMirror closed this issue

2026-05-18 05:36:36 -05:00

GiteaMirror commented

2026-05-18 05:36:37 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

MySQL is not a supported database backend. Hallucination

@Classic298 commented on GitHub (Apr 16, 2026): MySQL is not a supported database backend. Hallucination

GiteaMirror commented

2026-05-18 05:36:38 -05:00

@ashm-dev commented on GitHub (Apr 16, 2026):

MySQL is not a supported database backend. Hallucination

Pardon, I deleted it

@ashm-dev commented on GitHub (Apr 16, 2026): > MySQL is not a supported database backend. Hallucination Pardon, I deleted it

GiteaMirror commented

2026-05-18 05:36:38 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

We won't be caching get_user_by_id at the database layer. A few reasons:

Consistency trumps performance for this row. The user row is the source of truth for role and is consulted on every authenticated request. A 30–60s TTL means role changes (user -> pending, pending -> user), bans (pending, user deletion), and other permission mutations would take up to a minute to propagate with 60s TTL. That's a security regression. Demoting an admin or revoking access needs to be effective immediately, not 1 minute later. The same applies to token revocation logic, which must not be short-circuited by a stale user cache.

Multi-worker invalidation is a real bug surface, not a handwave. With uvicorn running N workers, an in-process TTL cache means a mutation in worker A does not invalidate workers B..N. You end up with per-worker stale windows that are effectively non-deterministic from the client's perspective ("sometimes my permission change works, sometimes it doesn't for 60s"). Redis-backed caching fixes that but then you're paying a network round-trip to a cache to avoid a network round-trip to the DB. And we already pay a Redis round-trip per request for token revocation (is_valid_token), so the saving is marginal at best.

In this case you'd trade the database query for redis queries and cross-worker communication overhead.

The query itself is cheap. It's a single-row SELECT by primary key on an indexed column, sub-millisecond on SQLite and Postgres. There is no join, no scan, no aggregation. The database is doing exactly one B-tree lookup. No cache we add in front of this will meaningfully improve the performance or beat that for the read path, and any cache adds invalidation complexity, stale-data risk, and a new class of bugs and maintenance burden and debugging complexity.

What does look interesting, because those are just "stop doing unnecessary work":

Removing the redundant double-fetches where a handler already received user from Depends(get_verified_user) and then immediately refetches the same row (e.g. routers/users.py:279,342,369,387,406). That's a delete, not a cache.
Fixing the real N+1 in routers/channels.py:931 by using the existing get_users_by_user_ids batch helper.
Reusing SESSION_POOL[sid] in the socket handlers that already have the user data in memory post-connect.
Column projection for the auth-only path (skip the settings/oauth/scim/info JSON blobs when all we need is id/role/email/name).

The TTL/Redis caching proposals would be closed as won't-fix. The consistency cost isn't worth the (practically non-existent) savings on an already cheap query plus security regressions.

@Classic298 commented on GitHub (Apr 16, 2026): We won't be caching `get_user_by_id` at the database layer. A few reasons: **Consistency trumps performance for this row.** The user row is the source of truth for `role` and is consulted on every authenticated request. A 30–60s TTL means role changes (user -> pending, pending -> user), bans (pending, user deletion), and other permission mutations would take up to a minute to propagate with 60s TTL. That's a security regression. Demoting an admin or revoking access needs to be effective immediately, not 1 minute later. The same applies to token revocation logic, which must not be short-circuited by a stale user cache. **Multi-worker invalidation is a real bug surface, not a handwave.** With uvicorn running N workers, an in-process TTL cache means a mutation in worker A does not invalidate workers B..N. You end up with per-worker stale windows that are effectively non-deterministic from the client's perspective ("sometimes my permission change works, sometimes it doesn't for 60s"). Redis-backed caching fixes that but then you're paying a network round-trip to a cache to avoid a network round-trip to the DB. And we already pay a Redis round-trip per request for token revocation (`is_valid_token`), so the saving is marginal at best. In this case you'd trade the database query for redis queries and cross-worker communication overhead. **The query itself is cheap.** It's a single-row SELECT **by primary key on an indexed column**, sub-millisecond on SQLite and Postgres. There is no join, no scan, no aggregation. The database is doing exactly one B-tree lookup. No cache we add in front of this will meaningfully improve the performance or beat that for the read path, and any cache adds invalidation complexity, stale-data risk, and a new class of bugs and maintenance burden and debugging complexity. What does look interesting, because those are just "stop doing unnecessary work": - Removing the redundant double-fetches where a handler already received `user` from `Depends(get_verified_user)` and then immediately refetches the same row (e.g. `routers/users.py:279,342,369,387,406`). That's a delete, not a cache. - Fixing the real N+1 in `routers/channels.py:931` by using the existing `get_users_by_user_ids` batch helper. - Reusing `SESSION_POOL[sid]` in the socket handlers that already have the user data in memory post-`connect`. - Column projection for the auth-only path (skip the `settings`/`oauth`/`scim`/`info` JSON blobs when all we need is `id`/`role`/`email`/`name`). The TTL/Redis caching proposals would be closed as won't-fix. The consistency cost isn't worth the (practically non-existent) savings on an already cheap query plus security regressions.

GiteaMirror commented

2026-05-18 05:36:39 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

25% of all queries (source?) doesn't matter

Frequency != Performance

it's one of the cheapest queries on the backend. You'd be optimizing the wrong thing

@Classic298 commented on GitHub (Apr 16, 2026): 25% of all queries (source?) doesn't matter Frequency != Performance it's one of the cheapest queries on the backend. You'd be optimizing the wrong thing

GiteaMirror commented

2026-05-18 05:36:40 -05:00

@ashm-dev commented on GitHub (Apr 16, 2026):

We have Sentry attached to our Open WebUI instance. When filtering traces by db.query spans, this specific query accounts for ~25% of total span duration across all database queries. That's not a guess — it's observed production telemetry.

Frequency * cost-per-call = total cost. The query is cheap individually, yes. But when it fires on every authenticated request, every socket event, and N times per channel message list — the cumulative time adds up. That's exactly what the Sentry data shows.

@ashm-dev commented on GitHub (Apr 16, 2026): We have Sentry attached to our Open WebUI instance. When filtering traces by db.query spans, this specific query accounts for ~25% of total span duration across all database queries. That's not a guess — it's observed production telemetry. Frequency * cost-per-call = total cost. The query is cheap individually, yes. But when it fires on every authenticated request, every socket event, and N times per channel message list — the cumulative time adds up. That's exactly what the Sentry data shows.

GiteaMirror commented

2026-05-18 05:36:40 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

@ashm-dev are you experiencing slowness / performance issues?

if yes - what is your setup? In the past every single time someone reported slow database queries it boiled down to the network round trip time between their open webui and the database due to cloud deployment of the database and multiple miliseconds (sometimes hundreds of milliseconds) of latency

@Classic298 commented on GitHub (Apr 16, 2026): @ashm-dev are you experiencing slowness / performance issues? if yes - what is your setup? In the past every single time someone reported slow database queries it boiled down to the network round trip time between their open webui and the database due to cloud deployment of the database and multiple miliseconds (sometimes hundreds of milliseconds) of latency

GiteaMirror commented

2026-05-18 05:36:41 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

As I said above, fixing the call sites by removing redundant queries and fixing N+1 makes sense but the query itself is basically as performant as it gets. Its the cheapest query a database can do essentially

@Classic298 commented on GitHub (Apr 16, 2026): As I said above, fixing the call sites by removing redundant queries and fixing N+1 makes sense but the query itself is basically as performant as it gets. Its the cheapest query a database can do essentially

GiteaMirror commented

2026-05-18 05:36:42 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

And since you are logging your database, how long does a single query take for you? If it's anything above 1 ms (just the database) then your deployment is probably subpar. This is an absolutely trivial query and should not take more than tenths of milliseconds.

If you measure it from open webui and you see multiple or hundreds of milliseconds then you have a network issue

@Classic298 commented on GitHub (Apr 16, 2026): And since you are logging your database, how long does a single query take for you? If it's anything above 1 ms (just the database) then your deployment is probably subpar. This is an absolutely trivial query and should not take more than tenths of milliseconds. If you measure it from open webui and you see multiple or hundreds of milliseconds then you have a network issue

GiteaMirror commented

2026-05-18 05:36:42 -05:00

@ashm-dev commented on GitHub (Apr 16, 2026):

No slowness — this was from Sentry trace analysis, not a user complaint. Setup is single node, DB local, sub-ms latency.
The query cost was never the point. The redundant callsites and N+1 are.

@ashm-dev commented on GitHub (Apr 16, 2026): No slowness — this was from Sentry trace analysis, not a user complaint. Setup is single node, DB local, sub-ms latency. The query cost was never the point. The redundant callsites and N+1 are.

GiteaMirror commented

2026-05-18 05:36:43 -05:00

@Classic298 commented on GitHub (Apr 16, 2026):

Ok. So we have been going in circles.

If your query is sub-ms : Then as I have already said: the query is not the issue and caching is not the solution and as I also already said above, the other fixes at the callsites are good if properly implemented.

So feel free to PR some of the fixes, i just opened one PR also to fix that callsite.

@Classic298 commented on GitHub (Apr 16, 2026): Ok. So we have been going in circles. If your query is sub-ms : Then as I have already said: the query is not the issue and caching is not the solution and as I also already said above, the other fixes at the callsites are good if properly implemented. So feel free to PR some of the fixes, i just opened one PR also to fix that callsite.

GiteaMirror commented

2026-05-18 05:36:44 -05:00

@ashm-dev commented on GitHub (Apr 16, 2026):

Okay, thanks

@ashm-dev commented on GitHub (Apr 16, 2026): Okay, thanks

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#107069

[GH-ISSUE #23793] feat: cache get_user_by_id — 25% of all DB queries with zero caching #107069

Check Existing Issues

Verify Feature Scope

Problem Description

Desired Solution you'd like

Tier 1 — no new dependencies, works on any DB/deploy config:

Tier 2 — when Redis is available (REDIS_URL is set):

Expected impact

Alternatives Considered

Additional Context

Tier 2 — when Redis is available (`REDIS_URL` is set):