PostgreSQL Migrations Without Downtime: The Complete Guide

Running migrations in production is the part of deployment that still causes most outages. Here's how to do it without taking your app down.

The Problem: Lock Contention

Most DDL operations in PostgreSQL acquire an ACCESS EXCLUSIVE lock that blocks all reads and writes. On a table with high traffic, this lock can queue behind existing transactions and cause a cascade of timeouts.

-- This blocks all reads/writes on users table for seconds (or minutes on large tables)
ALTER TABLE users ADD COLUMN last_seen_at TIMESTAMPTZ;

Safe Migration Patterns

Adding a Column

Adding a nullable column with no default is instant (safe):

-- Instant, no lock required in PostgreSQL 11+
ALTER TABLE users ADD COLUMN last_seen_at TIMESTAMPTZ;

Adding a column with a non-null default used to lock the table. In PostgreSQL 11+, it's fast because defaults are stored in the catalog, not written to each row. But for older versions:

-- Step 1: Add nullable column (instant)
ALTER TABLE users ADD COLUMN score INT;

-- Step 2: Backfill in batches (no lock)
DO $$
DECLARE batch_size INT := 1000;
DECLARE last_id BIGINT := 0;
BEGIN
  LOOP
    UPDATE users SET score = 0
    WHERE id > last_id AND score IS NULL
    LIMIT batch_size
    RETURNING id INTO last_id;
    EXIT WHEN NOT FOUND;
    PERFORM pg_sleep(0.01);  -- Throttle to avoid replication lag
  END LOOP;
END$$;

-- Step 3: Add NOT NULL constraint (fast, no table rewrite)
ALTER TABLE users ALTER COLUMN score SET NOT NULL;

Adding Indexes

Never add indexes without CONCURRENTLY:

-- Blocks all writes for minutes on large tables
CREATE INDEX idx_users_email ON users(email);

-- Non-blocking, takes longer but doesn't lock
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Gotcha: CONCURRENTLY can't run inside a transaction. Run it outside, or your migration framework needs to support that.

Renaming Columns

Renaming is dangerous because old and new code run simultaneously during deploy:

-- Step 1: Add new column, keep old one
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Step 2: Keep both in sync with a trigger
CREATE FUNCTION sync_name() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR NEW.username IS DISTINCT FROM OLD.username THEN
    NEW.full_name = NEW.username;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

-- Step 3: Backfill
UPDATE users SET full_name = username WHERE full_name IS NULL;

-- Step 4: Deploy code that reads full_name, writes to both
-- Step 5: Drop old column after old code is gone
ALTER TABLE users DROP COLUMN username;

Removing Columns

Your app must stop reading the column before you drop it:

1. Deploy code that ignores the column in SELECT/ORM models
2. Wait for all old instances to restart (no references)
3. Drop the column

In SQLAlchemy, use deferred() or exclude from model. In Rails, use ignored_columns.

Foreign Key Constraints

Adding FK constraints validates existing data and acquires locks:

-- Add NOT VALID to skip validation (instant, no lock)
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Validate separately (concurrent, slower but no write lock)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

Lock Timeout as a Safety Net

Set a short lock timeout so a migration fails fast instead of queuing forever:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN ...;

If it times out, retry in off-peak hours or use the multi-step approach above.

Tools That Handle This for You

• squitch -- PostgreSQL migration tool with change/revert/verify
• Flyway -- Java-based, widely used, handles CONCURRENTLY
• Atlas -- Schema-as-code with automatic safe migration plans
• pgroll -- Designed specifically for zero-downtime PostgreSQL migrations

The biggest wins come from treating migrations as a multi-step deploy process, not a single DDL statement.