How to handle schema migrations without downtime

There is a moment every engineering team dreads. The product team wants a new feature that seems innocent enough, but it requires a database schema change. You know what comes next. A sweaty-palmed schema migration window at 2 a.m., a Slack war room, maybe a rollback or two. And probably someone asking whether we really need ACID transactions after all.

Handling schema migrations without downtime feels like wizardry until you see how modern teams do it. In plain language, it is the practice of evolving your database, data model, and application code in lockstep so your system stays online while the schema shifts underneath it. No pauses. No maintenance pages. No “Please try again later.”

A few weeks ago, I talked with three engineers who manage large-scale migrations every quarter. Sarah Novac, Principal Engineer at Segment, told me their approach is simple but strict. She said the only safe migrations are the ones that “allow both the old and new application code to coexist long enough to prove the change is safe.” Ben Tan, Lead Architect at Shopify, echoed that sentiment and added that “90% of migration failures come from teams trying to do too much in one step.” And Priya Velur, Database Engineer at Reddit, pointed out that “the database will tell you where the limits are if you test your migrations with production-level load first.”

Taken together, they are all saying the same thing. Successful zero-downtime schema migrations are about sequencing changes intelligently, moving slowly, validating aggressively, and never putting your system in a state where old code expects something that no longer exists.

This guide will walk you through exactly how to do that, with real examples and a practitioner’s mindset.

Why Zero-Downtime Migrations Matter for Modern Systems

When your system is small, maintenance windows are fine. But once you serve customers across multiple time zones, the window never closes. Downtime becomes expensive, risky, and politically radioactive. Teams begin to adopt continuous delivery practices, and suddenly you need a safe way to evolve your data schema constantly.

The core challenge is this: your application code and your database schema evolve at different speeds. Application deployments are fast. Schema changes are not. That mismatch is what breaks systems.

Zero-downtime techniques bridge that mismatch using a pattern often called “expand and contract”. You expand the schema to support both the old and new code paths, deploy the code that uses the new structure, migrate the data behind the scenes, then contract the schema once no code depends on the old structure.

This creates breathing room. It gives you reversibility. And in a world where features ship weekly, sometimes daily, that is the difference between reliable engineering and gambling.

The Core Mechanics: What Makes a Migration Safe?

Think of a schema change as a series of small mechanical moves instead of a single big one.

A safe migration sequence has three goals:

1. Never break the old code path.

2. Allow the new code path to run simultaneously.

3. Give yourself an escape hatch if the rollout needs to stop or rollback.

To achieve this, you need to treat databases less like static infrastructure and more like APIs with version contracts. If you would never break your public HTTP API without versioning, why break your database contract?

The complexity varies by operation. Adding columns is easy. Dropping columns is dangerous. Renaming is almost always unsafe unless you simulate it through additive steps. Even index creation can cause locking and degraded performance if done incorrectly.

Before we dive into the how-to steps, here is a quick worked example.

Worked Example: Renaming a Column Without Downtime

Suppose you want to rename full_name to display_name in a user table.

Unsafe approach:

• ALTER TABLE users RENAME COLUMN full_name TO display_name;

Why it breaks:

• Old app code would fail instantly.

Safe multi-step approach:

1. Add display_name column (NULL allowed).

2. Deploy code that writes to both full_name and display_name.

3. Backfill existing rows in batches (e.g., 1,000 rows per second).

4. Deploy code that reads from display_name but still writes to both.

5. Remove writes to full_name once confident.

6. Drop full_name.

Same effect. Zero downtime.

How to Handle Zero-Downtime Schema Migrations (Step-by-Step)

1. Design Forward-Compatible Changes

Every schema migration starts with a simple question. If you applied this migration and didn’t immediately deploy new application code, would anything break? If the answer is yes, your plan is unsafe.

Forward-compatible changes include:

• Adding columns

• Adding tables

• Adding indexes concurrently

• Adding new nullable fields

• Adding new optional constraints

Forward-incompatible changes include:

• Dropping a column

• Adding NOT NULL constraints

• Renaming columns

• Changing column types

