Why does a fast ALTER TABLE freeze all traffic to a table?

Many schema changes finish almost instantly once they start. Adding a nullable column with no default, renaming a column, or dropping a constraint usually only rewrites catalog entries. The problem is what happens before the statement starts. It has to acquire an ACCESS EXCLUSIVE lock on the table, and that lock conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes.

Postgres grants table locks through a queue. If any session already holds a conflicting lock, the ALTER TABLE waits. That holder might be a reporting query that has been scanning for ten minutes, or a session sitting idle in transaction after it read one row and never committed. A transaction keeps its locks until it ends, so an idle session holds its lock indefinitely.

While the ALTER waits, Postgres does not let later requests jump past it if they conflict with the waiting request. Every new SELECT, INSERT, and UPDATE on that table lines up behind the migration. The table is now unavailable to every new query even though nothing is executing against it. Connection pools fill, request latency climbs, and upstream timeouts start failing. All of this happens before the migration has done any work.

The risk therefore comes from lock wait time, not execution time. A migration that runs in milliseconds can cause a full outage if it waits minutes for its lock. This applies to anyone who runs DDL against tables that serve live traffic, regardless of how cheap the change itself is.

Lock queue for table t (later conflicting requests cannot jump the queue)

+-------------------+     +-------------------+     +-------------------+
| Session A         |     | Migration         |     | Queued behind it  |
| idle in txn or    |     | ALTER TABLE t     |     | SELECT  [WAITING] |
| long-running query|---->| needs ACCESS      |---->| INSERT  [WAITING] |
| holds ACCESS SHARE|     | EXCLUSIVE         |     | UPDATE  [WAITING] |
| [GRANTED]         |     | [WAITING]         |     | SELECT  [WAITING] |
|                   |     | work done: none   |     | ...more arriving  |
+-------------------+     +-------------------+     +-------------------+
                                                              |
                                                              v
+-----------------------------------------------------------------------+
| Application: connection pool fills, latency climbs, timeouts fire     |
+-----------------------------------------------------------------------+

----> : blocks the request to its right
Session A keeps its lock until its transaction ends.
Outage length = lock WAIT time, not ALTER execution time.

A long-running query or idle-in-transaction session holds ACCESS SHARE, so the ALTER waits for ACCESS EXCLUSIVE, and every new SELECT, INSERT and UPDATE on the table queues behind the waiting ALTER before the migration has done any work.

Which Postgres DDL operations take an ACCESS EXCLUSIVE lock?

Lock level matters because of what the lock conflicts with. ACCESS EXCLUSIVE conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. While a statement holds it, or waits in the queue for it, even reads stop. Weaker modes block only some operations, so a long wait for one of them is less damaging.

Operation Typical lock mode Blocks reads? Blocks writes?
Most ALTER TABLE forms (add/drop column, change type, SET NOT NULL) ACCESS EXCLUSIVE Yes Yes
ALTER TABLE ... RENAME, DROP TABLE, TRUNCATE ACCESS EXCLUSIVE Yes Yes
CREATE INDEX SHARE No Yes
CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE No No
ALTER TABLE ... VALIDATE CONSTRAINT SHARE UPDATE EXCLUSIVE No No

A plain CREATE INDEX blocks writes because Postgres needs a stable set of rows to build from. The concurrent variant avoids that by scanning the table more than once and waiting for existing transactions to finish. It takes longer and cannot run inside a transaction block. VALIDATE CONSTRAINT can use a weaker lock because the constraint is already enforced for new rows. It only has to check existing rows, and that work does not conflict with ongoing reads or writes.

These levels are not fixed across releases. Some ALTER TABLE subcommands have moved to weaker locks over time. Whether an operation rewrites the whole table also varies by version, for example adding a column with a default. Check the explicit-locking and ALTER TABLE pages of the documentation for the Postgres version you run before you assume a migration is cheap.

How does lock_timeout work?

lock_timeout is an ordinary configuration parameter. Its value is a duration, and a bare number is read as milliseconds. The default is 0, which disables the timeout and lets a statement wait for a lock indefinitely.

When a statement requests a lock that cannot be granted immediately, the backend joins the wait queue for that lock and starts a timer set to lock_timeout. If the lock is granted before the timer expires, the timer is cleared and the statement continues normally. If the timer expires first, Postgres cancels the statement with ERROR: canceling statement due to lock timeout (SQLSTATE 55P03). When the canceled request leaves the queue, the requests that were waiting behind it can be granted, so normal traffic resumes.

