Zero-downtime schema changes, step by step: a deep dive

Zero-downtime schema changes, step by step

Every developer has felt the dread of a production database migration—the anxiety of downtime, the risk of rollback, the silent prayer that nothing goes wrong. I've seen deployments block entire platforms for minutes because a schema change locked the table. The good news: zero-downtime schema changes aren't magic. They're a methodical process that separates the old from the new, giving your system time to breathe while traffic keeps flowing.

Why schema changes cause downtime

Before we discuss the solution, let's understand the problem. Most relational databases acquire exclusive locks during ALTER TABLE operations. If you rename a column or add a non-nullable field, the database locks the entire table. Any query trying to read or write waits. On a production table with thousands of requests per second, that lock turns into timeouts, cascading failures, and angry on-call messages.

I once saw a 300-millisecond table lock on a payment processing table spike our error rate to 15% for two minutes. The migration itself took three seconds. The lesson: don't assume your local testing environment predicts production behavior.

The expand and contract pattern

Zero-downtime schema changes follow a three-phase workflow: expand the schema (add the new column), contract the schema (remove the old one). Between those phases, your application code handles the dual-write logic.

Phase 1: Expand (add the new column)

Start by adding a new nullable column to your table. This operation is fast and doesn't require an exclusive lock on most databases. PostgreSQL, for example, handles this nearly instantly because the column data is stored in a metadata structure, not written to every row immediately.

-- Migration: AddUserEmailNewColumn
ALTER TABLE users ADD COLUMN email_new VARCHAR(255) NULL;

That's it. Your table now has both the old column and the new one. No code changes yet.

Phase 2: Deploy dual-write logic

Once the column exists, deploy code that writes to both columns simultaneously. This ensures every new record—and every update—populates the new column while you backfill the old data.

// In your application layer (e.g., a user service)
async function updateUser(userId: string, email: string) {
  const query = `
    UPDATE users 
    SET email = $1, email_new = $2 
    WHERE id = $3
  `;
  await db.query(query, [email, email, userId]);
}

// Insertion also writes both
async function createUser(data: { email: string }) {
  const query = `
    INSERT INTO users (id, email, email_new)
    VALUES ($1, $2, $3)
  `;
  await db.query(query, [generateId(), data.email, data.email]);
}

After this deployment, every write operation targets both columns. Old rows still have NULL in email_new, but the backfill is next.

Phase 3: Backfill existing rows in batches

Now fill in the new column for millions of existing rows. The critical rule: do this in small batches. A single UPDATE that touches 5 million rows will lock the table. Instead, update 1,000 rows at a time, pause, then repeat.

// Backfill script
async function backfillEmailNew() {
  const batchSize = 1000;
  let updatedTotal = 0;

  while (true) {
    const result = await db.query(`
      UPDATE users
      SET email_new = email
      WHERE id IN (
        SELECT id FROM users
        WHERE email_new IS NULL
        LIMIT $1
      )
    `, [batchSize]);

    updatedTotal += result.rowCount;
    
    if (result.rowCount === 0) {
      console.log(`Backfill complete. Total rows: ${updatedTotal}`);
      break;
    }

    // Sleep between batches to reduce lock contention
    await sleep(100);
  }
}

This approach gives other queries time to execute between batches. A lock held for 50 milliseconds on a single batch causes far less disruption than a lock held for 30 seconds on a bulk update.

Phase 4: Switch reads to the new column

Once backfill completes, all rows have data in the new column. Deploy code that reads from email_new instead of email. The old column still exists; you're just ignoring it.

async function getUserEmail(userId: string) {
  const result = await db.query(
    'SELECT email_new as email FROM users WHERE id = $1',
    [userId]
  );
  return result.rows[0]?.email;
}

At this point, the old column is dead code. It's safe, harmless, and sits there taking up space.

Phase 5: Clean up (remove the old column)

Days or weeks later, when you're confident everything works, remove the old column in a separate deployment.

-- Migration: DropUserEmailOldColumn
ALTER TABLE users DROP COLUMN email;

This final step is optional from a functionality perspective, but it reclaims storage and reduces confusion in future maintenance.

Never rename in place

