Add WriteBatchService for project_authorizations

What does this MR do and why?

Today several writers of project_authorizations calculate a diff and write it later without a shared lock, so a slower writer can overwrite a newer result. This adds Authz::ProjectAuthorizations::WriteBatchService, the one write path every writer will go through (https://gitlab.com/groups/gitlab-org/-/work_items/24326).

For one user on some projects, or some users on one project, it:

  • takes per-user advisory locks in one sorted statement, so writers can't deadlock;
  • calculates the levels inside the transaction, after the locks;
  • deletes and inserts only the pairs that changed, and rewrites legacy duplicates as one row;
  • publishes the added and removed notifications after commit.

It writes at most 100 IDs per transaction, to keep deletes near the 100 ms guideline. It refuses to run inside a transaction, because the locks would then last until the caller's commit and the notifications would go out before it.

wait_for_writers waits for the same locks without keeping them. The recalculation services use it before their unlocked first pass (https://gitlab.com/gitlab-org/gitlab/-/work_items/637571 and https://gitlab.com/gitlab-org/gitlab/-/work_items/637572).

Nothing calls this yet, so there is no behavior change. The two-connection race spec is https://gitlab.com/gitlab-org/gitlab/-/work_items/637573.

References

Database

Each chunk holds at most 100 IDs, so every delete and insert touches at most 100 pairs.

Measured on a Database Lab clone of production-main (about 4.0B rows in project_authorizations), with 100 real pairs per run and every statement rolled back. The project path used gitlab-org/gitlab and 100 of its users. The user path used one user and 100 of their projects.

Query Trigger Time Shared hit / read
delete_for_project, 100 users on, cold 1,080 ms, 843 ms of it in the trigger 509 / 286
delete_for_project, 100 users on, warm 3.2 ms 699 / 0
delete_for_project, 100 users off, warm 4.7 ms 699 / 0
delete_for_user, 100 projects on, cold 388 ms, 321 ms of it in the trigger 300 / 84
delete_for_user, 100 projects off, warm 0.6 ms 300 / 0
Insert, 100 pairs on one project on 320 ms, 39 ms of it in the trigger 4,148 / 398
Insert, 100 pairs on one project off 4.5 ms 4,234 / 3
Insert, 100 projects for one user on 960 ms, 413 ms of it in the trigger 3,598 / 344
Insert, 100 projects for one user off 3.4 ms 4,237 / 0
  • Both deletes are an index scan on project_authorizations_pkey.
  • The insert is a nested loop over the 100 values, with an index-only lookup on users and projects per row, and ON CONFLICT DO NOTHING.
  • Cold time is mostly the existing sync_project_authorizations_to_migration trigger reading project_authorizations_for_migration from disk. Warm, it adds about 2 ms per 100-row delete.
  • The cold runs aren't fully cold, because the clone had been in use, so treat them as a rough upper bound. Each insert ran once, trigger on first, so the trigger-off insert had a warmer cache.

Before and after

These are warm timings from the same clone, the third of three runs, with every statement rolled back. The project path uses gitlab-org/gitlab (3,179 rows) and 100 of its users. The user path uses one user with 207 rows and 100 of their projects. "Before" means today's ProjectRecalculateService and FindRecordsDueForRefreshService reads, and ProjectAuthorizations::Changes#apply! writes.

Statement Before After
Read stored levels, project 6.7 ms, all rows, once per recalculation 0.7 ms per 100-user chunk
Read stored levels, user 0.4 ms, all rows, once per recalculation 1.0 ms per 100-project chunk
Lock none (Redis lease) 0.7 ms for 100 users, 0.1 ms for 1
Insert 100 rows, project 8.1 ms, planning 0.05 ms 6.9 ms, planning 4.6 ms
Insert 100 rows, user 8.3 ms, planning 0.06 ms 8.5 ms, planning 4.4 ms
Delete 100 rows 8–10 ms, about 7 ms of it in the trigger same SQL as today
  • The joined insert runs about as fast. It adds about 4.5 ms of planning, because the planner has to join a 100-row literal list. It also does one index-only lookup on users and one on projects per row (about 900 more buffer hits, all cached).
  • Each read is cheaper, but the service reads once per chunk, after taking the locks, instead of once per recalculation. Recalculating every user of gitlab-org/gitlab reads for about 20 ms in total, instead of about 7 ms.
  • The first, cold run of each statement was slower, both before and after (up to 756 ms for an insert). That's the cache and the sync trigger, not the change.
Lock, new
SELECT count(pg_advisory_xact_lock(1635017066, bucket))
FROM (SELECT bucket FROM unnest(ARRAY[<100 buckets>]::int[]) AS bucket ORDER BY bucket) sorted

This is the plan for 100 users. With one user it takes 0.14 ms. Locking on the user ID instead of a bucket only changes the array values, so the plan stays the same. wait_for_writers runs the same statement with pg_advisory_xact_lock_shared.

Aggregate  (cost=5.07..5.08 rows=1 width=8) (actual time=0.572..0.572 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Sort  (cost=4.32..4.57 rows=100 width=4) (actual time=0.055..0.061 rows=100 loops=1)
        Sort Key: bucket.bucket
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=3
        ->  Function Scan on unnest bucket  (cost=0.00..1.00 rows=100 width=4) (actual time=0.011..0.016 rows=100 loops=1)
Planning:
  Buffers: shared hit=17
Planning Time: 0.221 ms
Execution Time: 0.659 ms
Insert 100 rows on one project, before (`insert_all`)
INSERT INTO "project_authorizations" ("user_id", "project_id", "access_level", "is_unique")
VALUES (<user_id>, <project_id>, <access_level>, true), … 100 rows
ON CONFLICT DO NOTHING RETURNING "user_id", "project_id", "access_level"
Insert on project_authorizations  (cost=0.00..1.25 rows=100 width=17) (actual time=0.700..4.641 rows=100 loops=1)
  Conflict Resolution: NOTHING
  Tuples Inserted: 100
  Conflicting Tuples: 0
  Buffers: shared hit=3231 read=3 dirtied=3
  I/O Timings: shared read=2.236
  ->  Values Scan on "*VALUES*"  (cost=0.00..1.25 rows=100 width=17) (actual time=0.004..0.048 rows=100 loops=1)
Planning Time: 0.047 ms
Trigger sync_project_authorizations_to_migration: time=3.439 calls=100
Execution Time: 8.130 ms
Insert 100 rows on one project, after (joined to `users` and `projects`)
INSERT INTO project_authorizations (user_id, project_id, access_level, is_unique)
SELECT v.user_id, v.project_id, v.access_level, true
FROM (VALUES (<user_id>, <project_id>, <access_level>), … 100 rows) AS v (user_id, project_id, access_level)
INNER JOIN users ON users.id = v.user_id
INNER JOIN projects ON projects.id = v.project_id
ON CONFLICT DO NOTHING
Insert on project_authorizations  (cost=1.00..409.50 rows=0 width=0) (actual time=4.121..4.123 rows=0 loops=1)
  Conflict Resolution: NOTHING
  Tuples Inserted: 100
  Conflicting Tuples: 0
  Buffers: shared hit=4137 read=2 dirtied=2
  I/O Timings: shared read=1.339
  ->  Nested Loop  (cost=1.00..409.50 rows=100 width=17) (actual time=0.058..0.831 rows=100 loops=1)
        Buffers: shared hit=912
        ->  Nested Loop  (cost=0.44..198.25 rows=100 width=12) (actual time=0.031..0.633 rows=100 loops=1)
              Buffers: shared hit=411
              ->  Values Scan on "*VALUES*"  (cost=0.00..1.25 rows=100 width=12) (actual time=0.001..0.045 rows=100 loops=1)
              ->  Index Only Scan using users_pkey on users  (cost=0.44..1.97 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)
                    Index Cond: (id = "*VALUES*".column1)
                    Heap Fetches: 22
                    Buffers: shared hit=411
        ->  Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects  (cost=0.56..2.11 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=100)
              Index Cond: (id = "*VALUES*".column2)
              Heap Fetches: 100
              Buffers: shared hit=501
Planning:
  Buffers: shared hit=1624
Planning Time: 4.641 ms
Trigger sync_project_authorizations_to_migration: time=2.705 calls=100
Execution Time: 6.904 ms
Insert 100 projects for one user, before (`insert_all`)

Same SQL as the project insert before.

Insert on project_authorizations  (cost=0.00..1.25 rows=100 width=17) (actual time=0.900..5.305 rows=100 loops=1)
  Conflict Resolution: NOTHING
  Tuples Inserted: 100
  Conflicting Tuples: 0
  Buffers: shared hit=3227 read=2 dirtied=2
  I/O Timings: shared read=1.386
  ->  Values Scan on "*VALUES*"  (cost=0.00..1.25 rows=100 width=17) (actual time=0.006..0.077 rows=100 loops=1)
Planning Time: 0.064 ms
Trigger sync_project_authorizations_to_migration: time=2.966 calls=100
Execution Time: 8.329 ms
Insert 100 projects for one user, after (joined)

Same SQL as the project insert after.

Insert on project_authorizations  (cost=1.00..409.50 rows=0 width=0) (actual time=3.519..3.521 rows=0 loops=1)
  Conflict Resolution: NOTHING
  Tuples Inserted: 100
  Conflicting Tuples: 0
  Buffers: shared hit=3953
  ->  Nested Loop  (cost=1.00..409.50 rows=100 width=17) (actual time=0.061..0.986 rows=100 loops=1)
        Buffers: shared hit=799
        ->  Nested Loop  (cost=0.44..198.25 rows=100 width=12) (actual time=0.036..0.197 rows=100 loops=1)
              Buffers: shared hit=301
              ->  Values Scan on "*VALUES*"  (cost=0.00..1.25 rows=100 width=12) (actual time=0.001..0.036 rows=100 loops=1)
              ->  Index Only Scan using users_pkey on users  (cost=0.44..1.97 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=100)
                    Index Cond: (id = "*VALUES*".column1)
                    Heap Fetches: 0
                    Buffers: shared hit=301
        ->  Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects  (cost=0.56..2.11 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=100)
              Index Cond: (id = "*VALUES*".column2)
              Heap Fetches: 1
              Buffers: shared hit=498
Planning:
  Buffers: shared hit=1624
Planning Time: 4.427 ms
Trigger sync_project_authorizations_to_migration: time=4.949 calls=100
Execution Time: 8.544 ms
Read stored levels for a project, before (every row)
SELECT "project_authorizations"."user_id", "project_authorizations"."access_level"
FROM "project_authorizations" WHERE "project_authorizations"."project_id" = <project_id>
Index Only Scan using index_project_authorizations_on_project_user_access_level on project_authorizations  (cost=0.58..490.31 rows=8036 width=8) (actual time=0.049..6.495 rows=3179 loops=1)
  Index Cond: (project_id = N)
  Heap Fetches: 2001
  Buffers: shared hit=5363
Planning:
  Buffers: shared hit=184
Planning Time: 2.804 ms
Execution Time: 6.693 ms
Read stored levels for a project, after (one chunk)
SELECT "project_authorizations"."user_id", "project_authorizations"."access_level"
FROM "project_authorizations"
WHERE "project_authorizations"."project_id" = <project_id> AND "project_authorizations"."user_id" IN (<100 user 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.050..0.649 rows=100 loops=1)
  Index Cond: ((project_id = N) AND (user_id = ANY ('{…}'::integer[])))
  Heap Fetches: 137
  Buffers: shared hit=275
Planning:
  Buffers: shared hit=226
Planning Time: 3.380 ms
Execution Time: 0.699 ms
Read stored levels for a user, before (every row)
SELECT "project_authorizations"."project_id", "project_authorizations"."access_level"
FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <user_id>
Index Only Scan using project_authorizations_pkey on project_authorizations  (cost=0.58..1120.90 rows=19689 width=8) (actual time=0.067..0.360 rows=207 loops=1)
  Index Cond: (user_id = N)
  Heap Fetches: 15
  Buffers: shared hit=152
Planning:
  Buffers: shared hit=184
Planning Time: 2.781 ms
Execution Time: 0.419 ms
Read stored levels for a user, after (one chunk)
SELECT "project_authorizations"."project_id", "project_authorizations"."access_level"
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = <user_id> AND "project_authorizations"."project_id" IN (<100 project 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.047..0.901 rows=100 loops=1)
  Index Cond: ((project_id = ANY ('{…}'::integer[])) AND (user_id = N))
  Heap Fetches: 5
  Buffers: shared hit=477
Planning:
  Buffers: shared hit=226
Planning Time: 3.217 ms
Execution Time: 0.959 ms

How to set up and validate locally

Not applicable, nothing calls the service yet. The spec covers it.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

🤖 Generated with Claude Code

Edited by Ian Anderson

Merge request reports

Loading
Loading