Database migrations without downtime sound like a solved problem until you are the one running one against a table with millions of rows on a Tuesday afternoon while customers are actively using the product. I have caused an outage this way. It was years ago, it lasted about four minutes, and I still think about it more than most bugs I have shipped since.
The good news is that avoiding it is not really about fancy tooling. It is about a handful of habits that, once you have them, become completely automatic. This is the process I actually use across the SaaS products I run, including CampSuite, and it has kept me out of trouble for a long time.
Why migrations actually break production
Almost every migration horror story comes down to one of three things. A schema change locks a large table for longer than anyone expected. New code deploys before the schema change it depends on has finished. Or a backfill script runs as one enormous transaction that either locks rows for ages or blows up halfway through and leaves the data in a mess.
None of these are exotic problems. They are all avoidable with a bit of discipline. The trick is treating your schema and your application code as two separate deployments that have to remain compatible with each other at every point in between, not as one atomic change.
The expand and contract pattern
The single most useful idea in this entire topic is expand and contract. Instead of changing a column in place, you add the new thing alongside the old thing, migrate everything over while both exist, and only remove the old thing once nothing depends on it anymore. It takes more steps than a straight change but every single step is safe on its own.
Expand: add without removing
Say you are renaming a column. You do not rename it. You add a new column, and you write to both the old and new column from the application for a while. This deploy is safe because nothing existing breaks. Old code still works, because the old column is still there and still being written to.
Backfill in small batches
Once the new column exists, you backfill historic rows. Never do this as one giant update statement against a large table. Batch it, a few thousand rows at a time, with a short pause between batches. This keeps individual lock durations short and lets other queries get a look in between batches rather than queuing up behind one enormous transaction.
Contract: remove only when nothing needs the old thing
Once every write path uses the new column and the backfill is complete, you stop writing to the old column, and only after that do you actually drop it. By the time you remove anything, you already know nothing depends on it because you have been running with both in place for a while. This is the whole trick. You never destroy something that something else is still relying on.
Rules of thumb for backwards compatible changes
Adding a new nullable column is almost always safe on its own. Adding a new table is almost always safe on its own. Renaming or dropping anything is never safe on its own, it needs the expand and contract treatment. Changing a column type is often the riskiest of the lot, because on some databases it forces a full table rewrite, so treat it the same way you would treat a rename.
Adding a NOT NULL constraint to an existing column is a classic trap. If you add it before every row has a value, the migration fails outright. If you add it as part of the same deploy that starts writing the new required field, older application instances that have not redeployed yet will start failing inserts. Backfill first, deploy the code that always populates the field, then add the constraint once you are certain, not before.
Locking, and why big tables bite you specifically
Most schema changes on a small table are instant and nobody notices. The pain only shows up on tables with millions of rows, because certain operations need to hold a lock for the whole duration of the change rather than a moment. Adding an index without the online or concurrent option on most databases is a common culprit, it can lock writes to that table for as long as the index takes to build. On Postgres, use CREATE INDEX CONCURRENTLY. On SQL Server, use WITH (ONLINE = ON) where your edition supports it. These exist specifically for this problem, use them.
I wrote about picking the database in the first place in how to choose a database for your SaaS startup, and the migration story is one of the reasons that decision matters more than founders tend to assume up front.
Deploy order matters as much as the migration itself
A migration is not a single event, it is a sequence, and the order you do things in is what keeps you safe. Run the schema change first, before the code that depends on it, so the database is always ahead of the application rather than behind it. Never ship application code and a breaking schema change in the same release if you can avoid it. Separate them, even if that means two releases in the same afternoon.
This matters even more once you are running multiple application instances behind a load balancer, because during a rolling deploy old and new code run at the same time for several minutes. If the schema is not compatible with both versions at once, some proportion of your requests will fail during that window regardless of how careful your code is.
Always test the migration against a real copy of production data
Migrations that run instantly against your local development database with a few hundred rows can take twenty minutes against production with ten million rows, and that difference is exactly where outages come from. Before running anything serious against production, run it against a recent copy of the production database, ideally one restored from a proper backup rather than a stale sample. It tells you how long it will actually take and whether it locks anything you were not expecting.
This is also where a proper backup and restore process earns its keep well beyond disaster scenarios. I have written before about what a sensible disaster recovery setup actually needs to look like for a small SaaS team, and a restorable, reasonably fresh copy of production is the backbone of both that plan and this one.
What I actually use day to day
In practice this comes down to a short checklist I run through automatically now rather than a big process document nobody reads. Every migration goes through source control and gets reviewed like any other code change. Every migration is reversible, or I have explicitly decided it is not and know exactly why. Large backfills are batched and rate limited rather than run as one transaction. Anything touching an index or column type on a large table gets tested against a production sized copy first. Schema changes always ship ahead of the application code that depends on them, never alongside it.
None of this is clever. It is mostly patience and sequencing, done consistently enough that it stops feeling like a special occasion every time you need to change a schema. That is genuinely the goal. A migration should be boring. If it is exciting, something has already gone wrong before you have even run it.
If you are building out this kind of process for the first time and want a second pair of eyes on your architecture, this is exactly the sort of thing I help clients with through software development consulting.
