Database Migrations in Production: Zero-Downtime Schema Changes for Growing Startups

At some point, every growing startup runs ALTER TABLE on a production database and watches their p99 latency spike to 30 seconds. If they are lucky, the lock clears before users notice. If they are not lucky, they are writing a postmortem at 2 AM.

The problem is not that schema migrations are dangerous by nature. The problem is that the default tooling does the simplest possible thing: lock the table, rewrite it, unlock. That works fine on a table with ten thousand rows. It is catastrophic on a table with fifty million rows that your application is actively writing to every second.

This article covers why locks happen, how to avoid them, which tools actually work in production, and how to decide when you need zero-downtime DDL versus when a maintenance window is the right call.

Why ALTER TABLE Locks Your Database

PostgreSQL and MySQL both acquire an ACCESS EXCLUSIVE lock (or equivalent) for most DDL operations. This lock blocks every read and write on the table until the operation completes.

For small operations like adding a nullable column in Postgres 11+, the lock is held only briefly. For larger operations like adding a NOT NULL column with a default, or building an index, or changing a column type, the lock is held for the entire duration of the table rewrite. On a fifty-million-row table, that could be minutes.

The deeper issue is lock queuing. While your ALTER TABLE waits to acquire its exclusive lock, it blocks all incoming queries from acquiring their shared locks. They queue up behind it. Within seconds you have hundreds of queries waiting. Your connection pool fills. New requests are rejected. The cascade happens faster than you expect.

// This query pattern is fine in development on a small table
// In production on users (50M rows), it can take 5+ minutes while holding an exclusive lock
// ALTER TABLE users ADD COLUMN last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

// Checking lock status during a migration (useful for debugging, not running migrations):
import { Pool } from "pg";

const db = new Pool({ connectionString: process.env.DATABASE_URL });

async function checkTableLocks(tableName: string) {
  const result = await db.query<{
    pid: number;
    query: string;
    state: string;
    wait_event_type: string;
    wait_event: string;
    duration: string;
  }>(
    `
    SELECT
      pid,
      query,
      state,
      wait_event_type,
      wait_event,
      now() - pg_stat_activity.query_start AS duration
    FROM pg_stat_activity
    WHERE wait_event_type = 'Lock'
      AND query ILIKE $1
    ORDER BY duration DESC
  `,
    [`%${tableName}%`]
  );
  return result.rows;
}

The lock duration depends on the operation. Adding a nullable column in Postgres is near-instant because no table rewrite is needed. Adding a NOT NULL column forces a full table scan to validate constraints. Changing a VARCHAR(100) to VARCHAR(200) in Postgres requires no rewrite and is safe. Changing it to VARCHAR(50) forces a rewrite. These details matter and they are database-engine-specific.

The Expand-Contract Pattern

The fundamental technique for zero-downtime schema changes is expand-contract, sometimes called parallel change. Instead of modifying a column directly, you add the new shape alongside the old one, migrate data gradually, then remove the old shape once nothing depends on it.

It breaks into three phases:

Phase 1: Expand. Add the new column or table. It is nullable with no constraints. The application writes to both old and new. Reads still use the old column.

Phase 2: Migrate. Backfill the new column for existing rows. Once backfill is complete, switch reads to the new column. At this point the application reads from new, writes to both.

Phase 3: Contract. Stop writing to the old column. Once you confirm nothing reads it, drop it.

// Phase 1: add new column (instant, nullable, no lock held beyond metadata update)
// ALTER TABLE users ADD COLUMN email_normalized TEXT;

// Application code during Phase 1 and 2: dual-write
interface User {
  id: string;
  email: string;
  email_normalized: string | null; // new column, nullable during migration
}

async function updateUserEmail(
  db: Pool,
  userId: string,
  email: string
): Promise<void> {
  // Write to both columns during the transition
  await db.query(
    `UPDATE users
     SET email = $1, email_normalized = lower(trim($2))
     WHERE id = $3`,
    [email, email, userId]
  );
}

// Phase 2: backfill in batches (never backfill in a single UPDATE)
async function backfillEmailNormalized(db: Pool): Promise<void> {
  let lastId = "00000000-0000-0000-0000-000000000000";
  const batchSize = 1000;

  while (true) {
    const result = await db.query<{ id: string }>(
      `UPDATE users
       SET email_normalized = lower(trim(email))
       WHERE id > $1
         AND email_normalized IS NULL
       ORDER BY id
       LIMIT $2
       RETURNING id`,
      [lastId, batchSize]
    );

    if (result.rows.length === 0) break;

    lastId = result.rows[result.rows.length - 1].id;

    // Throttle to avoid overwhelming the database under write load
    await new Promise((resolve) => setTimeout(resolve, 50));
  }
}

