Writing Safe Migrations

---
name: writing-safe-migrations
description: Guides Postgres migrations when altering large or live tables, adding indexes or constraints, backfilling columns, changing column types, or planning zero-downtime rollouts. Covers lock levels, lock_timeout, CREATE INDEX CONCURRENTLY, NOT VALID constraints, and expand-and-contract.
---

# Writing Safe Migrations

## Overview

Two questions before any migration runs against a live table:

1. **Does this rewrite the table?** A rewrite holds `ACCESS EXCLUSIVE` for the whole rewrite — every read and write blocks.
2. **Can it wait for its lock without taking the table down?** A statement waiting for `ACCESS EXCLUSIVE` queues *ahead of* every query that arrives after it. One long-running `SELECT` plus one unguarded `ALTER TABLE` stalls the entire table, even for statements the migration itself would never have blocked.

Question 2 causes more outages than question 1, and `lock_timeout` is the whole fix.

## When to Use

- Adding an index to an existing large table.
- `ALTER TABLE`, adding or dropping columns, changing types, adding constraints.
- Backfilling a column across many rows.
- Any migration on a table with meaningful traffic.

## Always set a lock timeout

Never issue DDL against a live table without bounding the wait:

```sql
SET lock_timeout = '5s';        -- fail instead of building a lock queue
SET statement_timeout = '0';    -- but let a long index build finish
ALTER TABLE tasks ADD COLUMN priority int;
```

If the lock isn't acquired in 5s the statement errors — retry it later. That is the correct outcome: a failed migration is recoverable, a stalled table is an incident.

`lock_timeout` only bounds *acquiring* a lock, not holding one. It will not save you from a rewrite that takes ten minutes once it starts — check the lock table for that.

Find what's blocking you:

```sql
SELECT pid, pg_blocking_pids(pid), wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
```

## Quick Reference

| Operation | Danger | Safe way |
|---|---|---|
| `CREATE INDEX` | Blocks writes for the whole build | `CREATE INDEX CONCURRENTLY`, outside a transaction |
| Add check / FK constraint | Validation scan blocks writes | `NOT VALID`, then `VALIDATE CONSTRAINT` |
| `SET NOT NULL` | Full scan blocks reads and writes | Add an equivalent `CHECK (col IS NOT NULL) NOT VALID`, validate, then `SET NOT NULL` |
| `ALTER COLUMN TYPE` | Usually a full rewrite | New column + backfill + swap (expand-and-contract) |
| Backfill in one `UPDATE` | Long transaction, bloat, blocked autovacuum | Batch in separate transactions |
| Dropping columns | Hard to roll back | Keep migrations additive; expand-and-contract |

Full per-operation lock levels and which operations rewrite: [reference/lock-levels.md](reference/lock-levels.md).

## Patterns

```sql
-- Index without blocking writes. Cannot run inside a transaction block,
-- and do NOT set statement_timeout here — the build may be long.
SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_tasks_tenant ON tasks (tenant_id);
```

**Never wrap `CREATE INDEX CONCURRENTLY` in a transaction block: PostgreSQL rejects it.** Run it as a top-level statement. Do not set a short `statement_timeout`; a healthy build on a large table may take a long time.

**A failed `CREATE INDEX CONCURRENTLY` leaves an invalid index behind.** It is not automatically cleaned up, it consumes writes and disk, and the planner won't use it. Always check before retrying:

```sql
SELECT c.relname
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

DROP INDEX CONCURRENTLY idx_tasks_tenant;  -- then retry the create
```

```sql
-- Constraint without a write-blocking full-table scan
ALTER TABLE tasks ADD CONSTRAINT chk_status
    CHECK (status IN ('pending','running','done')) NOT VALID;

-- Validate separately: SHARE UPDATE EXCLUSIVE, does not block reads or writes
ALTER TABLE tasks VALIDATE CONSTRAINT chk_status;
```

```sql
-- Batched backfill: one transaction per chunk, not one for the whole table
UPDATE tasks SET priority = 0
WHERE id IN (
    SELECT id FROM tasks WHERE priority IS NULL LIMIT 5000
);
-- commit, sleep briefly, repeat until zero rows updated
```

### Large column-type change

**Never use one in-place `ALTER COLUMN TYPE` for a large live table when it rewrites the table.** Changes such as `integer` to `bigint` rewrite all rows and rebuild dependent indexes while `ACCESS EXCLUSIVE` is held. Use expand-and-contract across separate deploys:

1. Add the new column with `lock_timeout` set.
2. Dual-write old and new columns, then backfill bounded key ranges in separate transactions.
3. Build replacement indexes with `CREATE INDEX CONCURRENTLY`; add replacement constraints with `NOT VALID` and validate separately where supported.
4. Switch reads, stop writing the old column, verify, and only then remove old objects in a later deploy.

## Rules

- **Bound every lock.** `SET lock_timeout` before DDL, then retry on failure.
- **Additive migrations.** Prefer adding over removing. For unavoidable changes use expand-and-contract across separate deploys: add new → backfill → switch reads/writes → drop old.
- **Adding a column with a default is cheap in PostgreSQL 11+** — a non-volatile default is stored as metadata and does not rewrite the table. Do not backfill merely to materialize that value. A *volatile* default (`gen_random_uuid()`, `random()`) does rewrite it; add the column nullable and backfill in batches instead.
- **Short transactions.** Every `UPDATE` holds row locks until commit, and a long transaction holds back the oldest-transaction horizon so autovacuum cannot reclaim dead tuples (see `tuning-autovacuum-and-bloat`).
- **No external calls mid-transaction.** Don't hold a transaction open across an HTTP/RPC round trip — the locks stay held for its full duration.
- **Wrap in a transaction where possible** so failure rolls back cleanly. `CREATE INDEX CONCURRENTLY` and `DROP INDEX CONCURRENTLY` cannot run inside one.

## Common Mistakes

- DDL with no `lock_timeout` — the statement queues for `ACCESS EXCLUSIVE` and every query behind it queues too, taking the table down without ever acquiring the lock.
- `CREATE INDEX` without `CONCURRENTLY` on a big live table — blocks writes for the whole build.
- Retrying a failed `CREATE INDEX CONCURRENTLY` without dropping the invalid index it left behind.
- Adding a check or FK constraint without `NOT VALID` — the validation scan blocks writes.
- `SET NOT NULL` directly on a large table — full scan under `ACCESS EXCLUSIVE`.
- Backfilling a whole large table in one `UPDATE` — long transaction, mass dead tuples, autovacuum stalled behind it.
- A transaction that makes an external API call while holding row locks.
- Destructive column drops in the same release as the code change — no clean rollback.