If your product has real users, “just run the migration” stops being good enough the moment a schema change can lock a table, drop a column a deploy still depends on, or leave a background job writing to a field that no longer exists. This guide is for founders, CTOs and engineering leads who need to ship database changes without a maintenance window, a 2am deploy, or a support queue full of errors. We’ll walk through the patterns, tooling and rollback discipline that make zero-downtime database migrations routine rather than terrifying.
Why ordinary migrations break production
Most migration incidents aren’t caused by bad SQL. They’re caused by a mismatch between when the schema changes and when the application code that depends on it finishes deploying. A migration that renames a column, drops a table, or adds a `NOT NULL` constraint without a default will work perfectly in a test database and then take production down, because for a few seconds (or a few minutes, if your deploy is slow) old application code is still reading or writing against the old shape.
There’s a second, quieter cause: locking. Some schema changes — adding a column with a default value on older MySQL versions, rewriting a table, adding certain constraints — take a lock that queues every other query behind it. On a small table that’s invisible. On a busy table with millions of rows, it can stall your entire application for the duration of the change.
Zero-downtime migration isn’t a single trick. It’s a habit of making changes additive, reversible and decoupled from deploys, so that schema and code can move independently.
The expand/contract pattern for zero-downtime database migrations
The core technique almost every reliable migration strategy comes back to is expand and contract (sometimes called parallel change). It breaks a schema change into stages that are each individually safe:
- Expand — add the new column, table or index alongside the old one. Nothing that currently works stops working.
- Migrate — update application code to write to both the old and new structure, then backfill historical data in batches. Once writes are dual-written and backfilled, switch reads over to the new structure.
- Contract — once you’ve confirmed every code path uses the new structure (and ideally after a full deploy cycle with no regressions), remove the old column or table in a separate, later migration.
The point is that each of those three steps can be deployed, verified and rolled back independently. If step two reveals a problem, you haven’t touched anything destructive yet. This is the same discipline that underpins good database schema design that survives growth — most of the pain of a “big” migration later comes from schema decisions made without this kind of flexibility built in from day one.
A simple worked example
Renaming a column is the clearest case. Instead of one migration that renames `user.name` to `user.full_name`, you run three:
- Add `full_name`, nullable, no default. Deploy.
- Deploy application code that writes both columns on every update, and backfill `full_name` from `name` for existing rows in small batches (so you’re not holding a lock or a long transaction across the whole table). Deploy. Verify. Switch reads to `full_name`.
- Once nothing reads or writes `name`, drop it in its own migration, in its own deploy, days or weeks later.
Nobody notices. There’s no window where half your fleet expects one shape and half expects another.
Picking the right tool for the lock profile
What’s genuinely blocking depends heavily on your database engine, so it’s worth knowing the baseline behaviour before reaching for extra tooling.
PostgreSQL handles more of this natively than people expect. Adding a nullable column, or a column with a constant default, is a fast metadata-only change on modern Postgres versions — it doesn’t rewrite the table. Adding an index is the main risk: a plain `CREATE INDEX` takes a lock that blocks writes for the duration. `CREATE INDEX CONCURRENTLY` avoids that lock entirely, at the cost of scanning the table twice and taking roughly two to three times longer — a trade worth making on any table your application actually depends on. For heavier rewrites (changing a column type, for example), tools like `pg-osc` build a shadow table, backfill it, and swap names in, keeping the primary table writable throughout.
MySQL/MariaDB relies more on external tooling because its online DDL support is narrower. `gh-ost` and `pt-online-schema-change` are the two the community reaches for: both create a shadow table and copy data across in the background, but `pt-online-schema-change` uses triggers to keep the two tables in sync while `gh-ost` reads the binary log instead, which avoids the overhead and risk of triggers on a hot table.
Whichever engine you’re on, the rule is the same: check whether a given change takes a blocking lock before you run it against production, not after.
Decoupling the migration from the deploy
Two further habits remove most of the remaining risk:
- Run migrations before the new code deploys, not during it. If your migration tooling runs as part of your deploy pipeline, make the ordering explicit: schema changes land first, and they must be backward-compatible with the code that’s still running. This is exactly what expand/contract guarantees — the old code keeps working against the expanded schema until it’s replaced.
- Use feature flags to gate the cutover, not the deploy. Ship the code that can read from the new structure behind a flag, verify it against production traffic for a sample of users or requests, then flip the flag once you’re confident — completely decoupled from any deploy. If you’re not already doing this for migrations, it’s worth reading up on feature flags best practices for startups, since the same infrastructure covers both.
Good CI/CD practice for startups matters here too — migrations should run in staging against a realistic data volume before they ever touch production, specifically to catch locking and performance surprises that only show up at scale.
Always have a rollback plan that isn’t “restore from backup”
A backup restore is a last resort, not a rollback plan — it costs you data since the backup, and it costs you the time to restore a potentially large database. A real rollback plan means:
- Every migration is backward-compatible with the previous version of the application code, so reverting the deploy doesn’t require reverting the schema.
- Destructive migrations (drops, non-nullable constraints, renames) only run in the “contract” phase, once you’re certain nothing depends on the old shape — and ideally days after the “migrate” phase shipped cleanly.
- Batched backfills are idempotent and resumable, so a failed run halfway through doesn’t leave data in an inconsistent state.
If a migration can’t be made backward-compatible — occasionally true for deep structural changes — treat it as a special case: schedule it, communicate it, and consider a brief, deliberate maintenance window rather than pretending it’s zero-downtime when it isn’t. Honesty about the one migration in twenty that genuinely needs downtime is what keeps the other nineteen boring.
Where this fits in your roadmap
None of this needs to be built in week one. Early on, a short maintenance window for a schema change is a reasonable trade against the cost of building expand/contract tooling before you have real users to protect. The point at which it’s worth investing is when a bad migration would actually hurt — when you have paying customers, a support team, or background jobs that can’t tolerate a few minutes of inconsistency. If you’re not sure which side of that line you’re on, or you’re carrying technical debt that makes every migration feel risky, it’s worth getting a second opinion before your next big schema change. We cover related groundwork in our code audit guide for startup teams and in our thinking on prioritising technical debt without stalling the roadmap.
If you’d like a second pair of eyes on an upcoming migration, or want this built into your deployment process from the start, book a free consultation and we’ll talk through your specific schema and traffic profile.
FAQ
Do I need special tooling to do zero-downtime migrations, or can I do this with standard frameworks?
Most of this is achievable with standard migration tooling (Rails, Django, Prisma, Flyway, Liquibase, and so on) as long as you follow the expand/contract pattern. Specialist tools like gh-ost or pg-osc matter most for large tables on engines with limited online DDL support, or for heavier rewrites that standard tooling would otherwise lock.
How do I know if a migration will lock my table?
Test it against a copy of production-sized data in staging first, and check your database engine’s documentation for which operations are “online” (metadata-only) versus which rewrite the table. On Postgres, adding a nullable column or constant default is cheap; adding a plain index is not — use `CREATE INDEX CONCURRENTLY` instead.
What’s the difference between a migration rollback and a backup restore?
A migration rollback reverts a specific schema change, usually by running its reverse (dropping a column you just added, for example), and should be possible in seconds with no data loss for anything added since. A backup restore reverts your entire database to an earlier point in time, losing any data written since that backup — it should be a last resort, not your primary plan.
Can background jobs and cron tasks break during a migration even if the web app doesn’t?
Yes, and it’s a common gap. Any process reading or writing the database — queue workers, scheduled jobs, data exports — needs to be backward-compatible during the same migrate phase as your main application, or scheduled to pause and resume around the cutover.
Related: Disaster Recovery Plan for Startups: A Backup Guide — before you run a migration, make sure you can actually restore if it goes wrong.
