Geo: Add UNIQUE indexes for package_file_registry table

What does this MR do and why?

Geo secondary sites can accumulate duplicate rows in the package_file_registry table (same package_file_id, multiple registry rows). The table only has a non-unique index on package_file_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. DeduplicatePackageFileRegistry - batched removal of duplicate rows, keeping the best row per package_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. AddUniqueIndexToPackageFileRegistry - replaces the non-unique index on package_file_id with a unique index, making duplicates impossible from then on.

The old index is named index_package_file_registry_on_repository_id despite being on package_file_id (a historical copy-paste artifact), so this MR also gives the replacement index an accurate name: index_package_file_registry_on_package_file_id_unique.

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

Changelog: fixed EE: true

Database considerations

Destructive action

The DeduplicatePackageFileRegistry migration deletes duplicate rows from package_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 package files and they would re-sync. No primary data is touched.
2. Approximate number of records affected: only rows with a duplicated package_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 package files 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 package_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 package_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 package_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 "package_file_registry"."package_file_id"
FROM "package_file_registry"
WHERE "package_file_registry"."package_file_id" IN
  (SELECT "package_file_registry"."package_file_id"
   FROM "package_file_registry"
   WHERE "package_file_registry"."id" >= 1000001
     AND "package_file_registry"."id" < 1001001)
GROUP BY "package_file_registry"."package_file_id"
HAVING (COUNT(*) > 1);

Batch containing duplicates (500 found): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/57492/commands/162066 (5.0 ms, index scans only)

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

4. Verify rollback restores the non-unique index:

   bin/rails db:migrate:down:geo VERSION=20260915172257
   bin/rails db:migrate:down:geo VERSION=20260915172237

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