# Which ALTER TABLE statements lock a PostgreSQL table?

> **Quick answer:** Most `ALTER TABLE` forms take an `ACCESS EXCLUSIVE` lock
> (a handful take weaker ones, notably `ADD FOREIGN KEY`, which needs only
> `SHARE ROW EXCLUSIVE`), but most operations are **metadata-only** and release
> the lock in milliseconds.  The
> dangerous ones **rewrite the whole table**, some type changes, and adding a
> column with a *volatile* default, holding that lock for the entire rewrite.
> The other trap is the lock *queue*: a blocked `ALTER` makes every query behind
> it wait too.  Guard migrations with a short `lock_timeout` and retry.

:::note[Version-sensitive]
The exact behavior of several of these operations changed across PostgreSQL
versions.  The notes below call out the version; always confirm against the major
version you actually run.
:::

## The lock most ALTER TABLE statements take

Most `ALTER TABLE` subcommands acquire an `ACCESS EXCLUSIVE` lock, the strongest
lock, which blocks reads *and* writes on the table.  What differs is **how long**
it is held:

- **Metadata-only** changes update the catalog and release the lock almost
  immediately.
- **Table-rewrite** changes hold that lock while every row is rewritten, which
  on a large table can mean minutes of total unavailability.

## Metadata-only (fast, catalog change, no rewrite)

| Operation | Notes |
|---|---|
| `ADD COLUMN` with no default | Always metadata-only (all versions).  NULLs are supplied on readout |
| `ADD COLUMN` with a **constant** default | Metadata-only since **PostgreSQL 11** (the default is stored in the catalog) |
| `DROP COLUMN` | Logical, the column is marked dropped; space is reclaimed later |
| `RENAME COLUMN` / `RENAME` (table) | Catalog only |
| `ALTER COLUMN SET DEFAULT` / `DROP DEFAULT` | Catalog only |
| `ADD CONSTRAINT ... CHECK (...) NOT VALID` | Skips the validating scan; validate later (see below) |
| `ADD CONSTRAINT ... NOT NULL <col> NOT VALID` | **PostgreSQL 18+.** Skips the validating scan; enforced on new rows immediately, validate later |
| Widening `varchar(n)` → larger `varchar` or `text` | Binary-coercible, no rewrite |

## Table-rewrite or full scan (slow, holds ACCESS EXCLUSIVE for the whole operation)

| Operation | Notes |
|---|---|
| `ALTER COLUMN ... TYPE` requiring conversion (e.g. `integer` → `bigint`) | Rewrites every row |
| `ADD COLUMN` with a **volatile** default (e.g. `DEFAULT random()`) | Rewrites every row |
| `SET NOT NULL` | Full-table **scan** (not a heap rewrite) under `ACCESS EXCLUSIVE`. **PostgreSQL 12+** can skip the scan entirely, an instant metadata change, if a validated `CHECK (col IS NOT NULL)` constraint already exists.  On **PostgreSQL 18+** you can avoid `SET NOT NULL` entirely: add the constraint `NOT NULL <col> NOT VALID` (instant) and then `VALIDATE CONSTRAINT`, which scans under `SHARE UPDATE EXCLUSIVE` instead |

## The safer patterns

**Validate constraints in two steps.** Add the constraint `NOT VALID` (a quick
metadata change), then validate it separately.`VALIDATE CONSTRAINT` takes a
weaker `SHARE UPDATE EXCLUSIVE` lock that allows reads and writes:

```sql
ALTER TABLE orders ADD CONSTRAINT orders_total_positive
  CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;
```

On **PostgreSQL 18+** the same two-step pattern works for `NOT NULL`, because
not-null constraints are now stored in `pg_constraint` like any other constraint:

{/* sql-check: pg 18+ */}
```sql
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  NOT NULL region NOT VALID;                              -- instant catalog change
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;  -- SHARE UPDATE EXCLUSIVE
```