A few details matter for migrations:

  • It applies per lock request, not per statement. A statement that acquires several locks gets a fresh timer for each one that has to wait. The timer covers both explicit LOCK TABLE and the locks that DDL acquires implicitly. It also covers row-level locks, such as those taken by SELECT ... FOR UPDATE.
  • It cancels only the waiting statement. The enclosing transaction is put into an aborted state, and the client must roll it back. Any work done earlier in that transaction is discarded at rollback.
  • It interacts with statement_timeout. If both are set, lock_timeout needs to be shorter than statement_timeout. Otherwise the statement timeout fires first and you get a less specific error.
  • It is separate from deadlock detection. deadlock_timeout controls how long a backend waits before checking for a deadlock. It does not cancel waits that are merely long.

In effect, lock_timeout makes the migration fail fast instead of waiting indefinitely. Leaving the queue early is what keeps a blocked migration from turning into a table-wide outage.

Who should use lock_timeout for migrations?

Use it whenever your schema changes run against a database that is serving traffic at the same time. In practice that means:

  • Application developers who write migration files. They decide which statements run and in what order, and they can add SET LOCAL lock_timeout to each transactional migration.
  • Platform or infrastructure engineers who own the migration runner and deploy pipeline. They can set a role-level baseline for the migration role and build the retry behavior once for every team.
  • DBAs and on-call engineers. They benefit when a blocked migration fails with a clear 55P03 error instead of surfacing as an application-wide latency spike.

You need it less if every schema change runs in a maintenance window with application traffic stopped. With no concurrent queries, nothing can pile up behind the migration's lock request.

When should you use lock_timeout?

Set it for any migration statement that acquires a strong table lock on a table with live traffic. ACCESS EXCLUSIVE operations are the most important case, because a long wait for one of them blocks reads as well as writes. The cases where it pays off most:

  • Catalog-only ALTER TABLE changes on busy tables. The change is cheap, so the lock wait is the only real risk.
  • Automated migrations run from CI/CD during business hours, where nobody is watching each statement.
  • Databases where long-running reports, analytics queries, or idle in transaction sessions are possible, even if they are rare.
  • Weaker but still blocking locks, such as the SHARE lock taken by plain CREATE INDEX. A long wait for one of these still stalls writes behind it.

Set it inside the migration transaction or on a dedicated migration role, as described below. Do not set it server-wide.

When should you avoid relying on lock_timeout?

lock_timeout is the wrong tool, or not enough on its own, in these situations:

  • As a global default in postgresql.conf. Application code that normally waits briefly on row locks would start failing with lock-timeout errors.
  • For table rewrites or long scans. A type change that rewrites the table blocks traffic for the whole rewrite once its lock is granted. Use the patterns in the alternatives section instead.
  • During a true maintenance window. If traffic is stopped and you would rather wait for a straggling session than fail the deploy, a short timeout only adds retries.
  • On tables that always have long-running transactions. The lock may never be granted within a safe timeout. Fix the transactions, or schedule the change for a quiet period, rather than raising the timeout until it stops protecting anything.
  • When SET LOCAL will not take effect. That happens with autocommit runners and non-transactional statements like CREATE INDEX CONCURRENTLY. A SET LOCAL there silently does nothing. Use a session-level setting on a direct connection, or a role-level default, instead.

What is the difference between lock_timeout and statement_timeout?

The two settings bound different things. lock_timeout caps how long a statement waits to acquire a lock, and it applies separately to each lock request. statement_timeout caps the total run time of a statement, including any lock wait. Once a lock is granted, lock_timeout no longer applies.

lock_timeout statement_timeout
Measures Time waiting for each lock Total statement run time
Protects against Your DDL queueing behind a long transaction while other traffic queues behind your DDL A statement running far longer than planned
Typical migration value Short (about a second) Sized to the expected work, plus margin
Helps once the lock is held No Yes, but the abort comes after blocking has already happened

Migrations want both settings, chosen independently. A short lock_timeout makes the migration give up quickly instead of building a lock queue. A separate statement_timeout sets a ceiling on runaway work. Don't put either in postgresql.conf. A global lock_timeout turns ordinary row-lock waits in application code into errors, and a global statement_timeout kills legitimate reports and maintenance jobs.

lock_timeout does not make slow DDL safe. A table rewrite that gets its ACCESS EXCLUSIVE lock instantly still blocks every reader and writer for as long as the rewrite runs.

