PostgreSQL Schema Migrations in 2026: Zero-Downtime, Expand/Contract, and Rollback

Most schema changes break running applications if done naively. Adding a NOT NULL column without a default while the app runs causes constraint violations. Renaming a column while the old code is still deployed breaks queries. Dropping a column before the app stops reading it removes data the app still needs.

Zero-downtime migrations require thinking in three phases: expand (add new structure alongside old), transition (migrate app to use new structure), and contract (remove old structure). This post covers the patterns, Prisma migration workflow, non-blocking index creation, and rollback strategies.

---

The Problem: Naïve Migrations Break Production

-- ❌ Dangerous: blocks table for duration of migration
ALTER TABLE users ADD COLUMN phone TEXT NOT NULL;
-- Fails if existing rows have no phone value

-- ❌ Dangerous: acquires full table lock
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
-- On a 10M row table, this locks for minutes

-- ❌ Dangerous on large tables
CREATE INDEX idx_orders_status ON orders(status);
-- Standard CREATE INDEX takes AccessShareLock — blocks writes

---

Expand/Contract Pattern

Every breaking schema change becomes a three-deploy process:

Deploy 1: EXPAND — add new column/table alongside old (backward-compatible)
Deploy 2: MIGRATE — backfill data, switch app to use new structure
Deploy 3: CONTRACT — remove old column/table (app no longer references it)

Example: Splitting name into first_name + last_name

Deploy 1 — Expand (add new columns, keep old):

-- migration_001_expand_name.sql
-- Safe: adding nullable columns never locks or breaks existing queries

ALTER TABLE users ADD COLUMN first_name TEXT;
ALTER TABLE users ADD COLUMN last_name TEXT;

-- Backfill in batches (non-blocking)
DO $$
DECLARE
  batch_size INTEGER := 1000;
  last_id UUID := NULL;
  processed INTEGER := 0;
BEGIN
  LOOP
    UPDATE users
    SET
      first_name = split_part(name, ' ', 1),
      last_name  = NULLIF(split_part(name, ' ', 2), '')
    WHERE id IN (
      SELECT id FROM users
      WHERE (last_id IS NULL OR id > last_id)
        AND first_name IS NULL
      ORDER BY id
      LIMIT batch_size
    )
    RETURNING id INTO last_id;

    GET DIAGNOSTICS processed = ROW_COUNT;
    EXIT WHEN processed < batch_size;

    -- Yield to other queries between batches
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

Deploy 2 — Transition (app writes to both columns, reads from new):

// App code during transition — writes to both, reads from new
async function getUserName(userId: string) {
  const user = await db.user.findUnique({
    where: { id: userId },
    select: { firstName: true, lastName: true, name: true },
  });
  // Prefer new fields, fall back to old
  return user?.firstName ?? user?.name ?? "";
}

async function updateUserName(userId: string, firstName: string, lastName: string) {
  await db.user.update({
    where: { id: userId },
    data: {
      firstName,
      lastName,
      name: `${firstName} ${lastName}`.trim(), // Keep old field in sync
    },
  });
}

Deploy 3 — Contract (remove old column):

-- migration_003_contract_name.sql
-- Safe: app no longer reads/writes 'name' column at this point

ALTER TABLE users DROP COLUMN name;

---

Non-Blocking Index Creation

-- ❌ Blocks writes on large tables (standard index)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ✅ Non-blocking (CONCURRENTLY) — allows reads and writes during build
-- Takes longer but doesn't lock the table
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Check index build progress
SELECT
  phase,
  blocks_done,
  blocks_total,
  ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index
WHERE relid = 'orders'::regclass;

CONCURRENTLY caveats:

• Cannot run inside a transaction (BEGIN...COMMIT)
• Takes 2–5× longer than standard index
• If it fails, leaves an invalid index (must DROP INDEX before retrying)
• Cannot be used for unique indexes on tables with existing duplicates

-- Check for invalid indexes (from failed CONCURRENTLY builds)
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexname IN (
    SELECT relname FROM pg_class
    JOIN pg_index ON pg_class.oid = pg_index.indexrelid
    WHERE NOT pg_index.indisvalid
  );

---

Adding NOT NULL Columns Safely

-- ❌ Dangerous: fails if rows exist; locks table to add default
ALTER TABLE invoices ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';

-- ✅ Safe three-step approach:

-- Step 1: Add nullable column with default (fast, no lock)
ALTER TABLE invoices ADD COLUMN currency TEXT DEFAULT 'USD';

-- Step 2: Backfill existing rows (batched, non-blocking)
UPDATE invoices SET currency = 'USD' WHERE currency IS NULL;

-- Step 3: Add NOT NULL constraint (validates without table rewrite in PG 12+)
-- First add as NOT VALID (fast, no lock on existing rows)
ALTER TABLE invoices ADD CONSTRAINT invoices_currency_not_null
  CHECK (currency IS NOT NULL) NOT VALID;

