In PostgreSQL, which table lock do ADD COLUMN, ADD FOREIGN KEY, VALIDATE CONSTRAINT and RENAME COLUMN take, and what does each block?
answer
- ACCESS EXCLUSIVE unless noted
- foreign keys lock two tables
- SHARE ROW EXCLUSIVE blocks writes
- validation runs beside traffic
- strictest subcommand wins
basics
~20 sIn PostgreSQL, ADD COLUMN and RENAME COLUMN take ACCESS EXCLUSIVE, blocking everything; ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables, blocking writes; VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE, leaving reads and writes running.
solid answer
~40 sPostgreSQL's rule is that an `ALTER TABLE` subform takes `ACCESS EXCLUSIVE` unless its documentation says otherwise, and when one statement has several subcommands it takes the strictest. `ADD COLUMN` and `RENAME COLUMN` take `ACCESS EXCLUSIVE`, which blocks even plain `SELECT`, though both are quick once granted. `ADD FOREIGN KEY` takes `SHARE ROW EXCLUSIVE` on the referencing table and the referenced table, because it creates triggers on both; that blocks `INSERT`, `UPDATE` and `DELETE` on both tables, though not plain reads, for the whole validation scan. `VALIDATE CONSTRAINT` takes only `SHARE UPDATE EXCLUSIVE`, plus `ROW SHARE` on the referenced table for a foreign key, so traffic continues and only `VACUUM`, `ANALYZE` and other schema changes wait. That is why foreign keys on large tables are added `NOT VALID` and validated later.
code
sql · 9 linesSET lock_timeout = '3s';
-- brief SHARE ROW EXCLUSIVE on posts and accounts, no scan
ALTER TABLE posts
ADD CONSTRAINT posts_account_id_fkey
FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
-- scans posts under SHARE UPDATE EXCLUSIVE; inserts keep flowing
ALTER TABLE posts VALIDATE CONSTRAINT posts_account_id_fkey;go deeper
Remember that most ALTER TABLE forms take ACCESS EXCLUSIVE, which blocks even reads, and that a few, like VALIDATE CONSTRAINT, take weaker locks.
Name the mode for each common statement, explain why a foreign key locks the referenced table too, and say what SHARE UPDATE EXCLUSIVE still blocks.
Plan migrations around lock modes: NOT VALID then VALIDATE for foreign keys, separate weak-lock steps, lock_timeout on every ACCESS EXCLUSIVE step, and a referencing index built first.
Set a team rule for which DDL needs a staged plan by its lock mode and duration, and how reviews check it before a release reaches the busiest tables.
## The default rule The `ALTER TABLE` reference page states the rule plainly: each subform may need a different lock level, an `ACCESS EXCLUSIVE` lock is acquired unless explicitly noted, and when a statement lists several subcommands the lock taken is the strictest any of them needs. So the useful question is never "does this DDL lock the table?" but **which mode, on which tables, and for how long**. ## What each mode blocks PostgreSQL has eight table-level lock modes. Four matter for schema changes: | Mode | Taken by | Blocks | Lets through | |---|---|---|---| | `ACCESS EXCLUSIVE` | most `ALTER TABLE` forms, `DROP TABLE`, `TRUNCATE` | everything, including plain `SELECT` | nothing | | `SHARE ROW EXCLUSIVE` | `ADD FOREIGN KEY`, `CREATE TRIGGER` | `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `VACUUM`, other schema changes | plain `SELECT`, `SELECT ... FOR UPDATE` | | `SHARE` | plain `CREATE INDEX` | writes and schema changes | reads | | `SHARE UPDATE EXCLUSIVE` | `VALIDATE CONSTRAINT`, `SET STATISTICS`, `VACUUM`, `ANALYZE`, `CREATE INDEX CONCURRENTLY` | itself and stronger modes: other `VACUUM`, `ANALYZE`, schema changes | reads and writes | The full conflict matrix, row-level locks and `pg_locks` are a separate subject; for migrations, the table above is the part you reason with. ## The four statements | Statement | Lock | Held for | |---|---|---| | `ALTER TABLE accounts ADD COLUMN locale text DEFAULT 'en'` | `ACCESS EXCLUSIVE` on `accounts` | milliseconds if catalog-only; the whole rewrite otherwise | | `ALTER TABLE accounts RENAME COLUMN display_name TO full_name` | `ACCESS EXCLUSIVE` on `accounts` | milliseconds; nothing on disk changes | | `ALTER TABLE posts ADD FOREIGN KEY (account_id) REFERENCES accounts (id)` | `SHARE ROW EXCLUSIVE` on `posts` **and** on `accounts` | the full scan of `posts` checking every row | | `ALTER TABLE posts VALIDATE CONSTRAINT posts_account_id_fkey` | `SHARE UPDATE EXCLUSIVE` on `posts`, `ROW SHARE` on `accounts` | the full scan, but traffic keeps flowing | Three lessons follow: - **Quick is not harmless.** `ADD COLUMN` and `RENAME COLUMN` need the strongest lock, so they must wait for every open transaction that has touched the table, and while they wait, other sessions queue behind them. A `lock_timeout` on each such step is the guard. - **Duration matters as much as mode.** A weak lock held for a twenty-minute scan can hurt more than a strong one held for a millisecond. - **The foreign key is the expensive one,** and it hurts a table you did not name. ## Foreign keys lock both tables A foreign key is enforced by triggers on both tables: the referencing side checks inserts and updates, the referenced side checks deletes and key updates. Creating triggers needs at least `SHARE ROW EXCLUSIVE`, so `ADD FOREIGN KEY` takes it on `posts` **and** on `accounts`. While it scans `posts`, nobody can insert into `accounts`, which on a social network means sign-ups stop. The standard remedy has two steps: 1. `ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID` takes the same `SHARE ROW EXCLUSIVE` locks but skips the scan, so they are held only for a moment. New and updated rows are checked from then on. 2. `VALIDATE CONSTRAINT` scans the existing rows under `SHARE UPDATE EXCLUSIVE` on `posts` and `ROW SHARE` on `accounts`, neither of which blocks reads or writes. Two related habits: PostgreSQL does not create an index on the referencing column for you, and without one every delete from `accounts` scans `posts`, so build `posts (account_id)` first, concurrently. And because step 2 conflicts with `VACUUM` and with itself, run it when no manual `VACUUM` or other validation is running on that table. ## Renames `RENAME COLUMN`, `RENAME TO` and constraint renames are catalog-only and finish instantly once granted, but they take `ACCESS EXCLUSIVE`. The database side is therefore only a lock-queue risk, handled with `lock_timeout` and retries. The larger risk is application code still using the old name, which is a deployment-ordering problem, not a locking one. ## Combining subcommands Because the strictest mode wins, putting a cheap change beside an expensive one costs you: - `ALTER TABLE accounts ALTER COLUMN handle SET STATISTICS 500, ADD COLUMN bio text` takes `ACCESS EXCLUSIVE`, not the `SHARE UPDATE EXCLUSIVE` that `SET STATISTICS` alone needs. - Combining is still right when every subcommand needs the same strong lock or a rewrite, because PostgreSQL then makes a single pass. - Keep weak-lock steps such as `VALIDATE CONSTRAINT` in their own statements and their own transactions.
- Why does adding a foreign key on posts in PostgreSQL stop new rows going into accounts?Because `ADD FOREIGN KEY` creates triggers on both tables and so takes `SHARE ROW EXCLUSIVE` on the referenced table too. That mode conflicts with `ROW EXCLUSIVE`, the lock every `INSERT`, `UPDATE` and `DELETE` takes, so writes to `accounts` wait for the whole validation scan of `posts`. Adding it `NOT VALID` makes that lock momentary.
- In PostgreSQL, can VALIDATE CONSTRAINT and a manual VACUUM run on the same table at once?No. Both take `SHARE UPDATE EXCLUSIVE`, which conflicts with itself, so whichever comes second waits. Autovacuum is different: a conflicting lock request interrupts an ordinary autovacuum, but one running to prevent transaction ID wraparound is not interrupted, so the validation waits for it.
- ADD FOREIGN KEY only locks the table the constraint is declared on.
- RENAME COLUMN is instant, so it cannot block anyone.
- VALIDATE CONSTRAINT blocks writes for the whole scan, like ADD CONSTRAINT.
- An ALTER TABLE with several subcommands takes the weakest lock among them.
- PostgreSQL indexes the referencing column automatically when you add a foreign key.