Schema migrations are one of the few operations in a running system where a mistake can be genuinely difficult to reverse, particularly once real production data has flowed through the new schema for even a short period. A handful of practices consistently separate migrations that go smoothly from the ones that turn into extended incidents.
Never combine a schema change with a data backfill in the same migration
A common source of long-running, risky migrations is combining a structural change, adding a column, changing a type, with a data backfill that populates or transforms existing rows, in a single migration step. This couples two very different risk profiles: the structural change is usually fast and low-risk, while the backfill can be slow, resource-intensive, and prone to locking issues on large tables.
Separating these into distinct steps, add the new column as nullable first, backfill the data in a separate, throttled process, then apply any constraint that depends on the backfill being complete, breaks a single high-risk operation into several lower-risk ones, each independently verifiable and independently reversible if something goes wrong partway through.
Make every migration backward compatible with the previous application version
In any system with more than a single deployed instance, there’s inevitably a window during a deployment where old and new application code run simultaneously against the same database. A migration that isn’t compatible with the previous application version during this window causes errors for whichever instances haven’t yet received the new code, which is a surprisingly common cause of brief but disruptive production incidents during otherwise routine deployments.
The practical implication: renaming a column outright breaks backward compatibility, since old code still expects the original name. Adding a new column alongside the old one, migrating reads and writes to the new column over a series of deploys, and only removing the old column once every instance is confirmed running the new code, avoids this entire category of deployment-window failure.
Test migrations against a realistic data volume, not an empty or tiny database
A migration that runs instantly against a development database with a few hundred rows can behave completely differently against a production table with tens of millions of rows, particularly for operations that require a full table scan or an exclusive lock. Testing migrations exclusively against small datasets is one of the most common reasons a migration that looked completely safe in staging turns into an extended production lock once it runs against the real table size.
Testing against a database snapshot that approximates real production scale, or at minimum reviewing the specific operation’s locking behavior for the database engine in use at the actual table size involved, catches this category of surprise before it happens in production rather than during it.
Understand exactly what gets locked, and for how long
Different schema operations have very different locking implications depending on the specific database engine. Some operations that appear similar on the surface, adding a column with a default value versus adding one without, can have dramatically different locking behavior depending on the database version, sometimes requiring a full table rewrite under an exclusive lock in one case and completing near-instantly in another.
Checking the specific locking behavior for the exact operation, database engine, and version in use, rather than assuming based on general database knowledge that may not apply to the specific version running in production, avoids a class of migration surprises that are entirely predictable in hindsight but easy to miss without checking the specific documentation.
Have a tested rollback path before running the migration, not after
A rollback plan written after a migration has already caused a problem is being improvised under pressure, which tends to produce worse decisions than a rollback plan written and tested calmly beforehand. For any migration touching a table with meaningful production traffic, writing and testing the reverse migration, actually running it against a copy of the affected data, before running the forward migration in production, means a genuine rollback path exists rather than a hopeful assumption that one could be improvised if needed.
Some schema changes are difficult or impossible to cleanly reverse once data has flowed through them, dropping a column that gets written to during the interim window, for example. Recognizing this ahead of time, and structuring the migration to preserve reversibility for as long as reasonably possible, is a more reliable strategy than discovering the irreversibility mid-incident.
Run migrations during low-traffic windows for any operation with meaningful risk
Even a well-tested migration carries some residual uncertainty, since production conditions never fully replicate in staging. Scheduling migrations with any meaningful locking or performance risk during genuinely low-traffic periods, rather than during peak hours purely for deployment convenience, reduces both the likelihood that a migration problem gets noticed and the blast radius if one does occur.
The underlying discipline
None of these practices are exotic. Each one is a reasonably well-known best practice individually. What separates organizations that experience serious migration incidents from those that don’t usually isn’t unfamiliarity with these principles, it’s whether they’re consistently applied as a checklist for every migration touching meaningful production data, rather than reserved only for the migrations that are obviously identified as risky in advance. The migrations that cause the worst incidents are frequently the ones that looked routine enough to skip the checklist.
답글 남기기