Add secondary indexes to partitioned audit_events
What does this MR do?
Related to #241267 (closed)
This MR was split from !47581 (merged), so we could verify the indexes before swapping the tables. That MR contains the relevant details/discussion about the index management.
Indexes are created concurrently
per-partition, before creating on the parent table:
rails db:migrate:up VERSION=20201112215028
== 20201112215028 AddPartitionedAuditEventIndexes: migrating ==================
-- index_name_exists?(:audit_events_part_5fc467ac26, "analytics_index_audit_events_part_on_created_at_and_author_id")
-> 0.0008s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_000000", [:created_at, :author_id], {:name=>"index_2177f0dd80", :algorithm=>:concurrently})
-> 0.0011s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_000000", [:created_at, :author_id], {:name=>"index_2177f0dd80", :algorithm=>:concurrently})
-> 0.0030s
-- execute("RESET ALL")
-> 0.0002s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202011", [:created_at, :author_id], {:name=>"index_82131632c7", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202011", [:created_at, :author_id], {:name=>"index_82131632c7", :algorithm=>:concurrently})
-> 0.0020s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202012", [:created_at, :author_id], {:name=>"index_e84582b5f5", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202012", [:created_at, :author_id], {:name=>"index_e84582b5f5", :algorithm=>:concurrently})
-> 0.0017s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202101", [:created_at, :author_id], {:name=>"index_309cb73874", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202101", [:created_at, :author_id], {:name=>"index_309cb73874", :algorithm=>:concurrently})
-> 0.0016s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202102", [:created_at, :author_id], {:name=>"index_028162f283", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202102", [:created_at, :author_id], {:name=>"index_028162f283", :algorithm=>:concurrently})
-> 0.0016s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202103", [:created_at, :author_id], {:name=>"index_0043962d30", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202103", [:created_at, :author_id], {:name=>"index_0043962d30", :algorithm=>:concurrently})
-> 0.0016s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202104", [:created_at, :author_id], {:name=>"index_d42f8dc702", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202104", [:created_at, :author_id], {:name=>"index_d42f8dc702", :algorithm=>:concurrently})
-> 0.0017s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202105", [:created_at, :author_id], {:name=>"index_65b6207993", :algorithm=>:concurrently})
-> 0.0005s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202105", [:created_at, :author_id], {:name=>"index_65b6207993", :algorithm=>:concurrently})
-> 0.0017s
-- add_index(:audit_events_part_5fc467ac26, [:created_at, :author_id], {:name=>"analytics_index_audit_events_part_on_created_at_and_author_id"})
-> 0.0021s
-- index_name_exists?(:audit_events_part_5fc467ac26, "idx_audit_events_part_on_entity_id_desc_author_id_created_at")
-> 0.0004s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_000000", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_a71c4828a0", :algorithm=>:concurrently})
-> 0.0010s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_000000", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_a71c4828a0", :algorithm=>:concurrently})
-> 0.0018s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202011", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_63f4366262", :algorithm=>:concurrently})
-> 0.0009s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202011", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_63f4366262", :algorithm=>:concurrently})
-> 0.0015s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202012", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_acbdaa58e3", :algorithm=>:concurrently})
-> 0.0011s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202012", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_acbdaa58e3", :algorithm=>:concurrently})
-> 0.0016s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202101", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_f4e81fb9cf", :algorithm=>:concurrently})
-> 0.0009s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202101", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_f4e81fb9cf", :algorithm=>:concurrently})
-> 0.0017s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202102", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_0b3605a395", :algorithm=>:concurrently})
-> 0.0008s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202102", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_0b3605a395", :algorithm=>:concurrently})
-> 0.0015s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202103", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_b070ce7dc9", :algorithm=>:concurrently})
-> 0.0007s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202103", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_b070ce7dc9", :algorithm=>:concurrently})
-> 0.0015s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202104", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_1f4dff0a6d", :algorithm=>:concurrently})
-> 0.0007s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202104", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_1f4dff0a6d", :algorithm=>:concurrently})
-> 0.0013s
-- transaction_open?()
-> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202105", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_d9a68fddcf", :algorithm=>:concurrently})
-> 0.0007s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202105", [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"index_d9a68fddcf", :algorithm=>:concurrently})
-> 0.0014s
-- add_index(:audit_events_part_5fc467ac26, [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"idx_audit_events_part_on_entity_id_desc_author_id_created_at"})
-> 0.0016s
== 20201112215028 AddPartitionedAuditEventIndexes: migrated (0.0736s) =========
Conversely, indexes are dropped by simply removing the parent index, which automatically recurses to all partitions:
rails db:migrate:down VERSION=20201112215028
== 20201112215028 AddPartitionedAuditEventIndexes: reverting ==================
-- index_name_exists?(:audit_events_part_5fc467ac26, "idx_audit_events_part_on_entity_id_desc_author_id_created_at")
-> 0.0007s
-- remove_index(:audit_events_part_5fc467ac26, {:name=>"idx_audit_events_part_on_entity_id_desc_author_id_created_at"})
-> 0.0013s
-- index_name_exists?(:audit_events_part_5fc467ac26, "analytics_index_audit_events_part_on_created_at_and_author_id")
-> 0.0007s
-- remove_index(:audit_events_part_5fc467ac26, {:name=>"analytics_index_audit_events_part_on_created_at_and_author_id"})
-> 0.0008s
== 20201112215028 AddPartitionedAuditEventIndexes: reverted (0.0241s) =========
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
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
Edited by Patrick Bair