Sync sec mirror tables from namespace and project sync events

Rutger Wesselsrequested to merge
627115-sync-sec-mirror-tables into master

What does this MR do and why?

Data isolation on the sec database injects organization_id subqueries. Tables sharded by project_id or namespace_id cannot resolve those without local mirrors of the projects and namespaces tables. The mirror tables themselves were created in !255343 (merged); this MR adds the syncing that keeps them up to date.

Changes:

  • Ci::ProcessSyncEventsService is moved to Database::ProcessSyncEventsService (pure rename: it now has a non-CI consumer) and takes a consumer-to-mirror-class map, for example { ci: Ci::ProjectMirror, sec: Security::ProjectMirror }. Each consumer reads only the events it has not synced yet, using the ci_synced / sec_synced columns from !258480 (merged), and marks its own column when done.
  • Add Security::ProjectMirror.sync! and Security::NamespaceMirror.sync!, replicating the same attributes as the ci mirrors. The sec consumer is only active when the default-off ops feature flag sync_sec_mirror_tables is enabled.
  • The delete is handled by a new Database::CleanupSyncEventsService, called by both workers after processing: mark_records_processed now only marks the consumer's column, and the service deletes the fully synced rows in primary-key batches of 1000. With the flag enabled a row is fully synced when ci_synced AND sec_synced; with the flag disabled when ci_synced. Because the deletes are batched by primary key, no index on the flag columns is needed and no statement scans the whole table during a backlog.
  • Extract shared sync logic from Namespaces::SyncEvent and Projects::SyncEvent into a new Database::SyncEventTable concern to prevent code duplication.

A failing consumer does not stop the other consumers; its error is re-raised at the end of the run so Sidekiq retries the job, and consumers that already succeeded are a cheap no-op on the retry. Events consumed while the flag is off leave gaps in the sec mirrors; the follow-up backfill and consistency-check workers will repair them.

Out of scope, coming in follow-up MRs: backfilling existing rows and the sec consistency-check workers.

References

Issue: #627115

Screenshots or screen recordings

Not applicable, this is a backend-only change.

How to set up and validate locally

  1. Enable the flag in a Rails console: Feature.enable(:sync_sec_mirror_tables)
  2. Create a group and a project (UI or console).
  3. In the console, check Security::NamespaceMirror.find_by(namespace_id: group.id) and Security::ProjectMirror.find_by(project_id: project.id). Rows should exist with the correct traversal_ids, namespace_id, and organization_id.

Query Plans

Main database (gitlab-production-main)

Each query ran twice; the second (warm) run is shown. Plans 1-4 per table were taken on an earlier clone whose snapshot predated !258480 (merged), so the two columns and four partial indexes were recreated on that clone (ALTER TABLE, CREATE INDEX, ANALYZE) before planning; the batching and delete plans ran on a fresh clone that has them natively. The reads use the partial (id) WHERE sec_synced = false indexes, and the UPDATE, the EachBatch bound lookups, and the batched DELETE statements are all driven by the primary key, so every statement stays index-bound even when the table holds a backlog.

namespaces_sync_events

1. Read the next batch of events not synced to sec
SELECT "namespaces_sync_events".* FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."sec_synced" = FALSE ORDER BY "namespaces_sync_events"."id" ASC LIMIT 1000

Bitmap index scan on the partial index index_namespaces_sync_events_on_id_where_not_sec_synced. 0.555 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167241

