A PostgreSQL migration can appear to lock the whole table when it requests a lock that conflicts with ordinary reads and writes, or when it performs a scan or rewrite while holding a restrictive lock. The exact cause depends on the SQL and PostgreSQL major version: ALTER TABLE subcommands use different lock modes, and a combined statement takes the strictest one it needs. A safer approach is to split work into stages and use lower-blocking options where the operation supports them—not to assume any migration is lock-free.
Why did my Postgres migration lock the whole table?
In PostgreSQL 18, the ALTER TABLE reference says an ACCESS EXCLUSIVE lock is acquired unless a subcommand explicitly specifies otherwise. A statement with multiple subcommands takes the strictest lock required by any of them. So combining a modest metadata change with a more restrictive operation can make the entire statement wait for, and then hold, the stronger lock. PostgreSQL 18 ALTER TABLE documentation
As an Amazon Associate I earn from qualifying purchases.
ACCESS EXCLUSIVE conflicts with every table lock mode, including ACCESS SHARE, the mode used by ordinary reads. While that lock is held, reads as well as writes can be blocked. PostgreSQL 16 explicit locking documentation
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesThat explains how broad blocking can happen; it does not establish what happened in a particular incident. The title alone cannot identify the statement, lock wait, table size, transaction duration, or workload. A lock requirement also does not by itself prove that PostgreSQL rewrote the table.
#1 Best Overall
Did PostgreSQL rewrite the table, or just take a strong lock?
Locking and table-rewrite work are separate issues. A command may need a restrictive lock without rewriting every row; a rewrite or validation may also add substantial work while a lock is held. Check the exact subcommand and version rather than inferring a rewrite from blocked queries.
Adding a column and its default
In PostgreSQL 18, adding a column with a non-volatile default can use a metadata-only representation: the default is evaluated once and stored in table metadata rather than applied by rewriting all existing rows. This behavior is version-sensitive. The PostgreSQL 11 ALTER TABLE reference describes a rewrite for adding a column with a default, so do not assume PostgreSQL 18 behavior applies to older installations. PostgreSQL 18 ALTER TABLE documentation
Rank #2
PostgreSQL 18 documents rewrites of the table and indexes for volatile defaults and certain other column forms, including stored generated or identity columns, domains with constraints, and most type changes. Confirm the specific form in the reference for the server’s major version.
How can I add a constraint without doing all the blocking work at once?
For supported constraints, PostgreSQL lets you separate enforcing the rule for new or changed rows from checking rows that already exist. Add the constraint as NOT VALID, then validate it in a later step:
ALTER TABLE orders ADD CONSTRAINT orders_total_nonnegative CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_nonnegative;Use this example only when the constraint type and syntax support NOT VALID; verify both against the version-matched ALTER TABLE reference. The first step begins enforcing the constraint for new or changed rows without first validating all existing rows. The later VALIDATE CONSTRAINT checks pre-existing rows under SHARE UPDATE EXCLUSIVE. PostgreSQL documents that this validation does not lock out concurrent updates, because other transactions enforce the constraint on rows they insert or update. PostgreSQL 18 ALTER TABLE documentation
This splits the work; it does not make validation instantaneous or remove every lock. Plan for the scan and watch for constraint violations, which must be resolved before validation can succeed.
What pattern can reduce migration blocking?
- Identify the exact SQL and server major version. Read the version-matched command reference for each subcommand. Lock levels and behaviors can differ between versions and forms.
- Separate operations with different lock needs. Avoid bundling a low-impact change with a subcommand requiring a stronger lock; the combined
ALTER TABLEtakes the strictest required lock. - Stage work when PostgreSQL supports it. For eligible constraints, use
NOT VALIDfollowed by a separate validation step. For a unique constraint, PostgreSQL’sALTER TABLEreference describes creating a unique index concurrently and attaching it as a constraint as an option that can avoid blocking table updates for a long time. Check the version-matchedCREATE INDEXdocumentation for transaction restrictions, failure handling, and cleanup before using that approach. - Account for every relation the operation touches. A weaker lock on one table does not mean related tables have the same lock requirements.
- Set an operational plan for lock acquisition and abort behavior. Choose timeouts and recovery steps for the application’s production context; there is no universal safe timeout established by these command references.
Do “concurrent” and partition operations guarantee no blocking?
No. “Concurrent” describes an operation-specific behavior, not a promise that an operation needs no locks or cannot fail. For index creation, check the version-matched CREATE INDEX reference and plan for its restrictions and failure cleanup.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →For partitioned tables, PostgreSQL 18 says ATTACH PARTITION needs SHARE UPDATE EXCLUSIVE on the parent, while CREATE TABLE ... PARTITION OF uses a stronger lock there. But the table being attached and a default partition can still require ACCESS EXCLUSIVE; attaching may also scan the default partition unless a suitable constraint shows that the scan is unnecessary. This is relevant only when the schema and migration objective fit. PostgreSQL 18 partitioning documentation
How do I diagnose the actual lock incident?
Before naming a cause, gather the evidence that distinguishes lock acquisition from migration work and queueing:
- The exact SQL emitted by the migration, including every action in a combined
ALTER TABLE. - The PostgreSQL major version and the table’s schema, including whether it is partitioned.
- Which sessions were waiting, what they were waiting on, and whether another transaction held a conflicting lock.
- How long the migration and relevant transactions had been open, and whether the command scanned or rewrote data.
A migration framework does not change PostgreSQL’s lock semantics; the SQL it emits and its transaction boundaries determine the database behavior. Without the statement and lock-wait evidence, a long-running transaction, queueing, a rewrite, or another cause remains a possibility—not a diagnosis.
Quick 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.
