Skip to content
← All articlesEngineering

Database Migrations Without Downtime: A Practical Guide

How to change a live database schema safely: the expand and contract pattern, backfills, rollback plans and what to ask your outsourced team to do.

Most teams get application deployments under control long before they get database changes under control. A new release can be rolled back in minutes, but a schema change that has already rewritten a table cannot. That asymmetry is why database migrations cause a disproportionate share of release-day incidents, and why they are worth planning deliberately, especially when your product has users in several time zones and there is no quiet hour to hide in.

This guide explains how product teams can change a live database without taking the application offline, and what to expect from a development partner who does this work for you.

Why migrations go wrong

Three problems account for most failures:

  • Old and new code run at the same time. During a rolling deployment, some servers run the previous version while others run the new one. Both must work against the same schema.
  • Locks block live traffic. Some operations, such as adding a column with a default on certain database versions or building an index in the normal way, can lock a table while queries queue up behind them.
  • Changes cannot be undone. Dropping a column or rewriting data destroys information. A rollback of the code does not bring it back.

Every safe-migration technique below is a way of dealing with one of these three.

The expand and contract pattern

The core idea is to split one risky change into several small, individually safe ones. Suppose you want to rename a column from fullname to display_name. A single rename breaks any running code that still uses the old name. Instead:

  1. Expand. Add the new column display_name, nullable, without touching the old one.
  2. Write to both. Deploy code that writes to both columns whenever a record is saved.
  3. Backfill. Copy existing values from the old column to the new one in small batches.
  4. Switch reads. Deploy code that reads from the new column.
  5. Contract. Once nothing uses the old column, stop writing to it, wait a safe period, then drop it in a later release.

It takes more steps than a rename, but each step can be deployed and reversed independently, and the application stays up throughout.

Safe and risky changes at a glance

The exact behaviour depends on your database engine and version, so always check its documentation and test on a copy of production data. As a general guide:

Change Usually safe? What to do
Add a nullable column Yes Deploy directly
Add a column with a default Depends on engine and version Test first; otherwise add nullable, then backfill
Add an index Risky on large tables Use your database’s online or concurrent index option
Rename a column or table No Use expand and contract
Change a column type No Add a new column, backfill, switch, drop
Drop a column or table Risky Remove all code references first, drop in a later release
Add a NOT NULL constraint Risky Backfill first, then add the constraint

Backfills deserve their own plan

Copying millions of rows in one transaction is a classic way to overload a production database. A safer backfill:

  • Runs in small batches, with a short pause between them.
  • Is idempotent, so it can be stopped and restarted without corrupting data.
  • Runs outside peak hours and watches database load, replication lag and error rates.
  • Is separated from the schema change, so the two are not released together.
  • Is verified afterwards with a count or checksum comparison, not assumed correct.

Make migrations part of the pipeline

Migrations should be code, stored in version control, reviewed like any other change and applied by your CI/CD pipeline rather than by someone typing commands into a production console. Good habits include:

  • One migration file per change, never edited after it has been applied.
  • Running every migration against a restored copy of production data in a staging environment first.
  • A recorded backup or snapshot immediately before any destructive step.
  • A written rollback plan for each release: what to revert, in what order, and who decides.
  • Reviewing migrations separately from feature code, ideally by someone who has run them on large tables before.

Pairing these habits with feature flags, covered in our feature flags guide, lets you switch behaviour on and off without redeploying, which makes the read and write switches in expand and contract much less stressful. For the deployment side of the same problem, see zero-downtime deployment strategies.

A checklist to agree with your vendor

If development is outsourced, put these expectations in the statement of work or the team’s definition of done:

  • Every schema change is delivered as a reviewed, version-controlled migration.
  • Destructive changes are always split across at least two releases.
  • Migrations are rehearsed on production-sized data before release.
  • Backups are verified, not just taken, and restores are tested periodically.
  • Release notes state which migrations are included and how to roll back.
  • Long-running backfills are monitored and can be paused safely.

When to bring in specialist help

Plan for extra care when a table holds tens of millions of rows or more, when you are changing database engines, when you must keep two systems in sync during a move, or when regulation limits how data may be altered or moved. These are projects in their own right, not tasks to squeeze into a sprint.

Getting this right with a partner

Safe migrations are mostly discipline: small steps, rehearsal, monitoring and a clear way back. Our cloud and DevOps services cover pipeline automation, environment setup and release practices that make database changes routine rather than risky. If you are planning a schema overhaul, a platform move or a stalled release process, get in touch and we will review your current approach and suggest a safe path forward.

Let’s build what’s next.

Tell us about your platform, your product idea or the systems you want to modernise. Someone from our Chennai engineering team will get back to you personally.