Illustrative estimate (assumed numbers, not a benchmark). Assume the following inputs:

  • p99 query latency: 200 ms
  • Client timeout: 2 s

A query that arrives just after the migration starts waiting can queue for the full lock_timeout and then run normally. That gives:

worst-case latency ≈ lock_timeout + p99

To keep requests under the client timeout:

lock_timeout < client timeout − p99 = 2000 − 200 = 1800 ms

Choosing 1 s caps the worst case near 1.2 s. That leaves headroom for retries and network jitter. Plug your own p99 and client timeout into the same formula to get your limit.

-- Migration session only. SET LOCAL lasts until COMMIT/ROLLBACK,
-- so application connections keep the server defaults.
BEGIN;

-- Give up on any single lock request after 1 s instead of building a
-- queue that application queries pile up behind.
-- Per the illustrative estimate (assumed 200 ms p99, 2 s client timeout),
-- this must stay below 1800 ms.
SET LOCAL lock_timeout = '1s';

-- Separate ceiling on total run time, sized to the expected work.
SET LOCAL statement_timeout = '30s';

-- Adding a nullable column with no default is a catalog-only change. It needs
-- ACCESS EXCLUSIVE briefly but does not rewrite the table.
ALTER TABLE orders ADD COLUMN note text;

COMMIT;

-- If the lock is not granted within 1 s, Postgres raises:
--   ERROR:  canceling statement due to lock timeout   (SQLSTATE 55P03)
-- The transaction aborts and other traffic proceeds. Roll back, wait, and retry.

-- Counterexample: lock_timeout does NOT protect you here.
-- Changing int -> bigint rewrites the table. If the lock is granted immediately,
-- every reader and writer is blocked for the whole rewrite. statement_timeout
-- only aborts it after that blocking has already happened.
-- BEGIN;
-- SET LOCAL lock_timeout = '1s';
-- ALTER TABLE orders ALTER COLUMN id TYPE bigint;
-- COMMIT;

Shows a migration transaction that uses SET LOCAL to apply a short lock_timeout and a separately sized statement_timeout without changing server-wide defaults, and contrasts a cheap catalog-only ALTER with a table rewrite that lock_timeout cannot make safe.

How do you scope lock_timeout safely: SET LOCAL, session, or role?

Once you have a value, the next decision is where to set it. The scope determines whether the setting reaches only your DDL or also leaks into unrelated queries.

SET LOCAL lock_timeout applies only until the current transaction commits or rolls back, then reverts automatically. This makes it safe behind a transaction-mode pooler, where consecutive transactions from one client can land on different server connections, and one server connection serves many clients. A plain session-level SET persists on the server connection after your migration finishes. The pooler can then hand that connection to an application request, which inherits a 1 s lock timeout it never asked for and fails unexpectedly under ordinary contention.

SET LOCAL has one trap. Outside a transaction block it does nothing except emit a warning. If your runner executes statements in autocommit mode, the setting silently fails to apply.

Scope Lifetime Leak risk with pooling Best use
SET LOCAL Current transaction None Default for transactional migrations
Session SET Until connection closes or RESET High in transaction-mode pooling Direct, unpooled connections only
ALTER ROLE ... SET Every new session for that role Low, if the role is used only for migrations Baseline for a dedicated migration role

Integrating with migration tooling

You can integrate this in two ways. The first is to prepend SET LOCAL lock_timeout to each migration file. The second is to configure the runner's connection, for example by giving a dedicated migration role a role-level default. Using both gives you a floor plus per-migration overrides.

Before relying on SET LOCAL, check whether your tool wraps each migration in a transaction. Many tools let you disable that wrapper per migration. You need to disable it for statements that cannot run in a transaction block, such as CREATE INDEX CONCURRENTLY. For those non-transactional migrations, use a session SET on a direct connection, followed by RESET lock_timeout.

-- Option 1: per-migration scope. Reverts automatically at COMMIT/ROLLBACK,
-- so nothing leaks to a pooled server connection.
BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
COMMIT;

-- Confirm the value did not outlive the transaction (prints the default).
SHOW lock_timeout;

-- Trap: outside a transaction block, SET LOCAL does not apply.
-- PostgreSQL emits: WARNING: SET LOCAL can only be used in transaction blocks
SET LOCAL lock_timeout = '1s';
SHOW lock_timeout;  -- still the default

-- Avoid on pooled connections: a session-level SET persists on the server
-- connection until RESET or disconnect, so later clients can inherit it.
-- SET lock_timeout = '1s';

