Geo: Add UNIQUE indexes for job_artifact_registry table

Scott Murrayrequested to merge
606428-job-artifact-registry-unique-indexes into master

What does this MR do and why?

Geo secondary sites can accumulate duplicate rows in the job_artifact_registry table (same artifact_id, multiple registry rows). The table only has a non-unique index on artifact_id, so nothing at the database level prevents this. Duplicates come from a find-or-insert race: concurrent sync jobs (or a sync racing the Geo::RegistryConsistencyWorker bulk insert path) can both miss the existing row and both insert.

This MR follows the documented deduplication strategy with two post-deployment migrations on the Geo tracking database:

  1. DeduplicateJobArtifactRegistry - batched removal of duplicate rows, keeping the synced row per artifact_id when there is one (highest id as tiebreak), so a completed sync is never discarded in favour of a pending duplicate.
  2. AddUniqueIndexToJobArtifactRegistry - replaces the non-unique index on artifact_id with a unique index, making duplicates impossible from then on.

This is the same approach previously approved for project_repository_states in !227637 (merged).

Relates to #606428 (closed)

Changelog: fixed EE: true

Database considerations

Destructive action

The DeduplicateJobArtifactRegistry migration deletes duplicate rows from job_artifact_registry on the Geo tracking database.

  1. How the data could be recovered: registry rows are derived tracking metadata, not source data. Geo recreates any missing registry row automatically through Geo::RegistryConsistencyWorker, which runs every minute on the secondary. If the migration deleted too many rows, the worker would re-register the artifacts and they would re-sync. No primary data is touched.
  2. Approximate number of records affected: only rows with a duplicated artifact_id are deleted, and only the extra copies (synced rows preferred, highest id as tiebreak). Observed duplicate counts on affected sites are small - expected range is zero to a few hundred rows per secondary. GitLab.com is unaffected: Geo is not enabled there and the Geo tracking database does not exist on .com.
  3. User experience impact: none. Duplicate registry rows are redundant tracking metadata on a secondary site. Removing them does not affect the artifacts themselves or the primary site.

Race window between the two migrations

A duplicate could in theory be created after the dedup migration has scanned past an artifact_id and before the unique index build starts enforcing uniqueness (which happens at the start of CREATE INDEX CONCURRENTLY, before the build completes). On the largest known Geo sites (~100M registry rows) that window is bounded by the dedup migration runtime - minutes up to tens of minutes.

We considered the advisory-lock pattern from !223413 (merged) (prevent_concurrent_inserts + model uniqueness validation) to close this window and decided against it:

  • Duplicates historically appear at a rate of a handful per site over years. A collision landing inside a minutes-wide, one-off window is very unlikely.
  • A model uniqueness validation cannot prevent this race: concurrent transactions both pass the validation check before either commits - it is the same check-then-insert race that created the existing duplicates. And one of the two registry insert paths is bulk_insert! (RegistryConsistencyWorker), where the per-record advisory-lock workaround from !223413 (merged) is impractical. The unique index is the actual fix; the lock would be dead code one release later (!223413 (merged)'s lock was removed in !231718 (merged) at the first opportunity).

If the index build does hit a duplicate created in this window, the migration fails leaving an INVALID index behind; the fix is to re-run the dedup migration and then re-run this migration - add_concurrent_index drops the invalid index on retry (the spec covers this recovery path).

Migration runtime

The dedup migration is batched (each_batch, 10,000 rows per batch) with disable_ddl_transaction!, so each batch commits on its own and every query is index-driven (plans below). The largest known secondary holds ~300M registry rows (~10 minutes at the measured ~20 ms per batch); an extreme 1B row site works out to roughly 35-40 minutes of query time. The migration runs only on Geo secondaries' tracking databases (GitLab Dedicated DR sites and self-managed Geo installations), never on GitLab.com, and takes row locks only on the duplicate rows it deletes.

The index swap uses add_concurrent_index/remove_concurrent_index_by_name on a single integer column - no write locks.

Query plans

Database review notes:

  • Geo is not active on GitLab.com, so the Geo tracking database and the job_artifact_registry table do not exist in the postgres.ai snapshot. The table was created via DDL (schema from ee/db/geo/structure.sql, including the current non-unique index) and seeded manually.
  • Seeded 1,000,000 rows with states interleaved via modulo (85% synced, 5% failed, 10% pending) and timestamps decorrelated from the primary key via random(), plus 500 duplicate artifact_id rows scattered across the id range via (n * 1999) % 1000000 - not appended contiguously - so the plans are not flattered by PK/filter correlation. ANALYZE was run after seeding.

Query 1: batched duplicate detection (SELECT)

Executed once per batch of 10,000 rows by the dedup migration.

SELECT "job_artifact_registry"."artifact_id"
FROM "job_artifact_registry"
WHERE "job_artifact_registry"."artifact_id" IN
  (SELECT "job_artifact_registry"."artifact_id"
   FROM "job_artifact_registry"
   WHERE "job_artifact_registry"."id" >= 990001
     AND "job_artifact_registry"."id" < 1000001)
GROUP BY "job_artifact_registry"."artifact_id"
HAVING (COUNT(*) > 1);

Batch containing duplicates: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/57604/commands/162183 (18.7 ms, index scans only)

Batch with no duplicates (the common case): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/57604/commands/162184 (20.7 ms)

Query 2: duplicate removal (DELETE)

Executed only for batches where duplicates were found.

WITH duplicated_records AS MATERIALIZED (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY artifact_id ORDER BY (state = 2) DESC, id DESC) AS row_number
  FROM job_artifact_registry
  WHERE artifact_id IN (2000, 3999, 5998, 7997, 9996, 11995, 13994, 15993, 17992, 19991)
)
DELETE FROM job_artifact_registry
WHERE id IN (SELECT id FROM duplicated_records WHERE row_number > 1);

Plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/57492/commands/162024 (0.35 ms, 113 buffers, index-driven, deletes via CTE)

How to set up and validate locally

  1. On a GDK with Geo (or using the geo tracking test database), create duplicate registry rows in a Rails console:

    Geo::JobArtifactRegistry.create!(artifact_id: 1)
Geo::JobArtifactRegistry.create!(artifact_id: 1)
Geo::JobArtifactRegistry.create!(artifact_id: 2)

  2. Run the migrations:

    bin/rails db:migrate:geo

  3. Verify the duplicates are gone (highest id kept) and the unique index is in place:

    Geo::JobArtifactRegistry.where(artifact_id: 1).count # => 1
Geo::JobArtifactRegistry.create!(artifact_id: 1) # => raises RecordNotUnique

  4. Verify rollback restores the non-unique index:

    bin/rails db:migrate:down:geo VERSION=20260915155212
bin/rails db:migrate:down:geo VERSION=20260915155211

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.

Related to #606428 (closed)

Edited by Scott Murray

Merge request reports