Migration Table Locks

ActiveRecord migrations that issue DDL statements requiring an ACCESS EXCLUSIVE or SHARE lock on large tables. Common offenders include creating an index without CONCURRENTLY, adding a CHECK constraint without NOT VALID, changing a column’s type, or dropping a column under traffic.

Before / After

class AddStatusIndexAndConstraint < ActiveRecord::Migration[7.1]
def change
  # 1. Non-concurrent index on 50M rows:
  #    holds SHARE lock, blocks writes for 10+ min.
  add_index :orders, :status

  # 2. CHECK without NOT VALID scans the whole
  #    table under ACCESS EXCLUSIVE on PG 15+.
  add_check_constraint :orders,
    "status IN ('pending','paid','refunded')",
    name: 'orders_status_check'

  # 3. Changing a column type forces a rewrite.
  change_column :orders, :total_cents, :bigint
end
end

class AddStatusIndexAndConstraint < ActiveRecord::Migration[7.1]
disable_ddl_transaction!

def up
  # 1. Concurrent index, no exclusive lock.
  add_index :orders, :status, algorithm: :concurrently

  # 2. NOT VALID adds constraint instantly
  #    without scanning existing rows.
  add_check_constraint :orders,
    "status IN ('pending','paid','refunded')",
    name: 'orders_status_check',
    validate: false

  # 3. Validate in a second transaction,
  #    still on a SHARE UPDATE EXCLUSIVE lock
  #    that does not block writes.
  validate_check_constraint :orders,
    name: 'orders_status_check'

  # 4. Column type change stays in a separate
  #    migration: add new column, backfill in
  #    batches, swap, drop old.
end
end

Why this hurts

PostgreSQL’s lock model is the crux. ALTER TABLE ... ADD COLUMN DEFAULT 'constant' is a metadata-only operation from PostgreSQL 11 onward and completes in milliseconds, but developers often assume it always rewrites the table and underreact to the real offenders: CREATE INDEX without CONCURRENTLY holds a SHARE lock that blocks all writes for the full duration of the build. On a 50M-row table this is 10-30 minutes of failed checkouts, queued webhooks, and timed-out background jobs.

ADD CONSTRAINT ... CHECK (...) without NOT VALID takes an ACCESS EXCLUSIVE lock while PostgreSQL scans every existing row to prove the new constraint holds. During the scan, no read or write can touch the table. Deployments that bundle a new CHECK constraint with other DDL statements often exceed the lock_timeout and roll back partially, leaving the schema in a half-applied state that is painful to recover.

Changing a column type (change_column :orders, :total_cents, :bigint) forces a full table rewrite even on modern PostgreSQL, because every row must be physically rewritten to the new representation. The rewrite takes an ACCESS EXCLUSIVE lock and blocks both reads and writes for the duration. Rack::Timeout fires across web workers, Sidekiq jobs retry and amplify load, and the load balancer marks unhealthy backends that are really just waiting on a locked table.

The lock_waiter cascade is the worst failure mode: one slow DDL statement holds a lock, dozens of subsequent queries queue behind it, and the database’s max_connections saturates with waiting backends. PostgreSQL performance collapses across all tables, not just the one being altered, because connection slots are a global resource.

See also: Rails Zero-Downtime Database Deployment.

Get Expert Help

Inheriting a legacy Rails codebase with this problem? Request a Technical Debt Audit.