Migrations PostgreSQL Zero-Downtime : Index et Contraintes sur des Tables de 100M+ Lignes

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