PostgreSQL Migration Best Practices for Zero-Downtime Deployments

Zero-downtime schema migrations are not about a single trick. They require a combination of techniques: lock management, phased rollouts, backfill strategies, and CI enforcement. This guide covers the full playbook. 1. Always Set lock_timeout The single most important practice for safe migrations. Every DDL statement that acquires a table lock should be preceded by a lock_timeout: -- Set a 5-second lock timeout -- If the lock cannot be acquired in 5s, the statement fails -- This prevents the lock queue problem (see below) SET lock_timeout = '5s'; ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'; -- Reset after DDL RESET lock_timeout; Without lock_timeout, a DDL statement will wait indefinitely for a lock. While it waits, every subsequent query that needs a conflicting lock queues behind it. A single long-running analytics query can trigger a cascading outage: Long query holds ACCESS SHARE on the table ALTER TABLE waits for ACCESS EXCLUSIVE lock All new queries queue behind the