Create partitioned `security_findings` table
What does this MR do and why?
This MR changes the security_findings
table to be partitioned. The changes related to attaching and detaching partitions will be done in a separate MR to keep this simple.
Related to Implement retention period for Security::Findin... (#351524 - closed).
Extracted from [Draft] PoC Partition `security_findings` table (!89060 - closed).
Database review
rake db:migrate:up
main: == 20220728212701 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrating
main: -- execute("ALTER TABLE security_findings RENAME TO security_findings_1;\n")
main: -> 0.0249s
main: -- execute("ALTER INDEX security_findings_pkey RENAME TO security_findings_1_pkey;\n")
main: -> 0.0075s
main: -- execute("CREATE TABLE security_findings (\n LIKE security_findings_1 INCLUDING ALL\n) PARTITION BY LIST (partition_number);\n")
main: -> 0.0378s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY public.security_findings.id;\n")
main: -> 0.0056s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE;\n")
main: -> 0.0084s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE;\n")
main: -> 0.0056s
main: -- execute("ALTER TABLE security_findings_1 SET SCHEMA gitlab_partitions_dynamic;\n")
main: -> 0.0075s
main: -- execute("ALTER TABLE security_findings ATTACH PARTITION gitlab_partitions_dynamic.security_findings_1 FOR VALUES IN (1);\n")
main: -> 0.0193s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_unique_columns RENAME TO security_findings_1_uuid_scan_id_partition_number_idx;\n")
main: -> 0.0039s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_confidence RENAME TO security_findings_1_confidence_idx;\n")
main: -> 0.0036s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_project_fingerprint RENAME TO security_findings_1_project_fingerprint_idx;\n")
main: -> 0.0036s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_deduplicated RENAME TO security_findings_1_scan_id_deduplicated_idx;\n")
main: -> 0.0039s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_id RENAME TO security_findings_1_scan_id_id_idx;\n")
main: -> 0.0043s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scanner_id RENAME TO security_findings_1_scanner_id_idx;\n")
main: -> 0.0040s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_severity RENAME TO security_findings_1_severity_idx;\n")
main: -> 0.0049s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_on_security_findings_uuid_and_id_order_desc RENAME TO security_findings_1_uuid_id_idx;\n")
main: -> 0.0040s
main: == 20220728212701 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrated (0.1631s)
rake db:migrate:down
main: == 20220728212701 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverting
main: -- execute("SELECT\n partitions.relname AS partition_name\nFROM pg_inherits\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\nJOIN pg_class partitions ON pg_inherits.inhrelid = partitions.oid\nWHERE\n parent.relname = 'security_findings'\nORDER BY partitions.relname DESC\nLIMIT 1\n")
main: -> 0.0293s
main: -- execute("ALTER TABLE security_findings DETACH PARTITION gitlab_partitions_dynamic.security_findings_1;\n")
main: -> 0.0210s
main: -- execute("ALTER TABLE gitlab_partitions_dynamic.security_findings_1 SET SCHEMA public;\n")
main: -> 0.0053s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY security_findings_1.id;\n")
main: -> 0.0034s
main: -- execute("DROP TABLE security_findings;\n")
main: -> 0.0119s
main: -- execute("ALTER TABLE security_findings_1 RENAME TO security_findings;\n")
main: -> 0.0033s
main: -- execute("ALTER INDEX public.security_findings_1_pkey RENAME TO security_findings_pkey;\n")
main: -> 0.0031s
main: -- execute("ALTER INDEX public.security_findings_1_uuid_scan_id_partition_number_idx RENAME TO index_security_findings_on_unique_columns;\n")
main: -> 0.0044s
main: -- execute("ALTER INDEX public.security_findings_1_confidence_idx RENAME TO index_security_findings_on_confidence;\n")
main: -> 0.0035s
main: -- execute("ALTER INDEX public.security_findings_1_project_fingerprint_idx RENAME TO index_security_findings_on_project_fingerprint;\n")
main: -> 0.0027s
main: -- execute("ALTER INDEX public.security_findings_1_scan_id_deduplicated_idx RENAME TO index_security_findings_on_scan_id_and_deduplicated;\n")
main: -> 0.0023s
main: -- execute("ALTER INDEX public.security_findings_1_scan_id_id_idx RENAME TO index_security_findings_on_scan_id_and_id;\n")
main: -> 0.0026s
main: -- execute("ALTER INDEX public.security_findings_1_scanner_id_idx RENAME TO index_security_findings_on_scanner_id;\n")
main: -> 0.0035s
main: -- execute("ALTER INDEX public.security_findings_1_severity_idx RENAME TO index_security_findings_on_severity;\n")
main: -> 0.0029s
main: -- execute("ALTER INDEX public.security_findings_1_uuid_id_idx RENAME TO index_on_security_findings_uuid_and_id_order_desc;\n")
main: -> 0.0026s
main: == 20220728212701 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverted (0.1113s)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Mehmet Emin INAC