Add index for vulnerability_occurrences composite FK

Michał Zającrequested to merge
596858-add-index-vulnerability-occurrences-partition-fk into master

What does this MR do and why?

Adds idx_vuln_occurrences_on_partition_id_primary_identifier_id on vulnerability_occurrences (partition_id, primary_identifier_id) synchronously, following its asynchronous creation queued in the parent MR.

The index backs the composite foreign key fk_rails_c8661a61eb_p introduced in !244081 (merged) (step 4 of list partitioning vulnerability_identifiers). Closes #605110

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

⚠️ Merge precondition: this MR is blocked on the prepare_async_index MR and must be marked ready only after the index is confirmed created and valid on GitLab.com (async index jobs run on weekends; verify via Database Lab that the index exists in pg_indexes and is not INVALID). Merging earlier would make add_concurrent_index build the index synchronously during the post-deploy pipeline on this over_limit table.

Schema change exception request: https://gitlab.com/gitlab-org/database-team/team-tasks/-/work_items/656

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