-- Option 2: role-level baseline for a dedicated migration role.
-- Applies to every new session for this role, so application roles are unaffected.
CREATE ROLE migrator LOGIN PASSWORD 'change-me-placeholder';
ALTER ROLE migrator SET lock_timeout = '2s';

-- A migration can still tighten the baseline for one transaction.
BEGIN;
SET LOCAL lock_timeout = '500ms';
CREATE INDEX orders_shipped_at_idx ON orders (shipped_at);
COMMIT;

This example scopes lock_timeout with SET LOCAL inside a migration transaction, shows that SET LOCAL does nothing outside a transaction block, and sets a role-level baseline for a dedicated migration role that individual migrations can override.

How should a migration runner retry after a lock timeout?

When a lock timeout fires, Postgres raises an error and aborts the transaction. For transactional DDL, nothing is half-applied: the table, the catalog and the migration-tracking row are exactly as they were. That makes the failure safe to retry. The exception is non-transactional work. A CREATE INDEX CONCURRENTLY that fails can leave an invalid index, which you must drop before the runner tries again.

The runner should catch the lock-timeout error specifically, wait, and try again. The wait should use exponential backoff with random jitter so retries do not line up with each other or with the blocker's own cycle. Cap the number of attempts, then fail loudly and alert a human.

Retries are not free. Each attempt rejoins the lock queue, and while it waits, every query behind it waits too.

Illustrative estimate (assumed numbers, not a benchmark):

  • Worst-case cumulative stall = attempts × lock_timeout.
  • With an assumed lock_timeout of 2s and 5 attempts, that is 5 × 2s = 10s of stall. It arrives as five separate 2-second stalls.
  • Backoff of 1s, 2s, 4s and 8s between attempts adds 15s of wall time. The run takes about 25s before it gives up.

A tight retry loop on a hot table turns one stall into a stream of small ones. On tables with constant long-running transactions, the lock may never be granted. Teams then tend to raise the timeout until it no longer protects anything. When retries keep failing, fix the long transactions instead of raising the timeout.

The payoff is operational. A migration that would have frozen production for minutes becomes a routine failed deploy with a clear error. Someone can investigate it during working hours instead of during an incident.

import random
import time

import psycopg2

LOCK_NOT_AVAILABLE = "55P03"  # SQLSTATE raised when lock_timeout fires
MAX_ATTEMPTS = 5
BASE_DELAY_S = 1.0
MAX_DELAY_S = 8.0

DDL = "ALTER TABLE orders ADD COLUMN note text"

def run_migration(dsn: str) -> None:
    conn = psycopg2.connect(dsn)
    try:
        for attempt in range(1, MAX_ATTEMPTS + 1):
            try:
                with conn:  # one transaction: commit on success, rollback on error
                    with conn.cursor() as cur:
                        # Scoped to this transaction; bounds how long we block the queue.
                        cur.execute("SET LOCAL lock_timeout = '2s'")
                        cur.execute(DDL)
                        cur.execute("INSERT INTO schema_migrations (version) VALUES (%s)", ("0042",))
                return  # DDL and tracking row committed together
            except psycopg2.Error as e:
                if e.pgcode != LOCK_NOT_AVAILABLE:
                    raise  # real failure: do not retry
                if attempt == MAX_ATTEMPTS:
                    # Bounded: stop and hand off to a human instead of looping forever.
                    raise RuntimeError(f"lock not acquired after {attempt} attempts; check for long transactions") from e
                # Exponential backoff with full jitter so retries don't sync with the blocker.
                cap = min(MAX_DELAY_S, BASE_DELAY_S * 2 ** (attempt - 1))
                delay = random.uniform(0, cap)
                print(f"attempt {attempt}: lock timeout, retrying in {delay:.1f}s")
                time.sleep(delay)
    finally:
        conn.close()

if __name__ == "__main__":
    run_migration("postgresql://migrator:example-password@db.internal:5432/app")

A migration runner that retries only on lock-timeout errors (SQLSTATE 55P03). It uses jittered exponential backoff and a hard attempt cap, and each failed attempt rolls back the DDL and the tracking row together.

How do you find the session blocking your migration?

The fastest way to diagnose a failed attempt is to retry the migration without a timeout in one session while you inspect it from another. Run pg_blocking_pids() with the migration's PID. It returns an array of the sessions holding a conflicting lock, plus any sessions queued ahead with conflicting requests. That second group matters because the blocker is sometimes another waiter, not the original holder.

