Database Migrations Without Drama: Expand/Contract in Practice

If you’ve ever kicked off a quick ALTER TABLE on a live database and then watched your app freeze while everyone pings you on Slack, you already understand why “database migrations” deserve more respect than a one-liner in the deploy script.

Modern applications ship continuously. That means your schema needs to evolve while the app is running and users are clicking. The expand/contract pattern is one of the most reliable ways to do that: you change the database in small, safe steps that are compatible with both the old and new versions of your code.

This article walks through the core ideas behind safe database migrations, how expand/contract works, and the common pitfalls to avoid.

Why database migrations are tricky

Database changes look deceptively simple on paper: add a column, rename a field, tweak a constraint. In production, those same changes can lock tables, break running code, and turn a normal release into an outage. The challenge is that schema, data, and application code are tightly coupled, and all three are evolving at the same time.

Changing code is easy: you build a new version, deploy it, roll back if something’s wrong.

Changing the database is harder:

• Data is stateful – you can’t just rebuild it from scratch (usually).
• Schema changes can lock tables – causing slow queries or outages.
• Code and schema must agree – old code reading from a new schema (or vice versa) can explode in subtle ways.
• Rolling back is painful – dropping a column is easy; getting it back is not.

Because of this, we want migrations that:

1. Are backwards compatible for at least one deployment.
2. Can be rolled forward (fix forward) if things go wrong.
3. Avoid long-running locks and heavy blocking operations.

That’s where expand/contract comes in.

The expand/contract pattern in a nutshell

The expand/contract pattern is a way of turning one big, risky schema change into a sequence of smaller, safer steps. Instead of trying to jump straight from “old world” to “new world” in a single migration, you briefly live in a middle state where both the old and new schemas work side by side. This compatibility window is what allows you to do zero-downtime deploys and rolling updates without breaking anyone.

At a high level, expand/contract has two broad phases:

1. Expand phase – add new structures without breaking any existing code.
   • Add new columns, tables, or indexes.
   • Start writing to both old and new structures (if needed).
   • Keep old code and new code working at the same time.
2. Contract phase – remove old structures once nobody needs them.
   • Stop reading/writing the old column or table.
   • Drop deprecated columns, constraints, indexes.
   • Clean up feature flags and migration code.

Between those two phases, you have a compatibility window where both the old and new versions of your application can run against the same database. That window is your safety net.

A simple example: renaming a column

Renaming a column is one of the most common schema changes, and it’s a great example of how expand/contract works in practice. The unsafe version is a single ALTER TABLE that changes the column name and hopes that no running code notices. The safe version stretches the change over several deploys so the application always sees what it expects.

Let’s say you have a users table with a column fullname, and you want to split it into first_name and last_name.

Doing this in one shot is tempting:

But that’s risky if any running code still expects fullname. Instead, use expand/contract.

Step 1: Expand

In the expand phase, you add everything you need for the new design while keeping the old design intact. The goal is to make the schema strictly more capable so that both old and new code can live with it.

1. Add new columns (safe, additive change):
   
   ALTER TABLE users ADD COLUMN first_name text, ADD COLUMN last_name text;

   1 2 3 4

   ALTER TABLE users   ADD COLUMN first_name text,   ADD COLUMN last_name text;

2. Deploy application version A that:
   • Still reads fullname for now.
   • When it writes a user, it:
     • Saves fullname.
     • Also populates first_name and last_name (maybe by splitting the string).
   • Optionally, a background job starts backfilling first_name and last_name for existing rows.

At this point:

• Old code still works (it only uses fullname).
• New code works (it uses both, but still supports fullname).
• Data is being duplicated into the new columns.

Step 2: Migrate data

Next, you need to make sure existing records are ready for the new world. Rather than trying to modify every row during a deploy, you treat data migration as its own step and run it in a controlled way, usually in the background.

If you haven’t already, run a background job or migration script to backfill all rows:

Do this in batches if the table is large to avoid long locks.

Step 3: Switch reads (deploy new code)

Once the data is ready, you can start using the new shape from the application. This is where the feature or schema change becomes visible to the rest of the codebase.

Deploy application version B that:

• Reads from first_name and last_name.
• Writes to first_name and last_name.
• Still writes fullname for now (to keep old code from breaking if you roll back quickly).

Now the application’s logic is using the new schema, but the old column is still being maintained.

Step 4: Contract (cleanup)

The final step is cleanup. Once you’re confident that the new schema is stable and no one relies on the old column, you can safely remove the extra compatibility layer.

Once you’re sure that:

• All app instances are on version B or newer,
• No other systems depend on fullname,
• Data is fully backfilled,

you can:

1. Stop writing fullname (deploy version C if needed).
2. Drop the old column:
   
   ALTER TABLE users DROP COLUMN fullname;

   1 2	ALTER TABLE users DROP COLUMN fullname;

That’s your contract phase: safely removing the legacy schema after you’ve proven nobody needs it.

Another example: splitting a table

Larger schema changes follow the same rhythm, even if they look more complicated on the surface. Consider an application that starts with a single orders table that includes both order metadata and payment info. Over time, you realize that payment data has different lifecycle and security requirements, so you want to normalize it into its own payments table.

A naïve one-shot migration could cause serious downtime and foreign key issues. Expand/contract tackles it in phases:

1. Expand
   • Create payments table.
   • Add an optional payment_id foreign key to orders or otherwise relate the two.
   • Start writing new payments to both structures (or, better, only to payments and adapt your code).
2. Dual write / backfill
   • Backfill payments based on existing orders.
   • For a while, write both to orders.payment_* columns and the new payments row.