// Phase 3: add constraint, then in a later deploy drop old column
// ALTER TABLE users ALTER COLUMN email_normalized SET NOT NULL;
// ALTER TABLE users DROP COLUMN email; -- only after all code uses email_normalized

The expand-contract pattern is safe because each phase is independently deployable. Each deploy is backward-compatible with the previous schema state. You never need to coordinate a code deploy with a migration that requires downtime.

The cost is coordination overhead and duration. A single column rename that would take one migration now takes three deploys spread over days or weeks. For high-traffic tables, that cost is worth it. For low-traffic tables, it may not be.

Online Schema Migration Tools

For operations that cannot be avoided with expand-contract (adding an index on a large table, changing a column type that requires a rewrite), online DDL tools shadow the operation so the table stays available.

gh-ost (GitHub’s Online Schema Transmogrifier)

gh-ost is designed for MySQL. It works by creating a shadow table, streaming changes from the binary log into the shadow table while copying existing rows in the background, then atomically cutting over at the end.

The cutover is the clever part: it does not hold a lock while copying. The lock is held only for the final atomic swap, typically under a second.

# Example gh-ost command for adding an index to a large MySQL table
gh-ost \
  --user="migration_user" \
  --password="${DB_PASSWORD}" \
  --host="replica.internal" \
  --database="app" \
  --table="events" \
  --alter="ADD INDEX idx_events_user_created (user_id, created_at)" \
  --execute \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=1000" \
  --default-retries=120 \
  --cut-over=default \
  --verbose

The --max-load flag is important. gh-ost throttles itself when the database is under load above the threshold. This means the migration can take much longer than a direct ALTER TABLE but it will not take your database down.

pgroll (Postgres Online Rolling Migrations)

pgroll is a newer tool purpose-built for Postgres. It implements the expand-contract pattern at the tooling level: it maintains multiple schema versions simultaneously and uses Postgres views with triggers to keep them in sync.

The key insight in pgroll is that it lets you run two versions of your application simultaneously, one reading the old schema and one reading the new schema, without any code to manually dual-write.

// pgroll migration file: adding a NOT NULL column with a default
{
  "name": "add_status_to_orders",
  "operations": [
    {
      "add_column": {
        "table": "orders",
        "column": {
          "name": "status",
          "type": "text",
          "nullable": false,
          "default": "pending",
          "up": "'pending'",
          "down": "status"
        }
      }
    }
  ]
}

pgroll creates a new schema version that includes the column, backfills it using the up expression, and exposes a new Postgres schema (not just a table) that your new application version connects to. Old application versions continue using the previous schema. When you are confident the rollout is complete, you run pgroll complete to finalize the migration and clean up.

pt-online-schema-change (Percona Toolkit)

pt-osc is the older MySQL tool, predating gh-ost. It uses triggers instead of binary log streaming to capture changes during the copy phase. This has a higher write amplification cost since every INSERT, UPDATE, and DELETE on the original table fires a trigger to replicate to the shadow table.

For write-heavy tables, gh-ost is generally preferred because trigger overhead compounds under load. For read-heavy tables with moderate write volume, pt-osc works well and is simpler to set up.

TypeScript Migration Tooling

The online DDL tools above handle the database side. Your TypeScript application still needs a migration runner to track what has been applied and in what order.

Drizzle Kit

Drizzle generates SQL migrations from schema diffing. It does not run the SQL itself; it outputs files that you run through your chosen mechanism.

// drizzle.config.ts
import type { Config } from "drizzle-kit";

