Secure and Govern DB usage has been expanding at a significant rate, and certain upcoming and highly desired features such a [tracking vulnerabilities across multiple branches](https://gitlab.com/groups/gitlab-org/-/epics/3430 "Track Vulnerabilities in locations other than the default branch") are likely to significantly boost our DB demands. To ensure platform stability for GitLab.com, this information needs to be decomposed to a separate DB. At time of upgrading this issue to an epic, the following metrics were identified: In a 24 hour period the following feature sets accounted for percentage of total update transactions (Updated `2024-06-13`): * Vulnerabilities related tables are [9.2%](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22bw5%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%28sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28vulnerabilit%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28vulnerabilit%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28vulnerabilit%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%5Cn%2F%5Cn%28sum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-1h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) * Security related tables are [3.9%](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22bw5%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%28sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28security%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28security%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28security%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%5Cn%2F%5Cn%28sum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-1h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) * SBOM are [10.1%](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22bw5%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%28sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28sbom%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28sbom%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28sbom%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%5Cn%2F%5Cn%28sum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-1h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) * PackageMetadata are [negligible](https://gitlab.com/gitlab-org/gitlab/-/issues/478066 "Evaluate inclusion of PackageMetadata (pm_*)") The entire domain effectively accounting for [24%](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22bw5%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%28sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28vulnerabilit%7Csecurity%7Csbom%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28vulnerabilit%7Csecurity%7Csbom%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28vulnerabilit%7Csecurity%7Csbom%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%5Cn%2F%5Cn%28sum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-90d%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) (as high as 28% in the past 90 days on `2024-07-15`) of primary database updates. More granular: * sbom_occurrences: [4.3%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_occurrences).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_occurrences).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_occurrences).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * sbom_component_versions: [4%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_component_versions).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_component_versions).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_component_versions).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * sbom_components: [3.6%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_components).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_components).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_components).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * security_findings: [2.6%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(security_findings).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(security_findings).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(security_findings).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_occurrence_identifiers: [2.1%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrence_identifiers).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrence_identifiers).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrence_identifiers).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerabilities: [2%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerabilities).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerabilities).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerabilities).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_occurences: [1.3%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrences).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrences).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrences).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_occurrence_pipelines: [1.1%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrence_pipelines).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrence_pipelines).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_occurrence_pipelines).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_identifiers: [0.8%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_identifiers).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_identifiers).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_identifiers).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_reads: [0.7%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_reads).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_reads).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_reads).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * sbom_sources: [0.3%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_sources).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_sources).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_sources).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_finding_links: [0.3%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_links).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_links).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_links).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * security_scans: [0.2%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(security_scans).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(security_scans).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(security_scans).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_historical_statistics: [0.1%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_historical_statistics).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_historical_statistics).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_historical_statistics).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_statistics: [0.1%](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_statistics).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_statistics).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_statistics).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_feedback: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_feedback).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_feedback).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_feedback).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_finding_evidence: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_evidences).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_evidences).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_evidences).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_finding_signatures: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_signatures).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_signatures).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_finding_signatures).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_finding_remediations: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_findings_remediations).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_findings_remediations).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_findings_remediations).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_remediations: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_remediations).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_remediations).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_remediations).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_scanners: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_scanners).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_scanners).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_scanners).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_flags: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_flags).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_flags).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_flags).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_state_transitions: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_state_transitions).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_state_transitions).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_state_transitions).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * vulnerability_user_mentions: [nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_user_mentions).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_user_mentions).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(vulnerability_user_mentions).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * sbom_occurrence_vulnerabilities:[ nominal](https://thanos-query.ops.gitlab.net/graph?g0.expr=(sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_occurrences_vulnerabilities).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_occurrences_vulnerabilities).\*%22%2C%20type%3D%22patroni%22%7D)%20%2B%0Asum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(sbom_occurrences_vulnerabilities).\*%22%2C%20type%3D%22patroni%22%7D))%0A%2F%0A(sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_ins%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20%2B%20sum(pg_stat_user_tables_n_tup_del%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D))&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) * sbom_source_packages: [nominal](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22bw5%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%28sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28sbom_source_packages%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28sbom_source_packages%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%5Cnsum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20relname%3D\~%5C%22%28sbom_source_packages%29.%2A%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%5Cn%2F%5Cn%28sum%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%20%2B%20sum%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-1h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) Given this, we wish to begin decomposing these tables, targeting high traffic tables early in order to benefit from the reduced database load sooner and enable us to begin expanding scope for our new features. ## Implementation Plan Given the above statistics, the intended process for denormalisation is to identify what features are the highest database contenders and aim to denormalise them first without producing too much throw-away work. Some aspects, such as the mirroring of namespace and project information will likely be shared between different arms of the decomposition process, while other aspects will require effective syncing of entire arms of functionality in order to be feasible. <details> <summary>Blueprint for this process for the security_findings finder and model</summary> * Implement decomposed table and model for: * Security::Finding. * Security::Scans to facilitate partition recognition correctly. * Vulnerabilities::Scanner * Vulnerabilities::StateTransition * Vulnerabilities::IssueLink * Vulnerabilities::MergeRequestLink * CI::ProjectMirror * CI::NamespaceMirror * Note: No other models should be necessary for this ultra MVP approach yet. We can add them on piecemeal afterwards. * Append VulnerabilityDecompositionTask to the Vulnerability Ingestion Pipeline (or even enqueue a separate job entirely). This task will cycle through the data written during ingestion, and rewrite it to the decomposed database. This is to avoid slowing down the ingestion pipeline for existing user while we begin implementing this. * Modify the Security::FindingsFinder to not use \`pipeline.security_findings\` to avoid cross joining from primary database. We should be able to do this query using just the pipeline id stored in the Sec DB. * Implement Eventstore “Changed” events for the following models, which includes the type of change (update, delete), to which we subscribe a decomposition sync job: * Security::Finding. * Security::Scans. * Vulnerabilities::Scanner * Vulnerabilities::StateTransition * Vulnerabilities::IssueLink * Vulnerabilities::MergeRequestLink * Implement Background Migrations to backfill the following models to the decomposed database: * Security::Scans to facilitate partition recognition correctly. * Vulnerabilities::Scanner * Vulnerabilities::StateTransition * Vulnerabilities::IssueLink * Vulnerabilities::MergeRequestLink * CI::ProjectMirror * CI::NamespaceMirror * (We can potentially skip syncing security_findings, as they are discarded on a 3 month basis if we have already been syncing for 3 months) * Finally, this should provide us sufficient information in order to use a feature flag and experimentally enable use of the Security::FindingsFinder to retrieve data from the decomposed database with some minor modifications. </details> Ultimately, the process for decomposition can be abstracted as follows: 1. Identify tables needed for the feature and establish them in the new DB 2. Set their "gitlab_schema" to `gitlab_sec` and use our existing decomposition tooling to detect cross database interactions that won't work post decomposition. 3. Resolve the detected cross join issues by changing the way GitLab interacts with that data (not joining, preloading, or using table mirrors) 4. Once all decomposition issues are resolved, use logical/physical replication to move the sec tables to a newly instantiated DB cluster. 5. Instruct GitLab to begin reading/writing from this new DB cluster. 6. Drop the now redundant tables from the main gitlab DB cluster. ## Decomposition Table Scope As of 31 December 2024, the current list of planned tables to be decomposed is as follows: - dast_pre_scan_verification_steps - dast_pre_scan_verifications - dast_profile_schedules - dast_profiles - dast_profiles_tags - dast_scanner_profiles - dast_scanner_profiles_builds - dast_site_profile_secret_variables - dast_site_profiles - dast_site_profiles_builds - dast_site_tokens - dast_site_validations - dast_sites - dependency_list_export_parts - dependency_list_exports - group_security_exclusions - project_security_exclusions - project_security_statistics - sbom_component_versions - sbom_components - sbom_occurrences - sbom_occurrences_vulnerabilities - sbom_source_packages - sbom_sources - security_findings - security_scans - vulnerabilities - vulnerability_export_parts - vulnerability_exports - vulnerability_external_issue_links - vulnerability_feedback - vulnerability_finding_evidences - vulnerability_finding_links - vulnerability_finding_signatures - vulnerability_findings_remediations - vulnerability_flags - vulnerability_historical_statistics - vulnerability_identifiers - vulnerability_issue_links - vulnerability_merge_request_links - vulnerability_namespace_historical_statistics - vulnerability_occurrence_identifiers - vulnerability_occurrences - vulnerability_reads - vulnerability_remediations - vulnerability_representation_information - vulnerability_scanners - vulnerability_state_transitions - vulnerability_statistics - vulnerability_user_mentions ### Tables not to be included - `dast_profiles_pipelines` (deprecated) - `pm_epss` (deprecated) - `security_trainings` - `security_training_providers` - `users_security_dashboard_projects` - `vulnerability_occurrence_pipelines` (deprecated) ## Existing data There are already plans to partition tables related to gitlab~9412861 and ~"devops::secure" because they are getting too large but the overall dataset itself is a large workload on the whole in our Main DB with [`vulnerability_occurrences` being in the top 15 largest tables](https://thanos-query.ops.gitlab.net/graph?g0.expr=topk(15%2C%20avg(pg_total_relation_size_bytes%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)%20by%20(relname))&g0.tab=0&g0.stacked=0&g0.range_input=1w&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) and overall being a standout in terms of loose coupling to core GitLab functionality and therefore a good candidate for being extracted. There are also [quite a few Sec tables that are larger than 50GiB and several larger than 100GiB](https://thanos-query.ops.gitlab.net/graph?g0.expr=avg(pg_total_relation_size_bytes%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%2C%20relname%3D\~%22(.\*vulnerabil.\*%7C.\*security.\*%7Cpm.\*%7Csbom.\*)%22%7D)%20by%20(relname)&g0.tab=0&g0.stacked=0&g0.range_input=1w&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D).  Overall security related features account for [around 39% of tuple updates](https://thanos-query.ops.gitlab.net/graph?g0.expr=sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20relname%3D\~%22(.\*vulnerabil.\*%7C.\*security.\*%7Csbom.\*)%22%2C%20type%3D%22patroni%22%7D)%20%2F%20sum(pg_stat_user_tables_n_tup_upd%7Benv%3D%22gprd%22%2C%20type%3D%22patroni%22%7D)&g0.tab=0&g0.stacked=0&g0.range_input=1d&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D) in our main database. This roughly correlates with how much write IO they add to the primary database and therefore we might see a 39% drop in most of our biggest concerning metrics for (CPU and IO) for our main database. ## Longer term benefits 1. We expect to see a lot of growth in this data as we bring on newer features like continuous vulnerability scanning which might make it an even bigger standout workload 2. The teams working on these features are already struggling a lot with getting certain features past DB review because risky changes on such a large amount of data have a high potential to cause incidents that take out all of GitLab.com. A separate DB would give them a lower risk environment where mistakes are only capable of taking out a limited set of features and not all functionality. 3. Additional headroom will be possible in their own DB that may accelerate development of other features that require a lot more DB resources and give them more time to also implement things like partitioning. 4. As a business we'll be able to attribute the cost of these workloads more clearly because they have their own DB and we can make better business decisions about how much to invest in optimizing our DB design vs. adding features # Enabling sec connection This spec should fail with a dedicated connection prior to this change: ```sh gdk config set gitlab.rails.databases.sec.enabled true gdk config set gitlab.rails.databases.sec.use_main_database true gdk reconfigure bin/rspec ee/spec/lib/gitlab/usage/metrics/instrumentations/count_secure_pipelines_metric_spec.rb ``` **NOTE:** you can just run the config commands againt with `false` to reset, or remove the entries from your `gdk.yml` # FAQ Please see [Glossary](https://handbook.gitlab.com/handbook/company/working-groups/secure-govern-database-decomposition/#glossary) for common terms <details> <summary> 1\. How will this affect existing installations? </summary> Reference: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/159115#note_2024259500 Any existing installations will remain unaffected, as `restrict_gitlab_migration` should avoid [split-brain](https://en.wikipedia.org/wiki/Split-brain\_(computing)) by limiting execution to a single DB. [In the case of our current configuration that's a fallback to the main database](https://gitlab.com/gitlab-org/gitlab/-/blob/b76e6c10de5fd05892da3b4d5e8df78fc422547b/db/database_connections/sec.yaml#L15) which [is likely to remain the default for all instances](https://gitlab.com/gitlab-org/gitlab/-/issues/474638#note_2017765632 "gitlab_sec decomposition: Support for self-managed customers"). For those who have not yet executed this, similarly the default logical DB will remain unchanged as the main datastore. The only distributions that could theoretically execute the backfill against an empty `gitlab_sec` database (where existing vulnerability flags would only exist within `gitlab_main`) would be those executing [our multiple database migration tasks](https://docs.gitlab.com/ee/administration/postgresql/multiple_databases.html) prior to these background migrations running. That's certainly possible, I suppose, although an edge case. In which case this migration would be a no-op when populating `vulnerability_flags.project_id` due to attempting to work on an empty dataset. Decomposition for SM instances is still largely theoretical as GitLab.com is the only known instance but not an impossible scenario to run into. </details> <details> <summary> 2\. How do I verify changes locally? </summary> The decomposition process is intended to avoid disrupting standard GitLab operations and development. As such, changing the schema alone is not enough to make GitLab behave differently as the gitlab_schema is merely internal tooling for detecting cross joins ahead of the actual decomposition effort. Unless expressly permitted, these failures will surface in our CI whenever a cross DB interaction occurs unless expressly permitted or fixed. By default, `gitlab_sec` schema falls back to `gitlab_main` if configuration is not present, which is [currently](https://gitlab.com/gitlab-org/gitlab/-/issues/493983) the case in the GitLab CI, and (at time of writing) all deployments. If your local GDK runs and behaves without exception, and there's no failures in the CI pipeline, then the changes are likely successful and won't affect standard GitLab functioning. In certain cases, however failures may only be present once the `gitlab_sec` DB connection is enabled. Since our rspec tests run within transactions, any records created using [a separately-configured DB connection](https://gitlab.com/gitlab-org/gitlab-development-kit/-/blob/main/doc/configuration.md#rails) (i.e. `sec.use_main_database: false`) will not be present to the other DB connections. For example, an insert into the namespaces table of `gitlab_main` will not be visible to the `gitlab_sec` connection. This more accurately reflects the final state of our DB but may cause unexpected failures outside the scope of a given changeset so is advised only in special cases. </details> <details> <summary> 3\. How will this affect self managed instances? </summary> For now, it won't. Possibly in the future it won't either. There are no known instances of GitLab outside of GitLab.com in which a separate sec database is understood to be needed. Additionally, sec tables are unused by non-ultimate license instances. Given this, instances without a `sec` db configuration will default to using the `main` DB for their sec related tables. </details> <details> <summary> 4\. What impact does changing the gitlab_schema to \`gitlab_sec\` have? </summary> Technically, nothing. The "gitlab schema" is actually internal tooling intended to allow us to demarcate which tables should be accessed by what database connections, and detect when these boundaries are being violated. This allows us to prepare for decomposition of these tables to separate databases by ensuring that our code interacts with them in a way that will work regardless of their positioning on the same or other physical databases. </details> <details> <summary> 5\. When should a background migrations schema restriction be updated? </summary> The main concern is that old background migration interacts with tables that have been decomposed as migrations are run on specific DB connections. If the data migration reads the wrong data, it won't enqueue correctly and won't behave as intended. This can present challenges where we need to finalize migrations in multiple tables to ensure they are completed but due to complexity, as part of the rollout, we will ensure any tables that fall within the new gitlab_sec schema should already be finalized prior to data migration. </details> <details> <summary> 6\. What impact does the \`allow_cross_foreign_keys\` key have on the database schema definitions? </summary> This key allows the table to implicitly interact with tables on the schemas listed under it. The only current example of this is tables on `gitlab_main_cell` are permitted to interact with `gitlab_main_clusterwide` for cross join and cross modification purposes, as recent changes have meant these tables will use the same database connection. This key is (at time of writing) not needed for the gitlab_sec decomposition. </details> <details> <summary> 7\. Should existing/old migrations be updated to accommodate this change? </summary> This scenario would typically come up when the migration is performing a join with another table not in \\\`gitlab_sec\\\` (or any query/transaction that require tables in different DBs). So far, tests for such migrations have failed to indicate that an exception block is needed in such migrations. </details> <details> <summary> 8\. Does the `ApplicationRecord` need to be updated to `Gitlab::Database::SecApplicationRecord` separately from the `gitlab_schema` of the table being set to `gitlab_sec`? </summary> No. These changes are independent but there's no good reason to do them separately. The gitlab_schema affects our internal tooling to know if tables are being interacted with across database definitions. The application record merely defines the database connection to be used for the record. For now however, unless a sec database is defined, the change to the application record ancestor doesn't have a functional change. (with one defined, it uses it to be clear). </details>