A schema change carries a different kind of risk than an application code change. If the code is faulty, you can roll back to the previous version. If you have dropped a column, changed a type or split a table, going back usually means data loss or a long recovery. On top of that, modern deployments never run a single application version at one moment: during a rolling update, old and new instances share the same database for a while.
In this post I walk through the "expand, migrate, contract" approach to zero-downtime schema changes. The goal is to keep every step safe and reversible on its own.
The problem: two versions share one schema
Imagine you want to rename the Name column of a Customers table to FullName. If you rename it in a single migration, instances that have not been updated yet keep querying Name and fail. If you deploy the application first, the new code looks for a FullName column that does not exist yet.
Whichever order you choose, a window opens. The solution is to split the change into small steps in which the schema and the application are compatible at every moment. At each step, both the old and the new version must work.
Expand: add the new without breaking the old
The first phase only adds things. The new FullName column is added and allows nulls, because existing rows have no value yet and the old code never writes to it.
ALTER TABLE Customers ADD FullName nvarchar(200) NULL;
This step is invisible to the old application. One caution: on some databases, adding a column with a default value or NOT NULL can lock a large table. The behavior depends on the engine and version, so try the change first on a copy of production-like size. On large tables, adding the column empty and filling values afterwards is usually more predictable.
Adding indexes needs the same care. Many engines support online index creation, but the conditions and overhead differ. Check the documentation of your engine before building an index in production.
Dual write: the new code writes to both places
In the second phase the application is updated. The new version writes to both Name and FullName when creating and updating records. On the read side, it looks at FullName first and falls back to Name if it is empty.
public sealed class Customer
{
public string Name { get; set; } = "";
public string? FullName { get; set; }
public string DisplayName => FullName ?? Name;
public void Rename(string value)
{
Name = value; // older versions still read this column
FullName = value; // the new column
}
}
This transitional version is compatible in both directions. Old instances work with Name only; new instances keep both columns correct. In the middle of a rolling update, it does not matter which instance a request reaches: the result is consistent.
Backfill: move old data in small batches
Dual writing only covers new changes. The FullName value of existing rows has to be filled separately. Changing millions of rows with a single UPDATE means a long transaction, large locks and a bloated transaction log. Process the data in bounded batches instead.
UPDATE TOP (5000) Customers
SET FullName = Name
WHERE FullName IS NULL;
Run this command in a loop until it affects zero rows; a short pause after each batch leaves room for live traffic. If you write the job as a background task, make it restartable: because the condition is already WHERE FullName IS NULL, an interrupted job resumes where it stopped, and running it twice does no harm.
Verify when it finishes: check with queries that no rows with FullName IS NULL remain and that the two columns are equal. This check is the basis for the "go" decision on the next step.
Switch reads and tighten constraints
Once the data is complete, the version reads only FullName. On the write side, it makes sense to keep writing both columns for a while longer, because if this release causes problems, going back to the previous version must still be possible.
After an uneventful observation period, a NOT NULL constraint can be added to FullName. At this point you must be sure that no instance is left running old code that does not write this column. Otherwise the old version's insert query fails because of the constraint. That is why it matters to check the active versions in your deployment system and not to forget scheduled jobs.
Contract: remove the old column in a separate release
The final phase removes the Name column that no code uses anymore. Treat it as an irreversible step and do it separately from the main release. Before dropping the column, check: have all queries in the repository, reports, export jobs and ETL processes moved to the new column, and is there a restore plan from backup?
Some teams, instead of dropping the column immediately, first remove its usage in the application, wait one or two releases and only then drop it. The wait may look unnecessary, but a forgotten nightly report or another service's direct query usually only shows up this way.
Practical rules and the .NET side
A few rules make this easier. Keep migrations small and give each one a single intent. Do not combine adding and dropping in the same release. Plan the destructive change as the last step, independent of the application version. Measure lock duration and transaction log growth with production-like data.
If you use EF Core, review the script produced by dotnet ef migrations script --idempotent, and apply it as a controlled step in the deployment pipeline instead of running automatic migrations at application startup. If several instances try to run migrations at the same time during a rolling update, race conditions can occur. Also be aware that for a rename, the generated script may drop and re-create the column; that is data loss and has to be corrected by hand.
Finally, think about every phase in terms of rollback: the expand step is easy to undo, the dual-write version can go back to the previous release, and the backfill can be run again. Only the final contract step is permanent, so it should be the last and most careful one.
A zero-downtime schema change is less about one clever command and more about a disciplined sequence. When you split the change into expand, dual write, backfill, read switch and contract, every release is small, observable and reversible, and the database can evolve with the same confidence as the application.