Track per-database sync state on the sync event tables (!258480) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Today a sync event row is deleted as soon as the CI mirror tables (ci_namespace_mirrors, ci_project_mirrors) are updated. A new second consumer, the sec mirror tables (sec_namespace_mirrors, sec_project_mirrors), needs the row too, but it is already gone by the time it can read it.

This MR adds two boolean columns, ci_synced and sec_synced, to the namespaces_sync_events and projects_sync_events tables. Each consumer will mark its own column when done, and a row is only deleted once every active consumer has synced it.

Each consumer reads its unprocessed rows with a filter on its own column. A partial index per column (WHERE ci_synced = false, WHERE sec_synced = false) keeps that read, the existence check, and the count estimate cheap even when the other consumer has a backlog.

For now only the CI mirrors are wired up, so behavior does not change apart from one extra UPDATE per batch. The sec consumer follows in a later MR, which will be rebased on this one.

References

Related to issue: #627115
Follow-up MR: !255374 (merged)

How to set up and validate locally

Run these steps in a Rails console, with Sidekiq running in GDK.

Create a group and a project:

user = User.first
group = Groups::CreateService.new(user, name: 'Sync test', path: 'sync-test', organization_id: user.organization_id).execute[:group]
project = Projects::CreateService.new(user, name: 'sync-test-project', namespace_id: group.id).execute

Wait a few seconds, then check that the events were consumed and the CI mirrors were written:

Namespaces::SyncEvent.count # => 0
Projects::SyncEvent.count   # => 0
Ci::NamespaceMirror.find_by(namespace_id: group.id)
Ci::ProjectMirror.find_by(project_id: project.id)

Optional: create another group, then run the service by hand and check the result:
```
Ci::ProcessSyncEventsService.new(Namespaces::SyncEvent, Ci::NamespaceMirror).execute
```
Check the returned hash, and that the row is gone.

Query Plans

Plans were run on the gitlab-production-main clone after adding the two columns and the four partial indexes there (ALTER TABLE, CREATE INDEX), twice each; the second (warm) run is shown. Both tables are queues that are drained within seconds, so they are almost empty in production. The batch read, the count estimate, and the existence check use the new partial index on (id) WHERE ci_synced = false, so they do not depend on how many rows the other consumer still has to process. UPDATE and DELETE are scoped by id IN (...) and use the primary key.

`namespaces_sync_events`

1. Read the next batch of unprocessed events

SELECT "namespaces_sync_events".* FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."ci_synced" = FALSE ORDER BY "namespaces_sync_events"."id" ASC LIMIT 1000

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

Plan

