Zero-downtime migrations in PostgreSQL

The migration passed in every test environment. In production, an ALTER TABLE on a 200-million-row table blocks writes for minutes, requests pile up, connection pools run dry and the application goes down. The migration was correct. The problem was the lock.

This article collects the practices we use to change PostgreSQL schemas while the system stays up.

The real problem: the lock queue

Nearly every ALTER TABLE needs an ACCESS EXCLUSIVE lock, which blocks even reads. Even when the change itself is instantaneous, there's a trap: if a long query is using the table, the ALTER waits for it, and every query that arrives afterwards waits behind the ALTER. A millisecond change becomes a multi-minute outage.

The defense is a short lock_timeout. If the lock isn't acquired quickly, the migration fails and can be retried, instead of freezing traffic.

SET lock_timeout = '3s';       -- give up quickly instead of queueing all traffic behind us
SET statement_timeout = '15min';

Expand and contract

Changes that alter the contract between application and database (renaming, retyping, splitting a column) can't happen in one step, because during a deploy old and new versions of the application run at the same time. The expand/contract pattern breaks the change into compatible steps:

1. Expand: add the new structure (column, table) without removing the old one.
2. Write to both: the application writes to the new structure and the old one.
3. Migrate data: backfill the new structure in batches.
4. Read from the new one: the application reads only from the new structure.
5. Contract: remove the old structure, in a later deploy.

Renaming customer_name to full_name, for example, is never a direct RENAME COLUMN in a continuously deployed system. It's five steps, each safe on its own.

Adding columns

Since PostgreSQL 11, adding a column with a non-volatile default (a constant, for example) is instantaneous: the value is recorded in the catalog and the table isn't rewritten. A volatile default, such as random() or clock_timestamp(), still forces a rewrite.

ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';  -- metadata-only on PG 11+

Indexes: always CONCURRENTLY

CREATE INDEX blocks writes for the entire build. CREATE INDEX CONCURRENTLY allows writes, at the cost of taking longer. Two caveats:

• It can't run inside a transaction. Many migration tools wrap each file in a transaction, so you have to turn that off for this step.
• If it fails midway, it leaves an index marked invalid. Check for it and drop it before retrying.

-- migration tool: run outside a transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_created_idx
  ON orders (customer_id, created_at DESC);
 
-- after a failed attempt, find leftovers:
-- SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Constraints without a locked full-table scan

Adding NOT NULL or a foreign key validates every existing row while holding the lock. The safe path separates declaration from validation:

-- 1. Declare without checking existing rows (brief lock).
ALTER TABLE orders ADD CONSTRAINT orders_channel_not_null CHECK (channel IS NOT NULL) NOT VALID;
 
-- 2. Validate existing rows; this takes a weaker lock that allows reads and writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_not_null;
 
-- 3. On PG 12+, SET NOT NULL reuses the validated check and skips the full scan.
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_channel_not_null;

Foreign keys follow the same logic: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID followed by VALIDATE CONSTRAINT.

Backfilling data in batches

A single UPDATE over millions of rows produces a huge transaction, holds row locks for a long time and can delay replication. Prefer small batches with a pause between them, driven by the primary key:

UPDATE orders
   SET full_name = customer_name
 WHERE id IN (
   SELECT id FROM orders
    WHERE full_name IS NULL
    ORDER BY id
    LIMIT 5000
 );
-- repeat until 0 rows are updated, monitoring replication lag between batches

A checklist before every migration

• Do both the current and the new application version work with the resulting schema?
• Does any step rewrite the table or scan every row under a strong lock?
• Is lock_timeout set?
• Are indexes created CONCURRENTLY, outside a transaction?
• Do new constraints use NOT VALID + VALIDATE?
• Is there a rollback plan for each step?

Summary

Safe PostgreSQL migrations depend less on SQL and more on understanding locks. A short lock_timeout prevents queues that take traffic down, expand/contract keeps compatibility during deploys, and concurrent indexes, separately validated constraints and batched backfills let you change large tables without stopping the system.