Zero-downtime database migrations let you change a PostgreSQL schema while the application keeps serving traffic: no maintenance page, no failed checkouts, no queue of timed-out requests. The schema change itself is rarely the slow part. The outages come from locks: one ALTER TABLE waiting behind a long query can freeze every read on the table. This guide shows how PostgreSQL locks interact during migrations, how to guard every migration with lock_timeout, which operations are safe as written and which need a different recipe, and how to rename or retype a column with the expand-contract pattern and a batched backfill.

Zero-downtime database migrations with the expand-contract pattern: add the new column, write to both, backfill in batches, switch reads, then drop the old column
Expand, migrate, contract: each step works with both the old and the new version of the application.

From my own work: The worst migration incident I have cleaned up was a one-line ALTER TABLE that added a column. The statement itself takes milliseconds, but it waited behind a reporting query that ran for minutes, and every request touching that table queued behind it. A five-second lock_timeout and a retry loop would have turned that outage into a log line.

Why migrations cause downtime: the lock queue

Most forms of ALTER TABLE need an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including the ACCESS SHARE lock a plain SELECT takes (PostgreSQL: explicit locking). Locks are granted in queue order, so the sequence that causes outages looks like this:

  1. A long transaction or report holds ACCESS SHARE on orders.
  2. Your migration asks for ACCESS EXCLUSIVE and waits.
  3. Every new query on orders queues behind the migration, even simple reads.
  4. Connection pools fill, requests time out, and the site is down although nothing is broken.

The fix is to make migrations give up quickly instead of waiting. lock_timeout aborts a statement that waits longer than the limit for a lock, and statement_timeout caps total run time (PostgreSQL: client connection defaults):

-- at the top of every migration
SET lock_timeout = '5s';
SET statement_timeout = '15min';   -- generous for index builds, but never unlimited

-- find what is blocking right now
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

A migration that fails on a lock timeout is safe to retry. Wrap the migration command in a small retry loop so a busy moment does not fail the deploy:

#!/usr/bin/env bash
# migrate-with-retry.sh: retry a migration that gave up waiting for a lock
set -uo pipefail
for attempt in 1 2 3 4 5; do
  if psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f "$1"; then
    echo "migration applied on attempt $attempt"; exit 0
  fi
  echo "attempt $attempt failed, retrying in $((attempt * 10))s" >&2
  sleep $((attempt * 10))
done
echo "migration failed after 5 attempts" >&2; exit 1

Only retry migrations that are idempotent or run in a single transaction, so a failed attempt leaves nothing half applied. Use IF NOT EXISTS where PostgreSQL supports it.

Which schema changes are safe

The table below covers the operations teams run most often, based on the ALTER TABLE and CREATE INDEX documentation (PostgreSQL: ALTER TABLE).

Change As written Zero-downtime recipe
Add a nullable column Brief exclusive lock, no rewrite Safe with lock_timeout
Add a column with a constant default No rewrite; the default is stored in the catalog Safe with lock_timeout
Add a column with a volatile default such as clock_timestamp() Rewrites the table and its indexes Add nullable, backfill in batches, then set the default
Create an index Blocks writes for the whole build CREATE INDEX CONCURRENTLY
Add a foreign key or CHECK constraint Scans the table while holding locks NOT VALID, then VALIDATE CONSTRAINT
Set a column NOT NULL Full scan under an exclusive lock Validated CHECK or NOT VALID constraint first
Add a unique constraint Builds the index under lock Unique index concurrently, then USING INDEX
Change a column type Usually rewrites the table Expand-contract with a new column
Rename a column or table Fast, but breaks running code Expand-contract, or a compatibility view
Drop a column Fast, but breaks code that still selects it Remove all code references first, then drop

Create indexes concurrently

CREATE INDEX CONCURRENTLY builds the index while inserts, updates and deletes continue. It takes longer, scans the table twice and cannot run inside a transaction block. If it fails, it leaves an invalid index behind that still costs write time, so drop it and retry (PostgreSQL: building indexes concurrently):

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx ON orders (customer_id);

-- find invalid indexes left by a failed concurrent build
SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;   -- then run the CREATE again

-- unique constraint without a long lock
CREATE UNIQUE INDEX CONCURRENTLY users_email_key_idx ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_key_idx;

Check the new index with EXPLAIN before and after; a concurrent build is only worth its cost if the planner uses the result.

Foreign keys and NOT NULL without long locks

Constraints are split into two steps: add the constraint as NOT VALID, which only checks new rows and is fast, then validate it separately. Validation takes a SHARE UPDATE EXCLUSIVE lock that lets reads and writes continue.

SET lock_timeout = '5s';

-- foreign key
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

-- NOT NULL on a large table (works on every supported version)
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;   -- skips the scan: the CHECK proves it
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;

The third statement is fast because PostgreSQL skips the table scan when a valid CHECK constraint already proves no NULL exists. Since PostgreSQL 18 you can also add a NOT NULL constraint itself as NOT VALID and validate it later, which avoids the temporary CHECK. Fix existing NULLs with a batched backfill before validating, or validation will fail.

Rename a column with expand-contract

Suppose users.fullname must become users.display_name. A direct rename breaks every running instance of the old code the moment it commits. Expand-contract spreads the change over several deploys, each compatible with the version before it.

