Understanding Postgres Lock Queues
Migrating databases, especially PostgreSQL, often carries the specter of downtime. Developers typically assume this downtime is caused by the Data Definition Language (DDL) itself taking a long time to execute. However, field notes from recent migrations reveal a more insidious cause: the queuing mechanism of PostgreSQL's lock requests. Specifically, an ALTER TABLE command, which requires an ACCESS EXCLUSIVE lock, can become the bottleneck. This lock type blocks all reads and writes to the table. When this ALTER TABLE statement is pending, any subsequent query arriving at the database must wait in the same lock queue. This means even an instantaneous DDL operation can indirectly lead to minutes of application unavailability if it gets stuck behind a long-running query holding or waiting for a necessary lock.
PostgreSQL manages concurrency through a sophisticated locking system. When a transaction requests a lock, it's added to a queue. Locks are generally granted in the order they are requested. An ACCESS EXCLUSIVE lock, necessary for many ALTER TABLE operations like adding a column or changing a data type, is the most restrictive. It prevents any other operation—read or write—from accessing the table. If a long-running SELECT statement is currently holding a lock that the ALTER TABLE needs, or if the ALTER TABLE is waiting for a lock that is currently held, any new queries arriving will be added to the queue behind the pending ALTER TABLE. This creates a cascade effect: the ALTER TABLE waits for its lock, and everything else waits for the ALTER TABLE. This is why a seemingly simple `ALTER TABLE ADD COLUMN` can bring an application to its knees for an extended period.
Mitigating Downtime with lock_timeout
The critical insight is that the downtime isn't usually caused by the DDL execution time itself, but by the time spent acquiring the necessary lock. PostgreSQL provides the lock_timeout configuration parameter. This parameter sets a maximum duration that a statement will wait to acquire a lock. If the timeout is reached before the lock can be acquired, the statement will error out with a lock_not_available error. This is distinct from statement_timeout, which limits the total execution time of a statement after it has acquired its locks.
By setting a short lock_timeout—often just a few seconds—on the connection executing the migration, you prevent the migration statement from indefinitely blocking the lock queue. If the ALTER TABLE cannot acquire its required lock within the specified timeout, it will fail immediately. This failure is preferable to prolonged downtime. The application remains available, and the migration can be retried later, perhaps during a period of lower traffic or after the blocking query has completed. This strategy transforms a potential minutes-long outage into a brief, manageable error that can be handled programmatically.
Consider an example: An application has a table with millions of rows. A developer needs to add a new column. Without intervention, the ALTER TABLE ADD COLUMN statement might wait for an existing long-running report query to finish. This report query could take an hour. During that hour, all new writes to the table would be blocked by the pending ALTER TABLE. By setting lock_timeout = '5s', the ALTER TABLE will attempt to acquire the lock for 5 seconds. If it succeeds, the column is added quickly. If it fails, it raises an error, and the application continues to accept writes while the report query finishes. The migration can then be re-attempted.
The Expand/Contract Deployment Pattern
Beyond managing lock acquisition, a robust strategy for zero-downtime migrations involves breaking down schema changes into multiple, independent deployment phases. The