Prepare async index for vulnerability_occurrences composite FK

Michał Zającrequested to merge
596858-prepare-async-index-vulnerability-occurrences into master

What does this MR do and why?

Queues asynchronous creation of idx_vuln_occurrences_on_partition_id_primary_identifier_id on vulnerability_occurrences (partition_id, primary_identifier_id) via prepare_async_index.

The index backs the composite foreign key fk_rails_c8661a61eb_p introduced in !244081 (merged) (step 4 of the list partitioning guide for vulnerability_identifiers), covering the referential-integrity cascade lookups and satisfying the FK index coverage enforced by spec/db/schema_spec.rb.

vulnerability_occurrences is table_size: over_limit, so the index is created asynchronously. Schema change exception request: https://gitlab.com/gitlab-org/database-team/team-tasks/-/work_items/656

The migration performs no DDL itself — it only registers the index definition in postgres_async_indexes (no db/structure.sql change). On GitLab.com the index is then built by the scheduled async job as:

CREATE INDEX CONCURRENTLY idx_vuln_occurrences_on_partition_id_primary_identifier_id
ON vulnerability_occurrences USING btree (partition_id, primary_identifier_id);

The synchronous migration follows in #605110 (MR parked on this one).

References

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.

Merge request reports