Skip to content

Migration helper to sync trigger partitioned tables

Patrick Bair requested to merge 218424-migration-helper-to-sync-partitioned-tables into master

What does this MR do?

Related issue: #218424 (closed)

Overview

This change builds on the work started in #32440 (closed) to implement a set of migration helpers that can be used to leverage table partitioning. Since PG does not support partitioning existing tables, the migration logic has to create a partitioned copy of the original table, and copy the data over. Like most migrations, this has requirement that it can be performed without downtime or impact to the application.

In the first MR, the migration helpers were added with the functionality to create a copy of the existing table, and create the partitions by date range (one partition per month).

This MR builds on that by implementing the next piece of the migration helper, which is a trigger that fires for all writes on the source table, and performs the equivalent write on the partitioned table. This ensures that once the migration is in place, any future writes will be synced to the partitioned table.

The final MR will implement the last piece of the migration, which will copy historic data to the partitioned table using a BackgroundMigration.

Example Function:

BEGIN
IF (TG_OP = 'DELETE') THEN
  DELETE FROM audit_events_part_5fc467ac26 where id = OLD.id;
ELSIF (TG_OP = 'UPDATE') THEN
  UPDATE audit_events_part_5fc467ac26
  SET author_id = NEW.author_id,
    type = NEW.type,
    entity_id = NEW.entity_id,
    entity_type = NEW.entity_type,
    details = NEW.details,
    updated_at = NEW.updated_at,
    created_at = NEW.created_at
  WHERE audit_events_part_5fc467ac26.id = NEW.id;
ELSIF (TG_OP = 'INSERT') THEN
  INSERT INTO audit_events_part_5fc467ac26 (id,
    author_id,
    type,
    entity_id,
    entity_type,
    details,
    updated_at,
    created_at)
  VALUES (NEW.id,
    NEW.author_id,
    NEW.type,
    NEW.entity_id,
    NEW.entity_type,
    NEW.details,
    NEW.updated_at,
    NEW.created_at);
END IF;
RETURN NULL;
END

Transactions

The MR as implemented now expects the entire migration not to be run transactionally. This is primarily because adding a trigger to a table requires a ShareRowExclusiveLock and dropping a trigger from a table requires an AccessExclusiveLock, so we need to minimize the time that lock is held, as well as allow for lock retries.

For an up migration, we don't take the lock for the trigger until the final step, so the lock would be held for a minimal duration. For a down migration we first drop the trigger, then drop the table/partitions. This would hold the lock on the table with the trigger until the partitioned table is cleaned up. While the risk of removing the partitioned table seems low, I felt it was safer to break this up.

The helper checks for existence of objects before executing operations, so if a non-transactional migration were to fail, it should be seamless to rerun the set of migrations without intervention.

Example Use:

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

  DOWNTIME = false

  disable_ddl_transaction!

  def up
    partition_table_by_date :audit_events, :created_at, min_date: Date.new(2020, 1, 1), max_date: Date.new(2021, 1, 1)
  end

  def down
    drop_partitioned_table_for :audit_events
  end
end
Output of up 
rails db:migrate:up VERSION=20200602144438
== 20200602144438 PartitionAuditEvents: migrating =============================
-- transaction_open?()
   -> 0.0000s
-- table_exists?("audit_events_part_5fc467ac26")
   -> 0.0003s
-- transaction()
-- 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.0019s
-- remove_column("audit_events_part_5fc467ac26", "created_at")
   -> 0.0003s
-- rename_column("audit_events_part_5fc467ac26", "partition_key_bfab5f7bb7", "created_at")
   -> 0.0012s
-- change_column_default("audit_events_part_5fc467ac26", "id", nil)
   -> 0.0011s
   -> 0.0054s
-- table_exists?("audit_events_part_5fc467ac26_000000")
   -> 0.0003s