-- Then validate in background (shares lock, doesn't block writes)
ALTER TABLE invoices VALIDATE CONSTRAINT invoices_currency_not_null;

-- Step 4 (optional, Postgres 12+): Convert CHECK to actual NOT NULL
-- Only after all rows are confirmed non-null
ALTER TABLE invoices ALTER COLUMN currency SET NOT NULL;
ALTER TABLE invoices DROP CONSTRAINT invoices_currency_not_null;

---

Column Rename Strategy

Renaming a column requires dual-read/write during transition:

-- Prisma + PostgreSQL: rename user_id → creator_id on posts

-- Step 1 (Deploy 1): Add new column, copy data trigger
ALTER TABLE posts ADD COLUMN creator_id UUID REFERENCES users(id);

-- Copy existing data
UPDATE posts SET creator_id = user_id WHERE creator_id IS NULL;

-- Trigger to keep both in sync during transition
CREATE OR REPLACE FUNCTION sync_creator_id()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    IF NEW.user_id IS DISTINCT FROM NEW.creator_id THEN
      NEW.creator_id := NEW.user_id;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER sync_creator
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION sync_creator_id();

-- Step 2 (Deploy 2): App migrated to use creator_id
-- Step 3 (Deploy 3): Remove old column and trigger
DROP TRIGGER sync_creator ON posts;
DROP FUNCTION sync_creator_id();
ALTER TABLE posts DROP COLUMN user_id;

---

Prisma Migration Workflow

# Development: create migration
npx prisma migrate dev --name add_creator_id_to_posts

# The migration file is created at:
# prisma/migrations/20270204120000_add_creator_id_to_posts/migration.sql

# Production: apply migrations (non-destructive by default)
npx prisma migrate deploy

Editing generated migrations for safety:

-- prisma/migrations/20270204120000_add_creator_id_to_posts/migration.sql
-- GENERATED by Prisma -- review and modify before applying

-- Prisma generates: ALTER TABLE "posts" ADD COLUMN "creatorId" UUID
-- We modify to make it safe:

-- 1. Add nullable first (no DEFAULT required for nullable)
ALTER TABLE "posts" ADD COLUMN "creatorId" UUID;

-- 2. Copy data (in a single statement for small tables, batched for large)
UPDATE "posts" SET "creatorId" = "userId";

-- 3. Add index CONCURRENTLY (remove from Prisma migration, apply separately)
-- CREATE INDEX CONCURRENTLY idx_posts_creator_id ON posts("creatorId");

-- 4. Add FK constraint as NOT VALID, then validate separately
ALTER TABLE "posts" ADD CONSTRAINT "posts_creatorId_fkey"
  FOREIGN KEY ("creatorId") REFERENCES "users"("id") NOT VALID;

-- Note: run VALIDATE CONSTRAINT in a separate migration after the above

Separate migration for constraint validation:

-- prisma/migrations/20270204130000_validate_creator_fk/migration.sql
ALTER TABLE "posts" VALIDATE CONSTRAINT "posts_creatorId_fkey";

---

Locking Analysis Before Running Migrations

-- Check what locks your migration will acquire before running it
-- Run in a transaction, then rollback to not apply

BEGIN;

-- Test: will this acquire a dangerous lock?
ALTER TABLE orders ADD COLUMN priority TEXT;

-- Check current locks
SELECT mode, granted FROM pg_locks
WHERE relation = 'orders'::regclass::oid;

ROLLBACK;
-- Lock released — you've seen the impact without committing

Lock levels by operation (low → high risk):

---

Migration Rollback Strategy

Always write a down migration:

-- migrations/20270204_add_creator_id/up.sql
ALTER TABLE posts ADD COLUMN creator_id UUID REFERENCES users(id);
CREATE INDEX CONCURRENTLY idx_posts_creator_id ON posts(creator_id);

-- migrations/20270204_add_creator_id/down.sql
DROP INDEX CONCURRENTLY idx_posts_creator_id;
ALTER TABLE posts DROP COLUMN creator_id;

// scripts/rollback-migration.ts
// Run with: npx ts-node scripts/rollback-migration.ts 20270204_add_creator_id

import { readFileSync } from "fs";
import { pool } from "@/lib/db/pool";

async function rollback(migrationName: string) {
  const sql = readFileSync(
    `prisma/migrations/${migrationName}/down.sql`,
    "utf-8"
  );

  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query(sql);
    // Remove from migration history
    await client.query(
      `DELETE FROM "_prisma_migrations" WHERE migration_name = $1`,
      [migrationName]
    );
    await client.query("COMMIT");
    console.log(`Rolled back: ${migrationName}`);
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

---

Cost and Timeline

---

See Also

• PostgreSQL Connection Pooling — Connections during migrations
• PostgreSQL Advisory Locks — Preventing concurrent migration runs
• PostgreSQL Triggers Audit — Auditing data during migration
• AWS RDS Aurora — Managed PostgreSQL with blue/green deployments

---

Working With Viprasol

We run zero-downtime database migrations for production SaaS products — from single-column changes through complex table restructuring. Our team has executed migrations on PostgreSQL databases with billions of rows and strict SLA requirements.

What we deliver:

• Migration safety audit (identify lock risks before running)
• Expand/contract migration plans for breaking changes
• Non-blocking index creation strategy
• Rollback procedures and tested down migrations
• CI/CD migration pipeline with pre-deploy safety checks

Explore our web development services or contact us to plan your database migration safely.