Next, look up those PIDs in pg_stat_activity. The useful columns are state, xact_start, query_start and query. The most common culprit is a session in the idle in transaction state with an old xact_start. This is usually an application that opened a transaction, read from the table, and then went off to do something else. Its query column shows the last statement it ran, not anything it is doing now, which helps you trace it back to a code path.

To see exactly what each session holds, join pg_locks on pid and filter by the table's relation OID. Compare mode and granted to tell held locks from waiting ones.

To stop this pattern at the source, set idle_in_transaction_session_timeout on application roles. Postgres will then end abandoned transactions before they pin locks for hours.

Removing a blocker is a judgment call. pg_cancel_backend does nothing to an idle transaction, because there is no running query to cancel. pg_terminate_backend does release the lock, but it rolls back that session's uncommitted work and drops its connection. Make that call deliberately, with knowledge of who owns the session, and never build it into the migration runner as an automatic step.

How does lock_timeout affect development cost?

The setting itself costs almost nothing: one SET LOCAL line per migration, or one ALTER ROLE statement for the migration role. The real cost is in the work around it:

  • Runner changes. The runner needs to recognize SQLSTATE 55P03, roll back, and retry with backoff and an attempt cap. Teams usually write this once and share it.
  • Migration discipline. Migrations should keep one DDL change per transaction, keep backfills out of DDL transactions, and handle non-transactional statements separately. That means more, smaller migration files and more review attention per change.
  • Failed deploys become a normal outcome. Pipelines and runbooks need a defined path for "migration gave up on its lock". Someone has to own investigating it and re-running it.
  • Testing. Verifying timeout behavior requires a test that holds a conflicting lock from a second session. That test is more setup than a plain migration test.

Against this, the main cost it removes is outage response. A migration stuck in the lock queue can block every query on a table until someone notices and intervenes. That pulls engineers into incident response and possibly into customer-facing follow-up. A bounded, retryable failure is far cheaper to handle than an unbounded stall, even after counting the extra tooling.

How can lock_timeout save engineering time?

  • Faster diagnosis. A 55P03 error names the problem directly. Engineers do not have to work backward from latency alerts and connection-pool exhaustion to find a waiting ALTER TABLE.
  • Fewer incidents to coordinate. A migration that gives up after a short wait does not create a production outage. Nobody has to be paged, coordinate a response, or write a postmortem for it.
  • Safer unattended deploys. With a bounded wait and automatic retries, migrations can run from CI/CD during normal hours. Teams no longer have to schedule maintenance windows or have someone watch each schema change.
  • Reusable tooling. Retry logic and a role-level baseline, built once in the runner, protect every migration from every team without per-change effort.
  • Clearer follow-up work. Repeated lock timeouts point to specific long-running or idle transactions. That turns a vague "migrations are risky" concern into a concrete fix in application code.

What are the alternatives when lock_timeout alone is not enough?

lock_timeout limits how long a migration waits in the lock queue. It does not shorten how long the lock is held once granted, and it cannot make a full-table rewrite or scan cheap. The patterns below cover that gap.

One DDL change per transaction. Locks are held until commit. If one transaction alters three tables, the lock on the first table stays held while the runner waits for the third. Splitting the changes keeps each lock short and means a timeout retries only the step that failed.

Keep DDL out of backfill transactions. If an ALTER TABLE and a long UPDATE share a transaction, the ACCESS EXCLUSIVE lock lasts for the whole backfill. Commit the schema change first. Then backfill in small batches, each committed separately.

Split lock-light work from validation. ADD CONSTRAINT ... NOT VALID records a CHECK or foreign key constraint without scanning existing rows, so its lock is brief. Later, VALIDATE CONSTRAINT scans the table under a weaker lock that still allows reads and writes.

CREATE INDEX CONCURRENTLY. This builds an index without blocking writes, with two caveats:

  • It cannot run inside a transaction block, so SET LOCAL does not apply. Set lock_timeout at the session level instead, and reset it afterward.
  • A failed build can leave an index marked invalid. It stays in place and adds write overhead without serving queries. Find it through pg_index.indisvalid, then drop it before you retry.

Each of these patterns adds steps and review effort to every migration. In return, failures stay small and safe to retry, which keeps incidents short and recovery simple.

Before you rely on any of this, rehearse it on a production-like copy under realistic concurrent load. Lock levels and DDL behavior vary between releases, so check each statement against the documentation for the Postgres version you run.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres lock_timeout migration, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.