Step 1, expand: add the new column and keep it in sync while old code still writes the old one.

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN IF NOT EXISTS display_name text;

CREATE OR REPLACE FUNCTION users_sync_display_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.display_name := COALESCE(NEW.display_name, NEW.fullname);
  ELSIF NEW.fullname IS DISTINCT FROM OLD.fullname THEN
    NEW.display_name := NEW.fullname;            -- old code changed the old column
  ELSIF NEW.display_name IS DISTINCT FROM OLD.display_name THEN
    NEW.fullname := NEW.display_name;            -- new code changed the new column
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER users_sync_display_name
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION users_sync_display_name();

Step 2, backfill: copy existing rows in small batches, committing after each one so locks stay short and autovacuum can keep up. A procedure can commit inside its loop when it is called outside an explicit transaction:

CREATE OR REPLACE PROCEDURE backfill_display_name(batch_size int DEFAULT 5000)
LANGUAGE plpgsql AS $$
DECLARE
  last_id bigint := 0;
  max_id  bigint;
BEGIN
  SELECT coalesce(max(id), 0) INTO max_id FROM users;
  WHILE last_id < max_id LOOP
    UPDATE users
       SET display_name = fullname
     WHERE id > last_id AND id <= last_id + batch_size
       AND display_name IS NULL;
    last_id := last_id + batch_size;
    COMMIT;                     -- release row locks after every batch
    PERFORM pg_sleep(0.05);     -- leave room for normal traffic and replicas
  END LOOP;
END $$;

CALL backfill_display_name();   -- run outside BEGIN ... COMMIT

Transaction control in procedures is documented in the PL/pgSQL chapter (PostgreSQL: transaction management). Watch replication lag during large backfills; if replicas fall behind, increase the sleep or reduce the batch size. A logical replication setup like the one in the PostgreSQL logical replication guide is sensitive to bursts of updates in the same way.

Step 3, switch: deploy code that reads and writes display_name. The trigger keeps fullname current for any old instance still running during the rollout.

Step 4, contract: once no deployed code mentions fullname, remove the trigger and the column in a later release:

SET lock_timeout = '5s';
DROP TRIGGER IF EXISTS users_sync_display_name ON users;
DROP FUNCTION IF EXISTS users_sync_display_name();
ALTER TABLE users DROP COLUMN IF EXISTS fullname;

The same pattern changes a column type: add amount_cents bigint, sync and backfill from amount numeric, switch, then drop the old column.

Deploy order and framework settings

  • Expand migrations run before the code deploy; the old code ignores new nullable columns and new indexes.
  • Contract migrations run after every instance runs the new code, ideally one release later.
  • Never mix both in one migration during a rolling deploy.
  • Rails: use disable_ddl_transaction! with add_index :orders, :customer_id, algorithm: :concurrently.
  • Django: use AddIndexConcurrently from django.contrib.postgres.operations in a migration with atomic = False.
  • Any tool: set lock_timeout for the migration role, for example ALTER ROLE migrator SET lock_timeout = '5s';.

Rehearse risky migrations on a copy of production first. Database branches make this cheap, as shown in the Neon database branching guide, and a restore-tested backup is the safety net for anything irreversible; see automated PostgreSQL backup verification. Application-level rollouts such as blue-green deployments only stay zero-downtime if the schema changes underneath them follow these rules.

Zero-downtime database migrations checklist

  • SET lock_timeout and statement_timeout at the top of every migration, with retries.
  • No long-running transactions open when the migration starts.
  • Indexes built and dropped CONCURRENTLY; invalid indexes cleaned up.
  • Foreign keys and CHECK constraints added NOT VALID, then validated.
  • NOT NULL set through a validated constraint.
  • Renames and type changes done with expand-contract and batched backfills.
  • Expand before deploy, contract one release after.

Want this done on your own server or site?

I am Saizul Amin, a web developer and technical SEO consultant. I set up and harden VPS servers, migrate WordPress sites, fix indexing problems and build automations. See my portfolio or get in touch.

FAQ

What are zero-downtime database migrations?

They are schema changes applied while the application keeps running and serving traffic. Each step takes only brief locks and stays compatible with both the old and new application code, so no maintenance window is needed.

Why does ALTER TABLE block SELECT queries in PostgreSQL?

Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock. While it waits behind an existing query, new queries queue behind it, including plain SELECTs. A short lock_timeout makes the migration give up and retry instead of blocking everyone.

Can I run CREATE INDEX CONCURRENTLY in a transaction?

No. It cannot run inside a transaction block, so migration tools need a non-transactional migration for it. If the build fails, drop the invalid index it leaves behind and run it again.

How do I add NOT NULL to a large PostgreSQL table without downtime?

Add a CHECK (column IS NOT NULL) constraint as NOT VALID, validate it, then run SET NOT NULL, which skips the full scan because the valid constraint proves there are no NULLs. Finally drop the CHECK constraint.

What is the expand-contract pattern?

It splits a breaking schema change into backwards compatible steps: add the new structure, write to both old and new, backfill existing rows, switch reads, and only then remove the old structure in a later release.

Sources

NEW BLOGS. REAL STRATEGIES. REAL RESULTS.

Join our community and receive powerful SEO tips, web optimization guides, and growth strategies as soon as they’re published.

Don’t just read blogs — stay ahead of the competition.

We don’t spam! Read our privacy policy for more info.

Share.
Leave A Reply