How do I add an index or column to a big PostgreSQL table without downtime?

Caveats:

• It can't run inside a transaction block. Many migration frameworks wrap each migration in a transaction, so turn that off for this migration (Rails disable_ddl_transaction!, Django atomic = False, and so on).

• If it fails (for example a unique violation or a deadlock), it leaves an INVALID index behind that still slows down writes. Find it and drop it before retrying:

  SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
  DROP INDEX CONCURRENTLY idx_orders_customer;
  

• Use DROP INDEX CONCURRENTLY and REINDEX ... CONCURRENTLY (PostgreSQL 12+) for the same reasons.

Adding columns

ALTER TABLE orders ADD COLUMN note text;                        -- instant
ALTER TABLE orders ADD COLUMN priority int NOT NULL DEFAULT 0;  -- instant on PG 11+
ALTER TABLE orders ADD COLUMN token uuid DEFAULT gen_random_uuid(); -- REWRITES the table

Since PostgreSQL 11, a column with a constant (non-volatile) default is added instantly: the default is stored in the catalog, not written to every row. A volatile default such as random(), gen_random_uuid() or clock_timestamp() forces a full table rewrite under ACCESS EXCLUSIVE. Add the column without a default, backfill it in batches, then set the default.

Adding NOT NULL to an existing column

SET NOT NULL scans the whole table under ACCESS EXCLUSIVE. Split the scan out instead:

-- 1. Instant: constraint enforced for new rows only
ALTER TABLE orders ADD CONSTRAINT orders_customer_nn
  CHECK (customer_id IS NOT NULL) NOT VALID;

-- 2. Scans the table, but only takes SHARE UPDATE EXCLUSIVE (reads and writes continue)
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_nn;

-- 3. PG 12+ uses the validated CHECK to skip the scan
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_nn;

Foreign keys and check constraints

Use the same NOT VALID, then VALIDATE, 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;

Adding a unique constraint? Build the unique index CONCURRENTLY first, then attach it with ADD CONSTRAINT ... UNIQUE USING INDEX.

Changing a column's type

Most type changes rewrite the table (int → bigint, text → int, timestamp → timestamptz in most cases). A few are metadata-only: raising a varchar(n) limit, or changing varchar to text.

For a rewrite on a big table, use expand and contract:

1. Add the new column (instant).
2. Write to both columns from the app, or with a trigger.
3. Backfill old rows in batches.
4. Switch reads to the new column, then drop the old one.

It's the same dance for renames: the app must never depend on a column that doesn't exist yet or no longer exists.

Backfill in batches

UPDATE orders SET priority = 0
WHERE id IN (SELECT id FROM orders WHERE priority IS NULL LIMIT 5000);
-- repeat until 0 rows, with a short pause between batches

Small batches keep locks short, let autovacuum keep up, and avoid a replication-lag spike.

Checklist

1. Always SET lock_timeout for migrations and retry on timeout.
2. Use CREATE INDEX CONCURRENTLY outside a transaction, and clean up invalid indexes.
3. Use only constant defaults for new columns; backfill volatile values separately.
4. For NOT NULL, foreign keys and checks: add NOT VALID, then VALIDATE.
5. Use expand and contract for type changes and renames, and batch every backfill.

How PostgreSQL MVCC and VACUUM actually work

Every UPDATE in PostgreSQL leaves a dead row behind. Here's how MVCC works under the hood, what VACUUM cleans up, and how to keep autovacuum ahead of your workload.

#concurrency#internals#maintenance