Zero-Downtime PostgreSQL Migrations: Adding Indexes & Constraints on 100M+ Row Tables Safely
Avoid blocking production traffic: battle-tested SQL strategies for adding indexes, foreign keys, and NOT NULL constraints on multi-gigabyte PostgreSQL tables.
Running a standard ALTER TABLE orders ADD COLUMN status VARCHAR NOT NULL DEFAULT 'pending'; on a table with 50 million rows takes an ACCESS EXCLUSIVE lock. This blocks all incoming read and write transactions, creating a cascading queue of blocked HTTP requests that will crash your web application in seconds.
Here are the non-blocking SQL patterns required to perform zero-downtime database migrations at scale.
Safe Non-Blocking Index Creation
-- BAD: Locks table for writes during the entire index build
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- GOOD: Builds index in background without locking table writes
SET lock_timeout = '2s';
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
Frequently Asked Questions
Why does standard CREATE INDEX lock PostgreSQL tables?
A standard CREATE INDEX acquires a SHARE lock that blocks all incoming write queries. Using CREATE INDEX CONCURRENTLY avoids write locks entirely.
How do you add a NOT NULL column to a 50M row table safely?
Add the column with a default value without NOT NULL, backfill in batches, add a CHECK constraint with NOT VALID, validate it concurrently, and then enforce NOT NULL.
Subscribe to the Technical Newsletter
Get deep-dives into DevOps, Kubernetes, Linux performance, and self-hosted AI architecture.