Update database indexes for new report type: Running Container Scanning (!64323) · Merge requests · GitLab.org / GitLab

Alan (Maciej) Paruszewski requested to merge 330713-uodate-running-container-scanning-indexes into master Jun 17, 2021

What does this MR do?

This MR updates indexes used for fetching CI::Builds with security jobs, in scope of &3410 (closed) we are adding new report type: running_container_scanning and we need to update these indexes to fetch information about builds with these jobs in efficient way. This is used ie. in https://gitlab.com/gitlab-org/gitlab/blob/d55bceccd3b25add5c3e9efddb6d978d79af0e48/ee/lib/ee/gitlab/usage_data.rb#L416 to get usage data.

Migrations

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:up VERSION=20210616125722
== 20210616125722 UpdateIndexForRunningContainerScanningWithCommitIdAndArtifactsExpireAt: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:commit_id, :artifacts_expire_at, :id], {:name=>"index_ci_builds_on_commit_id_secure_artifacts_expired_at_and_id", :where=>"((name)::text = ANY (ARRAY[('sast'::character varying)::text,\n                                       ('secret_detection'::character varying)::text,\n                                       ('dependency_scanning'::character varying)::text,\n                                       ('container_scanning'::character varying)::text,\n                                       ('dast'::character varying)::text,\n                                       ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text) AND ((retried = false) OR (retried IS NULL))", :algorithm=>:concurrently})
   -> 0.0091s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:commit_id, :artifacts_expire_at, :id], {:name=>"index_ci_builds_on_commit_id_secure_artifacts_expired_at_and_id", :where=>"((name)::text = ANY (ARRAY[('sast'::character varying)::text,\n                                       ('secret_detection'::character varying)::text,\n                                       ('dependency_scanning'::character varying)::text,\n                                       ('container_scanning'::character varying)::text,\n                                       ('dast'::character varying)::text,\n                                       ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text) AND ((retried = false) OR (retried IS NULL))", :algorithm=>:concurrently})
   -> 0.0035s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0080s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_commit_id_artifacts_expired_at_and_id"})
   -> 0.0022s
