Which lock does ALTER TABLE take in PostgreSQL
The lock each PostgreSQL schema change takes, what it blocks, and the safer form, from ADD COLUMN and SET NOT NULL to foreign keys, indexes, partitions and lock_timeout.
Checked against The PostgreSQL 18.7 documentation, with release notes back to 11 on .
Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which blocks reads and writes on the table until the transaction commits. A few take something lighter, and most of the slow ones have a safer form. The table below gives the lock, what it blocks and the safer form for each common change. It holds for PostgreSQL 18, and the version notes say where older servers differ. If you only need one thing from this page, it is SET lock_timeout before every migration.
The table
Reads means a plain SELECT, which takes ACCESS SHARE. Writes means INSERT, UPDATE, DELETE and MERGE, which take ROW EXCLUSIVE. Work is what happens while the lock is held: instant is a catalog change, scan reads every row, rewrite copies the table and its indexes.
| Statement | Lock on the table | Reads | Writes | Work | Safer form |
|---|---|---|---|---|---|
ADD COLUMN with no default, or a non-volatile one such as 0 or now() |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant (11+) | Already the safe form. Bound the wait with lock_timeout |
ADD COLUMN with a volatile default (clock_timestamp(), gen_random_uuid()), identity, serial, stored generated |
ACCESS EXCLUSIVE |
Blocked | Blocked | Rewrite | Add the column bare, SET DEFAULT, backfill in batches |
ADD COLUMN … GENERATED ALWAYS AS (…) VIRTUAL |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant (18+) | Already the safe form |
DROP COLUMN |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant | Already the safe form |
ALTER COLUMN … TYPE |
ACCESS EXCLUSIVE |
Blocked | Blocked | Rewrite, unless binary coercible | None in PostgreSQL |
SET DEFAULT, DROP DEFAULT, DROP NOT NULL |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant | Already the safe form |
ALTER COLUMN … SET NOT NULL |
ACCESS EXCLUSIVE |
Blocked | Blocked | Scan | CHECK (col IS NOT NULL) NOT VALID, VALIDATE, SET NOT NULL, drop the check (12+) |
ADD CONSTRAINT … CHECK |
ACCESS EXCLUSIVE |
Blocked | Blocked | Scan | NOT VALID, then VALIDATE CONSTRAINT |
ADD CONSTRAINT … FOREIGN KEY |
SHARE ROW EXCLUSIVE on both tables |
Allowed | Blocked | Scan | NOT VALID, then VALIDATE CONSTRAINT |
VALIDATE CONSTRAINT |
SHARE UPDATE EXCLUSIVE, and ROW SHARE on a foreign key's referenced table |
Allowed | Allowed | Scan | This is the safer form |
ADD PRIMARY KEY, ADD UNIQUE |
ACCESS EXCLUSIVE |
Blocked | Blocked | Index build | CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT … USING INDEX |
ADD CONSTRAINT … USING INDEX |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant, or a scan for a primary key | Make the key columns NOT NULL first |
CREATE INDEX |
SHARE |
Allowed | Blocked | Index build | CREATE INDEX CONCURRENTLY |
CREATE INDEX CONCURRENTLY |
SHARE UPDATE EXCLUSIVE |
Allowed | Allowed | Two scans, waits for transactions | This is the safer form |
DROP INDEX |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant | DROP INDEX CONCURRENTLY |
RENAME a table, column or constraint |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant | Already the safe form |
ALTER INDEX … RENAME |
SHARE UPDATE EXCLUSIVE on the index (12+) |
Allowed | Allowed | Instant | Already the safe form |
REINDEX |
SHARE, and ACCESS EXCLUSIVE on the index |
Blocked, in practice | Blocked | Index build | REINDEX … CONCURRENTLY (12+) |
VACUUM FULL, CLUSTER |
ACCESS EXCLUSIVE |
Blocked | Blocked | Rewrite | Plain VACUUM, which keeps the space for reuse instead of returning it |
TRUNCATE |
ACCESS EXCLUSIVE |
Blocked | Blocked | Instant | DELETE in batches, under ROW EXCLUSIVE |
ATTACH PARTITION |
SHARE UPDATE EXCLUSIVE on the parent (12+), ACCESS EXCLUSIVE on the partition |
Parent allowed | Parent allowed | Scan of the partition | Add a valid CHECK that matches the bounds first |
DETACH PARTITION |
ACCESS EXCLUSIVE on the parent and the partition |
Blocked | Blocked | Instant | DETACH PARTITION … CONCURRENTLY (14+) |
Every row is "until the transaction commits". A lock is never released at the end of the statement that took it. The explicit locking chapter has the full list of modes and Table 13.2, which says which modes conflict. The ALTER TABLE page gives the lock for each subcommand. Its rule is "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted", and for several statements above, nothing is noted.
Instant still waits for its lock
ADD COLUMN, DROP COLUMN, RENAME and SET DEFAULT change the catalog and finish in milliseconds. They still need ACCESS EXCLUSIVE, and to get it they wait for every open transaction that has touched the table to finish. A report that has been reading orders for twenty minutes holds ACCESS SHARE for those twenty minutes, and your instant ALTER waits behind it. While it waits, it blocks everyone else.
The lock queue
A lock request waits in a queue. A new request has to wait for the ones already granted that it conflicts with, and also for the ones already waiting that it conflicts with. ACCESS EXCLUSIVE conflicts with everything, so once an ALTER is waiting, every SELECT that arrives after it waits too. The pg_blocking_pids documentation calls the second kind a soft block: a session that "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue".
Three sessions on orders show it.
-- Session 1: a transaction that read orders and is still open.
BEGIN;
SELECT count(*) FROM orders;
-- (no COMMIT yet)
-- Session 2: an instant change, which now waits for session 1.
ALTER TABLE orders ADD COLUMN gift_note text;
-- Session 3: an ordinary read, which now waits for session 2.
SELECT * FROM orders WHERE id = 1001;
Session 3 needs only ACCESS SHARE, which session 1 is happy to share. It waits anyway, because session 2 is ahead of it in the queue. Every query on orders now queues until session 1 commits, and then session 2 runs for a millisecond and lets them all go. If session 1 is a connection someone left idle in a transaction, that can be hours.
PostgreSQL waits for a lock indefinitely by default. lock_timeout puts a limit on it. Set it for the transaction that runs the migration:
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN gift_note text;
COMMIT;
If the lock isn't granted in three seconds, the statement fails with canceling statement due to lock timeout, SQLSTATE 55P03, and the queue behind it drains. Nothing was changed. Retry it a little later, or find what was holding the table first. The lock_timeout setting applies to each lock acquisition separately. A statement_timeout equal or shorter makes it pointless, so keep that one longer.
Two more rules follow from the locks being held to commit:
- Keep a migration's transaction short. Ten statements in one transaction hold the strongest lock any of them took until the tenth is done.
- Don't put a slow statement after a fast
ACCESS EXCLUSIVEone in the same transaction. The fast one's lock lasts as long as the slow one runs.
Who holds the lock, and who waits
This lists every session with a lock on orders, granted first, with what each one is blocked by:
SELECT a.pid,
pg_blocking_pids(a.pid) AS blocked_by,
l.mode,
l.granted,
a.state,
now() - a.xact_start AS transaction_age,
left(a.query, 60) AS query
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted DESC, a.xact_start;
A row with granted false is waiting. Its blocked_by names the sessions in its way, both the ones holding a lock and the ones queued ahead of it. A long transaction_age with the state idle in transaction is the usual culprit. The documentation warns that frequent calls to pg_blocking_pids can affect performance, because it briefly needs exclusive access to the lock manager's state, so run it when you need it, not in a loop.
SELECT pg_cancel_backend(pid) cancels a session's current query. pg_terminate_backend(pid) ends the session, which rolls back its transaction and releases its locks. Both need the right to signal that session.
Adding a column
From 11, a column added with no default, or with a non-volatile default, is a catalog change. The ALTER TABLE notes say the default "is evaluated at the time of the statement and the result stored in the table's metadata". DEFAULT 0, DEFAULT 'pending' and DEFAULT now() are all instant, because now() is stable within a transaction. On 10 and older, any default rewrote the table.
These still rewrite the table and every index on it, on every version:
- a volatile default, such as
clock_timestamp(),random()orgen_random_uuid() - an identity column, or
serial, whose default isnextval() - a stored generated column
- a column whose type is a domain with constraints
From 18, a virtual generated column never needs a rewrite, and generated columns are virtual unless you write STORED.
A rewrite holds ACCESS EXCLUSIVE for as long as copying the table takes. The safer form splits the work so the lock is short each time:
ALTER TABLE orders ADD COLUMN public_id uuid;
ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid();
-- Repeat until it updates 0 rows. Each run is its own short transaction.
UPDATE orders SET public_id = DEFAULT
WHERE ctid = ANY (ARRAY(
SELECT ctid FROM orders WHERE public_id IS NULL LIMIT 10000
));
The first two statements are instant. New rows get a value from the default. The UPDATE fills the old rows a batch at a time, under ROW EXCLUSIVE, which doesn't block reads or other writes to other rows. If the column must end up NOT NULL, add that last, the safe way below.
Setting NOT NULL
ALTER COLUMN … SET NOT NULL scans the whole table under ACCESS EXCLUSIVE to prove there are no nulls. From 12, the scan is skipped when a valid CHECK constraint already proves it. So the safer form builds that proof under a lighter lock first:
ALTER TABLE orders ADD CONSTRAINT orders_channel_nn_check
CHECK (channel IS NOT NULL) NOT VALID; -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_nn_check; -- scan, SHARE UPDATE EXCLUSIVE
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL; -- instant, the check proves it
ALTER TABLE orders DROP CONSTRAINT orders_channel_nn_check; -- instant
VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE, which blocks neither reads nor writes. Run each statement in its own transaction, or the ACCESS EXCLUSIVE from the first is held through the scan.
From 18, a not-null constraint can itself be added NOT VALID and validated later: ALTER TABLE orders ADD CONSTRAINT orders_channel_nn NOT NULL channel NOT VALID, then VALIDATE CONSTRAINT orders_channel_nn. The CHECK route still works on 18 and is the one that works back to 12.
Constraints and foreign keys
Adding a CHECK takes ACCESS EXCLUSIVE and scans. Adding a foreign key takes SHARE ROW EXCLUSIVE on both tables, the one you alter and the one it references, and scans the referencing table. SHARE ROW EXCLUSIVE lets reads through but blocks writes, so a foreign key from orders to users stops writes to both while it checks every order.
NOT VALID skips the scan. The constraint is enforced for new and changed rows from that moment, and the old rows are checked later by VALIDATE CONSTRAINT:
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;
The first statement still takes SHARE ROW EXCLUSIVE on both tables, briefly. The second takes SHARE UPDATE EXCLUSIVE on orders and ROW SHARE on users, and neither blocks reads or writes. NOT VALID works for foreign keys, CHECK and, from 18, not-null constraints. A NOT VALID foreign key on a partitioned table needs 18.
VALIDATE CONSTRAINT fails if an old row breaks the constraint. Find the rows first if you are not sure: for the foreign key above, the orders whose user_id has no match in users.
Indexes
CREATE INDEX takes SHARE. Reads continue, and every write to the table waits until the build ends. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, so writes continue. In return it scans the table twice and waits for transactions that could use the index to finish, so it takes longer.
CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);
What to know about the concurrent form, from the CREATE INDEX page:
- It can't run inside a transaction block. A migration tool that wraps each file in a transaction needs to be told not to for this one.
- If it fails, for example on a deadlock or a duplicate in a unique index, it leaves an invalid index behind. That index is ignored by queries and still costs every write. Drop it and build again.
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalidfinds them. - It isn't supported on a partitioned table. Build the index concurrently on each partition, then create the parent's index without
CONCURRENTLY, which is then a catalog change.
DROP INDEX takes ACCESS EXCLUSIVE on the table. DROP INDEX CONCURRENTLY waits for conflicting transactions instead, takes one index at a time, has no CASCADE, and can't drop an index behind a UNIQUE or PRIMARY KEY constraint. It doesn't work on a partitioned table's index either.
Primary keys and unique constraints
ADD PRIMARY KEY and ADD UNIQUE build their index under ACCESS EXCLUSIVE. Build the index concurrently first, then turn it into the constraint:
CREATE UNIQUE INDEX CONCURRENTLY orders_public_id_key ON orders (public_id);
ALTER TABLE orders ADD CONSTRAINT orders_public_id_key UNIQUE USING INDEX orders_public_id_key;
The second statement is instant for UNIQUE. For PRIMARY KEY, it also runs SET NOT NULL on any key column that isn't already NOT NULL, and that scans the table under ACCESS EXCLUSIVE. Make the columns NOT NULL first with the CHECK route above.
REINDEX
The REINDEX page says plain REINDEX "locks out writes but not reads" of the table, and takes ACCESS EXCLUSIVE on the index it rebuilds. Then it adds that the planner takes ACCESS SHARE on every index of a table for any query, "and so REINDEX blocks virtually any queries". Treat it as blocking reads. REINDEX … CONCURRENTLY, from 12, takes SHARE UPDATE EXCLUSIVE on the table and the indexes. It can't run in a transaction block either, and a failed run leaves an invalid index named with _ccnew, which you drop.
Rewrites with no online form
ALTER COLUMN … TYPE rewrites the table and its indexes unless the old type is binary coercible to the new one and USING doesn't change the values. varchar to text is the common case that skips the rewrite. Indexes are rebuilt unless PostgreSQL can tell they would come out the same. There is no concurrent form. A new column, a batched backfill and a switch in the application move the work out of the lock, at the cost of more steps.
VACUUM FULL and CLUSTER rewrite the table under ACCESS EXCLUSIVE and need room for a second copy of it. Plain VACUUM takes SHARE UPDATE EXCLUSIVE and runs beside reads and writes, but it keeps the freed space inside the table for reuse rather than returning it to the operating system. Its last step, cutting empty pages off the end of the table, needs ACCESS EXCLUSIVE, and VACUUM (TRUNCATE false) skips it.
TRUNCATE is instant and takes ACCESS EXCLUSIVE. A DELETE in batches takes ROW EXCLUSIVE instead, and leaves dead rows for VACUUM to clean.
Partitions
ATTACH PARTITION takes SHARE UPDATE EXCLUSIVE on the parent from 12, so queries on the partitioned table keep running. It takes ACCESS EXCLUSIVE on the table being attached, and on the default partition if there is one. It scans the new partition to check its rows fit the bounds, unless a valid CHECK constraint already proves it:
ALTER TABLE orders_2026_12 ADD CONSTRAINT orders_2026_12_bounds
CHECK (created_at >= '2026-12-01' AND created_at < '2027-01-01');
ALTER TABLE orders ATTACH PARTITION orders_2026_12
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
ALTER TABLE orders_2026_12 DROP CONSTRAINT orders_2026_12_bounds;
Plain DETACH PARTITION takes ACCESS EXCLUSIVE on the parent, so every query on the partitioned table waits. It also takes SHARE on tables with foreign keys to the partitioned table. DETACH PARTITION … CONCURRENTLY, from 14, uses two transactions: SHARE UPDATE EXCLUSIVE on both tables first, then a wait for every transaction using the partitioned table, then ACCESS EXCLUSIVE on the partition alone. It can't run in a transaction block, and it isn't allowed when the partitioned table has a default partition. If it is interrupted, DETACH PARTITION … FINALIZE completes it.
What changed, by version
| Version | Change |
|---|---|
| 11 | ADD COLUMN with a non-volatile default stops rewriting the table |
| 12 | REINDEX CONCURRENTLY. SET NOT NULL skips its scan when a valid CHECK proves it. ATTACH PARTITION takes a lighter lock on the parent. ALTER INDEX … RENAME takes SHARE UPDATE EXCLUSIVE |
| 14 | DETACH PARTITION … CONCURRENTLY and FINALIZE |
| 18 | Not-null constraints can be NOT VALID. NOT VALID foreign keys on partitioned tables. Generated columns are virtual by default and never rewrite. CHECK and foreign keys can be NOT ENFORCED |
The lock modes and the conflict table have not changed in that time.
What Seqora's lock forecast does
Seqora is a desktop database client, and I wrote it. Its lock forecast reads a PostgreSQL DDL statement before it runs and shows, for each table it touches, whether reads and writes are blocked, the work, from instant to rewrite, the table's size and row estimate from the catalog, its current write rate, and the sessions that hold a conflicting lock right now, with how long their transactions have been open. When the SQL editor stops a schema change for review, the forecast is on each statement's row. Forecast locks (⌥⌘L / CtrlAltL) shows it for the statement under the cursor without running anything. Where the forecast offers a safer form, Use safer form rewrites the statement into it. On production connections, DDL lock timeout in Settings sends a lock_timeout, 5 seconds by default, before each schema statement whose session has no limit of its own, and a timed-out statement offers Retry. The forecast is Free.
It does not predict how long anything will take. It knows the work is a scan of a large table, not how many seconds the scan is. It is PostgreSQL only. MySQL, MariaDB, SQLite and SQL Server keep the plain risk labels. A statement its rules don't cover reads Lock unknown instead of a guess. Design a schema change shows the same locks while you draw a change on the diagram, which is Pro. Seqora is free to download.
Caveats
- Everything here is from the PostgreSQL 18 documentation, with the version notes from the release notes for 11, 12, 14 and 18. Check the page for your own major version when it matters.
- Reads and writes in the table mean ordinary statements.
SELECT … FOR UPDATEand its relatives takeROW SHARE, whichEXCLUSIVEandACCESS EXCLUSIVEblock. - Row locks are a separate system. A long
UPDATEholds row locks that block other writes to those rows under any table lock. CREATE INDEX CONCURRENTLYwaits for older transactions through lock waits, so alock_timeoutcan cancel it and leave an invalid index. Leave it unset for the concurrent forms, or be ready to drop and rebuild.ALTER TABLErecurses into partitions and inheritance children unless you writeONLY, and it locks each of them.
Questions
Does ALTER TABLE ADD COLUMN lock the table?
Yes. It takes ACCESS EXCLUSIVE, which blocks reads and writes. From PostgreSQL 11, a column with no default or a non-volatile default is a catalog change that holds the lock for milliseconds. A volatile default, an identity column or a stored generated column rewrites the table under the lock. The danger in both cases is the wait for the lock, so set lock_timeout first.
Does CREATE INDEX block reads in PostgreSQL?
No. CREATE INDEX takes SHARE, which allows reads and blocks inserts, updates and deletes until the build ends. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE and blocks neither, but it can't run in a transaction block and takes longer.
Does adding a foreign key lock both tables?
Yes. ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on the table you alter and on the table it references, which blocks writes to both while the existing rows are checked. Add it NOT VALID, then run VALIDATE CONSTRAINT, which takes SHARE UPDATE EXCLUSIVE and ROW SHARE and blocks neither reads nor writes.
Why did a fast ALTER TABLE block every query?
It waited for its lock behind a long transaction, and every query after it queued behind the ALTER. ACCESS EXCLUSIVE conflicts with every other lock, so even a plain SELECT waits behind a waiting ALTER. Set lock_timeout so the statement gives up instead, and look for sessions idle in transaction.
How do I see which query is blocking my ALTER TABLE?
Join pg_locks to pg_stat_activity on pid for the table, and call pg_blocking_pids(pid) for the waiting session. It returns the sessions holding a conflicting lock and the ones queued ahead of it. The query is under "Who holds the lock, and who waits" above.
Does VACUUM FULL lock the table?
Yes. VACUUM FULL rewrites the table under ACCESS EXCLUSIVE and blocks reads and writes until it finishes. Plain VACUUM takes SHARE UPDATE EXCLUSIVE and runs alongside normal traffic, but it does not return space to the operating system in most cases.