<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Collaborate/take over this issue](https://contributors.gitlab.com/manage-issue?action=work&projectId=278964&issueIid=596859) </details> <!--IssueSummary end--> ## Summary Swap the primary key of `vulnerability_identifiers` from `(id)` to `(id, partition_id)`. ## Current state - Primary key: `vulnerability_identifiers_pkey PRIMARY KEY (id)` - Required unique index already exists: `index_vulnerability_identifiers_on_id_partition_id UNIQUE (id, partition_id)` ## Prerequisites - All foreign keys referencing `vulnerability_identifiers` must already use the composite key `(partition_id, id)` — see #596858 - The unique index on `(id, partition_id)` must exist ✅ ### Prerequisite verification (checked against a GitLab.com thinclone on 2026-09-10) Both prerequisites from #596858 are confirmed live: - `fk_rails_be2e49e1d0_p FOREIGN KEY (partition_id, identifier_id) REFERENCES vulnerability_identifiers(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE` on `vulnerability_occurrence_identifiers` ✅ - `fk_rails_c8661a61eb_p FOREIGN KEY (partition_id, primary_identifier_id) REFERENCES vulnerability_identifiers(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE` on `vulnerability_occurrences` ✅ - No remaining single-column FKs pointing at `vulnerability_identifiers(id)` ✅ - `index_vulnerability_identifiers_on_id_partition_id UNIQUE (id, partition_id)` exists ✅ - `vulnerability_identifiers.partition_id` has zero `NULL` values (`count(*) WHERE partition_id IS NULL` = 0), so `swap_primary_key`'s `ADD PRIMARY KEY USING INDEX` will succeed - Table size: ~51 GB, ~115.8M rows — `swap_primary_key` avoids a full rewrite (it drops the old PK constraint and attaches the existing unique index as the new PK), but still needs `with_lock_retries` because dropping the PK constraint (`cascade: true`) takes an `ACCESS EXCLUSIVE` lock briefly to also drop dependent FK constraints on other tables **Prerequisites are fully satisfied. This issue is unblocked.** ## Implementation plan ### 1. Post-deployment migration Add `db/post_migrate/<timestamp>_swap_primary_key_for_vulnerability_identifiers.rb`, milestone `19.5` (current dev milestone is 19.4, ending 2026-09-11), modeled on `db/post_migrate/20260317032614_swap_primary_key_for_ci_build_needs.rb`: ```ruby # frozen_string_literal: true class SwapPrimaryKeyForVulnerabilityIdentifiers < Gitlab::Database::Migration[2.3] disable_ddl_transaction! milestone '19.5' TABLE_NAME = :vulnerability_identifiers PRIMARY_KEY = :vulnerability_identifiers_pkey OLD_INDEX_NAME = :vulnerability_identifiers_pkey_no_partitioning NEW_INDEX_NAME = :index_vulnerability_identifiers_on_id_partition_id def up swap_primary_key(TABLE_NAME, PRIMARY_KEY, NEW_INDEX_NAME) end def down add_concurrent_index(TABLE_NAME, :id, unique: true, name: OLD_INDEX_NAME) add_concurrent_index(TABLE_NAME, [:id, :partition_id], unique: true, name: NEW_INDEX_NAME) unswap_primary_key(TABLE_NAME, PRIMARY_KEY, OLD_INDEX_NAME) end end ``` Notes: - `NEW_INDEX_NAME` reuses the already-existing `index_vulnerability_identifiers_on_id_partition_id` index (no need to create it, per prerequisites). - `down` recreates a single-column unique index on `id` (name it distinctly, e.g. `vulnerability_identifiers_pkey_no_partitioning`, matching the `ci_build_needs` precedent) and re-creates the composite index before unswapping, since `swap_primary_key`'s `drop_constraint(..., cascade: true)` will drop the composite index along with the old PK. - `swap_primary_key` wraps the drop/add in `with_lock_retries(raise_on_exhaustion: true)`, appropriate for a 51 GB / ~116M row table with live FK traffic from `vulnerability_occurrences` and `vulnerability_occurrence_identifiers`. - Since `cascade: true` is used when dropping the old PK constraint, double check in review whether any other constraint metadata (e.g. replica identity) depends on it — none currently found for this table. ### 2. Model change In `ee/app/models/vulnerabilities/identifier.rb`, add directly under `include ShaAttribute` (or near `self.table_name`): ```ruby self.primary_key = :id ``` This is required because ActiveRecord doesn't support composite primary keys natively; `self.table_name` is already set in this model. ### 3. Regenerate `db/structure.sql` Run migrations locally and verify `db/structure.sql` reflects the swapped PK (constraint name should stay `vulnerability_identifiers_pkey`, but now covering `(id, partition_id)`), plus the corresponding `db/schema_migrations/<timestamp>` file. ### 4. Tests - Run `ee/spec/models/vulnerabilities/identifier_spec.rb` to confirm no regressions in model behavior (associations, validations, `search_identifier_name`, etc. all rely on default AR query-by-`id` semantics which remain valid since `self.primary_key = :id` is unchanged from an app-level perspective). - Add/verify a migration spec for the new post-deploy migration (following the pattern of specs for `SwapPrimaryKeyForCiBuildNeeds`, if one exists) asserting the PK swap and its `down` path. - No query plan prep needed for this step (no new SELECT/UPDATE/DELETE queries are introduced — this is a constraint-only DDL change), but flag the migration for database review given the table size, and mention the lock-retry behavior explicitly in the MR description per team convention. ### 5. Rollout considerations - This is a metadata-only operation (`ADD PRIMARY KEY USING INDEX` on an existing unique index, `DROP CONSTRAINT` on the old PK) — no table rewrite, so it should be fast, but still needs `with_lock_retries` due to the brief `ACCESS EXCLUSIVE` lock and its interaction with autovacuum/high-traffic writers. - Coordinate timing with reviewers/maintainers given production table size; reference the [list partitioning docs — Step 5: Swap primary key](https://docs.gitlab.com/development/database/partitioning/list#step-5---swap-primary-key) in the MR. ## Reference - [List partitioning docs — Step 5: Swap primary key](https://docs.gitlab.com/development/database/partitioning/list#step-5---swap-primary-key) - Example: `db/post_migrate/20260317032614_swap_primary_key_for_ci_build_needs.rb` - Prerequisite issue (composite FKs): #596858 ## Acceptance criteria - [ ] Primary key of `vulnerability_identifiers` is `(id, partition_id)` - [ ] `Vulnerabilities::Identifier` model explicitly sets `self.primary_key = :id` - [ ] All queries and associations still function correctly