Explain Analyze

← Notes

Adding a column and an index without downtime

26 Oct 2026 · 3 min read

Most schema changes in Postgres are safe. The unsafe ones share one trait: they wait for a lock, and everything queued behind that lock waits too.

Locks are the real risk

Many DDL statements take an ACCESS EXCLUSIVE lock. While that lock is waiting in the queue, every new query on the table queues behind it, including plain SELECTs. A long-running transaction plus a migration equals an outage, even if the migration itself would take milliseconds.

Set a lock timeout so the migration gives up instead of blocking traffic, then retry it from your deploy tooling:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web';

Adding a column

Since Postgres 11, adding a column with a non-volatile default does not rewrite the table. The default is stored in the catalog and applied when rows are read. The statement still needs its brief lock, which is why the timeout matters. A volatile default such as random() still forces a rewrite, so keep those out of ALTER TABLE.

Adding a default to an existing column with ALTER COLUMN ... SET DEFAULT only affects new rows and is metadata-only.

Indexes with CREATE INDEX CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

Reads and writes continue during the build. The cost is time and total work, since it scans the table twice and waits for transactions that might still need the old view. It cannot run inside a transaction block, so migration tools that wrap every migration in a transaction need that wrapping turned off for this file.

The failure mode is an INVALID index. If the build fails from a deadlock, a cancel, or a unique violation, the index stays behind. The planner ignores it, but writes still maintain it, so it costs you on every insert. Find them with:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

Drop it with DROP INDEX CONCURRENTLY and try again. Be careful with IF NOT EXISTS on retry. The name already exists, so the statement quietly does nothing and you keep the broken index.

NOT NULL without a full scan

ALTER COLUMN ... SET NOT NULL scans the whole table under a strong lock. The safe route goes through a check constraint:

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

VALIDATE still reads the table, but it takes a lock that allows concurrent reads and writes. Since Postgres 12, SET NOT NULL recognizes the validated check and skips its own scan, so the final step is quick.

Foreign keys

Use the same pattern:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Creating the constraint still takes a SHARE ROW EXCLUSIVE lock on both tables, briefly. Build an index on orders (customer_id) concurrently before you add it. Without that index, deletes on customers scan orders every time.

Backfilling in batches

One giant UPDATE holds row locks for its whole run, bloats the table, and can exhaust replication lag budgets. Walk the table by primary key instead:

UPDATE orders
SET channel = 'web'
WHERE id IN (
  SELECT id FROM orders
  WHERE channel IS NULL AND id > $1
  ORDER BY id
  LIMIT 5000
)
RETURNING id;

The application loops, uses the largest returned id as the next cursor, commits each batch, and sleeps briefly between batches. Watch autovacuum while this runs, since every update leaves a dead tuple behind.

Expand and contract

Schema and code changes should move in steps that each deploy on their own:

  1. Expand: add the nullable column and the index.
  2. Deploy code that writes both the old and new representation.
  3. Backfill old rows in batches, then add the NOT NULL and foreign key constraints.
  4. Deploy code that reads only the new column.
  5. Contract: stop writing the old column, then drop it in a later release.

Dropping a column is metadata-only but still needs a lock, and older app instances may still reference it during a rolling deploy. I have shipped that exact mistake, and the fix was a day of cleanup.