The column is enforced against new inserts and updates the moment the `NOT VALID`
constraint lands.`VALIDATE CONSTRAINT` only checks the pre-existing rows.  On
**PostgreSQL 17 and earlier** there is no unvalidated not-null, so you still need
the longer sequence: `CHECK (col IS NOT NULL) NOT VALID` → `VALIDATE CONSTRAINT` →
`SET NOT NULL` → drop the now-redundant check.

### Not everything takes ACCESS EXCLUSIVE

A few forms take weaker locks, which is worth knowing before you schedule a
maintenance window you do not need:

| Operation | Lock | Effect |
|---|---|---|
| `ADD FOREIGN KEY` | `SHARE ROW EXCLUSIVE`, on **both** the referencing and referenced tables | Blocks writes, allows reads |
| `VALIDATE CONSTRAINT` | `SHARE UPDATE EXCLUSIVE` | Allows reads **and** writes |
| `SET STATISTICS`, `CLUSTER ON`, `SET (attribute options)` | `SHARE UPDATE EXCLUSIVE` | Allows reads and writes |

Adding a foreign key still scans the referencing table to verify existing rows,
so on a big table use `NOT VALID` first and `VALIDATE CONSTRAINT` after, that
moves the scan under the weakest lock of the three.

**Always set a lock_timeout for migrations.** This is the single most important
habit.  Without it, a migration blocked behind a long query will itself block all
new traffic:

```sql
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN notes text;   -- fails fast if it cannot get the lock
```

Wrap it in a retry loop in your migration tooling: on `lock_not_available`, wait
and try again, so a transient long query does not turn into an outage.

## Why does aI-generated migration code get this wrong?

Your AI coding agent (Claude Code, Cursor) writes the `ALTER TABLE` that matches
the request.  "add a `status` column with a default", "change `id` to bigint", and it is syntactically perfect.  What it cannot see is that `orders` has 200
million rows, that `id → bigint` triggers a full rewrite, or that a reporting
query holds a lock right now.  Whether a statement is metadata-only or a
multi-minute table rewrite depends on your data volume and your live workload,
neither of which is in the source it reasons over.

## How do I ship schema changes safely?

- Know which category each statement falls in *before* running it in production.
- Prefer `NOT VALID` + `VALIDATE` for check constraints, foreign keys, and, on
  PostgreSQL 18+.`NOT NULL`.
- Set a short `lock_timeout` and retry on every migration.
- For a rewriting type change on a big table, consider the add-new-column /
  backfill / swap approach instead of an in-place `ALTER COLUMN TYPE`.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the real context around a migration, the table's row count, its size, and what is
currently holding locks, so it can warn that an `ALTER` will rewrite a huge
table or block behind live traffic *before* you run it.  It surfaces and explains;
it does not execute your migrations. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**Does adding a column with a default rewrite the table?**
Since PostgreSQL 11, a constant (non-volatile) default is metadata-only.  A
volatile default (e.g. `random()`) still rewrites every row.  On PostgreSQL 10 and
earlier, any default rewrote the table.

**Why did a quick ALTER TABLE freeze my whole application?**
`ALTER TABLE` needs an `ACCESS EXCLUSIVE` lock.  If a long query holds a weaker
lock, the `ALTER` waits, and every new query queues behind its pending lock, so
one slow query plus one migration stalls the table.

**How do I make a migration fail fast instead of blocking?**
`SET lock_timeout` (e.g. `'3s'`) before the `ALTER`, so it errors instead of
queuing when it cannot get the lock, and retry in a loop.

**Does changing a column type always rewrite the table?**
No.  Binary-coercible changes (widening `varchar`, or to `text`) are
metadata-only.  Conversions like `integer` → `bigint` rewrite the whole table.

## Related

- [How to add a PostgreSQL index without downtime](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-index-without-downtime/)
- [Why did my PostgreSQL query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)
- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)