# PostgreSQL migrations that lock a busy table

> Which schema changes in PostgreSQL block reads or writes on a live table, why each one does, and the safer sequence of statements to use in its place.

Published 2026-10-27 · https://aiskills402.com/blog/postgres-migration-locks

A PostgreSQL migration is risky for a live application when it requests a strong table lock and keeps it while a lengthy scan or table rebuild runs. Four everyday statements do that: adding a column with a default that changes per row, turning on `NOT NULL` for a column with existing rows, creating an index in the plain way, and adding a foreign key or check constraint that validates every row at once. Each has a safer form made of two or three smaller steps, and the extra steps cost little compared with a table that stops answering for the length of a scan.

## What a lock does to your application

PostgreSQL has eight table-level lock modes, and every statement takes one. The mode that matters most for migrations is `ACCESS EXCLUSIVE`. It conflicts with every other mode, so while it is held no one else can read or write the table. The manual notes that only this mode blocks a plain `SELECT`, and that many forms of `ALTER TABLE` take it unless the documentation says otherwise ([explicit locking](https://www.postgresql.org/docs/current/explicit-locking.html)). A statement that takes a weaker mode, such as `SHARE UPDATE EXCLUSIVE`, still allows ordinary reads and writes while it runs.

Two details turn a short lock into an outage. First, the statement has to wait for its turn: if a long transaction is already using the table, the migration queues behind it. Second, while it waits, every new query on that table queues behind the migration. So a lock that would be held for a very short time can still stop the application until the oldest open transaction finishes. Setting `lock_timeout` before anything else in the migration session makes it give up instead of building a queue, and a failed attempt that you retry is far better than a stalled service.

## 1. Adding a column with a default

This one is often feared unfairly. When a column is added with a default that is not volatile, PostgreSQL evaluates the value once and keeps it in the catalog, so existing rows are not touched and the statement returns quickly, even on a large table ([ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)). A constant such as `0`, `'new'` or `false` is safe. A column with no default, which means null, is safe too.

The trouble begins when the default is evaluated per row, such as `clock_timestamp()` or `random()`. According to the manual, four kinds of new column force a complete rebuild of the table together with its indexes: those with a per-row default, stored generated values, identity behaviour, or a domain type that carries constraints ([ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)). The same page warns that such rebuilds may need roughly twice the disk space for a while. For a large table, the safer sequence is to create the column bare, give new rows their default in a second statement, fill old rows in batches that each commit quickly, and add constraints last.

## 2. Setting a column to NOT NULL

`ALTER TABLE ... ALTER COLUMN ... SET NOT NULL` is the classic trap. By default it checks every row by reading the whole table, and it does that while holding the strong lock. The manual describes an exception that is the basis of the safe pattern: if a validated `CHECK` constraint already guarantees there is no null, the scan is skipped ([ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)).

The pattern, in order:

1. Add a check constraint that says the column is not null, marked `NOT VALID`. This is quick, and from then on new and changed rows are held to the rule.
2. Run `VALIDATE CONSTRAINT` on it. This scans the table but takes only a weaker lock, so reads and writes continue.
3. Run `SET NOT NULL`. The scan is now skipped because the validated check proves the point.
4. Drop the helper check constraint, which is optional housekeeping.

```sql
ALTER TABLE orders ADD CONSTRAINT orders_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_nn;
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_email_nn;
```

## 3. Creating an index

A plain `CREATE INDEX` takes a lock that blocks writes to the table until it finishes, although reads continue ([CREATE INDEX](https://www.postgresql.org/docs/current/sql-createindex.html)). On a table that receives constant inserts, that means every writer waits for the whole build. The remedy is `CREATE INDEX CONCURRENTLY`, which builds without blocking inserts, updates or deletes.

It comes with conditions that the manual states. It reads the table twice and waits for older transactions, so it takes longer and does more work than the plain form. It refuses to run within a transaction block, which matters because many migration tools wrap each file in a transaction by default; you need to mark the file to run outside one. Only one concurrent build can run on a table at a time. And if it fails, for example on a uniqueness violation, it leaves behind an index marked invalid. That index is ignored by queries but still slows every write, so the recommended recovery is to drop it and try again ([CREATE INDEX](https://www.postgresql.org/docs/current/sql-createindex.html)). Check for invalid indexes after any failed concurrent build; the `\d` command in `psql` shows them.

## 4. Adding a foreign key or a check constraint

Adding most constraints needs the strong lock plus a scan, because PostgreSQL must verify the existing rows. Foreign keys are the milder case: the manual says that `ADD FOREIGN KEY` takes `SHARE ROW EXCLUSIVE` on both tables instead, which still blocks writes to them ([ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)).

The way out is the same split used for `NOT NULL`. Add the constraint as `NOT VALID`, which skips the long scan and applies the rule to new and updated rows only, then run `VALIDATE CONSTRAINT` as a separate statement. The validation scans the table under `SHARE UPDATE EXCLUSIVE` for the check and foreign key forms, a mode that does not stop ordinary traffic ([ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)). The two steps can even be separate deployments, so the long scan happens at a quiet hour.

```sql
ALTER TABLE invoices
  ADD CONSTRAINT invoices_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE invoices VALIDATE CONSTRAINT invoices_customer_fk;
```

## 5. Changing a column type, dropping and renaming

Converting an existing column to another type usually rebuilds the table and each index on it. The manual lists an exception: when the new type can reuse the stored bytes without any change, the table is left alone, though indexes may still be rebuilt ([ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)). Everything else is better done as a new column, a backfill, a switch in the application, and a later drop.

Dropping or renaming a column is quick in the database, but it breaks every piece of application code still reading the old name. During a rolling deploy, old and new application versions run at once. So the order is: stop reading the column in code, deploy, then remove it in a later migration. A rename follows the same reasoning, and the safer path is to add the new column and copy.

## A routine that keeps migrations small

Put four habits in every migration file. Set `lock_timeout` so a blocked statement fails fast. Keep one risky statement per file so a failure is easy to locate. Backfill in batches, never in one statement. And run the migration against a copy of the real data volume first, because a statement that takes a moment on a development database can run for a long time on production. For how a rebuild-style migration can fail on a different platform, see our write-up of [a table rebuild that D1 rejects](/blog/llm-d1-migrations-rebuild-fails), which shows the same habit applied to SQLite.

## When this does not apply

- The guide follows the current manual as read on 27 October 2026. Older server versions behave differently, and the fast path for constant defaults in particular is not available everywhere. Check the manual for the version you actually run.
- Tiny tables, new empty tables and databases that are taken offline for maintenance can use the plain forms without harm. The cost of the safer patterns is only worth paying when the table is large or busy.
- Managed services and extensions can add their own behaviour, such as replication that must apply the same lock on a replica, and we did not test any of them.
- We did not time any statement for this guide. How long a scan lasts depends on table size, hardware and concurrent load, and the manual gives no durations.
- Some tools wrap migrations in a transaction automatically, which makes concurrent index builds impossible until you change that setting. The details differ between tools and we did not compare them.
- Lock behaviour has more cases than are listed here, including partitioned tables, where attaching and detaching have their own rules.

## A second reader for your own migration

Before a migration is deployed, the skill [postgres-migration-lock-review](https://aiskills402.com/skills/postgres-migration-lock-review) reads a single migration file and lists just the statements that block a table in use or rebuild it, giving for each the statement, the reason and the safer form, or stating that nothing was found. It deliberately ignores the harmless lookalikes, such as an index built concurrently or a constraint added as not valid and validated later. The skill store is at https://aiskills402.com.