Postgres Migration Timeouts: What Values to Use and Where

Use lock_timeout = '2s' before any production DDL that can request a strong lock. Keep statement_timeout longer, often five to ten minutes for schema work, and set idle_in_transaction_session_timeout = '30s' when the migration runs inside a transaction.

Those are starting values, not laws. The non-negotiable rule is placement: the timeout must be active before the first dangerous statement. A perfect value on line 20 cannot protect the ALTER TABLE on line 5.

A migration header that works

For a normal transactional migration:

BEGIN;

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '5min';
SET LOCAL idle_in_transaction_session_timeout = '30s';
SET LOCAL application_name = 'migrate:0142_orders_status';

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

COMMIT;

For a statement that must run outside a transaction, use session-scoped SET:

SET lock_timeout = '2s';
SET statement_timeout = '20min';
SET application_name = 'migrate:0143_orders_status_idx';

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

RESET lock_timeout;
RESET statement_timeout;
RESET application_name;

CREATE INDEX CONCURRENTLY cannot run in a transaction block, so SET LOCAL is the wrong tool for that file. PostgreSQL documents that SET LOCAL outside a transaction has no useful duration.

Recommended starting values

Setting	Starting value	What it bounds	Failure meaning
lock_timeout	2s	Time waiting for each lock acquisition	Could not find a safe lock window
statement_timeout	5min	Total execution time of each statement	Work itself took too long
idle_in_transaction_session_timeout	30s	Time sitting idle inside an open transaction	Client stopped sending commands while holding a transaction
application_name	migration identifier	Not a timeout	Makes the session identifiable in pg_stat_activity

PostgreSQL’s client connection settings define all three timeouts. lock_timeout applies separately to each lock acquisition attempt. statement_timeout covers the whole statement. A value of zero disables either timeout.

What should lock_timeout be for a Postgres migration?

A migration waiting for ACCESS EXCLUSIVE is not harmless. While it waits, later queries that need even weak locks can queue behind it. That is the lock queue death spiral: one old transaction blocks the DDL, then the DDL becomes the thing blocking fresh traffic.

Two seconds is short enough to fail before the queue becomes a customer-visible incident and long enough to catch a normal quiet gap on many workloads. pgfence warns when lock_timeout exceeds five seconds by default because a long lock wait weakens the protection.

The right question is not “how long should the migration be willing to wait?” It is “how long can production safely tolerate a strong lock request sitting in the queue?”

If your answer is ten minutes, the migration tool is being optimized at the expense of the application.

Why statement_timeout must be longer

PostgreSQL notes that setting lock_timeout equal to or above statement_timeout is pointless. The whole-statement timer will expire first, so you will not know whether the statement was waiting for a lock or doing expensive work.

Keep the timers meaningfully separated:

SET lock_timeout = '2s';
SET statement_timeout = '5min';

That produces two useful failure classes:

• A failure near two seconds means the migration could not acquire a lock safely.
• A failure near five minutes means it acquired its locks but the operation itself exceeded its execution budget.

The second value should reflect the operation. A metadata-only rename might deserve 30 seconds. A validated constraint scan or concurrent index build may need 20 minutes. One global statement timeout for every migration is less useful than an explicit per-file budget.

pgfence’s default upper bound is ten minutes. Teams can change the configured threshold, but a larger number should represent a deliberate operational decision rather than a copied migration header.

Placement protects the first statement

This migration is not protected:

ALTER TABLE orders ADD COLUMN archived_at timestamptz;

SET lock_timeout = '2s';

The ALTER TABLE has already requested its ACCESS EXCLUSIVE lock before PostgreSQL sees the setting. If it queues, it waits under the old value, which is often zero and therefore unlimited.

Move the setting to the top:

SET lock_timeout = '2s';

ALTER TABLE orders ADD COLUMN archived_at timestamptz;

The same ordering rule applies to ORM wrappers. The generated SQL must put the setting before the first DDL statement inside the connection and transaction that execute the migration.

pgfence reports lock-timeout-after-dangerous-statement as an error for exactly this case. Merely finding the string SET lock_timeout somewhere in the file would create false confidence.

SET versus SET LOCAL

SET changes the current session. If issued inside a transaction that commits, it remains active for later statements on the same connection until it is reset or the connection closes.

SET LOCAL lasts only until the current transaction ends. That is usually safer for migration frameworks that wrap each file in an explicit transaction:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN archived_at timestamptz;
COMMIT;

After COMMIT, the pooled connection returns to its previous value automatically.

Do not use SET LOCAL for nontransactional migration files. Outside a transaction, PostgreSQL warns and the setting has no useful effect after that statement. This matters for:

• CREATE INDEX CONCURRENTLY
• DROP INDEX CONCURRENTLY
• REINDEX ... CONCURRENTLY
• other operations your framework deliberately runs without a transaction

Use SET, run the operation on a dedicated migration connection, then RESET the values if that connection can be reused.

Why idle_in_transaction_session_timeout is separate

statement_timeout stops a running statement. It does not stop a client that has finished a statement and then goes silent while the transaction remains open.

An idle in transaction session can keep locks and old snapshots alive even though no query is running. PostgreSQL documents idle_in_transaction_session_timeout specifically to stop idle sessions from holding locks for an unreasonable time.

SET LOCAL idle_in_transaction_session_timeout = '30s';

Thirty seconds is a useful migration default because migration tools should not pause for human input between statements. If the framework legitimately performs client-side work for longer than that between commands, tune the value to the observed gap, not to infinity.

Application names turn incidents into lookups

This line does not prevent a lock problem:

SET application_name = 'migrate:0142_orders_status';

It makes the problem diagnosable. When a deploy is stuck, pg_stat_activity can distinguish the migration from web requests, background jobs, and an engineer’s psql session:

SELECT
  pid,
  application_name,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start AS transaction_age,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

The timeout tells PostgreSQL when to stop. The application name tells the operator what stopped.

How to tune the values from evidence

Start with 2s, 5min, and 30s, then adjust one setting at a time:

1. Record how often migrations fail on lock_timeout.
2. Inspect the blockers rather than immediately increasing the timeout.
3. If ordinary lock acquisition regularly needs more than two seconds without creating a queue, raise it cautiously, staying below five seconds unless the traffic model proves otherwise.
4. Set statement_timeout from the expected duration of the actual operation, with margin for production scale.
5. Keep lock_timeout well below statement_timeout so failures remain attributable.
6. Treat repeated idle-transaction kills as a client bug or framework problem, not as a reason to disable the guard.

A timeout failure is not a failed schema change if PostgreSQL never acquired the requested lock and never started the statement. It is a safe refusal to begin under current traffic.

What pgfence checks

pgfence reports:

• missing lock_timeout
• lock_timeout = 0, which disables it
• values above the configured maximum
• a timeout placed after the first dangerous statement
• missing or excessive statement_timeout
• missing idle_in_transaction_session_timeout
• missing application_name

npx @flvmnt/pgfence analyze migrations/*.sql

The key distinction is between presence and protection. A timeout string in the file is not enough. It has to be active, nonzero, reasonably bounded, and ordered before the statement it is meant to protect.

For the queue mechanism and incident sequence, see the lock_timeout death spiral. If a migration is already stuck, start with Postgres ALTER TABLE hangs.