A common mistake is trying to rename a column directly:

-- DON'T DO THIS IN PRODUCTION
ALTER TABLE users RENAME COLUMN email TO email_new;

Here's why this breaks: your application instances are running code that references the old column name. When the rename happens mid-deployment, some instances see the new name and some see the old name. Queries fail. One application pod reads from email; another tries to read from email_new (which doesn't exist yet) and crashes.

Even worse, you can't cleanly roll back. The name is already changed in the database. I've seen teams spend 90 minutes recovering from a rename-in-place disaster that could have been avoided with a 5-minute expand-and-contract migration.

The expand-and-contract pattern avoids this entirely. Each step is reversible. If the dual-write code is bad, you roll back the deployment and the database is untouched. If the backfill goes wrong, you pause, investigate, and resume.

Batch backfills and lock contention

The difference between a lock held for milliseconds and one held for seconds is batch size. I've tested this with production-scale databases. On a 100 million row table:

• A single UPDATE without batching: 45-second lock, 12% error spike.
• Batches of 10,000 rows: 200-millisecond locks per batch, negligible error rate.

Smaller batches also give you observability. Each batch completes, and you log progress. If a batch hangs, you see it and can investigate without having already locked millions of rows.

The sleep between batches is not wasted time—it's your insurance policy. 100 milliseconds of sleep between 1,000-row updates means your lock is released frequently enough that other queries aren't starved.

// Monitor batch progress
const startTime = Date.now();
for (let offset = 0; offset < totalRows; offset += batchSize) {
  const batchStart = Date.now();
  
  await updateBatch(offset, batchSize);
  
  const batchDuration = Date.now() - batchStart;
  const elapsedTotal = Date.now() - startTime;
  const estimatedRemaining = (totalRows - offset) / batchSize * batchDuration;
  
  console.log(`Batch ${offset / batchSize}: ${batchDuration}ms | Total: ${elapsedTotal}ms | ETA: ${estimatedRemaining}ms`);
  
  await sleep(100);
}

Test with production-sized data

This is where most teams fail. A migration that finishes in 200 milliseconds on your laptop with 10,000 test rows takes 45 seconds on the production table with 80 million rows.

Before running any schema migration in production, clone your production database and run the migration against it. Measure query times. Measure lock durations. Run your backfill script and watch for slowness.

I recommend setting up a staging environment that's a 1:1 replica of production, refreshed weekly. When you're about to deploy a schema change, spin up that staging database and dry-run the migration end-to-end:

• Run the ALTER TABLE.
• Deploy the dual-write code.
• Run the backfill script and measure timing.
• Flip to reads from the new column.
• Run load tests against staging to confirm no regressions.

The 30 minutes spent in this dry-run prevents a production incident that costs hours.

Common mistakes

• Skipping the dual-write phase. Developers sometimes backfill the column and then switch reads without ever deploying dual-write logic. If a bug requires a rollback, new writes miss the backfill window and inconsistency creeps in.
• Backfilling too aggressively. A batch size of 100,000 rows to save time on a large table will lock for seconds and trigger alarms. Start conservatively—1,000 rows—and increase only if monitoring shows plenty of headroom.
• Removing the old column too soon. Impatient teams drop the old column after the read-flip, forgetting about rollback scenarios or cached code in older instances. Wait at least one deployment cycle (24-48 hours) after the read-flip before cleanup.
• Forgetting application layer changes. A schema change without corresponding code changes either breaks the app or leaves it reading stale columns. Keep the migration and code change in the same pull request and deploy them together.
• Not testing foreign keys. If other tables reference the column being changed, the migration can fail partway through. Test these dependencies in staging first.

Key takeaways

• Schema changes cause downtime because of exclusive locks. Break the migration into expand, dual-write, backfill, and contract phases to avoid them.
• Never rename columns in place; add the new column and deprecate the old one instead.
• Backfill in small batches (1,000-5,000 rows) with sleep between them to minimize lock duration and allow other queries to execute.
• Always test with production-scale data in a staging environment. What's instant locally can be catastrophic at scale.
• Each phase of the expand-and-contract pattern is independently reversible, making rollbacks safe and debugging easier.