Postgres ALTER TABLE: limit the lock queue

A fast Postgres ALTER TABLE can still interrupt traffic while waiting for its lock. Identify both the transaction holding the table and the migration queued behind it. Use a short migration-scoped lock_timeout, a longer statement deadline, and a short transaction. If lock acquisition times out inside an explicit transaction, roll it back before retrying. ^[1][2][3]

Scenario: the application needs an optional delivery_note column on orders. The migration makes a small schema change, but a reporting transaction already holds a read lock. The migration waits. Requests that arrive afterward can then wait behind the migration’s stronger lock request, even though those requests would have been compatible with the original reader.

The investigation below follows that queue. It uses a disposable lab table and separate sessions, not a production incident transcript. Its purpose is to distinguish acquisition time, statement duration and transaction hold time, then decide which change removes the blockage without terminating unrelated work.

Why did adding a nullable column stop ordinary reads?#

PostgreSQL documents the lock level for each ALTER TABLE subcommand. The default is ACCESS EXCLUSIVE unless a subform explicitly says otherwise. That mode conflicts with every table-lock mode, including the ACCESS SHARE used by an ordinary SELECT. A metadata change can therefore need a powerful lock even when its eventual work is small. ^[1][3]

There are two separate delays. First, the migration may wait to acquire its lock. Second, after acquisition it may execute and retain the lock through the transaction. A small empty-table rehearsal often shows only the second part under no contention. It does not show what a queued request does to incoming production traffic.

The lock manager also considers earlier waiters. PostgreSQL’s pg_blocking_pids() documentation distinguishes a held conflicting lock from a conflicting request ahead in the wait queue. The latter is a soft block. This explains why a newly arriving reader can be blocked by the waiting migration rather than directly by the original reader. ^[4]

Do not interpret this as a universal first-in-first-out scheduler for every lock and backend activity. Inspect the actual relations, modes and queue. A partitioned relation, a transaction touching several tables or a parallel query can produce a more complicated graph. Follow the PID that prevents this backend from acquiring its requested lock.

Reconstruct the queue before changing anything#

Capture the affected database, relation, application names and backend PIDs. Look for lock waits, then inspect the blockers returned by the server. Do not start by killing whichever backend has the oldest timestamp; age identifies a candidate, not its business importance or its role in this chain.

-- Monitoring session; visibility depends on your role.
SELECT pid, application_name, state,
       wait_event_type, wait_event,
       clock_timestamp() - xact_start AS transaction_age,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 160) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND (wait_event_type = 'Lock'
       OR application_name LIKE 'ddl-lab-%')
ORDER BY xact_start NULLS LAST;

Read the result as a set of relationships. A reporting backend can hold the compatible read lock, the DDL backend can wait for an exclusive lock, and a later read can name the DDL backend as its blocker. The state and wait_event fields answer different questions: an active backend can be waiting while its query remains in progress. ^[5]

Resolve the relation and lock mode with pg_locks if the application touches several tables. Keep monitoring targeted. Frequently polling the complete lock graph adds work to a server already under pressure. Limited privileges can also hide query text; request the appropriate diagnostic view from the database owner instead of broadening application credentials.

If cancelling the migration is the least disruptive action, coordinate with its owner and migration runner. Cancellation is not the same as terminating a session, and neither is the same as the application handling a failed migration correctly. Confirm whether the runner rolls back, retries or leaves a transaction open before choosing the operational action.

What does the three-session exercise establish?#

Use a disposable database and a dedicated schema. Initialize the fixture in a setup session, then open the three sessions below. The table contains only synthetic data; do not substitute a live business table. Keep each session identifiable with application_name.

-- Setup once in a disposable database.
CREATE SCHEMA ddl_lab;
CREATE TABLE ddl_lab.orders (id bigint PRIMARY KEY);
INSERT INTO ddl_lab.orders VALUES (1);

Session A acquires a read lock and deliberately leaves its transaction open. The SELECT may finish, but the transaction continues holding the table lock. This is a controlled way to keep the acquisition boundary observable without depending on a particular query runtime.

-- Session A
SET application_name = 'ddl-lab-reader';
BEGIN;
SELECT * FROM ddl_lab.orders;
-- Leave this transaction open until the exercise says COMMIT.

Session B starts the migration. Its one-second acquisition timeout and five-second statement timeout are lab values, not production defaults. Start Session C while B is waiting; if B has already timed out, reset the fixture transaction and repeat the observation with a suitable lab window.

-- Session B
SET application_name = 'ddl-lab-migration';
BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5s';
ALTER TABLE ddl_lab.orders ADD COLUMN delivery_note text;
-- On a timeout/error, run ROLLBACK before another attempt.

Session C requests an ordinary read while B is queued. Use the monitoring query to observe its blocker. Once B times out and rolls back, the later read should be able to proceed while A remains open, because the two readers’ lock modes are compatible. Verify that result; do not infer it from the timeout message alone.

-- Session C, while B is waiting
SET application_name = 'ddl-lab-later-read';
SET statement_timeout = '10s';
SELECT * FROM ddl_lab.orders;