-- execute("CREATE TABLE audit_events_part_5fc467ac26_000000 PARTITION OF audit_events_part_5fc467ac26\nFOR VALUES FROM (MINVALUE) TO ('2020-01-01')\n")
   -> 0.0022s
-- table_exists?("audit_events_part_5fc467ac26_202001")
   -> 0.0003s
-- 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.0019s
-- table_exists?("audit_events_part_5fc467ac26_202002")
   -> 0.0003s
-- 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.0031s
-- table_exists?("audit_events_part_5fc467ac26_202003")
   -> 0.0003s
-- 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.0025s
-- table_exists?("audit_events_part_5fc467ac26_202004")
   -> 0.0003s
-- 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.0028s
-- table_exists?("audit_events_part_5fc467ac26_202005")
   -> 0.0003s
-- 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.0027s
-- table_exists?("audit_events_part_5fc467ac26_202006")
   -> 0.0003s
-- 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.0027s
-- table_exists?("audit_events_part_5fc467ac26_202007")
   -> 0.0006s
-- 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
-- table_exists?("audit_events_part_5fc467ac26_202008")
   -> 0.0003s
-- 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.0021s
-- table_exists?("audit_events_part_5fc467ac26_202009")
   -> 0.0003s
-- 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.0025s
-- table_exists?("audit_events_part_5fc467ac26_202010")
   -> 0.0003s
-- 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.0029s
-- table_exists?("audit_events_part_5fc467ac26_202011")
   -> 0.0004s
-- 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.0022s
-- table_exists?("audit_events_part_5fc467ac26_202012")
   -> 0.0003s
-- 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.0022s
-- table_exists?("audit_events_part_5fc467ac26_202101")
   -> 0.0003s
-- 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.0030s
-- execute("CREATE  FUNCTION table_sync_function_2be879775d()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF (TG_OP = 'DELETE') THEN\n  DELETE FROM audit_events_part_5fc467ac26 where id = OLD.id;\nELSIF (TG_OP = 'UPDATE') THEN\n  UPDATE audit_events_part_5fc467ac26\n  SET author_id = NEW.author_id,\n    type = NEW.type,\n    entity_id = NEW.entity_id,\n    entity_type = NEW.entity_type,\n    details = NEW.details,\n    updated_at = NEW.updated_at,\n    created_at = NEW.created_at\n  WHERE audit_events_part_5fc467ac26.id = NEW.id;\nELSIF (TG_OP = 'INSERT') THEN\n  INSERT INTO audit_events_part_5fc467ac26 (id,\n    author_id,\n    type,\n    entity_id,\n    entity_type,\n    details,\n    updated_at,\n    created_at)\n  VALUES (NEW.id,\n    NEW.author_id,\n    NEW.type,\n    NEW.entity_id,\n    NEW.entity_type,\n    NEW.details,\n    NEW.updated_at,\n    NEW.created_at);\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0013s
-- execute("CREATE TRIGGER table_sync_trigger_ee39a25f9d\nAFTER INSERT OR UPDATE OR DELETE ON audit_events\nFOR EACH ROW\nEXECUTE PROCEDURE table_sync_function_2be879775d()\n")
   -> 0.0003s
== 20200602144438 PartitionAuditEvents: migrated (0.0615s) ====================
Output of down 
rails db:migrate:down VERSION=20200602144438
== 20200602144438 PartitionAuditEvents: reverting =============================
-- transaction_open?()
   -> 0.0000s
-- execute("DROP TRIGGER IF EXISTS table_sync_trigger_ee39a25f9d ON audit_events")
   -> 0.0006s
-- execute("DROP FUNCTION IF EXISTS table_sync_function_2be879775d()")
   -> 0.0004s
-- drop_table("audit_events_part_5fc467ac26")
   -> 0.0099s
== 20200602144438 PartitionAuditEvents: reverted (0.0145s) ====================

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by 🤖 GitLab Bot 🤖

Merge request reports