Writing safe migrations

Writing Safe Migrations

Overview

Before running any migration, answer one question: does this block all my writes, or not? Many DDL statements take a lock that stalls every insert/update on the table until they finish — fatal on a large live table. The safe variants exist to avoid exactly that.

When to Use

• Adding an index to an existing large table.
• ALTER TABLE, adding columns, or adding check constraints.
• Any migration on a table with meaningful traffic.
• Writing update-heavy queries inside transactions.

Quick Reference

Operation	Danger	Safe way
CREATE INDEX	Locks table against writes	CREATE INDEX CONCURRENTLY
Add check/FK constraint	Full-table scan blocks writes	Add NOT VALID, then VALIDATE CONSTRAINT later
Dropping columns	Hard to roll back	Keep migrations additive; expand-and-contract
Long transaction	Holds locks, blocks autovacuum	Keep transactions short

Patterns

-- Build the index without locking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY idx_tasks_tenant ON tasks (tenant_id);

-- Add a constraint without a write-blocking full-table scan, validate afterwards
ALTER TABLE tasks ADD CONSTRAINT chk_status
    CHECK (status IN ('pending','running','done')) NOT VALID;
ALTER TABLE tasks VALIDATE CONSTRAINT chk_status;  -- takes only a lighter lock

Rules

• Additive migrations. Prefer adding over removing. Avoid dropping/renaming columns in the same deploy that changes app code. For unavoidable changes use expand-and-contract: add new → backfill → switch reads/writes → remove old, across separate deploys.
• Wrap in a transaction where possible so a failure rolls back cleanly. Note CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
• Short transactions. Every UPDATE holds a row-level lock until commit, and long transactions block autovacuum (see tuning-autovacuum-and-bloat). Lock only the rows you actually need.
• No external calls mid-transaction. Don't hold a transaction open across an HTTP/RPC call — the locks stay held for the whole round trip.

Common Mistakes

• CREATE INDEX (without CONCURRENTLY) on a big live table — blocks all writes for the whole build.
• Adding a check/FK constraint without NOT VALID — the validation scan blocks writes.
• A transaction that makes an external API call while holding row locks — turns a fast update into a lock held for seconds.
• Destructive column drops coupled to the same release as the code change — no clean rollback path.