Helper to safely add indexes to partitioned tables (!45211) · Merge requests · GitLab.org / GitLab

Patrick Bair requested to merge 241267-partitioned-table-add-index-helper into master Oct 14, 2020

What does this MR do?

From the Postgres docs here: https://www.postgresql.org/docs/11/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Concurrent builds for indexes on partitioned tables are currently not supported. However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out. In this case, building the partitioned index is a metadata only operation.

This MR adds a new migration helper which implements the above workaround to building indexes concurrently on partitioned tables. Once the index is successfully created on the parent table, new partitions will automatically have the index created on them, so there isn't a need to manage the indexes going forward.

Used a test migration against dblab to see that the helper works correctly:

class AddIndexesToAuditEventsPartitioned < ActiveRecord::Migration[6.0]
  include Gitlab::Database::PartitioningMigrationHelpers

  DOWNTIME = false

  INDEX_NAME = 'index_audit_events_partitioned_created_at_author_id'

  disable_ddl_transaction!

  def up
    add_concurrent_partitioned_index :audit_events_part_5fc467ac26, [:created_at, :author_id], name: INDEX_NAME
  end

  def down
    remove_concurrent_partitioned_index_by_name :audit_events_part_5fc467ac26, INDEX_NAME
  end
end

Result of running migration `up` (truncated to show relevant details)

== 20201013190517 AddIndexesToAuditEventsPartitioned: migrating ===============
-- index_name_exists?(:audit_events_part_5fc467ac26, "index_audit_events_partitioned_created_at_author_id")
   -> 0.0717s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_000000", [:created_at, :author_id], {:name=>"index_74ea6a28bb", :algorithm=>:concurrently})
   -> 0.0776s
-- execute("SET statement_timeout TO 0")
   -> 0.0742s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_000000", [:created_at, :author_id], {:name=>"index_74ea6a28bb", :algorithm=>:concurrently})
   -> 0.3371s
-- execute("RESET ALL")
   -> 0.0712s
...
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202008", [:created_at, :author_id], {:name=>"index_c3424110e9", :algorithm=>:concurrently})
   -> 0.0744s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202008", [:created_at, :author_id], {:name=>"index_c3424110e9", :algorithm=>:concurrently})
   -> 109.0959s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202009", [:created_at, :author_id], {:name=>"index_9e18bf99d9", :algorithm=>:concurrently})
   -> 0.0748s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202009", [:created_at, :author_id], {:name=>"index_9e18bf99d9", :algorithm=>:concurrently})
   -> 119.5958s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202010", [:created_at, :author_id], {:name=>"index_50bc1a2586", :algorithm=>:concurrently})
   -> 0.0745s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202010", [:created_at, :author_id], {:name=>"index_50bc1a2586", :algorithm=>:concurrently})
   -> 79.4918s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202011", [:created_at, :author_id], {:name=>"index_d664a1fa09", :algorithm=>:concurrently})
   -> 0.0773s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202011", [:created_at, :author_id], {:name=>"index_d664a1fa09", :algorithm=>:concurrently})
   -> 0.2552s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202012", [:created_at, :author_id], {:name=>"index_ec0aaf9a97", :algorithm=>:concurrently})
   -> 0.0759s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202012", [:created_at, :author_id], {:name=>"index_ec0aaf9a97", :algorithm=>:concurrently})
   -> 0.2409s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202101", [:created_at, :author_id], {:name=>"index_1aa93af63a", :algorithm=>:concurrently})
   -> 0.0773s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202101", [:created_at, :author_id], {:name=>"index_1aa93af63a", :algorithm=>:concurrently})
   -> 0.2410s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202102", [:created_at, :author_id], {:name=>"index_3523466a72", :algorithm=>:concurrently})
   -> 0.0782s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202102", [:created_at, :author_id], {:name=>"index_3523466a72", :algorithm=>:concurrently})
   -> 0.2735s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202103", [:created_at, :author_id], {:name=>"index_ffd7456ec7", :algorithm=>:concurrently})
   -> 0.0747s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202103", [:created_at, :author_id], {:name=>"index_ffd7456ec7", :algorithm=>:concurrently})
   -> 0.2331s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202104", [:created_at, :author_id], {:name=>"index_55e14c311b", :algorithm=>:concurrently})
   -> 0.0790s
-- add_index("gitlab_partitions_dynamic.audit_events_part_5fc467ac26_202104", [:created_at, :author_id], {:name=>"index_55e14c311b", :algorithm=>:concurrently})
   -> 0.2343s
-- add_index(:audit_events_part_5fc467ac26, [:created_at, :author_id], {:name=>"index_audit_events_partitioned_created_at_author_id"})
   -> 0.3797s
== 20201013190517 AddIndexesToAuditEventsPartitioned: migrated (2819.6410s) ===

The important details to note here is that the index is created concurrently on each partition, and the final index creation on the parent table is a fast operation taking 0.3797s

Result of running migration `down`

== 20201013190517 AddIndexesToAuditEventsPartitioned: reverting ===============
-- index_name_exists?(:audit_events_part_5fc467ac26, "index_audit_events_partitioned_created_at_author_id")
   -> 0.0773s
-- remove_index(:audit_events_part_5fc467ac26, {:name=>"index_audit_events_partitioned_created_at_author_id"})
   -> 87.3375s
== 20201013190517 AddIndexesToAuditEventsPartitioned: reverted (87.4151s) =====

Does this MR meet the acceptance criteria?

Conformity

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 Oct 16, 2020 by Patrick Bair

Helper to safely add indexes to partitioned tables