Then run ROLLBACK in B if it failed, and COMMIT in A. Retry the short DDL transaction in B with the same local bounds and commit only after success. Check that the column exists. The exercise tests queue release and transactional recovery, not large-table migration speed, replication effects or application compatibility.

A useful negative case is leaving B’s transaction open after a successful ALTER. Session C should remain blocked until B ends that transaction. This distinguishes “the DDL statement finished” from “the lock is no longer held.” Do not combine a slow data backfill with this strong-lock transaction.

How do lock_timeout and statement_timeout overlap?#

lock_timeout measures time spent attempting to acquire a lock, separately for each acquisition attempt. statement_timeout measures the overall statement interval described by the protocol, including time spent waiting. It does not start only after the lock has been acquired. Setting the two to the same nonzero value therefore does not create separate equal-sized waiting and execution budgets. ^[2]

For the scenario, choose an acquisition bound short enough to limit the disruption the waiting DDL can impose. Choose the statement deadline from the particular operation’s expected work and operational tolerance. Use SET LOCAL inside the migration transaction so these values end with that transaction instead of becoming an accidental connection-pool default. ^[6]

A short bound is not free. A busy relation may repeatedly deny the migration a quiet interval. That is useful information: the operation needs a different window, a different lock strategy or coordination with long transactions. Increasing the timeout until the deployment turns green trades away the very traffic protection the bound was meant to provide.

Separate migration configuration from a global database timeout change. The reporting workload and online requests can have different legitimate execution windows. A narrowly scoped migration setting makes the decision easier to review and avoids changing unrelated query behavior.

When is retrying the migration the wrong response?#

A lock acquisition failure is different from a syntax error, a missing privilege or a data constraint failure. The deployment runner must classify the error and end its failed transaction before retrying. Repeating every SQL error can hide a permanent problem and repeatedly obstruct traffic.

Even retryable acquisition failures need a limit. Several one-second attempts can create several separate periods of queued reads. A per-attempt timeout is not a bound on cumulative disruption. Space attempts, cap the overall deployment window, and stop when observation shows a persistent blocker rather than a transient overlap.

Do not put a blind retry around a sequence that also calls an external service or writes to a second system. The PostgreSQL transaction does not roll back those external effects. Keep the DDL unit small and know the runner’s transaction boundaries. After an uncertain connection loss, inspect schema and migration history before repeating the change.

Adding IF NOT EXISTS is not a substitute for reconciliation. It can avoid a duplicate-column error while leaving a column with an unexpected type or definition. Check the actual schema against the intended change. The accepted result is the right column and migration record, not merely a command that stops returning an error. ^[3]

The condition that changes the plan is a genuinely expensive operation. A rewrite, validation or broad dependency change needs its own analysis. The nullable-column scenario should not become a promise that all ALTER TABLE operations are quick or can share its timeout settings.

What evidence belongs in the deployment record?#

Record the exact DDL, server major version, targeted relation, configured acquisition and statement deadlines, transaction boundary, retry classification and stop condition. Include a representative queue observation from the rehearsal and the evidence that readers recovered after a failed attempt. Keep captured query text restricted if it contains business data.

Before release, rehearse with a competing reader and a later arriving read. After release, verify schema, migration history and the absence of the migration’s waiting transaction. Watch application request behavior and the relevant database waits rather than declaring success from the migration process exit alone.

For the illustrative delivery_note change, the decision is a separate short schema transaction followed by any data population in separately reviewed batches. If the application can tolerate the new nullable field, its rollout can be staged without forcing a bulk update into the schema transaction. Client behavior still needs testing; database lock mechanics cannot prove that a serializer or ORM accepts the changed schema.

Keep the rollback decision concrete. Before commit, rollback can abandon this schema change. After the new application writes meaningful data, dropping the column may destroy information. A forward compatibility repair can be safer than reversing the migration. The incident response should name who makes that decision, not embed a destructive “undo” command in an automatic retry loop.

If the original holder is waiting on its client, use the idle-in-transaction investigation. If an index build failed instead, concurrent-index recovery has different cleanup rules.

Does statement_timeout exclude lock waiting?

No. Its documented interval includes the statement’s wait as well as its execution. lock_timeout is narrower and applies separately to acquisition attempts. Keep the latter shorter when both controls are enabled for a migration. ^[2]

Does a nullable column avoid an exclusive lock?

Do not infer lock level from whether existing rows need rewriting. Consult the specific subcommand. PostgreSQL documents ACCESS EXCLUSIVE as the ALTER TABLE default unless a subform states otherwise. ^[3]

Why is a reader blocked by a migration that has not acquired its lock?

An earlier conflicting request in the wait queue can be a soft blocker. pg_blocking_pids() accounts for this case, which is why it can identify the queued migration as the blocker for a later read. ^[4]

Can I retry immediately inside the failed transaction?

The failed explicit transaction must be rolled back or recovered through an intentionally designed savepoint procedure. For the small migration unit here, roll it back and let the runner start a new bounded attempt after classifying the error. Confirm the runner’s real behavior in the rehearsal.