Postgres Migrations on Large Tables Without Downtime

Schema changes that are instant on a development database can take an application down in production — not because they're slow, but because of locks. A single ALTER TABLE waiting behind a long-running query can block every other query on that table while it waits. This article covers the lock mechanics and the patterns that keep migrations online. (What Are Database Migrations? and Zero-Downtime Deploys cover the application-side contract.)

Lock levels that matter

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock — conflicting with everything, including plain SELECT. Some take weaker locks:

Operation	Lock	Blocks reads?	Blocks writes?
Most ALTER TABLE (add/drop column, set default, alter type)	ACCESS EXCLUSIVE	Yes	Yes
CREATE INDEX	SHARE	No	Yes
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	No	No
ALTER TABLE … VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	No	No
ADD FOREIGN KEY	SHARE ROW EXCLUSIVE (on both tables)	No	Yes

An ACCESS EXCLUSIVE lock held for 5 ms is harmless. The problem is acquiring it.

The lock queue problem

1. A long analytics query (or an idle-in-transaction session) holds an ACCESS SHARE lock on orders.
2. Your migration requests ACCESS EXCLUSIVE and waits.
3. Every new query on orders — even simple SELECTs — conflicts with the waiting exclusive request and queues behind it.
4. Connection pool fills, requests time out, the app is down — for as long as step 1's query runs.

The migration itself would take milliseconds. The outage is the waiting.

Always set lock_timeout — and retry

SET lock_timeout = '2s';
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN notes text;

If the lock can't be acquired within 2 seconds, the statement fails instead of queueing the world behind it. Wrap migrations in a retry loop with backoff. Many migration tools let you set these per migration; if yours doesn't, set them in the migration SQL. Also set idle_in_transaction_session_timeout globally so abandoned transactions can't block migrations indefinitely.

Operations that are cheap vs ones that rewrite

Metadata-only (fast, but still needs the brief exclusive lock):

• ADD COLUMN with no default, or with a non-volatile default (since PostgreSQL 11 the default is stored in the catalog — no rewrite).
• DROP COLUMN (marks it dropped; space is reclaimed later).
• ALTER COLUMN … SET DEFAULT / DROP DEFAULT.
• Widening varchar(n) to a larger n or to text.
• Renaming (but see compatibility below).

Full table rewrite or scan under an exclusive lock (dangerous on big tables):

• ADD COLUMN … DEFAULT with a volatile default (clock_timestamp(), gen_random_uuid()).
• Most ALTER COLUMN … TYPE changes (e.g. int → bigint, text → jsonb).
• SET NOT NULL without a supporting validated constraint (scans the table).
• Adding a CHECK or foreign key without NOT VALID (scans while holding the lock).

Indexes: always CONCURRENTLY

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

• Doesn't block writes; takes longer and does two table scans.
• Can't run inside a transaction block — many migration frameworks wrap each migration in a transaction by default; disable it for this migration.
• On failure it leaves an INVALID index behind that still costs write overhead. Check pg_index.indisvalid, drop it, retry.
• DROP INDEX CONCURRENTLY and REINDEX … CONCURRENTLY exist too.

(Database Indexes.)

Constraints: NOT VALID, then VALIDATE

-- 1. Instant: enforced for new writes, existing rows not checked
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- 2. Later: scans existing rows without blocking reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Same for CHECK constraints.

Adding NOT NULL safely

ALTER TABLE orders ADD CONSTRAINT orders_currency_nn CHECK (currency IS NOT NULL) NOT VALID;
-- backfill any NULLs (batched, below)
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;   -- PG12+: uses the valid CHECK, skips the scan
ALTER TABLE orders DROP CONSTRAINT orders_currency_nn;

Backfills: batches, not one UPDATE

UPDATE orders SET currency = 'USD' WHERE currency IS NULL on 200 million rows is one enormous transaction: long-held row locks, massive WAL generation, replication lag, and bloat. Batch it:

-- repeat until 0 rows updated; pause between batches
WITH batch AS (
  SELECT id FROM orders
  WHERE currency IS NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders o SET currency = 'USD'
FROM batch WHERE o.id = batch.id;

Run it from a script or background job, not the migration runner. Monitor replication lag and throttle when it grows. Ensure the WHERE condition is index-supported (a partial index on the unfilled rows helps), or batch by primary-key ranges.

Type changes: expand, migrate, contract

Changing orders.id from int to bigint in place rewrites the table under an exclusive lock. Instead:

1. Expand: add id_new bigint. Keep it in sync for new writes (trigger or application dual-write).
2. Backfill id_new in batches.
3. Index it concurrently; add constraints NOT VALID then validate.
4. Switch in a short transaction with lock_timeout: swap names, move the primary key using a pre-built unique index (ADD CONSTRAINT … PRIMARY KEY USING INDEX), update sequences and dependent foreign keys.
5. Contract: drop the old column in a later deploy.

The same pattern applies to renames: add the new column, dual-write, backfill, switch readers, drop the old one — because during a rolling deploy, old code that references the old name is still running.

Guardrails worth automating

• A migration linter (tools such as squawk for Postgres) that flags non-concurrent index creation, volatile defaults, missing NOT VALID, and type changes in CI. (GitHub Actions CI Basics.)
• lock_timeout injected into every migration by default.
• Rehearsal against a production-sized copy — timings on a small dataset are meaningless.
• A fresh backup or snapshot before risky migrations. (Postgres Point-in-Time Recovery.)
• For AI-generated migrations: require the agent to state the lock level and whether each statement rewrites or scans, and to run the linter. It's easy for generated migrations to look innocent and not be.

Checklist

• lock_timeout (seconds) and statement_timeout set; retries with backoff
• Indexes created/dropped CONCURRENTLY, outside a transaction; invalid indexes checked
• Foreign keys and checks added NOT VALID, validated separately
• NOT NULL via validated CHECK first
• Backfills batched, throttled, run outside the migration
• Type changes and renames via expand/contract
• Rehearsed on production-sized data; backup taken

---

EasySpawn provisions managed Postgres per project with daily backups and on-demand snapshots on the Team plan, so Claude Code can rehearse a migration against a restored copy and you can snapshot right before running it for real. See pricing or join the waitlist.

Related: Postgres Major Version Upgrades · Database Transactions Explained · Multi-Tenant Postgres Patterns