Free tools Windows power users keep installed
One-click scans. No signup required.
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
A PostgreSQL migration can block an entire table because the command needs a lock that conflicts with normal reads and writes, or because it performs lengthy work while holding a restrictive lock. The exact cause depends on the SQL and PostgreSQL version; there is no single migration pattern that makes every schema change nonblocking.
Why did my Postgres migration lock the whole table?
Usually, “the whole table locked” means the migration requested a lock mode that conflicts with other operations on that table. In PostgreSQL 18, the ALTER TABLE reference says an ACCESS EXCLUSIVE lock is acquired unless a subcommand explicitly specifies otherwise. The lock-conflict rules in the PostgreSQL 16 explicit-locking documentation show that this mode conflicts with every table lock mode, including ACCESS SHARE, the mode ordinary reads need. So a command holding it can block SELECT queries as well as writes.
Not every ALTER TABLE subcommand uses the same lock. If one statement combines multiple actions, PostgreSQL takes the strictest lock required by any of them. A low-impact change bundled with a more restrictive operation therefore does not retain the weaker lock; the stronger requirement governs the combined statement.
A lock does not prove that PostgreSQL rewrote the table
Lock acquisition and table rewriting are separate issues. A command may require a restrictive lock without rewriting every row. Conversely, a rewrite can make a lock disruptive for longer. The SQL, table contents, workload, transaction history and PostgreSQL major version all matter.
#1 Best Overall
For example, PostgreSQL 18 can add a column with a non-volatile default using a metadata-only representation: the default is evaluated once and stored in table metadata rather than applied by rewriting every existing row. The same version’s ALTER TABLE documentation describes cases that can require rewriting the table and indexes, including volatile defaults, stored generated columns, identity columns, domains with constraints, and most type changes.
Do not assume that behavior applies to older releases. The documentation for the target major version is the right reference for a production migration’s semantics.
Rank #2
How do I add a constraint without blocking writes for the full validation?
For supported constraints, a useful staged pattern is to add the constraint as NOT VALID, then validate existing rows in a separate operation. In PostgreSQL 18, validation checks pre-existing rows under a SHARE UPDATE EXCLUSIVE lock and does not lock out concurrent updates; new or changed rows are subject to enforcement while the existing data is checked. See the version 18 ALTER TABLE reference for the supported constraint forms and details.
Recommended Free Tools
- Add the constraint without validating old rows: issue the appropriate
ALTER TABLE ... ADD CONSTRAINT ... NOT VALIDcommand, after confirming that the constraint type supports this form in your PostgreSQL version. - Validate separately: run
ALTER TABLE ... VALIDATE CONSTRAINT constraint_nameas a later migration step. This separates the check of pre-existing rows from the initial constraint addition.
This is a way to reduce the impact of checking existing data under a restrictive lock, not a promise of zero locking or zero operational risk. Confirm the exact lock behavior and constraints in the documentation for the server version you run.
What if the migration needs a unique constraint?
The PostgreSQL 18 ALTER TABLE reference describes creating a unique index with CREATE UNIQUE INDEX CONCURRENTLY and then attaching it as a constraint as a way to avoid blocking table updates for a long time. This is an operation-specific option, not a lock-free switch. Before using it, consult the target version’s CREATE INDEX reference for its transaction restrictions, failure behavior, cleanup requirements and operational implications.
Why splitting a migration can help
Separate independently reviewed changes instead of combining a relatively light operation with one that needs a stronger lock. That lets each step have a clearer lock requirement and makes it easier to identify which operation is waiting or doing lengthy work. Splitting statements does not, by itself, guarantee that a step will be fast or nonblocking; assess the exact SQL and plan it against the production workload.
Does “concurrent” or “online” mean no locks?
No. Those labels describe particular operations and trade-offs, not a general guarantee that no lock is taken or no other object is affected. Index operations have their own requirements and failure modes. Partition operations can require different locks on different relations.
Partition attachment is not lock-free
In PostgreSQL 18, ATTACH PARTITION requires only SHARE UPDATE EXCLUSIVE on the parent table, while CREATE TABLE ... PARTITION OF uses a stronger lock on the parent. But the table being attached and a default partition can still require ACCESS EXCLUSIVE. Attachment may also scan the default partition unless a suitable constraint proves that scan unnecessary. These details are in the official PostgreSQL 18 partitioning documentation.
This approach applies only when the schema is partitioned and the change fits the partitioning design. A weaker lock on the parent does not mean every table touched by the command is held under a weaker lock.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How to investigate the actual incident
The headline alone cannot establish why a particular migration blocked traffic or how long the block should last. A long-running transaction, lock queueing, table size, workload, the migration’s own work, or a combination may be involved. A migration framework does not change PostgreSQL’s lock semantics: the emitted SQL and transaction boundaries determine the database behavior.
- Capture the exact migration SQL, including all actions bundled into each statement.
- Record the PostgreSQL major version and check its matching command documentation.
- Identify which statements were waiting, which were holding locks, and how long the relevant transactions had been open.
- Check the schema and related objects touched by the command, including indexes, partitions and any default partition.
- Determine whether the operation scans or rewrites data, and whether concurrent application traffic was adding work or waiting behind the migration.
- Test the exact migration plan and define how the deployment will handle lock acquisition and aborts for your production context. There is no universally safe timeout value established for every application.
Without the SQL, version, schema and lock-wait evidence, naming a specific root cause would be guesswork.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →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.
