Designing Safe Database Schema Changes

Of all the operational risks I worry about as a CTO in regulated payments, the one that keeps recurring in postmortems is not a fancy distributed-systems failure. It is a schema change. A column renamed at the wrong moment, an index added under a lock, a migration that ran fine against a few thousand test rows and then crawled against three hundred million in production. The database is where our settlement records, ledger entries, and customer balances live, and it is the one part of the stack that cannot simply be rolled back by redeploying the previous container.

Over the years I have come to treat schema changes as a discipline in their own right, separate from ordinary feature work. The goal is not to avoid changing the database. We change it constantly. The goal is to make every change boring, reversible, and invisible to the people moving money through our platform. This post is how I think about getting there.

Why Schema Changes Are Different

Application code is ephemeral. If a deployment goes wrong, I roll back to the previous image and the bad code is gone in seconds. State, by contrast, is sticky. Once a migration has rewritten a table or dropped a column, the data that was there is genuinely altered, and the only way back is another forward change plus, in the worst case, a restore from backup. That asymmetry is the entire reason schema work deserves special care.

There is also the matter of locks. Many naive DDL statements take exclusive locks that block reads, writes, or both for the duration of the operation. On a quiet table this is harmless. On a hot table that backs the payment authorization path, a multi-second lock during peak traffic is an outage. The same statement that passed code review and ran instantly in staging can hold a lock long enough to cascade into connection-pool exhaustion across every service that touches that table.

Finally, schema and code are deployed by different mechanisms and rarely at the same instant. For a window of seconds or minutes, old application code runs against a new schema, or new code runs against an old schema. If you do not design for that overlap, you ship a bug that only exists during the deployment itself, which is precisely when nobody is looking at steady-state behaviour.

The Expand and Contract Pattern

The single most valuable idea I can offer is expand and contract, sometimes called the parallel-change pattern. Instead of mutating the schema in one destructive step, you split every breaking change into a sequence of backward-compatible steps. You expand the schema to support both the old and new shapes, migrate the code and data across, and only then contract by removing the old shape once nothing depends on it.

Consider renaming a column from amount to amount_minor to make explicit that it stores minor units. The destructive version is a single rename that instantly breaks any running code referencing the old name. The safe version is a sequence:

• Add the new amount_minor column as nullable, deploy it, change nothing in code.
• Deploy application code that writes to both columns and reads from the old one.
• Backfill amount_minor for historical rows in controlled batches.
• Deploy code that reads from the new column and writes to both.
• Stop writing the old column, then drop it in a later release once you are confident.

Each step is independently deployable and independently reversible. At no point does old code meet a schema it cannot handle. The change takes longer in wall-clock terms, often spanning several releases, but every intermediate state is safe, and that is the trade I will make every single time on systems that hold customer money.

Online Migrations and Locking Behaviour

Knowing the locking semantics of your specific database engine is not optional. In modern PostgreSQL, adding a nullable column with no default is a fast metadata-only operation, but adding a column with a volatile default historically rewrote the whole table. Creating an index with the ordinary statement locks the table against writes for the entire build, whereas the concurrent variant avoids that lock at the cost of a slower build and the need to handle failure cleanly.

Here is the difference that matters in practice on a large table:

-- Dangerous on a hot table: takes a SHARE lock,
-- blocks all writes until the index is fully built.
CREATE INDEX idx_txn_account
    ON transactions (account_id);

-- Safe: builds without blocking writes. Cannot run
-- inside a transaction, and may leave an INVALID index
-- behind if it fails, which you must detect and drop.
CREATE INDEX CONCURRENTLY idx_txn_account
    ON transactions (account_id);

The same principle applies to adding constraints. Adding a foreign key or check constraint normally validates every existing row while holding a lock. The safer route is to add the constraint marked as not valid so it applies only to new and modified rows, then validate it separately in a step that takes a far weaker lock. These details vary by engine and version, so I expect every migration touching a large table to come with a one-line note in the pull request stating what lock it takes and how long it is expected to hold.

Backfilling Large Tables Safely

The migration that adds a column is rarely the dangerous part. The dangerous part is populating it. A single update statement across a table with hundreds of millions of rows will hold locks, bloat the transaction log, generate enormous replication lag, and quite possibly time out after running for an hour. I have seen exactly this turn a routine data-cleanup into a replica falling far enough behind that read traffic served stale balances.

Backfills belong in batched, throttled jobs that run outside the migration framework, not inside it. The pattern I insist on is a loop that processes a bounded number of rows per iteration, commits each batch, and pauses briefly between iterations to let replication catch up and to leave headroom for live traffic. The job must be idempotent and resumable, because it will be interrupted at some point and you do not want to start over.

// Throttled, resumable backfill in C#.
// Processes the table in key-ordered batches so it can
// resume from the last processed id after any interruption.
long lastId = await LoadCheckpointAsync();
const int batchSize = 2_000;

