Related to https://gitlab.com/gitlab-com/gl-infra/tenant-scale/organizations/organizations-feature-parity/-/work_items/34+. The following tables should have their indices updated to include the sharding key `project_id` : ```markdown # Summary: Problematic Unique Indexes The following **24 unique indexes** are missing their sharding key, which will cause issues in a sharded environment: ## Tables Sharded by `project_id` (22 problematic indexes) 1. **sbom_occurrences_vulnerabilities** - `i_sbom_occurrences_vulnerabilities_on_occ_id_and_vuln_id`: `(sbom_occurrence_id, vulnerability_id)` 2. **vulnerability_archived_records** - `index_vulnerability_archived_records_on_unique_attributes`: `(vulnerability_identifier, date)` 3. **vulnerability_external_issue_links** (2 indexes) - `idx_vulnerability_ext_issue_links_on_vulne_id_and_ext_issue`: `(vulnerability_id, external_type, external_project_key, external_issue_key)` - `idx_vulnerability_ext_issue_links_on_vulne_id_and_link_type`: `(vulnerability_id, link_type)` 4. **vulnerability_finding_evidences** - `finding_evidences_on_unique_vulnerability_occurrence_id`: `(vulnerability_occurrence_id)` 5. **vulnerability_finding_links** (2 indexes) - `finding_link_name_url_idx`: `(vulnerability_occurrence_id, name, url)` - `finding_link_url_idx`: `(vulnerability_occurrence_id, url)` 6. **vulnerability_finding_risk_scores** - `index_vulnerability_risk_scores_on_vulnerability_finding`: `(finding_id)` 7. **vulnerability_finding_signatures** - `idx_vuln_signatures_uniqueness_signature_sha`: `(finding_id, algorithm_type, signature_sha)` 8. **vulnerability_findings_remediations** - `index_vulnerability_findings_remediations_on_unique_keys`: `(vulnerability_occurrence_id, vulnerability_remediation_id)` 9. **vulnerability_flags** - `index_vulnerability_flags_on_unique_columns`: `(vulnerability_occurrence_id, flag_type, origin)` 10. **vulnerability_issue_links** (2 indexes) - `idx_vulnerability_issue_links_on_vulnerability_id_and_issue_id`: `(vulnerability_id, issue_id)` - `idx_vulnerability_issue_links_on_vulnerability_id_and_link_type`: `(vulnerability_id, link_type)` 11. **vulnerability_management_policy_rules** - `index_vuln_mgmt_policy_rules_on_unique_policy_rule_index`: `(security_policy_id, rule_index)` 12. **vulnerability_merge_request_links** - `unique_vuln_merge_request_link_vuln_id_and_mr_id`: `(vulnerability_id, merge_request_id)` 13. **vulnerability_occurrence_identifiers** - `index_vulnerability_occurrence_identifiers_on_unique_keys`: `(occurrence_id, identifier_id)` 14. **vulnerability_occurrences** - `index_vuln_findings_on_uuid_including_vuln_id_1`: `(uuid)` 15. **vulnerability_reads** (2 indexes) - `index_vulnerability_reads_on_uuid`: `(uuid)` - `index_vulnerability_reads_on_vulnerability_id`: `(vulnerability_id)` 16. **vulnerability_user_mentions** (3 indexes) - `index_vulnerability_user_mentions_on_note_id`: `(note_id)` - `index_vulns_user_mentions_on_vulnerability_id`: `(vulnerability_id)` - `index_vulns_user_mentions_on_vulnerability_id_and_note_id`: `(vulnerability_id, note_id)` ## Tables Sharded by `namespace_id` (2 problematic indexes) 17. **vulnerability_namespace_historical_statistics** - `index_vuln_namespace_historical_statistics_traversal_ids_date`: `(traversal_ids, date)` 18. **vulnerability_namespace_statistics** - `index_vuln_namespace_statistics_btree_traversal_ids`: `(traversal_ids)` ``` Example MR: - https://gitlab.com/gitlab-org/gitlab/-/merge_requests/208594 - https://gitlab.com/gitlab-org/gitlab/-/merge_requests/214499 ## Updated status on non-compliant indexes: Here is the analysis so far, we may need to update this based on how strict we are about FK relation in few tables: | # | Table | Index | Columns | Sharding Key | Needs sharding key? | Status | Reasoning | |---|---|---|---|---|---|---|---| | 1 | sbom_component_versions | `index_sbom_component_versions_on_component_id_and_version` | component_id, version | organization_id | **Yes** | 🔄 In review https://gitlab.com/gitlab-org/gitlab/-/merge_requests/237371 | `version` is a string, not globally unique | | 2 | sbom_occurrences | `index_sbom_occurrences_on_uuid` | uuid | project_id | **No** | Not required | `uuid` is a checksum that includes `project_id`, already org-scoped. UUID formula: https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/sbom/occurrence_uuid.rb#L6 | | 3 | sbom_occurrences_vulnerabilities | `i_sbom_occurrences_vulnerabilities_on_occ_id_and_vuln_id` | sbom_occurrence_id, vulnerability_id | project_id | **Yes** (done) | ✅ Merged ([!233025](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/233025)) | Sharding key added in 19.0 | | 4 | security_scans | `idx_security_scans_on_build_and_scan_type` | build_id, scan_type | project_id | **No** | Not required | `build_id` (PK) is globally unique; the pair is unique per-org | | 5 | vulnerability_archived_records | `index_vulnerability_archived_records_on_unique_attributes` | vulnerability_identifier, date | project_id | **Yes** (done) | ✅ Merged ([!233500](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/233500)) | `vulnerability_identifier` is a free-form attribute, not globally unique; sharding key needed | | 6 | vulnerability_external_issue_links | `idx_vulnerability_ext_issue_links_on_vulne_id_and_ext_issue` | vulnerability_id, external_type, external_project_key, external_issue_key | project_id | **No** | Closed because Not required ([!234887](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/234887) - Draft) | `vulnerability_id` is unique per project; the rest narrows further. **MR may not be needed based on this analysis** | | 7 | vulnerability_external_issue_links | `idx_vulnerability_ext_issue_links_on_vulne_id_and_link_type` | vulnerability_id, link_type | project_id | **No** | Closed because Not required ([!234887](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/234887) - Draft) | `vulnerability_id` is unique per project; the rest narrows further. **MR may not be needed based on this analysis** | | 8 | vulnerability_finding_due_dates | `index_vulnerability_finding_due_dates_on_occurrence_id` | vulnerability_occurrence_id | project_id | **No** | Not required | `vulnerability_occurrence_id` is a PK, unique per-project | | 9 | vulnerability_finding_evidences | `finding_evidences_on_unique_vulnerability_occurrence_id` | vulnerability_occurrence_id | project_id | **No** | Not required | `vulnerability_occurrence_id` is a PK | | 10 | vulnerability_finding_links | `finding_link_occurrence_id_name_url_idx` | vulnerability_occurrence_id, name, url | project_id | **No** | Not required | occurrence_id pins it to a project; name/url just narrow | | 11 | vulnerability_finding_risk_scores | `index_vulnerability_risk_scores_on_vulnerability_finding` | finding_id | project_id | **No** | Not required | finding_id is unique per-project | | 12 | vulnerability_finding_signatures | `idx_vuln_signatures_uniqueness_signature_sha` | finding_id, algorithm_type, signature_sha | project_id | **No** | Not required | finding_id anchors to project | | 13 | vulnerability_findings_remediations | `index_vulnerability_findings_remediations_on_unique_keys` | vulnerability_occurrence_id, vulnerability_remediation_id | project_id | **No** | Not required | both FKs unique per-project | | 14 | vulnerability_flags | `index_vulnerability_flags_on_unique_columns` | vulnerability_occurrence_id, flag_type, origin | project_id | **No** | Not required | occurrence_id pins to project | | 15 | vulnerability_identifiers | `index_vulnerability_identifiers_on_id_partition_id` | id, partition_id | project_id | **No** | Not required | PK + partition_id is globally unique | | 16 | vulnerability_issue_links | `idx_vulnerability_issue_links_on_vulnerability_id_and_issue_id` | vulnerability_id, issue_id | project_id | **No** | Not required | vulnerability_id unique per-project | | 17 | vulnerability_issue_links | `idx_vulnerability_issue_links_on_vulnerability_id_and_link_type` | vulnerability_id, link_type | project_id | **No** | Not required | vulnerability_id unique per-project | | 18 | vulnerability_merge_request_links | `unique_vuln_merge_request_link_vuln_id_and_mr_id` | vulnerability_id, merge_request_id | project_id | **No** | Not required | vulnerability_id unique per-project | | 19 | vulnerability_namespace_historical_statistics | `index_vuln_namespace_historical_statistics_traversal_ids_date` | traversal_ids, date | namespace_id | **Yes** | 🔄 In review https://gitlab.com/gitlab-org/gitlab/-/merge_requests/237590 | `traversal_ids` is an array; namespace_id makes intent explicit and protects against org-move semantics | | 20 | vulnerability_namespace_statistics | `index_vuln_namespace_statistics_btree_traversal_ids` | traversal_ids | namespace_id | **Yes** | 🔄 In review https://gitlab.com/gitlab-org/gitlab/-/merge_requests/238953 | Same as above | | 21 | vulnerability_occurrence_identifiers | `index_vulnerability_occurrence_identifiers_on_unique_keys` | occurrence_id, identifier_id | project_id | **No** | Not required | Both FKs unique per-project | | 22 | vulnerability_occurrences | `index_vuln_findings_on_uuid_including_vuln_id_1` | uuid | project_id | **No** | Not required | UUID is a checksum that _includes_ project_id (Brian's point) | | 23 | vulnerability_reads | `index_vulnerability_reads_on_uuid` | uuid | project_id | **No** | Not required | Same as above | | 24 | vulnerability_reads | `index_vulnerability_reads_on_vulnerability_id` | vulnerability_id | project_id | **No** | Not required | vulnerability_id unique per-project | | 25 | vulnerability_reads | `index_vulnerability_reads_on_vulnerability_occurrence_id_unique` | vulnerability_occurrence_id | project_id | **No** | Not required | occurrence_id unique per-project | | 26 | vulnerability_user_mentions | `index_vulnerability_user_mentions_on_note_id` | note_id | project_id | **Maybe** | 🔄 In review https://gitlab.com/gitlab-org/gitlab/-/merge_requests/238961 | `note_id` is globally unique (PK on `notes`), so technically safe. But `notes` is polymorphic; **safer to add** | | 27 | vulnerability_user_mentions | `index_vulns_user_mentions_on_vulnerability_id` | vulnerability_id | project_id | **No** | Not required | vulnerability_id unique per-project | | 28 | vulnerability_user_mentions | `index_vulns_user_mentions_on_vulnerability_id_and_note_id` | vulnerability_id, note_id | project_id | **No** | Not required | vulnerability_id unique per-project |