Stage 3 · Build

PostgreSQL Operations

Zero-Downtime Schema Migrations

Expand/contract, non-blocking index builds, and migration frameworks.

8 min readDatabases for SREBuild

Migration Challenges

Schema migrations in production are dangerous because they can lock tables, block queries, and cause downtime. A simple ALTER TABLE on a large table can take minutes or hours, during which the table is inaccessible.

Dangerous migration examples


-- This locks the table for the entire operation
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- On a 100M row table, this rewrites the entire table
-- Table is inaccessible during the rewrite
ALTER TABLE orders ALTER COLUMN total TYPE DECIMAL(12, 2);

-- This creates an index and blocks all writes
CREATE INDEX idx_orders_status ON orders(status);

ALTER TABLE ... ADD COLUMN with a non-constant DEFAULT (like gen_random_uuid()) requires a full table rewrite in PostgreSQL < 11. In PostgreSQL 11+, metadata-only defaults are fast.

Always test migrations on production-sized data

A migration that takes 1 second on 1000 rows may take 1 hour on 100M rows. Create a staging environment with a copy of production data and measure migration duration before deploying.

Expand/Contract Pattern

The expand/contract pattern (also called parallel change) separates schema changes into three phases: expand (add new structure), migrate (copy data), and contract (remove old structure). This allows zero-downtime deployments.

Expand phase: add new column


-- Phase 1: Expand — add the new column
ALTER TABLE users ADD COLUMN email_normalized VARCHAR(255);

-- Backfill existing rows
UPDATE users SET email_normalized = LOWER(TRIM(email))
WHERE email_normalized IS NULL;

-- Add a trigger to keep new/updated rows in sync
CREATE OR REPLACE FUNCTION sync_email_normalized()
RETURNS TRIGGER AS $$
BEGIN
    NEW.email_normalized := LOWER(TRIM(NEW.email));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

During the expand phase, both old and new columns exist. The trigger ensures new writes keep them in sync. The backfill can run in batches during low-traffic periods.

Contract phase: remove old column


-- Phase 2: Migrate application code to use email_normalized
-- Phase 3: Contract — remove old column after code is deployed
DROP TRIGGER trg_sync_email_normalized ON users;
ALTER TABLE users DROP COLUMN email;

Only drop the old column after all application code has been deployed using the new column. Keep a rollback window before the contract phase.

Non-Blocking Index Builds

CONCURRENTLY index creation


-- Standard CREATE INDEX blocks writes
CREATE INDEX idx_orders_status ON orders(status);

-- CONCURRENTLY allows reads and writes during build
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- If CONCURRENTLY fails, it leaves an invalid index
SELECT indexrelid::regclass AS index_name, indisvalid AS is_valid
FROM pg_index WHERE NOT indisvalid;

-- Drop the invalid index and retry
DROP INDEX CONCURRENTLY idx_orders_status;

CONCURRENTLY builds the index in multiple passes, allowing other operations to proceed. It takes longer than a regular build but does not block traffic. Always use CONCURRENTLY in production.

Use CONCURRENTLY for every production index

CREATE INDEX CONCURRENTLY is the only safe way to add indexes in production. The performance difference is negligible compared to the risk of blocking all writes for minutes or hours.

Adding Columns Safely

Safe column addition patterns


-- Safe in PostgreSQL 11+ (metadata-only, instant)
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT '';

-- Safe: add NOT NULL with a default (instant in PostgreSQL 11+)
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0 NOT NULL;

-- Unsafe: adding NOT NULL without default on existing data
-- This requires a full table scan and rewrite
ALTER TABLE orders ADD COLUMN region VARCHAR(50) NOT NULL;

-- Safe workaround: add nullable, backfill, then add constraint
ALTER TABLE orders ADD COLUMN region VARCHAR(50);
UPDATE orders SET region = 'us-east-1' WHERE region IS NULL;
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

PostgreSQL 11+ handles DEFAULT values as metadata for most types, making ADD COLUMN instant. For NOT NULL constraints on existing data, add the column as nullable first, backfill, then enforce NOT NULL.

Renaming Columns

Safe column rename pattern


-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);

-- Step 2: Create a view that maps old name to new
CREATE VIEW users_compat AS
SELECT id, name AS full_name_compat, full_name, email, created_at
FROM users;

-- Step 3: Update application to read from full_name
-- Step 4: Backfill full_name from name
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Step 5: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

-- Step 6: Drop old column and view
DROP VIEW users_compat;
ALTER TABLE users DROP COLUMN name;

Never use RENAME COLUMN in production — it breaks all queries referencing the old name simultaneously. The expand/contract pattern with a compatibility view allows gradual migration.

Migration Frameworks

Framework	Language	Key Feature
Flyway	Any (SQL)	SQL-based, no code required
Alembic	Python	Auto-generates from SQLAlchemy models
golang-migrate	Go	CLI-first, supports multiple databases
ActiveRecord	Ruby	Built into Rails, migration DSL
TypeORM	TypeScript	Auto-generates from entity decorators

Migration file naming convention


-- Files are numbered sequentially
migrations/
  001_create_users.up.sql
  001_create_users.down.sql
  002_add_email_index.up.sql
  002_add_email_index.down.sql
  003_add_orders_table.up.sql
  003_add_orders_table.down.sql

-- Always provide rollback (down) migrations
-- Test both up and down before deploying

Every migration must have a down script that reverses it. Without rollback capability, a failed migration leaves the database in an inconsistent state.

Migration safety checklist

1) Test on production-sized data. 2) Use CONCURRENTLY for indexes. 3) Use expand/contract for column changes. 4) Provide rollback scripts. 5) Keep migrations small and atomic. 6) Deploy migrations before application code when adding columns.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.