Migration helpers to partition existing tables (!32240) · Merge requests · GitLab.org / GitLab

Patrick Bair requested to merge 202618-migration-strategy-to-partition-existing-tables into master May 14, 2020

What does this MR do?

As part of the effort to use PG partitioning to scale the database, we need to have new migration helpers that will enable developers to add partitioning to existing tables in a uniform way. Since existing tables cannot be partitioned, the helpers will have to create a new table with the correct partitioning scheme, and setup the automation to sync rows between the old and new table.

This MR implements the first step in that process, which is adding a new migration helper that creates a partitioned copy of an existing table (structure only, no data). The new table will be partitioned by a date range, with one partition for each month. Otherwise the existing schema of the partitioned table should closely mirror the original table.

One note is that only the primary key is recreated on the partitioned table, as secondary indexes would slow down the migration process and can be created after the data is synced. Foreign keys are also out of scope for this change, and not needed for the initial selection of the audit_events table, since it has none.

Linked issue: #202618 (closed)

Example Migration

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

  DOWNTIME = false

  def up
    partition_table_by_date :audit_events, :created_at,
      min_time: Time.utc(2014, 12, 1), max_time: Time.utc(2021, 1, 1)
  end

  def down
    drop_partitioned_table_for :audit_events
  end
end

Example Migration Output

UP:

rails db:migrate:up VERSION=20200519153403
== 20200519153403 PartitionAuditEventsByDate: migrating =======================
-- execute("CREATE TABLE audit_events_part_5fc467ac26 (\n  LIKE audit_events INCLUDING ALL EXCLUDING INDEXES,\n  partition_key_bfab5f7bb7 timestamp without time zone NOT NULL,\n  PRIMARY KEY (id, partition_key_bfab5f7bb7)\n) PARTITION BY RANGE (partition_key_bfab5f7bb7)\n")
   -> 0.0029s
-- remove_column("audit_events_part_5fc467ac26", "created_at")
   -> 0.0003s
-- rename_column("audit_events_part_5fc467ac26", "partition_key_bfab5f7bb7", "created_at")
   -> 0.0013s
-- change_column_default("audit_events_part_5fc467ac26", "id", nil)
   -> 0.0009s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_000000 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM (MINVALUE) TO ('2014-12-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201412 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2014-12-01') TO ('2015-01-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201501 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-01-01') TO ('2015-02-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201502 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-02-01') TO ('2015-03-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201503 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-03-01') TO ('2015-04-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201504 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-04-01') TO ('2015-05-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201505 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-05-01') TO ('2015-06-01')\n")
   -> 0.0017s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201506 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-06-01') TO ('2015-07-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201507 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-07-01') TO ('2015-08-01')\n")
   -> 0.0018s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201508 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-08-01') TO ('2015-09-01')\n")
   -> 0.0018s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201509 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-09-01') TO ('2015-10-01')\n")
   -> 0.0029s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201510 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-10-01') TO ('2015-11-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201511 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-11-01') TO ('2015-12-01')\n")
   -> 0.0025s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201512 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2015-12-01') TO ('2016-01-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201601 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-01-01') TO ('2016-02-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201602 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-02-01') TO ('2016-03-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201603 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-03-01') TO ('2016-04-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201604 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-04-01') TO ('2016-05-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201605 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-05-01') TO ('2016-06-01')\n")
   -> 0.0015s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201606 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-06-01') TO ('2016-07-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201607 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-07-01') TO ('2016-08-01')\n")
   -> 0.0024s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201608 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-08-01') TO ('2016-09-01')\n")
   -> 0.0026s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201609 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-09-01') TO ('2016-10-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201610 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-10-01') TO ('2016-11-01')\n")
   -> 0.0017s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201611 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-11-01') TO ('2016-12-01')\n")
   -> 0.0024s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201612 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2016-12-01') TO ('2017-01-01')\n")
   -> 0.0017s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201701 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-01-01') TO ('2017-02-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201702 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-02-01') TO ('2017-03-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201703 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-03-01') TO ('2017-04-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201704 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-04-01') TO ('2017-05-01')\n")
   -> 0.0024s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201705 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-05-01') TO ('2017-06-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201706 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-06-01') TO ('2017-07-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201707 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-07-01') TO ('2017-08-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201708 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-08-01') TO ('2017-09-01')\n")
   -> 0.0015s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201709 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-09-01') TO ('2017-10-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201710 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-10-01') TO ('2017-11-01')\n")
   -> 0.0026s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201711 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-11-01') TO ('2017-12-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201712 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2017-12-01') TO ('2018-01-01')\n")
   -> 0.0034s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201801 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-01-01') TO ('2018-02-01')\n")
   -> 0.0018s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201802 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-02-01') TO ('2018-03-01')\n")
   -> 0.0018s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201803 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-03-01') TO ('2018-04-01')\n")
   -> 0.0026s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201804 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-04-01') TO ('2018-05-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201805 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-05-01') TO ('2018-06-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201806 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-06-01') TO ('2018-07-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201807 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-07-01') TO ('2018-08-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201808 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-08-01') TO ('2018-09-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201809 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-09-01') TO ('2018-10-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201810 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-10-01') TO ('2018-11-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201811 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-11-01') TO ('2018-12-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201812 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2018-12-01') TO ('2019-01-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201901 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-01-01') TO ('2019-02-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201902 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-02-01') TO ('2019-03-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201903 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-03-01') TO ('2019-04-01')\n")
   -> 0.0028s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201904 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-04-01') TO ('2019-05-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201905 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-05-01') TO ('2019-06-01')\n")
   -> 0.0027s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201906 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-06-01') TO ('2019-07-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201907 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-07-01') TO ('2019-08-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201908 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-08-01') TO ('2019-09-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201909 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-09-01') TO ('2019-10-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201910 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-10-01') TO ('2019-11-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201911 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-11-01') TO ('2019-12-01')\n")
   -> 0.0024s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_201912 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2019-12-01') TO ('2020-01-01')\n")
   -> 0.0021s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202001 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-01-01') TO ('2020-02-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202002 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-02-01') TO ('2020-03-01')\n")
   -> 0.0024s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202003 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-03-01') TO ('2020-04-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202004 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-04-01') TO ('2020-05-01')\n")
   -> 0.0019s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202005 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-05-01') TO ('2020-06-01')\n")
   -> 0.0028s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202006 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-06-01') TO ('2020-07-01')\n")
   -> 0.0020s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202007 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-07-01') TO ('2020-08-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202008 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-08-01') TO ('2020-09-01')\n")
   -> 0.0038s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202009 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-09-01') TO ('2020-10-01')\n")
   -> 0.0022s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202010 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-10-01') TO ('2020-11-01')\n")
   -> 0.0016s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202011 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-11-01') TO ('2020-12-01')\n")
   -> 0.0023s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202012 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2020-12-01') TO ('2021-01-01')\n")
   -> 0.0026s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_202101 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM ('2021-01-01') TO ('2021-02-01')\n")
   -> 0.0027s
== 20200519153403 PartitionAuditEventsByDate: migrated (0.1749s) ==============

DOWN:

rails db:migrate:down VERSION=20200519153403
== 20200519153403 PartitionAuditEventsByDate: reverting =======================
-- drop_table("audit_events_part_5fc467ac26")
   -> 0.0196s
== 20200519153403 PartitionAuditEventsByDate: reverted (0.0196s) ==============

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 Jan 11, 2021 by Alex Kalderimis

Migration helpers to partition existing tables