Geo: Add UNIQUE indexes for file_registry table

Scott Murrayrequested to merge
606430-file-regsitry-unique-indexes into master

What does this MR do and why?

Geo secondary sites can accumulate duplicate rows in the file_registry table (same file_id, multiple registry rows). file_registry is the registry table for the Uploads replicable - the table name predates the per-type registry split (it dates from Geo 9.0) and is backed by Geo::UploadRegistry. The table only has a non-unique index on file_id, so nothing at the database level prevents duplicates. They 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. DeduplicateFileRegistry - batched removal of duplicate rows, keeping the best row per file_id: synced rows first, then verified rows, then highest id, so a completed sync or verification is never discarded in favour of a lesser duplicate.
  2. AddUniqueIndexToFileRegistry - replaces the non-unique index on file_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) and applied to job_artifact_registry in !246511 (merged).

Relates to #606430 (closed)

Changelog: fixed EE: true

Database considerations

Destructive action

The DeduplicateFileRegistry migration deletes duplicate rows from file_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 uploads and they would re-sync. No primary data is touched.
  2. Approximate number of records affected: only rows with a duplicated file_id are deleted, and only the extra copies (synced rows preferred, then verified rows, then highest id). 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 uploads 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 a file_id and before the unique index build starts enforcing uniqueness (which happens at the start of CREATE INDEX CONCURRENTLY, before the build completes). The window is bounded by the dedup migration runtime - minutes on the largest known Geo sites.

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). It completes well within post-deployment migration guidelines on typical Geo sites, and runs only on Geo secondaries' tracking databases (GitLab Dedicated DR sites and self-managed Geo installations), never on GitLab.com. It 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 file_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 file_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. The plans below were captured with a 1,000-row batch range; !246511 (merged) captured the identical query shape on job_artifact_registry at the 10,000-row batch size (dupes batch, clean batch - ~19-21 ms, still index-driven).

SELECT "file_registry"."file_id"
FROM "file_registry"
WHERE "file_registry"."file_id" IN
  (SELECT "file_registry"."file_id"
   FROM "file_registry"
   WHERE "file_registry"."id" >= 1000001
     AND "file_registry"."id" < 1001001)
GROUP BY "file_registry"."file_id"
HAVING (COUNT(*) > 1);

Batch containing duplicates (500 found): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/57492/commands/162056 (5.3 ms, 2,011 buffers, index scans only)

Batch with no duplicates (the common case): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/57492/commands/162057 (2.6 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 file_id ORDER BY (state = 2) DESC, (verification_state = 2) DESC, id DESC) AS row_number
  FROM file_registry
  WHERE file_id IN (2000, 3999, 5998, 7997, 9996, 11995, 13994, 15993, 17992, 19991)
)
DELETE FROM file_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/162058 (1.1 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::UploadRegistry.create!(file_id: 1)
Geo::UploadRegistry.create!(file_id: 1)
Geo::UploadRegistry.create!(file_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::UploadRegistry.where(file_id: 1).count # => 1
Geo::UploadRegistry.create!(file_id: 1) # => raises RecordNotUnique

  4. Verify rollback restores the non-unique index:

    bin/rails db:migrate:down:geo VERSION=20260915165916
bin/rails db:migrate:down:geo VERSION=20260915165857

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 #606430 (closed)

Edited by Scott Murray

Merge request reports