Add stored level and pair deletion queries to ProjectAuthorization
What does this MR do and why?
The shared write path for project_authorizations (https://gitlab.com/groups/gitlab-org/-/work_items/24326) compares calculated access with the stored rows for a chunk of pairs, then rewrites the ones that differ. This adds the three queries it needs to ProjectAuthorization:
- stored levels for one user on some projects;
- stored levels for one project's users;
- deleting one user's rows on some projects, or some users' rows on one project.
Levels come back as lists, because the table can still hold more than one row for a pair until https://gitlab.com/gitlab-org/gitlab/-/work_items/418205, and the write path rewrites those as one row. Nothing calls these yet.
Database
Measured on a Database Lab clone. Every query is an index scan on the primary key (user_id, project_id) or on project_id.
| Query | Rows | Time | Pages |
|---|---|---|---|
stored_levels_for_user, 1,000 projects |
1,000 | 3.5 ms | about 4,200 (cached) |
stored_levels_for_project, every user on gitlab-org/gitlab |
all | 1.4 ms | about 3,000 (cached) |
stored_levels_for_project, 100 users |
100 | 0.2 ms | 155 |
delete_for_user, 1,000 projects |
1,000 | 4 ms, plus 111 ms in the existing sync trigger | about 3,000 (cached) |
delete_for_project, 100 users |
100 | 842 ms with 369 cold page reads, plus 860 ms in the existing sync trigger | about 800 |
Deletes spend most of their time in the existing sync_project_authorizations_to_migration trigger, which runs once per deleted row on any delete from this table. Cold reads on the clone are slow (about 2 ms a page), so the 100-user delete is mostly disk.
Query plans (IDs replaced by placeholders)
stored_levels_for_user: a user with over 10,000 group memberships, on 1,000 of their projects
3.467 ms, buffers: hit=4174
SELECT "project_authorizations"."project_id", "project_authorizations"."access_level" FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <id> AND "project_authorizations"."project_id" IN (<1000 ids>)Index Only Scan using index_project_authorizations_on_project_user_access_level on project_authorizations (cost=0.58..2091.87 rows=37 width=8) (actual time=0.035..3.404 rows=1000 loops=1)
Index Cond: ((project_id = ANY ('{<1000 ids>}'::integer[])) AND (user_id = <id>))
Heap Fetches: 58
Buffers: shared hit=4174
Planning Time: 5.669 ms
Execution Time: 3.467 msstored_levels_for_project: every user on gitlab-org/gitlab
1.353 ms, buffers: hit=3051
SELECT "project_authorizations"."user_id", "project_authorizations"."access_level" FROM "project_authorizations" WHERE "project_authorizations"."project_id" = <id>Index Only Scan using index_project_authorizations_on_project_user_access_level on project_authorizations (cost=0.58..207.53 rows=7456 width=8) (actual time=0.023..1.211 rows=3125 loops=1)
Index Cond: (project_id = <id>)
Heap Fetches: 169
Buffers: shared hit=3051
Planning Time: 0.069 ms
Execution Time: 1.353 msstored_levels_for_project: 100 users on gitlab-org/gitlab
0.238 ms, buffers: hit=155
SELECT "project_authorizations"."user_id", "project_authorizations"."access_level" FROM "project_authorizations" WHERE "project_authorizations"."project_id" = <id> AND "project_authorizations"."user_id" IN (<100 ids>)Index Only Scan using index_project_authorizations_on_project_user_access_level on project_authorizations (cost=0.58..210.54 rows=4 width=8) (actual time=0.031..0.219 rows=100 loops=1)
Index Cond: ((project_id = <id>) AND (user_id = ANY ('{<100 ids>}'::integer[])))
Heap Fetches: 4
Buffers: shared hit=155
Planning Time: 0.678 ms
Execution Time: 0.238 msdelete_for_user: 1,000 projects of that user (rolled back)
116.263 ms
DELETE FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <id> AND "project_authorizations"."project_id" IN (<1000 ids>)Delete on project_authorizations (cost=0.58..2139.63 rows=0 width=0) (actual time=4.306..4.307 rows=0 loops=1)
Buffers: shared hit=3004
-> Index Scan using project_authorizations_pkey on project_authorizations (cost=0.58..2139.63 rows=37 width=6) (actual time=0.071..2.617 rows=1000 loops=1)
Index Cond: ((user_id = <id>) AND (project_id = ANY ('{<1000 ids>}'::integer[])))
Buffers: shared hit=1004
Planning:
Buffers: shared hit=225
Planning Time: 13.825 ms
Trigger sync_project_authorizations_to_migration: time=111.395 calls=1000
Execution Time: 116.263 msdelete_for_project: 100 users on gitlab-org/gitlab (rolled back)
1702.705 ms
DELETE FROM "project_authorizations" WHERE "project_authorizations"."project_id" = <id> AND "project_authorizations"."user_id" IN (<100 ids>)Delete on project_authorizations (cost=0.58..214.52 rows=0 width=0) (actual time=842.481..842.482 rows=0 loops=1)
Buffers: shared hit=425 read=369 dirtied=186
I/O Timings: shared read=837.973
-> Index Scan using project_authorizations_pkey on project_authorizations (cost=0.58..214.52 rows=4 width=6) (actual time=86.588..840.634 rows=100 loops=1)
Index Cond: ((user_id = ANY ('{<100 ids>}'::integer[])) AND (project_id = <id>))
Buffers: shared hit=131 read=369
I/O Timings: shared read=837.973
Planning:
Buffers: shared hit=225
Planning Time: 4.002 ms
Trigger sync_project_authorizations_to_migration: time=860.011 calls=100
Execution Time: 1702.705 msHow to set up and validate locally
bin/rspec spec/models/project_authorization_spec.rb
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist.
Closes https://gitlab.com/gitlab-org/gitlab/-/work_items/637567