Adding an index to a partitioned table and its children in PostgreSQL and Rails

Published: March 30, 2022

Postgres 10+ made partitioning tables much easier, however adding an index to a table with many partitions and data in them already is not straightforward if you want to avoid long table locking.

With partition tables in PostgreSQL, you can add an index to the parent table and it will cascade to all child tables. However, this operation will lock all child tables until the index is applied. While it is not possible to concurrently add an index to the parent table and have it propagate, you can add an index to the parent table only, apply the index concurrently to each of the child tables, and then attach the index to the parent.

Below is an example migration for Rails 7 using PostgreSQL 13 to do just that:

class AddSomeIndexToAParentTable < ActiveRecord::Migration[7.0]
  PARENT_TABLE_NAME = "the_parent_partition_table"
  INDEX_COLUMN_NAME = "column_to_index"
  PARENT_INDEX_NAME = "index_#{PARENT_TABLE_NAME}_on_#{INDEX_COLUMN_NAME}"

  disable_ddl_transaction!

  def up
    # Create base index on parent table for future tables to get
    execute "CREATE INDEX #{PARENT_INDEX_NAME} ON ONLY #{PARENT_TABLE_NAME} (#{INDEX_COLUMN_NAME})"

    child_tables = execute(<<~SQL).pluck("child")
      SELECT inhrelid::regclass AS child
      FROM   pg_catalog.pg_inherits
      WHERE  inhparent = '#{PARENT_TABLE_NAME}'::regclass
    SQL

    child_tables.each do |child_table|
      # Index name matched up with existing child table index names PostgreSQL automatically generates
      child_index_name = "#{child_table}_#{INDEX_COLUMN_NAME}_idx"

      # Add index to child table
      execute "CREATE INDEX CONCURRENTLY #{child_index_name} ON #{child_table} (#{INDEX_COLUMN_NAME})"

      # "Attach" child index to parent index so it can be dropped if the parent is dropped
      execute "ALTER INDEX #{PARENT_INDEX_NAME} ATTACH PARTITION #{child_index_name}"
    end
  end

  def down
    # If we're running the "down" on this, I hope it's only locally
    execute "DROP INDEX #{PARENT_INDEX_NAME}"
  end
end

PostgreSQL Partitioning Docs (for version 13): https://www.postgresql.org/docs/13/ddl-partitioning.html

Tags: rails, postgresql