Limit  (cost=0.12..3.14 rows=1 width=18) (actual time=0.018..0.019 rows=1 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_namespaces_sync_events_on_id_where_not_ci_synced on public.namespaces_sync_events  (cost=0.12..3.14 rows=1 width=18) (actual time=0.017..0.017 rows=1 loops=1)
         Buffers: shared hit=2
         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: -3756212606454579229

Time: 0.486 ms
  - planning: 0.441 ms
  - execution: 0.045 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2 (~16.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 unprocessed events

SELECT COALESCE(MAX(id) - MIN(id) + 1, 0) AS upper_bound_count FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."ci_synced" = FALSE

MIN and MAX via index-only scans on the partial index. 0.642 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59973/commands/165013

Plan

Aggregate  (cost=3.14..3.16 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)
   Buffers: shared hit=3
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_namespaces_sync_events_on_id_where_not_ci_synced on public.namespaces_sync_events  (cost=0.12..3.14 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)
         Heap Fetches: 1
         Buffers: shared hit=3
         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: -6046541400279563776

Time: 0.642 ms
  - planning: 0.564 ms
  - execution: 0.078 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

3. Check whether unprocessed events remain (re-enqueue)

SELECT 1 AS one FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."ci_synced" = FALSE LIMIT 1

Index-only scan on the partial index, first row only. 0.572 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59973/commands/165014

Plan

Limit  (cost=0.12..3.14 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1)
   Buffers: shared hit=3
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_namespaces_sync_events_on_id_where_not_ci_synced on public.namespaces_sync_events  (cost=0.12..3.14 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)
         Heap Fetches: 1
         Buffers: shared hit=3
         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: -871890293990581537

Time: 0.572 ms
  - planning: 0.519 ms
  - execution: 0.053 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.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 ci

UPDATE "namespaces_sync_events" SET "ci_synced" = TRUE WHERE "namespaces_sync_events"."id" IN (1, 2)

0.571 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59110/commands/164279

Plan

Update on public.namespaces_sync_events  (cost=0.25..5.03 rows=0 width=0) (actual time=0.022..0.023 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using namespaces_sync_events_pkey on public.namespaces_sync_events  (cost=0.25..5.03 rows=1 width=7) (actual time=0.021..0.021 rows=0 loops=1)
         Index Cond: (namespaces_sync_events.id = ANY ('{1,2}'::bigint[]))
         Buffers: shared hit=2
         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: -8593265959376103734

Time: 0.571 ms
  - planning: 0.475 ms
  - execution: 0.096 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

5. Delete the fully synced rows of the batch

DELETE FROM "namespaces_sync_events" WHERE "namespaces_sync_events"."id" IN (1, 2) AND "namespaces_sync_events"."ci_synced" = TRUE

0.613 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59110/commands/164281

Plan

Delete on public.namespaces_sync_events  (cost=0.25..5.03 rows=0 width=0) (actual time=0.021..0.022 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using namespaces_sync_events_pkey on public.namespaces_sync_events  (cost=0.25..5.03 rows=1 width=6) (actual time=0.020..0.021 rows=0 loops=1)
         Index Cond: (namespaces_sync_events.id = ANY ('{1,2}'::bigint[]))
         Filter: namespaces_sync_events.ci_synced
         Buffers: shared hit=2
         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: 6020704159070926491

Time: 0.613 ms
  - planning: 0.511 ms
  - execution: 0.102 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

`projects_sync_events`

6. Read the next batch of unprocessed events

SELECT "projects_sync_events".* FROM "projects_sync_events" WHERE "projects_sync_events"."ci_synced" = FALSE ORDER BY "projects_sync_events"."id" ASC LIMIT 1000

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

Plan

Limit  (cost=0.12..3.14 rows=1 width=18) (actual time=0.004..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_ci_synced on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=18) (actual time=0.003..0.004 rows=0 loops=1)
         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: 6856082832041413991

Time: 0.506 ms
  - planning: 0.475 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

7. Estimate the number of unprocessed events

SELECT COALESCE(MAX(id) - MIN(id) + 1, 0) AS upper_bound_count FROM "projects_sync_events" WHERE "projects_sync_events"."ci_synced" = FALSE

MIN and MAX via index-only scans on the partial index. 0.628 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59973/commands/165020

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_ci_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: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: -9163460011699997053

Time: 0.628 ms
  - planning: 0.567 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

8. Check whether unprocessed events remain (re-enqueue)

SELECT 1 AS one FROM "projects_sync_events" WHERE "projects_sync_events"."ci_synced" = FALSE LIMIT 1

Index-only scan on the partial index, first row only. 0.507 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59973/commands/165021

Plan

Limit  (cost=0.12..3.14 rows=1 width=4) (actual time=0.009..0.010 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_ci_synced on public.projects_sync_events  (cost=0.12..3.14 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=1)
         Heap Fetches: 0
         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: 3467512066456079028

Time: 0.507 ms
  - planning: 0.468 ms
  - execution: 0.039 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

9. Mark the batch as synced to ci

UPDATE "projects_sync_events" SET "ci_synced" = TRUE WHERE "projects_sync_events"."id" IN (1, 2)

0.761 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59110/commands/164289

Plan

Update on public.projects_sync_events  (cost=0.25..5.03 rows=0 width=0) (actual time=0.074..0.075 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.25..5.03 rows=1 width=7) (actual time=0.073..0.073 rows=0 loops=1)
         Index Cond: (projects_sync_events.id = ANY ('{1,2}'::bigint[]))
         Buffers: shared hit=2
         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: -1627970164861750191

Time: 0.761 ms
  - planning: 0.588 ms
  - execution: 0.173 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

10. Delete the fully synced rows of the batch

DELETE FROM "projects_sync_events" WHERE "projects_sync_events"."id" IN (1, 2) AND "projects_sync_events"."ci_synced" = TRUE

0.507 ms total. Postgres.ai: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59110/commands/164291

Plan

Delete on public.projects_sync_events  (cost=0.25..5.03 rows=0 width=0) (actual time=0.021..0.022 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using projects_sync_events_pkey on public.projects_sync_events  (cost=0.25..5.03 rows=1 width=6) (actual time=0.020..0.020 rows=0 loops=1)
         Index Cond: (projects_sync_events.id = ANY ('{1,2}'::bigint[]))
         Filter: projects_sync_events.ci_synced
         Buffers: shared hit=2
         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: 7912613759067143714

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: 2 (~16.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.

🤖 Generated with Claude Code

Edited Oct 01, 2026 by Rutger Wessels

Track per-database sync state on the sync event tables

What does this MR do and why?

References

How to set up and validate locally

Query Plans

namespaces_sync_events

1. Read the next batch of unprocessed events

2. Estimate the number of unprocessed events

3. Check whether unprocessed events remain (re-enqueue)

4. Mark the batch as synced to ci

5. Delete the fully synced rows of the batch

projects_sync_events

6. Read the next batch of unprocessed events

7. Estimate the number of unprocessed events

8. Check whether unprocessed events remain (re-enqueue)

9. Mark the batch as synced to ci

10. Delete the fully synced rows of the batch

MR acceptance checklist

Merge request reports

`namespaces_sync_events`

`projects_sync_events`