## Summary List-partition `vulnerability_occurrence_identifiers` on `partition_id`, following the [list partitioning guide](https://docs.gitlab.com/development/database/partitioning/list/). Sibling effort to &21705 (`vulnerability_identifiers`). ## Why now `vulnerability_occurrence_identifiers` is **118 GB** (45 GB data + 73 GB indexes, ~593M rows, `gitlab_sec` schema) and growing. It must be partitioned alongside the rest of the vulnerability tables for the VAC scalability work (&19372). ## Lessons applied from &21705 - **One issue = one MR.** In &21705, Step 3 alone ballooned into 11 MRs over 4 months because async/sync migration pairs were bundled into single issues. Here every async→sync pair is split into separate issues targeting separate milestones (async must complete on GitLab.com before the sync MR can merge). - **All prerequisites verified up front** against a production thin clone (2026-09-07), no mid-flight scope discovery. - Progress is tracked in this description; issues close as their single MR merges, so PCLs only ever pause one small in-flight issue. ## Already done (no issues needed) - ✅ `partition_id bigint DEFAULT 1` column with **validated** NOT NULL check constraint (`check_aacd1ff57e`) - ✅ Composite FK `fk_rails_be2e49e1d0_p (partition_id, identifier_id) → vulnerability_identifiers(partition_id, id)` `ON UPDATE CASCADE ON DELETE CASCADE`, **validated**; old single-column FK removed (done in gitlab-org/gitlab#596858) - ✅ Supporting index `idx_vuln_occurrence_identifiers_on_partition_id_identifier_id` - ✅ **No inbound FKs and no loose-FK entries reference this table** — the entire "re-point referencing tables" phase from &21705 is not needed here ## Plan | Phase | Issue | Milestone | Status | |---|---|---|---| | A1 | #627924 — Prepare async unique index `(occurrence_id, identifier_id, partition_id)` | 19.5 | open | | B1 | #627925 — Prepare async unique index `(id, partition_id)` | 19.5 | open | | A2 | #627926 — Create unique index `(occurrence_id, identifier_id, partition_id)` synchronously | 19.6 | open | | B2 | #627927 — Create unique index `(id, partition_id)` synchronously | 19.6 | open | | A3 | #627928 — Drop old unique index and scope model uniqueness validation to partition | 19.6 | open | | C1 | #627929 — Swap primary key to `(id, partition_id)` | 19.7 | open | | D1 | #627930 — Prepare list partitioning constraint (async validation) | 19.7 | open | | D2 | #627931 — Validate list partitioning constraint synchronously | 19.8 | open | | D3 | #627932 — Convert table to first list partition of `p_vulnerability_occurrence_identifiers` | 19.9 | open | | E1 | #627933 — Register partitioned table, analyze, update db/docs | 19.9 | open | | E2 | #627934 — Update `Vulnerabilities::FindingIdentifier` for partitioned parent | 19.9 | open | | E3 (optional) | #627935 — Drop redundant `index_vulnerability_occurrence_identifiers_on_identifier_id` | backlog | open | ## Risks / special considerations - **Siphon publication**: the table is in `prd_sec_siphon_publication_1`. Coordinate with the Siphon/ClickHouse owners before D3 — attaching the table under a partitioned parent changes the replication source relation. - **Sharding-key trigger** `trigger_02450faab875` (backfills `project_id` from `vulnerability_occurrences`) must survive/be recreated on the parent in D3. - **Outbound FK** `fk_rails_e4ef6d027c (occurrence_id) → vulnerability_occurrences(id)`: `vulnerability_occurrences` is not yet partitioned (PK still `(id)`), so no composite key is needed; the conversion helper recreates this FK on the parent. - Every migration MR needs Database Lab query plans and a `db:gitlabcom-database-testing` run before database review.