Prepare async unique index on vulnerability_occurrence_identifiers

What does this MR do and why?

Adds a post-deployment migration that queues asynchronous creation of a unique index on vulnerability_occurrence_identifiers (id, partition_id).

The index (index_vulnerability_occurrence_identifiers_on_id_partition_id) is required before the table's primary key can be swapped to (id, partition_id) as part of partitioning vulnerability_occurrence_identifiers. The same approach was used for vulnerability_identifiers.

The table is large. A thin clone of the sec database measured on 2026-09-29 had 47.18 GiB of heap data, 78.26 GiB of existing indexes, and approximately 623 million rows according to pg_class.reltuples. The index is queued for background creation by the async index worker rather than created inline. A follow-up migration will promote it synchronously: #627927.

RuboCop's Migration/PreventIndexCreation cop fires because the table is flagged table_size: large. The index is needed for the primary key swap and will replace the current primary key index. The exception request is awaiting approval; the disable comment links to this request.

No db/structure.sql change is included — preparing an async index only inserts a row into postgres_async_indexes.

No application code, feature flag, or user-facing changes.

References

• Related to #627925 (closed)
• Epic: &23499
• Follow-up (synchronous creation): #627927

Screenshots or screen recordings

Not applicable — no UI changes.

How to set up and validate locally

1. Run the migration:

   bundle exec rails db:migrate

2. Confirm a row is inserted into postgres_async_indexes for index_vulnerability_occurrence_identifiers_on_id_partition_id:

   SELECT name, definition FROM postgres_async_indexes
   WHERE name = 'index_vulnerability_occurrence_identifiers_on_id_partition_id';

   Expected definition:

   CREATE UNIQUE INDEX CONCURRENTLY "index_vulnerability_occurrence_identifiers_on_id_partition_id"
   ON "vulnerability_occurrence_identifiers" ("id", "partition_id")

3. Roll back and confirm the row is removed:

   bundle exec rails db:migrate:down VERSION=20260923154800

4. Re-apply to leave the environment in the migrated state:

   bundle exec rails db:migrate

Database validation

The database migration testing pipeline passed for commit 6eacbb9c9fee030d4f9636be79d9ab33e5cc3d4a on all three databases. The bot report shows migration runtimes of 5.5 s on main, 7.5 s on CI, and 6.8 s on sec. Note: this report is tied to commit 6eacbb9c9fee030d4f9636be79d9ab33e5cc3d4a; the bot invalidated it after subsequent commits to this MR, so it is not a fresh result against the current HEAD, and it only validates the async-index registration migration, not the full index build on the populated sec table.

The full CREATE UNIQUE INDEX CONCURRENTLY build has now been measured against a thin clone of the populated sec table (2026-09-29, 15:19:53–15:28:33 UTC). See the "Thin-clone index build evidence" details below for the exact SQL, environment, and verification output. This clone timing reflects a single isolated run and is not a production estimate.

The schema-change exception request has been opened and is awaiting Database team approval.

Migration output (up / down / up)

Up:

main: == 20260923154800 PrepareAsyncVoiIdPartitionIdUniqueIndex: migrating
main: -- index_exists?(:vulnerability_occurrence_identifiers, [:id, :partition_id], {:unique=>true, :name=>"index_vulnerability_occurrence_identifiers_on_id_partition_id", :algorithm=>:concurrently})
main: -- add_index_options(:vulnerability_occurrence_identifiers, [:id, :partition_id], {:unique=>true, :name=>"index_vulnerability_occurrence_identifiers_on_id_partition_id", :algorithm=>:concurrently})
main: == 20260923154800 PrepareAsyncVoiIdPartitionIdUniqueIndex: migrated

Down:

main: == 20260923154800 PrepareAsyncVoiIdPartitionIdUniqueIndex: reverting
main: == 20260923154800 PrepareAsyncVoiIdPartitionIdUniqueIndex: reverted

Up (second time): same as first up run above.

Thin-clone index build evidence (2026-09-29)

SQL executed:

CREATE UNIQUE INDEX CONCURRENTLY index_vulnerability_occurrence_identifiers_on_id_partition_id
    ON public.vulnerability_occurrence_identifiers (id, partition_id);

Build timing:

• Started: 2026-09-29 15:19:53.483298+00 UTC
• Completed: 2026-09-29 15:28:33.368029+00 UTC
• Elapsed: 519275.012 ms (8 m 39.275 s)

Environment:

• PostgreSQL 18.4
• maintenance_work_mem = 2GB
• max_parallel_maintenance_workers = 2

Pre-build sizes:

• Estimated rows: 622,910,528 (from pg_class.reltuples; an estimate, not an exact row count)
• Heap size: 50,658,082,816 bytes (~47.18 GiB)
• Existing indexes total: 84,032,380,928 bytes (~78.26 GiB)

Post-build verification (checked after reconnecting once the build completed):

index	unique	valid	ready	size
index_vulnerability_occurrence_identifiers_on_id_partition_id	t	t	t	19,706,421,248 bytes (~18.35 GiB)

• Old primary key index (vulnerability_occurrence_identifiers_pkey): 16,848,158,720 bytes (~15.69 GiB), unchanged and still present. No primary key swap has been performed on the clone.
• Net storage difference once the old primary key index is eventually dropped: 2,858,262,528 bytes (~2.66 GiB). Until then, the new index adds its full ~18.35 GiB alongside the existing indexes while both coexist.
• These index-size figures exclude transient sort/WAL space used during the build; they are not a measurement of peak storage usage during the CREATE INDEX CONCURRENTLY operation.

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.