Plan 
 Limit  (cost=3.15..3.16 rows=1 width=18) (actual time=0.027..0.028 rows=0 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=3.15..3.16 rows=1 width=18) (actual time=0.026..0.026 rows=0 loops=1)
         Sort Key: namespaces_sync_events.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Heap Scan on public.namespaces_sync_events  (cost=1.63..3.14 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=1)
               Recheck Cond: (NOT namespaces_sync_events.sec_synced)
               Buffers: shared hit=1
               I/O Timings: read=0.000 write=0.000
               ->  Bitmap Index Scan on index_namespaces_sync_events_on_id_where_not_sec_synced  (cost=0.00..1.63 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                     Buffers: shared hit=1
                     I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -7774873633539322151

Time: 0.555 ms
  - planning: 0.494 ms
  - execution: 0.061 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
2. Estimate the number of events not synced to sec
SELECT COALESCE(MAX(id) - MIN(id) + 1, 0) AS upper_bound_count FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."sec_synced" = FALSE

Aggregate over a bitmap index scan on the same partial index. 0.687 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167244

Plan 
 Aggregate  (cost=3.15..3.16 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Bitmap Heap Scan on public.namespaces_sync_events  (cost=1.63..3.14 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
         Recheck Cond: (NOT namespaces_sync_events.sec_synced)
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Index Scan on index_namespaces_sync_events_on_id_where_not_sec_synced  (cost=0.00..1.63 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Buffers: shared hit=1
               I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 717447522522593245

Time: 0.687 ms
  - planning: 0.610 ms
  - execution: 0.077 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
3. Check whether events not synced to sec remain (re-enqueue)
SELECT 1 AS one FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."sec_synced" = FALSE LIMIT 1

Bitmap index scan on the same partial index, stops at the first row. 0.509 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167246

Plan 
 Limit  (cost=1.63..3.14 rows=1 width=4) (actual time=0.006..0.007 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Bitmap Heap Scan on public.namespaces_sync_events  (cost=1.63..3.14 rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=1)
         Recheck Cond: (NOT namespaces_sync_events.sec_synced)
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Index Scan on index_namespaces_sync_events_on_id_where_not_sec_synced  (cost=0.00..1.63 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Buffers: shared hit=1
               I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -8668400778988443777

Time: 0.509 ms
  - planning: 0.472 ms
  - execution: 0.037 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
4. Mark the batch as synced to sec
UPDATE "namespaces_sync_events" SET "sec_synced" = TRUE WHERE "namespaces_sync_events"."id" IN (1, 2)

Primary key index scan, scoped by id IN (...). 0.669 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167248

Plan 
 Update on public.namespaces_sync_events  (cost=0.12..4.78 rows=0 width=0) (actual time=0.009..0.010 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using namespaces_sync_events_pkey on public.namespaces_sync_events  (cost=0.12..4.78 rows=1 width=7) (actual time=0.008..0.009 rows=0 loops=1)
         Index Cond: (namespaces_sync_events.id = ANY ('{1,2}'::bigint[]))
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: 8848176903881546374

Time: 0.669 ms
  - planning: 0.586 ms
  - execution: 0.083 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
5. Find the first batch bound (EachBatch)
SELECT "namespaces_sync_events"."id" FROM "namespaces_sync_events" ORDER BY "namespaces_sync_events"."id" ASC LIMIT 1

Index-only scan on the primary key. 0.475 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168945

Plan 
Limit  (cost=0.12..7.14 rows=1 width=8) (actual time=0.008..0.009 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using namespaces_sync_events_pkey on public.namespaces_sync_events  (cost=0.12..7.14 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: 5613686347033402667

Time: 0.475 ms
  - planning: 0.446 ms
  - execution: 0.029 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
6. Find the next batch bound (EachBatch)
SELECT "namespaces_sync_events"."id" FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."id" >= 1 ORDER BY "namespaces_sync_events"."id" ASC LIMIT 1 OFFSET 1000

Primary-key scan; reads at most one batch of index entries. 0.616 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168947

Plan 
Limit  (cost=3.16..3.17 rows=1 width=8) (actual time=0.033..0.034 rows=0 loops=1)
   Buffers: shared hit=7
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=3.16..3.16 rows=1 width=8) (actual time=0.032..0.033 rows=0 loops=1)
         Sort Key: namespaces_sync_events.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=7
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Heap Scan on public.namespaces_sync_events  (cost=1.63..3.15 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=1)
               Recheck Cond: (namespaces_sync_events.id >= 1)
               Buffers: shared hit=4
               I/O Timings: read=0.000 write=0.000
               ->  Bitmap Index Scan on namespaces_sync_events_pkey  (cost=0.00..1.63 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                     Index Cond: (namespaces_sync_events.id >= 1)
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: -8174267902570412301

Time: 0.616 ms
  - planning: 0.551 ms
  - execution: 0.065 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 7 (~56.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
7. Delete the fully synced rows of one batch (flag enabled)
DELETE FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."id" >= 1 AND "namespaces_sync_events"."id" < 1001 AND "namespaces_sync_events"."ci_synced" = TRUE AND "namespaces_sync_events"."sec_synced" = TRUE

Index scan on the primary key, bounded to the batch's id range; the flag columns are only a filter, so no index on them is needed. 0.601 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168949

Plan 
Delete on public.namespaces_sync_events  (cost=0.12..3.14 rows=0 width=0) (actual time=0.015..0.015 rows=0 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using namespaces_sync_events_pkey on public.namespaces_sync_events  (cost=0.12..3.14 rows=1 width=6) (actual time=0.014..0.014 rows=0 loops=1)
         Index Cond: ((namespaces_sync_events.id >= 1) AND (namespaces_sync_events.id < 1001))
         Filter: (namespaces_sync_events.ci_synced AND namespaces_sync_events.sec_synced)
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: 1316990375456836185

Time: 0.601 ms
  - planning: 0.515 ms
  - execution: 0.086 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
8. Delete the synced rows of one batch (flag disabled)
DELETE FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."id" >= 1 AND "namespaces_sync_events"."id" < 1001 AND "namespaces_sync_events"."ci_synced" = TRUE

Same primary-key index scan, filtering on ci_synced only. 0.766 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168951

Plan 
Delete on public.namespaces_sync_events  (cost=0.12..3.14 rows=0 width=0) (actual time=0.023..0.024 rows=0 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using namespaces_sync_events_pkey on public.namespaces_sync_events  (cost=0.12..3.14 rows=1 width=6) (actual time=0.021..0.022 rows=0 loops=1)
         Index Cond: ((namespaces_sync_events.id >= 1) AND (namespaces_sync_events.id < 1001))
         Filter: namespaces_sync_events.ci_synced
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -2683180050142387920

Time: 0.766 ms
  - planning: 0.656 ms
  - execution: 0.110 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

projects_sync_events

9. Read the next batch of events not synced to sec
SELECT "projects_sync_events".* FROM "projects_sync_events" WHERE "projects_sync_events"."sec_synced" = FALSE ORDER BY "projects_sync_events"."id" ASC LIMIT 1000

Index scan on the partial index index_projects_sync_events_on_id_where_not_sec_synced, stops after 1000 rows. 0.521 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167254

Plan 
 Limit  (cost=0.12..3.14 rows=1 width=18) (actual time=0.005..0.005 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_projects_sync_events_on_id_where_not_sec_synced on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=1)
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 7878316785664152715

Time: 0.521 ms
  - planning: 0.490 ms
  - execution: 0.031 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
10. Estimate the number of events not synced to sec
SELECT COALESCE(MAX(id) - MIN(id) + 1, 0) AS upper_bound_count FROM "projects_sync_events" WHERE "projects_sync_events"."sec_synced" = FALSE

Aggregate over an index-only scan on the same partial index. 0.704 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167257

Plan 
 Aggregate  (cost=3.14..3.16 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_projects_sync_events_on_id_where_not_sec_synced on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: 1741162842410594102

Time: 0.704 ms
  - planning: 0.643 ms
  - execution: 0.061 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
11. Check whether events not synced to sec remain (re-enqueue)
SELECT 1 AS one FROM "projects_sync_events" WHERE "projects_sync_events"."sec_synced" = FALSE LIMIT 1

Index-only scan on the same partial index, stops at the first row. 0.477 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167259

Plan 
 Limit  (cost=0.12..3.14 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_projects_sync_events_on_id_where_not_sec_synced on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=4) (actual time=0.006..0.007 rows=0 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 2977598492216925648

Time: 0.477 ms
  - planning: 0.439 ms
  - execution: 0.038 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
12. Mark the batch as synced to sec
UPDATE "projects_sync_events" SET "sec_synced" = TRUE WHERE "projects_sync_events"."id" IN (1, 2)

Primary key index scan, scoped by id IN (...). 0.680 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62125/commands/167261

Plan 
 Update on public.projects_sync_events  (cost=0.12..4.78 rows=0 width=0) (actual time=0.006..0.006 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.12..4.78 rows=1 width=7) (actual time=0.004..0.005 rows=0 loops=1)
         Index Cond: (projects_sync_events.id = ANY ('{1,2}'::bigint[]))
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -4451864209027536900

Time: 0.680 ms
  - planning: 0.596 ms
  - execution: 0.084 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
13. Find the first batch bound (EachBatch)
SELECT "projects_sync_events"."id" FROM "projects_sync_events" ORDER BY "projects_sync_events"."id" ASC LIMIT 1

Index-only scan on the primary key. 0.444 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168953

Plan 
Limit  (cost=0.12..3.14 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -5478686156897950664

Time: 0.444 ms
  - planning: 0.412 ms
  - execution: 0.032 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
14. Find the next batch bound (EachBatch)
SELECT "projects_sync_events"."id" FROM "projects_sync_events" WHERE "projects_sync_events"."id" >= 1 ORDER BY "projects_sync_events"."id" ASC LIMIT 1 OFFSET 1000

Primary-key scan; reads at most one batch of index entries. 0.514 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168955

Plan 
Limit  (cost=3.14..6.16 rows=1 width=8) (actual time=0.024..0.025 rows=0 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=1)
         Index Cond: (projects_sync_events.id >= 1)
         Heap Fetches: 0
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -6297640312932193069

Time: 0.514 ms
  - planning: 0.460 ms
  - execution: 0.054 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
15. Delete the fully synced rows of one batch (flag enabled)
DELETE FROM "projects_sync_events" WHERE "projects_sync_events"."id" >= 1 AND "projects_sync_events"."id" < 1001 AND "projects_sync_events"."ci_synced" = TRUE AND "projects_sync_events"."sec_synced" = TRUE

Index scan on the primary key, bounded to the batch's id range; the flag columns are only a filter, so no index on them is needed. 0.585 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168957

Plan 
Delete on public.projects_sync_events  (cost=0.12..3.14 rows=0 width=0) (actual time=0.018..0.019 rows=0 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=6) (actual time=0.017..0.017 rows=0 loops=1)
         Index Cond: ((projects_sync_events.id >= 1) AND (projects_sync_events.id < 1001))
         Filter: (projects_sync_events.ci_synced AND projects_sync_events.sec_synced)
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -5464361277934050222

Time: 0.585 ms
  - planning: 0.480 ms
  - execution: 0.105 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
16. Delete the synced rows of one batch (flag disabled)
DELETE FROM "projects_sync_events" WHERE "projects_sync_events"."id" >= 1 AND "projects_sync_events"."id" < 1001 AND "projects_sync_events"."ci_synced" = TRUE

Same primary-key index scan, filtering on ci_synced only. 0.507 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/63749/commands/168959

Plan 
Delete on public.projects_sync_events  (cost=0.12..3.14 rows=0 width=0) (actual time=0.015..0.016 rows=0 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=6) (actual time=0.014..0.015 rows=0 loops=1)
         Index Cond: ((projects_sync_events.id >= 1) AND (projects_sync_events.id < 1001))
         Filter: projects_sync_events.ci_synced
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -7779449094674887354

Time: 0.507 ms
  - planning: 0.424 ms
  - execution: 0.083 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

sec database (gitlab-production-sec)

Plans were run on the gitlab-production-sec Postgres.ai clone, twice each; the second (warm) run is shown. The tables are empty in production because the flag is off, so the plans show the insert path; a conflict takes the same primary-key arbiter path. The namespaces and projects reads in sync! are the same primary-key lookups the existing Ci::*Mirror.sync! methods do and are not new.

17. Security::NamespaceMirror.sync! upsert

Called from Namespaces::ProcessSyncEventsWorker for every namespace sync event when sync_sec_mirror_tables is enabled. Source: app/models/security/namespace_mirror.rb.

INSERT INTO "sec_namespace_mirrors" ("namespace_id","traversal_ids","organization_id")
VALUES (9970, '{9970}', 1)
ON CONFLICT ("namespace_id") DO UPDATE SET "traversal_ids"=excluded."traversal_ids","organization_id"=excluded."organization_id"
RETURNING "namespace_id"

Single-row insert with the primary key as conflict arbiter. Execution 0.5 ms, 59 buffer hits, no reads.

Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58760/commands/163794

Plan 
Insert on public.sec_namespace_mirrors  (cost=0.00..0.01 rows=1 width=48) (actual time=0.394..0.395 rows=1 loops=1)
   Buffers: shared hit=59
   WAL: records=6 fpi=0 bytes=554
   I/O Timings: read=0.000 write=0.000
   ->  Result  (cost=0.00..0.01 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=1)
         I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB'
Query ID: -3055173780859584508

Time: 0.768 ms
  - planning: 0.226 ms
  - execution: 0.542 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 59 (~472.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

18. Security::ProjectMirror.sync! upsert

Called from Projects::ProcessSyncEventsWorker for every project sync event when sync_sec_mirror_tables is enabled. Source: app/models/security/project_mirror.rb.

INSERT INTO "sec_project_mirrors" ("project_id","namespace_id","organization_id")
VALUES (278964, 9970, 1)
ON CONFLICT ("project_id") DO UPDATE SET "namespace_id"=excluded."namespace_id","organization_id"=excluded."organization_id"
RETURNING "project_id"

Single-row insert with the primary key as conflict arbiter. Execution 0.4 ms, 18 buffer hits, no reads.

Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58760/commands/163795

Plan 
Insert on public.sec_project_mirrors  (cost=0.00..0.01 rows=1 width=24) (actual time=0.188..0.189 rows=1 loops=1)
   Buffers: shared hit=18
   WAL: records=5 fpi=0 bytes=327
   I/O Timings: read=0.000 write=0.000
   ->  Result  (cost=0.00..0.01 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1)
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 8613458091875372358

Time: 0.525 ms
  - planning: 0.155 ms
  - execution: 0.370 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 18 (~144.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Rutger Wessels

Merge request reports