Adjust indices and PK of security_findings table for partitioning

What does this MR do and why?

This MR adjusts the unique indices of the security_findings table and changes the PK of it for partitoning.

Follow-up from Add `partition_number` and related indices to `... (!91683 - merged).

Related to Implement retention period for Security::Findin... (#351524 - closed).

Database review

This MR commits 2 indices that were created asynchronously by !91683 (merged) to structure.sql, drops an unnecessary existing index, and changes the PK of the security_findings table as the PK must contain the partition column.

Commit `security_findings_partitioned_pkey` index

rake db:migrate:up

main: == 20220715185348 AddIndexOnSecurityFindingsIdAndPartitionNumber: migrating ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:security_findings, [:id, :partition_number], {:unique=>true, :name=>"security_findings_partitioned_pkey", :algorithm=>:concurrently})
main:    -> 0.0150s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0008s
main: -- add_index(:security_findings, [:id, :partition_number], {:unique=>true, :name=>"security_findings_partitioned_pkey", :algorithm=>:concurrently})
main:    -> 0.0053s
main: -- execute("RESET statement_timeout")
main:    -> 0.0008s
main: == 20220715185348 AddIndexOnSecurityFindingsIdAndPartitionNumber: migrated (0.0540s)

rake db:migrate:down

main: == 20220715185348 AddIndexOnSecurityFindingsIdAndPartitionNumber: reverting ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:security_findings)
main:    -> 0.0206s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- remove_index(:security_findings, {:algorithm=>:concurrently, :name=>"security_findings_partitioned_pkey"})
main:    -> 0.0057s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20220715185348 AddIndexOnSecurityFindingsIdAndPartitionNumber: reverted (0.0489s)

Commit `index_security_findings_on_unique_columns` index

rake db:migrate:up

main: == 20220715185436 AddIndexOnSecurityFindingsUniqueColumns: migrating ==========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:security_findings, [:uuid, :scan_id, :partition_number], {:unique=>true, :name=>"index_security_findings_on_unique_columns", :algorithm=>:concurrently})
main:    -> 0.0086s
main: -- add_index(:security_findings, [:uuid, :scan_id, :partition_number], {:unique=>true, :name=>"index_security_findings_on_unique_columns", :algorithm=>:concurrently})
main:    -> 0.0033s
main: == 20220715185436 AddIndexOnSecurityFindingsUniqueColumns: migrated (0.0203s) =

rake db:migrate:down

main: == 20220715185436 AddIndexOnSecurityFindingsUniqueColumns: reverting ==========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:security_findings)
main:    -> 0.0215s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- remove_index(:security_findings, {:algorithm=>:concurrently, :name=>"index_security_findings_on_unique_columns"})
main:    -> 0.0054s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20220715185436 AddIndexOnSecurityFindingsUniqueColumns: reverted (0.0461s) =

Drop `index_security_findings_on_uuid_and_scan_id` index

rake db:migrate:up

main: == 20220715190612 DropIndexOnSecurityFindingsUuidAndScanId: migrating =========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:security_findings)
main:    -> 0.0228s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0012s
main: -- remove_index(:security_findings, {:algorithm=>:concurrently, :name=>"index_security_findings_on_uuid_and_scan_id"})
main:    -> 0.0074s
main: -- execute("RESET statement_timeout")
main:    -> 0.0012s
main: == 20220715190612 DropIndexOnSecurityFindingsUuidAndScanId: migrated (0.0539s)

rake db:migrate:down

main: == 20220715190612 DropIndexOnSecurityFindingsUuidAndScanId: reverting =========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:security_findings, [:uuid, :scan_id], {:unique=>true, :name=>"index_security_findings_on_uuid_and_scan_id", :algorithm=>:concurrently})
main:    -> 0.0153s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- add_index(:security_findings, [:uuid, :scan_id], {:unique=>true, :name=>"index_security_findings_on_uuid_and_scan_id", :algorithm=>:concurrently})
main:    -> 0.0059s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20220715190612 DropIndexOnSecurityFindingsUuidAndScanId: reverted (0.0373s)

Change the PK of the table

rake db:migrate:up

main: == 20220715191629 ChangePrimaryKeyOfSecurityFindingsTable: migrating ==========
main: -- execute("ALTER TABLE security_findings DROP CONSTRAINT security_findings_pkey;\n")
main:    -> 0.0051s
main: -- execute("ALTER TABLE security_findings ADD CONSTRAINT security_findings_pkey PRIMARY KEY USING index security_findings_partitioned_pkey;\n")
main:    -> 0.0028s
main: == 20220715191629 ChangePrimaryKeyOfSecurityFindingsTable: migrated (0.0113s) =

rake db:migrate:down

main: == 20220715191629 ChangePrimaryKeyOfSecurityFindingsTable: reverting ==========
main: -- execute("ALTER TABLE security_findings DROP CONSTRAINT security_findings_pkey;\n")
main:    -> 0.0029s
main: -- execute("ALTER TABLE security_findings ADD CONSTRAINT security_findings_pkey PRIMARY KEY (id);\n")
main:    -> 0.0045s
main: -- execute("CREATE UNIQUE INDEX security_findings_partitioned_pkey ON security_findings USING btree(id, partition_number);\n")
main:    -> 0.0017s
main: == 20220715191629 ChangePrimaryKeyOfSecurityFindingsTable: reverted (0.0119s) =

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.