while (true)
{
    var rows = await db.QueryAsync(
        @"SELECT id FROM transactions
          WHERE id > @lastId AND amount_minor IS NULL
          ORDER BY id
          LIMIT @batchSize",
        new { lastId, batchSize });

    if (!rows.Any()) break;

    await db.ExecuteAsync(
        @"UPDATE transactions
          SET amount_minor = amount * 100
          WHERE id = ANY(@ids)",
        new { ids = rows.Select(r => r.Id).ToArray() });

    lastId = rows.Max(r => r.Id);
    await SaveCheckpointAsync(lastId);
    await Task.Delay(TimeSpan.FromMilliseconds(200));
}

The delay and the small batch size look wasteful, and they are, in the sense that the job runs longer than it strictly needs to. That is the point. A backfill that finishes in two hours without anyone noticing is infinitely better than one that finishes in twenty minutes and triggers an incident.

Making Migrations Reversible by Default

Every migration should have a thought-through path backward, even when that path is not a literal automated rollback. For additive changes, reversal is trivial: a new nullable column or a new table can simply be dropped. For destructive changes, true reversal is impossible once data is gone, which is exactly why expand and contract defers the destructive step until the new path has been proven in production for days.

A migration you cannot reverse is not a migration, it is a one-way bet. I am willing to make those bets, but only deliberately, with a backup taken immediately before, and never bundled silently inside an ordinary release.

In practice this means I separate destructive operations into their own clearly labelled deployments. Dropping a column is not allowed to ride along with a feature change. It gets its own pull request, its own review, and a checklist confirming that no code path, no report, and no downstream consumer still references the thing being removed. The few minutes of extra ceremony have saved us from deleting data that some forgotten batch job quietly depended on.

Testing Against Production-Shaped Data

The most common reason a migration surprises us is that it was only ever tested against trivial data. A statement that is instantaneous against ten thousand rows behaves completely differently against three hundred million, both in execution time and in the locks it acquires. Volume changes the physics.

I want migrations rehearsed against a dataset that resembles production in size and distribution, on hardware that resembles production. We maintain a restored, anonymised copy of the production database for exactly this purpose, scrubbed of personally identifiable information but preserving row counts and value distributions. Running a candidate migration against that copy and timing it tells me far more than any amount of staging on a near-empty schema. If a migration takes nine minutes there, I know to schedule it accordingly and to confirm it uses a non-blocking approach.

This rehearsal step has caught problems that no unit test would: a missing index that turned a backfill query into a sequential scan, a default value that triggered a full table rewrite, and a constraint validation that would have locked the orders table during business hours. Each of those was cheap to fix before deployment and would have been an incident after.

Coordinating Schema and Code Deploys

Because schema and application code are deployed separately, the ordering between them is part of the design, not an afterthought. The reliable rule is that the schema must always be able to tolerate both the currently running code and the code about to be deployed. Additive schema changes go out first and stand alone; the code that uses them follows in a later deploy. Removals go out last, after the code that used the old shape is fully gone.

This has a direct implication for how migrations relate to releases. A migration that adds a column should never be in the same atomic unit as the code that requires that column to be non-null. If the code deploy fails and rolls back while the schema stays forward, you must still be in a consistent state. Designing for that means new columns start nullable, new constraints start as not enforced, and tightening happens only once both the data and the code have caught up.

In a multi-service environment the discipline extends further. If three services read a table and you are changing its shape, you cannot assume they all deploy at once. The schema change has to be compatible with the slowest-updating consumer until you have positively confirmed every one of them has moved. I treat the database as a shared contract between services, and contracts change in compatible steps, never in unilateral breaks.

The Review and Runbook Discipline

None of this works on heroics. It works because it is written down and enforced. Every migration in our codebase goes through a review that checks a specific set of questions before it is allowed near production. The reviewer is not looking for elegance; they are looking for the failure modes I have described.

• What lock does this take, and for how long on a production-sized table?
• Is it backward compatible with currently deployed code?
• If this is destructive, is the old path provably unused, and was a backup taken?
• Is any backfill batched, throttled, idempotent, and resumable?
• Has it been rehearsed against production-shaped data, with a recorded run time?
• What is the rollback or roll-forward plan if it stalls midway?

For anything touching a large or hot table we also write a short runbook: when it will run, who is watching, what metrics to monitor, and the explicit abort condition. Replication lag crossing a threshold, lock wait times climbing, or error rates rising on the affected service all mean stop. Having the abort condition decided in advance, in writing, removes the temptation to let a struggling migration run just a little longer in the hope it recovers.

Conclusion

Safe schema changes are not about clever tooling, though good tooling helps. They are about accepting that state is the part of the system you cannot casually undo, and treating every change to it with corresponding respect. Expand and contract keeps every intermediate state compatible. Online operations and throttled backfills keep the lights on while the change happens. Rehearsal against real-sized data turns surprises into known quantities, and a disciplined review with a written abort condition keeps human judgment in the loop at the moment it matters most. None of it is glamorous, and that is the highest compliment I can pay a database migration: the best ones are the ones nobody ever noticed.