Lock Timeout: Stop PostgreSQL Migrations From Stalling

The single PostgreSQL setting that keeps a migration from stalling production is lock_timeout — it makes any statement that waits too long for a lock fail fast instead of hanging indefinitely. Set it to a few seconds (e.g. SET lock_timeout = '5s') before running a migration, and the migration will abort with an error rather than block your production writes.

Formula

The calculator above helps you pick a sensible lock_timeout value. The core formula is simple:

Timeout ratio (%) = (lock_timeout / estimated migration duration) × 100

Where:

• lock_timeout — the value you set in PostgreSQL (in seconds). This is the maximum time a statement will wait to acquire a lock before it is aborted.
• estimated migration duration — how long you expect the migration to run in total (in seconds), based on table size and index builds.

The second output suggests a starting point: 20% of the estimated migration duration, clamped to at least 1 second. This is a heuristic — if your migration normally takes 30 seconds, a 6-second timeout gives the lock a fair chance while still failing fast if something is stuck.

Worked Example

Using the defaults from the calculator:

1. Lock timeout = 5 seconds
2. Estimated migration duration = 30 seconds
3. Timeout ratio = (5 / 30) × 100 = 16.7%
4. Suggested lock_timeout = max(1, 30 × 0.2) = 6 seconds

So with a 5-second timeout on a 30-second migration, you are asking the lock to be granted within about 17% of the total migration time. If the lock is not available in 5 seconds, the migration aborts — which is usually better than waiting forever.

Why Migrations Stall in the First Place

Most PostgreSQL migrations that stall are not slow — they are blocked. A typical scenario:

• An ALTER TABLE needs an ACCESS EXCLUSIVE lock on a table.
• Meanwhile, a long-running query (or an idle-in-transaction connection) holds a lesser lock, like ACCESS SHARE.
• The migration waits for that lock, and every new query that needs the table also queues up behind it.
• Production writes pile up, connection pools exhaust, and the database appears hung.

Without a timeout, the migration waits indefinitely. With lock_timeout, it gives up after a few seconds, letting you cancel the blocking query or retry during a quieter window.

How to Set lock_timeout

You can set it in several ways, depending on your workflow:

• Per-session — run SET lock_timeout = '5s'; before the migration in the same connection.
• Per-transaction — BEGIN; SET LOCAL lock_timeout = '5s'; ALTER TABLE ...; COMMIT; — the setting only applies inside that transaction.
• Globally — ALTER DATABASE mydb SET lock_timeout = '5s'; affects all new connections to that database.
• In a migration tool — many tools (like Rails, Django, or Flyway) let you execute raw SQL before the migration, or you can set it in the session configuration.

Note that lock_timeout applies to all lock waits, not just DDL. It also affects SELECT FOR UPDATE and other explicit locks. If you only want to protect DDL, consider using lock_timeout in the migration session only.

Choosing the Right Value

There is no universal number, but here are practical guidelines:

• Start with 5 seconds — short enough to fail fast, long enough to let a legitimate lock queue clear.
• For known long migrations (e.g. adding an index to a huge table), use a value that is a fraction of the expected duration — say 20–30%.
• For critical production windows, use 1–2 seconds so you can quickly retry or abort.
• Monitor — if you see frequent timeouts, that is a signal of lock contention, not a reason to raise the timeout blindly.

Remember: lock_timeout does not make the migration faster. It only makes it fail cleanly instead of hanging.

Common Misconceptions

“lock_timeout will cancel the migration if it takes too long.” No — it only aborts if the migration waits for a lock longer than the timeout. Once the lock is acquired, the migration can run for hours without being affected.

“statement_timeout is enough.” statement_timeout cancels a statement that runs too long, but it does not help if the statement is stuck waiting for a lock — the wait time does not count toward statement_timeout in older versions. Actually, since PostgreSQL 9.6, statement_timeout does include lock wait time, but using both is clearer: lock_timeout for the wait, statement_timeout for the total execution.

“It’s a global setting that will affect everything.” Only if you set it globally. Per-session or per-transaction settings are the safest for migrations.

Safety and Alternatives

Always test migrations on a staging environment with a similar lock profile. In production, consider these complementary approaches:

• Use pg_locks and pg_stat_activity to see who is holding the lock before you abort.
• Use online DDL tools like pg_repack or pt-online-schema-change for large tables — they avoid long exclusive locks.
• Schedule migrations during low-traffic windows even with a timeout.
• Set idle_in_transaction_session_timeout to kill connections that sit idle in a transaction, which often cause lock contention.

If a migration times out, do not just retry blindly. Investigate the blocking query, kill it if safe, then retry. If the table is huge and the lock is unavoidable, consider a maintenance window.

Frequently Asked Questions

What is the difference between lock_timeout and statement_timeout?

statement_timeout limits the total time a statement can run, including lock wait time (since PostgreSQL 9.6). lock_timeout only limits the time spent waiting to acquire a lock. For migrations, lock_timeout is more precise because it lets the migration run as long as needed once it gets the lock.

Can I set lock_timeout inside a migration file?

Yes, if your migration tool allows raw SQL. For example, in Rails you can execute execute "SET lock_timeout = '5s'" at the start of the migration. In raw SQL scripts, just include the SET command before the DDL.

What happens when a migration times out?

PostgreSQL aborts the statement and returns an error like canceling statement due to lock timeout. The transaction is rolled back, so the migration leaves no partial changes. You can catch the error in your migration tool and decide whether to retry or abort.

Does lock_timeout affect SELECT queries?

Yes, if the SELECT needs to acquire a lock (e.g. SELECT FOR UPDATE), it will also respect lock_timeout. Plain SELECT statements do not take locks that block DDL, so they are not affected.

Is there a way to avoid the lock entirely?

For some operations, yes. Adding a column with a default value in PostgreSQL 11+ is instant (no table rewrite). Creating an index concurrently avoids blocking writes, but it takes longer and can fail if it cannot finish. For other DDL, you may need a maintenance window or a tool like pg_repack.

What is a good default for a production database?

Many teams use 5 seconds as a starting point. If you have frequent short migrations, 2 seconds might be better. For long maintenance tasks, set it higher (e.g. 30 seconds) but still finite. The key is to never leave it unset in production, because the default is infinite wait.

Related

• Folder Shortcuts to Start Menu: Easy Setup
• Export Evernote to OneNote: Step-by-Step Guide
• Realme 7 5G: Dimensity 800U, 120Hz, 48MP Cameras

in t | Lock timeout (seconds) | s | 5 | 0 | 3600
in d | Estimated migration duration (seconds) | s | 30 | 1 | 86400
out ratio | Timeout as % of migration | % | (t / d) * 100 | 1
out advice | Suggested lock_timeout (seconds) | s | max(1, d * 0.2) | 0