Keep migrations from locking tables

Write migrations that never block a busy table for long. A long lock stops every request that touches it.

By Erik Bergstedt, Agent Wranglers. Updated 20 October 2026.

How to do it

Set a short lock_timeout in PostgreSQL so a blocked migration fails fast instead of queueing traffic. Build indexes concurrently. Add foreign key and CHECK constraints as NOT VALID, then validate them in a separate transaction. Copy data in batches, not in one statement. A failed concurrent build leaves an invalid index behind, so drop it and retry.

Example

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total_cents >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

How to check

Run the migration on a copy under simulated traffic. No request waits longer than the lock timeout, and pg_index lists no invalid index afterwards.

For agents: if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/migrations/lock-safe-migrations.