Schema changes without downtime in Postgres: locks, NOT VALID and safer migrations

A migration that runs in 40 milliseconds on your laptop can still take a production service down. The reason is rarely the work the statement does. It is the lock it needs, and the queue that forms behind that lock.

This post covers the lock levels that matter for common ALTER TABLE changes, three patterns that keep them short, and a habit that protects you when you get it wrong.

Why a small change can block everything

Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock. The PostgreSQL docs say it directly: that lock is taken unless a subcommand is noted otherwise. It conflicts with every other lock, including a plain SELECT.

Here is the part that surprises people. Suppose a slow report is reading the table. Your migration asks for ACCESS EXCLUSIVE and has to wait for the report. Meanwhile, every new query on that table queues up behind your migration, not behind the report. One long transaction plus one eager ALTER TABLE and the table is effectively frozen.

sequenceDiagram
    participant R as Slow report
    participant M as Migration
    participant A as App queries
    R->>R: holds a normal lock (running)
    M->>M: asks for ACCESS EXCLUSIVE (waits)
    A->>A: new queries queue behind the migration
    R-->>M: report finishes
    M-->>A: migration runs, queue drains

So there are two questions for any change: how long does the statement hold its lock, and how long might it wait to get it?

Set a lock timeout first

The cheap protection is to put a ceiling on the waiting. This is general good practice rather than something from one document, and the exact value depends on your traffic.

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
COMMIT;

If the lock is not available within three seconds, the statement fails with an error instead of freezing the table. Your migration tool can retry a minute later. A failed migration you can retry is far better than a stalled production table.

Tip

Make the timeout part of your migration runner, not something each author must remember. A default of a few seconds, with retries and a pause between them, removes a whole class of incidents.

Adding columns

Adding a nullable column with no default is a quick metadata change. Adding one with a default used to rewrite the table; today it depends on the default. The docs explain that a non-volatile default is stored in the table's metadata and needs no rewrite, while a volatile default such as clock_timestamp() rewrites the whole table and its indexes.

-- Fast: constant or non-volatile default, no rewrite
ALTER TABLE measurements ADD COLUMN mtime timestamptz DEFAULT now();

-- Slow on a big table: volatile default forces a rewrite
ALTER TABLE measurements ADD COLUMN stamp timestamptz DEFAULT clock_timestamp();

If you need a volatile value, add the column without a default, backfill in small batches, and then set the default for new rows.

Constraints: add first, validate later

Adding a CHECK or foreign key constraint normally scans the whole table while holding a strong lock. The NOT VALID option splits that into two steps:

ALTER TABLE orders
  ADD CONSTRAINT amount_positive CHECK (amount_minor > 0) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT amount_positive;

The first statement skips the scan. From then on, new inserts and updates are checked. The second statement scans the existing rows, but it takes only a SHARE UPDATE EXCLUSIVE lock, which does not block normal reads and writes. If it finds a bad old row, you fix the row and run it again.

Foreign keys follow the same shape. Per the docs, adding one needs a SHARE ROW EXCLUSIVE lock on both tables, and NOT VALID skips the scan during that step. Validation then happens separately.

Step	Lock (from the docs)	Scans the table?
ADD CONSTRAINT ... NOT VALID (CHECK)	ACCESS EXCLUSIVE, briefly	No
ADD FOREIGN KEY ... NOT VALID	SHARE ROW EXCLUSIVE on both tables	No
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	Yes, existing rows

Making a column NOT NULL

SET NOT NULL normally scans the full table to prove there are no nulls. The docs add one useful exception: if a valid CHECK constraint already proves the column has no nulls, the scan is skipped. That gives a safe recipe:

ALTER TABLE orders
  ADD CONSTRAINT note_present CHECK (note IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT note_present;
ALTER TABLE orders ALTER COLUMN note SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT note_present;

The SET NOT NULL step is now quick, because Postgres can rely on the validated constraint. Newer Postgres versions also describe NOT VALID for not-null constraints directly, so check what your server version supports before relying on it.

Indexes and the expand-and-contract habit

A plain CREATE INDEX blocks writes while it builds. CREATE INDEX CONCURRENTLY avoids that, at the cost of taking longer and not running inside a transaction block. If you are weighing whether an index is worth its write cost, our post on how indexes speed up reads and slow down writes covers the trade-off.

For changes that are not a single statement, such as renaming a column or splitting one into two, use expand and contract:

1. Expand: add the new column or table alongside the old one.
2. Write both: deploy code that writes to old and new.
3. Backfill: copy old data across in small batches.
4. Switch reads: deploy code that reads the new one.
5. Contract: after a quiet period, drop the old column.

Each step is small, reversible and safe to run while the previous version of the code is still serving traffic. That last point is the real goal. A migration should never need the app to stop.

A short checklist

• Know the lock each statement takes before you run it.
• Set lock_timeout and retry on failure.
• Use NOT VALID then VALIDATE CONSTRAINT for checks and foreign keys.
• Backfill in batches, not one giant UPDATE.
• Build indexes with CONCURRENTLY.
• Try the migration on a copy of production-sized data and read the plan and timing.
• Separate schema changes from code releases, so each can be rolled back on its own.

Run through that list for a month and migrations stop being the scary part of a release.

Sources

• PostgreSQL documentation: ALTER TABLE