export default {
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

// src/db/schema.ts
import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  emailNormalized: text("email_normalized"), // nullable during Phase 1 migration
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Generate migration: npx drizzle-kit generate:pg
// This outputs a file like drizzle/0003_add_email_normalized.sql
// You can then inspect it, wrap it in a transaction, or pipe it to gh-ost

Drizzle’s approach gives you full visibility into the SQL before anything runs. The downside is that it does not automatically handle the expand-contract lifecycle. You manage that through your migration files and deploy sequence.

Prisma Migrate

Prisma Migrate takes a more opinionated stance. It tracks a shadow database to detect drift, generates migration files, and applies them with a deployment command.

// schema.prisma
model User {
  id              String    @id @default(uuid())
  email           String    @unique
  emailNormalized String?   // nullable: true during Phase 1
  createdAt       DateTime  @default(now())
}

// prisma/migrations/20260310_add_email_normalized/migration.sql
-- This file is generated; do not edit manually
-- AddColumn
ALTER TABLE "users" ADD COLUMN "email_normalized" TEXT;

// src/db/migrate.ts - run as part of your deployment pipeline
import { execSync } from "child_process";

async function runMigrations(): Promise<void> {
  console.log("Running Prisma migrations...");
  execSync("npx prisma migrate deploy", {
    stdio: "inherit",
    env: {
      ...process.env,
      DATABASE_URL: process.env.DATABASE_URL,
    },
  });
  console.log("Migrations complete.");
}

runMigrations().catch((err) => {
  console.error("Migration failed:", err);
  process.exit(1);
});

Prisma Migrate’s deploy command is safe for production: it only runs migrations that have not been applied and never generates new migrations in a non-interactive context. The risk comes from the generated SQL itself, which can still cause locks if you are not paying attention to what it produces.

Kysely Migrations

Kysely provides a programmatic migration interface that is explicit about the SQL you are running. This is the right choice when you want TypeScript types throughout, including in your migration code.

// migrations/2026_03_10_add_email_normalized.ts
import { Kysely, sql } from "kysely";

export async function up(db: Kysely<unknown>): Promise<void> {
  // Phase 1: add nullable column (no table lock concern)
  await db.schema
    .alterTable("users")
    .addColumn("email_normalized", "text")
    .execute();

  // Phase 2: create an index concurrently (no lock on Postgres)
  // Note: CONCURRENTLY cannot run inside a transaction
  // Kysely lets you drop to raw SQL when needed
  await sql`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS
    idx_users_email_normalized
    ON users (email_normalized)
  `.execute(db);
}

export async function down(db: Kysely<unknown>): Promise<void> {
  await db.schema
    .alterTable("users")
    .dropColumn("email_normalized")
    .execute();
}

The CREATE INDEX CONCURRENTLY call is worth noting. Postgres supports online index creation through CONCURRENTLY, which does not hold an exclusive lock. It takes longer and requires two table scans, but the table stays readable and writable throughout. Kysely lets you drop to raw SQL for this case without losing type safety elsewhere.

Handling Backfills at Scale

Backfilling data is where migrations most often go wrong. A UPDATE users SET email_normalized = lower(email) that looks harmless on a staging database with a thousand rows will lock rows and generate write-ahead log (WAL) that overwhelms replication on a production database with fifty million rows.

The rules for safe backfills:

Batch by primary key, not by offset. LIMIT / OFFSET scans from the beginning of the table each time and gets slower with every batch. Cursor-based batching by primary key stays fast.

Keep batches small. One thousand to five thousand rows per batch is a reasonable starting point. Monitor replication lag and adjust.

Add a sleep between batches. Even 50ms matters. It gives replicas time to catch up and reduces sustained write pressure.

Run backfills from a job, not from a migration file. Migration files run synchronously during deployment. A fifty-million-row backfill in a migration file means your deployment is blocked for hours.

// src/jobs/backfill-email-normalized.ts
// Run this as a one-off job, not during deployment

import { Pool } from "pg";

interface BackfillOptions {
  batchSize: number;
  sleepMs: number;
  dryRun: boolean;
}

async function backfillEmailNormalized(
  db: Pool,
  options: BackfillOptions = { batchSize: 2000, sleepMs: 50, dryRun: false }
): Promise<void> {
  const { batchSize, sleepMs, dryRun } = options;
  let processed = 0;
  let lastId = "00000000-0000-0000-0000-000000000000";

  console.log(
    `Starting backfill: batchSize=${batchSize}, sleepMs=${sleepMs}, dryRun=${dryRun}`
  );

  while (true) {
    const countResult = await db.query<{ count: string }>(
      `SELECT count(*) FROM users WHERE id > $1 AND email_normalized IS NULL LIMIT 1`,
      [lastId]
    );

    if (countResult.rows[0]?.count === "0") break;

    if (!dryRun) {
      const result = await db.query<{ id: string; email_normalized: string }>(
        `UPDATE users
         SET email_normalized = lower(trim(email))
         WHERE id IN (
           SELECT id FROM users
           WHERE id > $1
             AND email_normalized IS NULL
           ORDER BY id
           LIMIT $2
         )
         RETURNING id, email_normalized`,
        [lastId, batchSize]
      );

      if (result.rows.length === 0) break;

      lastId = result.rows[result.rows.length - 1].id;
      processed += result.rows.length;
    }

    console.log(`Processed ${processed} rows, last id: ${lastId}`);

    await new Promise((resolve) => setTimeout(resolve, sleepMs));
  }

  console.log(`Backfill complete. Total rows processed: ${processed}`);
}

For very large tables (hundreds of millions of rows), consider running the backfill from a read replica to generate the new values, then applying the updates to the primary in batches. This reduces primary write load at the cost of additional read replica load and complexity.

Rollback Strategies

Most migration tools only think forward. Rollback is an afterthought. In production, you need to think about rollback before you run any migration.

The safest rollback strategy is non-destructive: only add columns and tables in the forward migration, never drop or constrain them. Dropping is reserved for later cleanup migrations that run after you have confirmed the deploy is stable. If you need to roll back, the old column still exists and the old code can run against the schema unchanged.

For the expand-contract lifecycle specifically:

• Roll back a Phase 1 deploy: drop the new column. No data was in it, nothing references it.
• Roll back a Phase 2 deploy: revert the code to read from the old column. The new column stays but is ignored.
• Roll back a Phase 3 deploy: you cannot roll back a dropped column. This is why Phase 3 cleanup should only happen after weeks of observation, not days.

// migration structure that supports safe rollback

// 0001_expand_add_email_normalized.sql
// Safe to roll back: just drop the new column
// ALTER TABLE users ADD COLUMN email_normalized TEXT;

// 0002_contract_set_not_null.sql
// Only run after backfill is 100% complete and app reads email_normalized
// Rollback: ALTER TABLE users ALTER COLUMN email_normalized DROP NOT NULL;
// ALTER TABLE users ALTER COLUMN email_normalized SET NOT NULL;

// 0003_cleanup_drop_email.sql
// Run weeks later, after full confidence. No rollback possible.
// ALTER TABLE users DROP COLUMN email;

For index migrations, Postgres DROP INDEX CONCURRENTLY is the rollback of CREATE INDEX CONCURRENTLY. Both are non-locking. Include both in your migration runner’s up and down functions.

Decision Framework: Online DDL vs. Maintenance Window

Not every production database needs zero-downtime migrations. The overhead of online DDL tools and multi-phase deploys is real. Here is how to decide:

Scenario	Recommendation
Table under 1M rows, low write traffic	Direct ALTER TABLE, off-peak hours
Table 1M-10M rows, moderate traffic	Test lock duration on staging, use maintenance window if under 30s
Table 10M+ rows, any write traffic	Expand-contract or online DDL tool required
Adding a nullable column (Postgres 11+)	Direct ALTER TABLE, near-instant even on large tables
Adding NOT NULL column with default	Expand-contract: add nullable, backfill, add constraint
Adding an index on large table	CREATE INDEX CONCURRENTLY in Postgres, gh-ost or pt-osc in MySQL
Changing column type (compatible)	Test on staging first; some type changes are rewrites in Postgres
Dropping a column or table	Always safe to defer; never a reason to drop during a high-stakes deploy
New table, any size	Direct CREATE TABLE, no lock concern

The one scenario where a maintenance window is often the right call is a critical migration on a system with low traffic volume and a clear off-peak window. Scheduling a two-minute maintenance window at 3 AM is less complex than coordinating a three-phase expand-contract migration on a table that gets ten writes per minute. Optimize for the actual risk, not for avoiding maintenance windows in principle.

Production Considerations

Lock timeouts. Set lock_timeout at the session level before any DDL in production. This prevents your migration from queuing indefinitely and blocking all traffic. If the lock cannot be acquired within the timeout, the migration fails fast and you can retry.

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

Statement timeouts. For online tools that run queries during migration, set statement_timeout to prevent any single batch from running too long.

Replication lag. Monitor replication lag during large backfills. If lag grows beyond your acceptable threshold (typically seconds for synchronous replicas, minutes for async), pause the backfill and wait for replicas to catch up. Most backfill scripts should check this automatically.

Connection pooling interactions. PgBouncer in transaction-mode pooling does not support session-level settings like lock_timeout because sessions are not persistent per client. Use statement-level SET LOCAL where possible, or ensure your migration runner uses a direct Postgres connection (not through PgBouncer) for DDL.

Schema migration tracking table. All migration tools maintain a table like _prisma_migrations or drizzle_migrations to track applied migrations. Never edit this table manually. If you need to mark a migration as applied without running it (for example, after applying it manually through an online DDL tool), most frameworks provide a command for this.

The Underlying Principle

Most database outages from migrations come from treating schema changes as a deployment detail rather than a data operation. A code deploy is typically reversible and fast. A schema change on a fifty-million-row table is neither.

The expand-contract pattern enforces the right mental model: schema changes happen in parallel with running software, not instead of it. The online DDL tools enforce it at the database level. Your migration runner just needs to stay out of the way.

The failure mode to avoid is running a long DDL inside a deployment that is already in progress under traffic. If the migration blocks, and the app cannot start because it expects the new schema, you are in a rollback situation with a table lock still held. Get migrations out of the startup path for any table over a few hundred thousand rows.