Database migrations without the 2 a.m. phone call
Changing the schema of a live database is one of the few things in software that can go badly wrong in a way that is genuinely hard to undo. A botched migration can lock a table, corrupt data, or take an application down at its busiest moment. And yet schemas have to change, because the businesses they serve keep changing. Over the years we have developed a set of habits that let us make these changes calmly, without maintenance windows and without late-night emergencies.
Every change is a small, reversible step
The foundation of everything is that schema changes are versioned migrations checked into the same repository as the code, applied automatically and in order. There is no manual editing of production databases, ever. Each migration is small and does one thing, so that if something goes wrong, the blast radius is tiny and the cause is obvious. A migration that tries to do five things at once is five times harder to reason about and to recover from.
Separate the schema change from the code that needs it
The most important technique we use is to make schema changes backwards compatible, so that the old code keeps working after the migration runs. This is what lets us deploy without downtime. The trick is to break a change into stages that are each safe on their own.
Take the seemingly simple act of renaming a column. Done in one step, it breaks every running instance of the old code the moment it lands. Done safely, it becomes a sequence: add the new column, change the code to write to both columns, backfill the old data into the new column, switch reads over to the new column, and only then — once nothing references the old one — remove it. Each step is safe to deploy and safe to roll back. It is more work, but it is the difference between a routine change and an outage.
Expand, migrate, contract. Add the new thing, move everything over, and only then remove the old thing.
Beware the operations that lock
Some database operations take a lock that blocks reads or writes while they run, and on a large, busy table that can mean an effective outage. We know which operations on our databases are dangerous at scale, and we plan around them — adding indexes concurrently where the database supports it, backfilling large tables in small batches rather than one enormous statement, and avoiding changes that rewrite an entire table during peak hours. The details differ between database engines, but the principle is universal: understand what your migration will actually do to a table that has millions of rows and active traffic, not just to the empty one on your laptop.
Backfills are their own kind of migration
Populating a new column for existing rows is often the riskiest part of a change, precisely because there is so much data. We treat backfills as careful, resumable jobs that work in small batches, pause if the database comes under pressure, and can be restarted safely if interrupted. A backfill that tries to update every row in a single transaction is a classic way to exhaust resources and bring everything to a halt.
Always know how to go back
Before we run any migration against production, we ask a simple question: if this goes wrong, what do we do? For most changes the answer is a tested rollback migration. For changes that genuinely cannot be reversed — dropping a column, for instance — we make sure the data is safely backed up first and we delay the irreversible step until we are completely confident nothing depends on it. The expand-migrate-contract pattern helps enormously here, because the contract step is the only irreversible one, and by the time we reach it, everything else has already proven itself in production.
Practise on real data
A migration that runs in a second against a tiny test database can take hours against production. We test significant migrations against a realistic copy of the real data, so that we know roughly how long they will take and whether they will cause problems before we run them for real. Surprises about duration are surprises we would much rather have in rehearsal.
The calm comes from the process
None of this is dramatic. There is no clever trick that makes database changes safe; there is just a set of unglamorous habits applied consistently. Small steps, backwards compatibility, awareness of locks, careful backfills, a known way back, and rehearsal on real data. Put together, they turn schema changes from the thing engineers dread into something we can do on an ordinary afternoon, with the system fully live and nobody reaching for the phone.
Written by the Arcwell engineering team. If you're wrestling with something similar, we're happy to compare notes.