Classic298 ac8af4996c perf: index group_member on (user_id, group_id) (#27822) ...

Permission checks are the most repeated database work in a request, and every one of them asks the same question: which groups is this user in. Today that question cannot use an index.

`group_member` has only its primary key and a `(group_id, user_id)` unique constraint. That constraint leads on `group_id`, so a lookup by `user_id` has to walk the entire membership table, every time. `Groups.get_groups_by_member_id` sits under `has_permission`, `has_access`, `check_model_access` and the `AccessGrants` fallbacks, so an ordinary chat completion pays that walk several times before the model is even called, and the admin user list pays it once per row.

The cost scales with total memberships across all users rather than with the size of any one user's, so it stays invisible on a small instance and then arrives all at once on a large one.

Measured on SQLite, timing the real join from `get_groups_by_member_id`:

| memberships | before | after |
|---|---|---|
| 5,000 | 0.04 ms | 0.03 ms |
| 50,000 | 0.10 ms | 0.04 ms |
| 200,000 | 1.33 ms | 0.04 ms |
| 500,000 | 2.94 ms | 0.04 ms |

The after column is flat because the lookup becomes a seek instead of a scan. Concretely: on a deployment with 500k memberships, say 10,000 users in 50 groups each, one chat completion currently spends roughly 15 ms of database time answering the same question over and over. Afterwards it is under 0.2 ms. On a small install you will not be able to measure the difference, and that is fine, the point is that the curve stops bending.

The index is `(user_id, group_id)`. The trailing column makes those lookups index-only, since `group_id` is the column they select. Queries that lead on `group_id`, such as `get_group_user_ids_by_id` and the `chat_messages` subqueries, are already served by the existing unique constraint and are unaffected.

What to expect when the migration runs: on PostgreSQL this is a plain `CREATE INDEX`, which takes a SHARE lock, so reads continue while writes to `group_member` block until it completes. The table holds one row per membership, so expect sub-second even on the numbers above. `CONCURRENTLY` cannot be used here because the migration runner wraps the upgrade in a transaction, and it is not warranted at this table size.

2026-07-31 19:09:15 -05:00

23 KiB

Raw Permalink Blame History

View Raw