Zero-Downtime PostgreSQL Migrations: Adding Indexes & Constraints on 100M+ Row Tables Safely

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);