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 ms

stored_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 ms

stored_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 ms

delete_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 ms

delete_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 ms

How 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

Edited by Ian Anderson

Merge request reports

Loading
Loading