== 20210616125722 UpdateIndexForRunningContainerScanningWithCommitIdAndArtifactsExpireAt: migrated (0.0252s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:up VERSION=20210616125724
== 20210616125724 UpdatePartialIndexForRunningContainerScanningReportType: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :name], {:name=>"index_partial_ci_builds_on_user_id_name_secure_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                             ('dast'::character varying)::text,\n                                             ('dependency_scanning'::character varying)::text,\n                                             ('license_management'::character varying)::text,\n                                             ('license_scanning'::character varying)::text,\n                                             ('sast'::character varying)::text,\n                                             ('coverage_fuzzing'::character varying)::text,\n                                             ('secret_detection'::character varying)::text,\n                                             ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0090s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:user_id, :name], {:name=>"index_partial_ci_builds_on_user_id_name_secure_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                             ('dast'::character varying)::text,\n                                             ('dependency_scanning'::character varying)::text,\n                                             ('license_management'::character varying)::text,\n                                             ('license_scanning'::character varying)::text,\n                                             ('sast'::character varying)::text,\n                                             ('coverage_fuzzing'::character varying)::text,\n                                             ('secret_detection'::character varying)::text,\n                                             ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0036s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0071s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_partial_ci_builds_on_user_id_name_parser_features"})
   -> 0.0021s
== 20210616125724 UpdatePartialIndexForRunningContainerScanningReportType: migrated (0.0241s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:up VERSION=20210616125726
== 20210616125726 UpdateIndexForRunningContainerScanningWithUserIdAndNameReportType: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :name, :created_at], {:name=>"index_ci_builds_on_user_id_name_created_at_secure_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                            ('dast'::character varying)::text,\n                                            ('dependency_scanning'::character varying)::text,\n                                            ('license_management'::character varying)::text,\n                                            ('license_scanning'::character varying)::text,\n                                            ('sast'::character varying)::text,\n                                            ('coverage_fuzzing'::character varying)::text,\n                                            ('apifuzzer_fuzz'::character varying)::text,\n                                            ('apifuzzer_fuzz_dnd'::character varying)::text,\n                                            ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0092s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:user_id, :name, :created_at], {:name=>"index_ci_builds_on_user_id_name_created_at_secure_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                            ('dast'::character varying)::text,\n                                            ('dependency_scanning'::character varying)::text,\n                                            ('license_management'::character varying)::text,\n                                            ('license_scanning'::character varying)::text,\n                                            ('sast'::character varying)::text,\n                                            ('coverage_fuzzing'::character varying)::text,\n                                            ('apifuzzer_fuzz'::character varying)::text,\n                                            ('apifuzzer_fuzz_dnd'::character varying)::text,\n                                            ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0038s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0076s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_secure_ci_builds_on_user_id_name_created_at"})
   -> 0.0021s
== 20210616125726 UpdateIndexForRunningContainerScanningWithUserIdAndNameReportType: migrated (0.0250s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:up VERSION=20210616125728
== 20210616125728 UpdateIndexForRunningContainerScanningWithNameAndIdReportType: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:name, :id], {:name=>"index_ci_builds_on_name_and_id_secure_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                                  ('dast'::character varying)::text,\n                                                  ('dependency_scanning'::character varying)::text,\n                                                  ('license_management'::character varying)::text,\n                                                  ('sast'::character varying)::text,\n                                                  ('secret_detection'::character varying)::text,\n                                                  ('coverage_fuzzing'::character varying)::text,\n                                                  ('license_scanning'::character varying)::text,\n                                                  ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0095s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:name, :id], {:name=>"index_ci_builds_on_name_and_id_secure_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                                  ('dast'::character varying)::text,\n                                                  ('dependency_scanning'::character varying)::text,\n                                                  ('license_management'::character varying)::text,\n                                                  ('sast'::character varying)::text,\n                                                  ('secret_detection'::character varying)::text,\n                                                  ('coverage_fuzzing'::character varying)::text,\n                                                  ('license_scanning'::character varying)::text,\n                                                  ('running_container_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0037s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0079s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_security_ci_builds_on_name_and_id_parser_features"})
   -> 0.0027s
== 20210616125728 UpdateIndexForRunningContainerScanningWithNameAndIdReportType: migrated (0.0263s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes  env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:down VERSION=20210616125722                                                                                                            
== 20210616125722 UpdateIndexForRunningContainerScanningWithCommitIdAndArtifactsExpireAt: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:commit_id, :artifacts_expire_at, :id], {:name=>"index_ci_builds_on_commit_id_artifacts_expired_at_and_id", :where=>"((name)::text = ANY (ARRAY[('sast'::character varying)::text,\n                                       ('secret_detection'::character varying)::text,\n                                       ('dependency_scanning'::character varying)::text,\n                                       ('container_scanning'::character varying)::text,\n                                       ('dast'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text) AND ((retried = false) OR (retried IS NULL))", :algorithm=>:concurrently})
   -> 0.0097s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:commit_id, :artifacts_expire_at, :id], {:name=>"index_ci_builds_on_commit_id_artifacts_expired_at_and_id", :where=>"((name)::text = ANY (ARRAY[('sast'::character varying)::text,\n                                       ('secret_detection'::character varying)::text,\n                                       ('dependency_scanning'::character varying)::text,\n                                       ('container_scanning'::character varying)::text,\n                                       ('dast'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text) AND ((retried = false) OR (retried IS NULL))", :algorithm=>:concurrently})
   -> 0.0050s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0076s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_commit_id_secure_artifacts_expired_at_and_id"})
   -> 0.0030s
== 20210616125722 UpdateIndexForRunningContainerScanningWithCommitIdAndArtifactsExpireAt: reverted (0.0276s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:down VERSION=20210616125724                                                                                                           
== 20210616125724 UpdatePartialIndexForRunningContainerScanningReportType: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :name], {:name=>"index_partial_ci_builds_on_user_id_name_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                             ('dast'::character varying)::text,\n                                             ('dependency_scanning'::character varying)::text,\n                                             ('license_management'::character varying)::text,\n                                             ('license_scanning'::character varying)::text,\n                                             ('sast'::character varying)::text,\n                                             ('coverage_fuzzing'::character varying)::text,\n                                             ('secret_detection'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0102s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:user_id, :name], {:name=>"index_partial_ci_builds_on_user_id_name_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                             ('dast'::character varying)::text,\n                                             ('dependency_scanning'::character varying)::text,\n                                             ('license_management'::character varying)::text,\n                                             ('license_scanning'::character varying)::text,\n                                             ('sast'::character varying)::text,\n                                             ('coverage_fuzzing'::character varying)::text,\n                                             ('secret_detection'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0032s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0075s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_partial_ci_builds_on_user_id_name_secure_parser_features"})
   -> 0.0024s
== 20210616125724 UpdatePartialIndexForRunningContainerScanningReportType: reverted (0.0256s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:down VERSION=20210616125726                                                                                                          
== 20210616125726 UpdateIndexForRunningContainerScanningWithUserIdAndNameReportType: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :name, :created_at], {:name=>"index_secure_ci_builds_on_user_id_name_created_at", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                            ('dast'::character varying)::text,\n                                            ('dependency_scanning'::character varying)::text,\n                                            ('license_management'::character varying)::text,\n                                            ('license_scanning'::character varying)::text,\n                                            ('sast'::character varying)::text,\n                                            ('coverage_fuzzing'::character varying)::text,\n                                            ('apifuzzer_fuzz'::character varying)::text,\n                                            ('apifuzzer_fuzz_dnd'::character varying)::text,\n                                            ('secret_detection'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0127s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:ci_builds, [:user_id, :name, :created_at], {:name=>"index_secure_ci_builds_on_user_id_name_created_at", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                            ('dast'::character varying)::text,\n                                            ('dependency_scanning'::character varying)::text,\n                                            ('license_management'::character varying)::text,\n                                            ('license_scanning'::character varying)::text,\n                                            ('sast'::character varying)::text,\n                                            ('coverage_fuzzing'::character varying)::text,\n                                            ('apifuzzer_fuzz'::character varying)::text,\n                                            ('apifuzzer_fuzz_dnd'::character varying)::text,\n                                            ('secret_detection'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0044s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0094s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_user_id_name_created_at_secure_features"})
   -> 0.0053s
== 20210616125726 UpdateIndexForRunningContainerScanningWithUserIdAndNameReportType: reverted (0.0346s)

⋊> ~/D/g/gitlab on 330713-uodate-running-container-scanning-indexes ⨯ env VERBOSE=true RAILS_ENV=test bundle exec rake db:migrate:down VERSION=20210616125728
== 20210616125728 UpdateIndexForRunningContainerScanningWithNameAndIdReportType: reverting
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:name, :id], {:name=>"index_security_ci_builds_on_name_and_id_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                                  ('dast'::character varying)::text,\n                                                  ('dependency_scanning'::character varying)::text,\n                                                  ('license_management'::character varying)::text,\n                                                  ('sast'::character varying)::text,\n                                                  ('secret_detection'::character varying)::text,\n                                                  ('coverage_fuzzing'::character varying)::text,\n                                                  ('license_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0097s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, [:name, :id], {:name=>"index_security_ci_builds_on_name_and_id_parser_features", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n                                                  ('dast'::character varying)::text,\n                                                  ('dependency_scanning'::character varying)::text,\n                                                  ('license_management'::character varying)::text,\n                                                  ('sast'::character varying)::text,\n                                                  ('secret_detection'::character varying)::text,\n                                                  ('coverage_fuzzing'::character varying)::text,\n                                                  ('license_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
   -> 0.0032s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0081s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_name_and_id_secure_parser_features"})
   -> 0.0027s
== 20210616125728 UpdateIndexForRunningContainerScanningWithNameAndIdReportType: reverted (0.0261s)

Does this MR meet the acceptance criteria?

Conformity

I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.
This change is backwards compatible across updates, or this does not apply.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
[-] I have tested this MR in all supported browsers, or it's not needed.
[-] I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

[-] Label as security and @ mention @gitlab-com/gl-security/appsec
[-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
[-] Security reports checked/validated by a reviewer from the AppSec team

Related to #330713 (closed)

Edited Jun 21, 2021 by Alan (Maciej) Paruszewski

Update database indexes for new report type: Running Container Scanning