iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In PostgreSQL, the line is SET lock_timeout = '5s';, placed in the migration’s own session or transaction before the first statement that takes a lock. It caps how long that statement will wait for a lock. If the wait exceeds the limit, the statement fails instead of joining a queue.
That matters because a schema change that waits behind a long-running query can block every later query on the same table, even though the migration has not yet done any work. The setting is a guardrail against prolonged lock waits. It is not a promise that a migration cannot cause an outage.
Why a waiting migration hurts more than it looks
PostgreSQL queues lock requests on a table. When an ALTER TABLE is waiting for its lock, requests that arrive afterward wait behind it, including ordinary reads and writes that would otherwise have run immediately. A migration that is merely waiting can therefore stall live traffic. The failure is often visible as a spike in query latency or a pile-up of connections, not as an error in the migration itself.
What lock_timeout actually limits
PostgreSQL’s documentation defines lock_timeout as a limit on time spent waiting to acquire a lock. The limit applies separately to each lock acquisition. A statement that needs several locks can wait up to the limit for each one, so the total wait can be longer than the value you set.
#1 Best Overall
Once a lock is granted, the setting no longer applies to it. How long the statement runs after that is a separate question. A migration that acquires its lock and then performs a slow table rewrite is not limited by lock_timeout. Execution time is governed by statement_timeout, which is unlimited by default.
Set it for the migration, not the server
Scope the setting to the migration so it does not change behavior for other sessions. The PostgreSQL documentation, in its discussion of client connection defaults, states that setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions.
Rank #2
- Open the migration file or the script your migration runner executes.
- Place the setting before the first statement that takes a lock on the affected tables. For a session-wide scope on a dedicated migration connection, use
SET lock_timeout = '5s';. - Inside an explicit transaction, use
SET LOCAL lock_timeout = '5s';so the value ends with the transaction. - Leave the server configuration file free of this setting, so ordinary application sessions keep their current behavior.
BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN fulfilled_at timestamptz;
COMMIT;The value 5s is an example, not a recommendation. PostgreSQL does not establish one safe duration for all workloads. Pick a value that matches how long your service can tolerate a migration failing and retrying, and how long a lock wait is acceptable to users during deployment.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
lock_timeout and statement_timeout are different guards
The two settings are often confused. They measure different things and fire at different points.
| Setting | What it measures | Fires when | Typical use in a migration |
|---|---|---|---|
lock_timeout |
Time spent waiting to acquire each lock | A lock wait exceeds the configured value | Stopping a DDL statement from queuing behind live traffic |
statement_timeout |
Total duration of a statement | The statement runs longer than the configured value | Capping a statement that runs unexpectedly long |
PostgreSQL also documents an interaction: if statement_timeout is set to a nonzero value, a lock_timeout equal to or greater than it does nothing, because the statement timeout fires first. If you use both, set lock_timeout to a value below statement_timeout.
When the timeout fires
A lock timeout aborts the statement with an error. Treat that as a failed migration, not a successful one, and handle it deliberately.
- Read the error. PostgreSQL reports a lock timeout as “canceling statement due to lock timeout” with SQLSTATE 55P03.
- Check the transaction. If the migration ran inside a transaction, the failed statement leaves that transaction unable to proceed, so the change is not partially applied.
- Find the blocker. Inspect
pg_stat_activityandpg_locksto see which long-running session held the lock. A reporting job or an open transaction left idle is a common cause. - Retry at a quieter moment. Rerun the migration when the blocking work has finished. Supabase’s migration guidance acknowledges lock-timeout errors and suggests considering a higher
lock_timeoutin that situation. That suggestion does not identify a specific duration, and raising the limit trades a shorter failure for a longer wait on live traffic.
What the setting does not do
- It does not make a schema change compatible with old and new application versions running at the same time.
- It does not bound the total runtime of a migration.
- It does not make a destructive change safe. A migration that drops a column still drops it as soon as it obtains its lock.
- It does not replace reviewing the SQL a tool generates before it runs.
Review the generated SQL before it reaches production
Migration tools generate SQL on your behalf, and that SQL deserves the same review as hand-written DDL. Microsoft’s EF Core guidance on applying migrations tells teams to inspect generated migrations and test them before production, because a migration may drop a column unintentionally or fail for other reasons. The guidance also describes several ways to deploy migrations, and they differ in how reviewable and coordinated they are.
| Deployment approach | SQL reviewable before execution | EF migration locking | Notes from the guidance |
|---|---|---|---|
| Reviewed SQL scripts | Yes; the script can be reviewed and adjusted before it runs | Not stated for this approach | Gives the team control over the exact statements, including any lock timeout they add |
| Migration bundles | Not in the same way; the SQL is not exposed for inspection in the same manner | Provided by bundles | Coordinates application of migrations; review must happen through other means |
| Command-line and runtime migration | Not stated in the guidance | Migration locks in EF Core 9 and later, with limitations the guidance describes | Trade-offs differ by team setup; check the locking limitations before relying on them |
The database privileges the migration runner requires are part of the same decision. Choose the approach your team can review, coordinate, and run with the access it actually needs.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Breaking changes need a staged rollout
A lock timeout does nothing for a change that breaks the running application. Netlify’s documentation on migrations, last updated April 28, 2026, recommends an expand, migrate, and contract sequence for breaking changes. Old structure is removed only after application code has switched to the new one.
- Expand. Add the new column, table, or index alongside the old structure, and deploy application code that works with both.
- Migrate. Backfill data into the new structure and move reads and writes over to it.
- Contract. Remove the old structure once no running code depends on it.
Netlify notes that renaming or dropping a column can fail during the transition between old and new application versions, which is why it recommends the staged approach. The same guidance states: “Still, as a good practice, we recommend that you always write backwards-compatible migrations.”
How widely the practice is used
No source establishes how often teams set lock_timeout on their migrations. The headline’s claim that almost nobody adds it is an editorial impression, not a measured fact, and it should not be repeated as a statistic. What the documentation does establish is narrower: the setting exists, it limits lock waiting rather than total runtime, and it is safest when scoped to the migration.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