3. Switch reads
   • Change the application to read payment data from payments.
   • Keep writing any redundant data needed for rollback compatibility.
4. Contract
   • Remove payment columns from orders once you’re sure everything is stable.
   • Remove any dual-write logic.

Same pattern, different shape.

Coordinating code and schema changes

Expand/contract works because you decouple the schema change from the code change. Instead of “one deploy that changes everything,” you do several small deploys, each one moving you a little closer to the desired end state while remaining compatible with what’s already running.

In practical terms, this means being intentional about the order in which you ship things:

• Never deploy code that requires a column/table that doesn’t exist yet.
  • The schema must always be at least as capable as the most recent code.
• Avoid breaking changes like:
  • Renaming/dropping columns that existing code still uses.
  • Tightening constraints too early.
  • Changing data types incompatibly in one step.
• Use feature flags:
  • Gate new behavior behind a flag so you can switch traffic gradually.
  • If you need to roll back, flip the flag instead of doing another migration first.

When code and schema changes are planned together, the database stops being a mysterious external dependency and becomes a first-class part of your deployment story.

Handling data backfills safely

Backfills are where big tables can hurt you. A careless UPDATE on millions of rows can lock the table, thrash the I/O subsystem, and generally ruin your day. Treating data migration as an operational concern, not just a developer convenience, makes the difference between a smooth rollout and middle-of-the-night firefighting.

Better approaches include:

• Batch updates:
  • Update rows in small chunks (e.g., by primary key range or using LIMIT).
  • Add a small delay between batches to reduce load.
• Idempotent jobs:
  • Design your data migration job so it can be resumed safely if interrupted.
  • Record progress (e.g., last processed ID).
• Off-peak execution:
  • Run heavy operations during times of low traffic if you can.
• Indexes first (carefully):
  • Big indexes can also take time to build.
  • Some databases support “concurrently” or “online” index creation to reduce blocking.

Rollbacks and “roll forward”

One harsh reality of database migrations is that rollback is often not symmetrical. With code you can usually redeploy the previous version and call it a day. With schema and data, destructive changes are much harder to reverse, and sometimes impossible without restoring from backup.

Instead of relying on “undo” migrations, many teams adopt a “roll forward” mentality:

• If a migration goes wrong, you write another migration to fix it.
• You keep the schema change history as a strictly forward-moving timeline.

To make this safe:

• Avoid destructive changes until the very end (the contract phase).
• Keep backups and point-in-time recovery available as the last resort.
• Test migrations against a copy of production data whenever possible.

Tooling: migrations as code

Hand-crafted SQL files work, but tooling helps keep things consistent and repeatable. Popular migration tools treat schema changes as versioned artifacts checked into source control, so you can apply the same sequence of changes to dev, staging, and production.

Typical migration tools give you:

• Versioned migrations – each change has an ID and is applied in order.
• Checksums – detect drift between what the code expects and what the DB has.
• Environment support – the same migrations can be run across multiple databases.

Examples include SQL-based tools (Flyway, Liquibase) and ORM-integrated tools (Rails migrations, Django migrations, Laravel migrations, Alembic, Prisma, etc.). Regardless of tool:

• Treat migrations as code (reviewed, tested, and deployed).
• Keep them small and focused.
• Use the tool to orchestrate expand/contract steps, not to bypass them.

Common anti-patterns and how to avoid them

Once you start looking for them, you see the same migration mistakes over and over again. Knowing the common anti-patterns makes it easier to spot trouble early and nudge your team toward safer patterns.

1. Big bang schema changes

“We’ll just run this giant script over lunch.”

Problems:

• Long-running locks.
• Hard to roll back if something goes wrong halfway.
• Huge blast radius.

Better:

• Break changes into small expand/contract steps.
• Backfill in batches.
• Keep each migration reasonably quick.

2. Application unaware of schema changes

“Ops changed the DB, devs will figure it out.”

Problems:

• Code and schema drift.
• Random runtime errors when incompatible.

Better:

• Pair schema migrations with corresponding app changes.
• Make the migration plan part of the feature design, not an afterthought.

3. Destructive changes first

“We’re not using that column, right? Just drop it.”

Problems:

• Hidden dependencies (old scripts, integrations) break.
• Hard to recover if you were wrong.

Better:

• First stop reading/writing it in the app.
• Monitor for any remaining usage (queries, logs).
• Drop only after a safe window has passed.

4. No staging rehearsal

“It worked on my laptop…”

Problems:

• Prod data volume and shape are very different.
• Edge cases appear under real load.

Better:

• Rehearse on a staging environment with a realistic dataset if at all possible.
• Measure how long migrations take.
• Adjust the plan if things are slower than expected.

A sensible workflow for safe migrations

Putting it all together, a practical workflow for safe database migrations looks like a small pipeline. Each step moves you closer to the new schema while preserving the ability to serve traffic and recover from mistakes.

A typical workflow:

1. Design the change
   • Decide how to model it with expand/contract.
   • Identify backfills, dual-writes, and cleanup steps.
2. Implement the expand phase
   • Add new columns/tables/indexes.
   • Deploy schema and app changes that start populating the new structures.
3. Migrate the data
   • Backfill in batches.
   • Keep new data in sync (dual-write if needed).
4. Switch the application
   • Change reads to use the new structures.
   • Keep data paths compatible with rollback for a short period.
5. Contract
   • Remove old columns/tables.
   • Drop extra indexes or constraints no longer needed.
   • Clean up feature flags and compatibility code.
6. Document
   • Record what changed and why.
   • Note any special operational considerations for future maintainers.

Follow that pattern and database migrations stop being terrifying one-off events and become just another part of your regular deployment flow.