Zero-Downtime Schema Migrations in PostgreSQL

Most schema changes are instant. A few take heavy locks or rewrite entire tables, which can freeze a busy app. Knowing which is which is the key to safe migrations.

Always set a lock timeout

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN notes TEXT;

Even a fast ALTER TABLE needs a brief exclusive lock. If a long query holds a conflicting lock, your ALTER waits, and every new query queues behind it. A lock timeout makes the migration fail fast (and retry) instead of causing an outage.

Safe vs risky changes

• Safe: adding a nullable column; adding a column with a constant default (no table rewrite in modern versions); dropping a column (metadata only).
• Use the concurrent version: CREATE INDEX CONCURRENTLY instead of CREATE INDEX.
• Split into steps: adding NOT NULL, foreign keys or CHECK constraints to big tables.
• Risky: changing a column type (often rewrites the table), renaming columns in use.

Adding a constraint without a long lock

-- 1. Add it without checking existing rows (fast)
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- 2. Validate later; this allows normal reads and writes meanwhile
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Expand and contract

To rename users.name to full_name without breaking running app versions:

1. Expand: add full_name; deploy code that writes both columns.
2. Backfill: copy old data in small batches.
3. Switch: deploy code that reads full_name.
4. Contract: stop writing name, then drop it in a later release.

Backfill in batches

UPDATE users SET full_name = name
WHERE id IN (SELECT id FROM users WHERE full_name IS NULL LIMIT 5000);
-- repeat until 0 rows are updated, pausing between batches

One giant UPDATE holds locks for a long time and creates lots of dead rows at once. Batches keep the database responsive.