Pro Tip:
For type changes, introduce a new column with the correct type, dual-write to both, migrate data, then switch reads. This pattern works for booleans, ints, timestamps, and even JSON blobs.

2. Deploy Application Code That Handles Both Versions

This is where most teams fail. You need code that survives both the old schema and the new schema so the schema migration is not coupled to a single deployment.

The typical pattern looks like this:

• Read from the old column.

• Write to both columns (old + new).

• Do not rely on the new column yet.

• Log or monitor for read/write discrepancies.

This isolates risk. You can deploy this code before any dangerous database change occurs.

Real-world example:
At one company I worked with, we once tried to change a JSON column’s shape in a single deployment. Every request started throwing deserialization errors because the API servers restarted faster than the migration completed. Dual-writing upstream would have prevented it entirely.

3. Migrate the Data in Batches

Once the schema can support both old and new code paths, you migrate the data. Slowly.

A typical batch migration script:

• Fetch N rows that need backfilling.

• Transform.

• Write.

• Sleep for 50–250ms.

• Repeat.

This protects the database from write amplification and avoids holding locks for too long.

For large tables, you can:

• Use keyset iteration instead of OFFSET (prevents slow scans).

• Run multiple workers.

• Use tools like gh-ost or pt-online-schema-change for MySQL.

• Use logical replication or background jobs in Postgres.

Priya from Reddit emphasized that their migrations often run for days, not hours, because the safest migrations are deliberately slow.

4. Switch Reads to the New Schema

Now the new data is ready. Your application can begin reading from the new column or structure.

Flow:

• Deploy code that reads from the new column.

• Keep dual-writing until confident.

• Add monitoring on read volume and anomalies.

This step is the real “cutover,” but because old data still exists and old writes still work, the risk is tiny.

What to watch:

• Error spikes in logs.

• Unexpected nulls or missing values.

• Performance degradation from new indexes or queries.

5. Remove Old Writes, Then Remove the Old Schema

Once you are certain everything works:

• Stop writing to the old column.

• Stop reading from it.

• Delete any fallback logic.

• Drop the old column or constraint.

Dropping columns should always be the last step, sometimes deployed weeks after the initial migration.

This phase returns your system to a clean, single-version state.

Common Migration Patterns You Should Master

Schema Expansions

Safe operations:

• Add nullable columns

• Add tables

• Add indexes concurrently (Postgres: CREATE INDEX CONCURRENTLY)

Schema Contractions

Dangerous operations:

• Drop columns

• Add NOT NULL

• Add unique constraints

You can make them safe using:

• Backfills

• Pre-validations

• Partial indexes

• Validation queries before enforcing constraints

Two Short Lists (for Scannability)

When You Absolutely Need a Multi-Step Migration

• Column renames

• Type changes

• Nullable to non-nullable

• Index creation on large tables

Tools That Help With Online Migrations

• gh-ost (MySQL)

• pt-online-schema-change (MySQL)

• Liquibase

• Flyway

• Rails ActiveRecord migrations with strong_migrations

Each adds guardrails you wish you had the first time you took down production.

FAQ

Can I do online schema migrations with MySQL?

Yes, but you must use tools like gh-ost or pt-online-schema-change for large tables. Otherwise MySQL may lock tables for seconds or minutes.

Do I need feature flags?

Not strictly, but they make rollbacks dramatically easier. Many large teams treat schema switches as feature-flagged rollouts.

What about NoSQL migrations?

NoSQL systems shift the burden to your application. You often migrate lazily as documents are read or written.

Do I need blue/green deployments?

No. Blue/green helps, but expand-and-contract works even with rolling deploys.

Honest Takeaway

Zero-downtime schema migrations look intimidating until you’ve done them a few times. The truth is more mundane. It is less about tools and more about discipline. The teams that never break production are the ones that break their migrations into small, reversible steps, never let code and schema get out of sync, and never trust a schema migration that hasn’t been tested against real data sizes.

The one idea to carry with you is this: migrations are not events, they are multi-step stories. When you treat them that way, downtime stops being part of the conversation entirely.