Zero-Downtime Database Migration Interview Questions

Q1 What does zero downtime actually require during a rolling deployment, and why do naive migrations break it?#

Short answer: During a rolling deployment, old and new versions of your application run side by side against the same database, so every schema change must work correctly with both versions at once, not just with the version you are about to ship. A naive migration — a straight rename, a required column with no default, a table rewrite — breaks that rule because it assumes the whole fleet flips atomically, which a rolling deployment never does.

Concretely, a RenameColumn from Name to DisplayName is fine for the new application version, but any pod still running the old version selects and inserts Name, which no longer exists, and starts failing immediately. A required column with no default breaks inserts from old-version pods the instant the migration runs, well before the new code that populates it has deployed anywhere. The fix is not a clever migration tool; it is a discipline: split every breaking change into additive steps that are each safe with two application versions in flight, and only remove the old shape once you are certain nothing older is still running. That discipline is what the rest of these questions build out in practice, and it applies equally to relational schemas and to document stores — the same two-versions-at-once constraint is why a Cosmos DB partition key migration also needs a staged, dual-write rollout rather than an in-place change.

What interviewers look for: stating the two-versions-at-once constraint explicitly, not just "migrate carefully"; connecting specific migration types (rename, required column, type change) to specific failure modes; recognizing this is a deployment topology problem, not only a database problem.

Common mistakes: assuming a migration is safe because it works in a single-instance staging environment, where only one application version is ever running at a time; treating "the migration succeeded" as equivalent to "the deployment was safe."

Q2 Walk through the expand-contract pattern for renaming a column that's actively used in production.#

Short answer: Split the rename into four releases: expand by adding the new column alongside the old one and dual-writing both, backfill existing rows in the background, switch reads and writes to the new column while keeping the old one as a compatibility shim, and only then contract by dropping the old column — each step shippable and rollback-safe on its own.

// Release 1 (expand): purely additive
public partial class ExpandCustomerDisplayName : Migration
{
    protected override void Up(MigrationBuilder mb) =>
        mb.AddColumn<string>("DisplayName", "Customers", maxLength: 200, nullable: true);
}

// Release 1 application code: write both, read the new column with a fallback
customer.Name = input.DisplayName;
customer.DisplayName = input.DisplayName;
var name = customer.DisplayName ?? customer.Name;

Release 3 removes Name from the C# entity but must keep the column around as a nullable shadow property until release 4, or the next scaffolded migration drops it immediately and breaks any release-1 instance still draining traffic. Only release 4's migration issues DropColumn, once you are certain nothing older is deployed anywhere, including canaries and long-lived background workers. This is the same shape you would use to split a column, move data to a new table, or change a column's type — expand, backfill, switch, contract, each a separate, individually safe release.

What interviewers look for: the full four-step sequence, not just "add a column then rename later"; explicit handling of the awkward middle release where the CLR property is gone but the column must stay; awareness that each step is independently rollback-safe.

Common mistakes: collapsing expand and contract into two releases instead of four, which reintroduces the two-versions-at-once problem during the switch; forgetting that a shadow property is needed to prevent EF Core from scaffolding a premature DropColumn.

Follow-up questions:

• How long do you wait between the switch release and the contract release, and what decides that?
• What happens if release 2's backfill job is still running when release 3 ships?

Q3 How do you add a required (NOT NULL) column to a large, actively written table without downtime?#

Short answer: Add it nullable first, backfill existing rows in batches, deploy application code that always populates it going forward, and only then tighten the constraint to NOT NULL — never add a required column directly on a table that is already receiving writes from a running application version that does not know about it.

// Step 1: nullable, safe immediately — old code ignores it, new code starts writing it
migrationBuilder.AddColumn<string>("Sku", "Products", maxLength: 64, nullable: true);

// Step 3, after backfill and once every deployed version writes it: tighten
migrationBuilder.AlterColumn<string>("Sku", "Products", maxLength: 64, nullable: false);

The tightening step itself is not free on a large table, because most engines validate every existing row against the new constraint. PostgreSQL can skip that full-table scan if a validated CHECK (col IS NOT NULL) constraint already exists, added the same NOT VALID / VALIDATE CONSTRAINT way as a foreign key. Schedule the tightening step for a quiet traffic period regardless, and confirm the backfill is complete with a count query first — an ALTER COLUMN ... NOT NULL that hits even one null row fails outright and, depending on the engine, can hold a lock for the duration of the scan it attempted.

What interviewers look for: the nullable-then-backfill-then-tighten sequence as a reflex; awareness that the final tightening step still has a cost on a large table and needs its own care; knowledge of at least one engine-specific trick (the validated check constraint) for avoiding a full scan.

Common mistakes: adding the column as required with a default value and assuming that solves everything — it unblocks inserts but still means every historical row got the same default, which is often the wrong data; running the tightening ALTER without first confirming the backfill actually completed.

Q4 How do you build an index on a large table without blocking writes?#

Short answer: Use the engine's non-blocking index build — CREATE INDEX ... WITH (ONLINE = ON) on SQL Server editions that support it, or CREATE INDEX CONCURRENTLY on PostgreSQL — instead of a default index build, which takes a lock that blocks writes (and on some engines, reads) for the build's entire duration.

-- SQL Server: requires an edition that supports online index operations
CREATE INDEX IX_Products_Sku ON catalog.Products (Sku) WITH (ONLINE = ON);

-- PostgreSQL: builds without holding a table lock for the duration
CREATE INDEX CONCURRENTLY IX_Products_Sku ON catalog.Products (Sku);

In EF Core migrations, express the same intent in the model rather than raw SQL where the provider supports it: IsCreatedOnline() on the SQL Server provider, IsCreatedConcurrently() on the Npgsql provider. Both non-blocking builds trade something for the lack of blocking: they take longer overall, and a concurrent PostgreSQL build that fails leaves behind an invalid index that must be dropped and retried, so treat the migration step as one that needs monitoring, not a fire-and-forget operation. A build that must run outside the migration's normal transaction also needs suppressTransaction: true in EF Core, since most online or concurrent index operations cannot run inside one.

What interviewers look for: naming the specific non-blocking syntax for at least one major engine; the trade-off (slower, needs monitoring, PostgreSQL can leave an invalid index on failure) instead of treating online builds as strictly free; the suppressTransaction detail for EF Core specifically.

Common mistakes: assuming ONLINE = ON is available on every SQL Server edition without checking; forgetting to detect and clean up an invalid index after a failed concurrent PostgreSQL build.

Q5 How would you safely backfill millions of rows of existing data as part of a migration?#

Short answer: Move the backfill out of the migration itself and into a separate, batched, idempotent background job — never a single large UPDATE inside a migration's transaction, which holds locks for the whole run, bloats the transaction log, and cannot be paused or resumed.

const int batchSize = 5_000;
int updated;
do
{
    updated = await db.Database.ExecuteSqlAsync($"""
        UPDATE TOP ({batchSize}) Customers
        SET DisplayName = Name
        WHERE DisplayName IS NULL AND Name IS NOT NULL
        """, ct);

    await Task.Delay(TimeSpan.FromMilliseconds(200), ct); // leave headroom for live traffic
}
while (updated > 0);

Each batch commits on its own, so any single lock is short-lived, and the WHERE clause makes the whole job safe to stop and restart at any point, which matters because large backfills routinely get interrupted by deploys or incidents. Run it as a hosted background service or a one-off job, throttle it so it does not starve foreground traffic of database capacity, log progress so you can estimate completion, and verify with a count query before you depend on the backfill being done — for instance, before the "switch" release of an expand-contract sequence assumes every row already has the new column populated.

What interviewers look for: batching with a bounded size, an idempotent WHERE clause, and throttling, named specifically rather than "just batch it"; recognizing that migrations and backfills are different kinds of code with different durability requirements.

Common mistakes: writing a backfill that is not idempotent, so re-running it after an interruption double-processes or corrupts already-migrated rows; running the backfill at full speed with no delay and starving production traffic of connections or I/O.

Q6 How do feature flags interact with a zero-downtime migration strategy?#

Short answer: Feature flags decouple deploying code from activating behavior, which lets you ship the application code for a schema change — the dual-write logic, the new read path — well before you flip traffic onto it, and roll the behavior back instantly by toggling the flag instead of rolling back a deployment or a migration.

In an expand-contract sequence, the riskiest moment is the switch release, where reads and writes move from the old shape to the new one. Gating that switch behind a flag means you can deploy the switch code dark, enable it for a small percentage of traffic or a single tenant, watch error rates and data correctness, and turn it off in seconds if something looks wrong — all without a redeploy, a rollback migration, or coordinating with the database at all. It also decouples the migration's timeline from the release's timeline: the schema can be fully expanded and backfilled well ahead of the flag flip, so the flip itself touches no infrastructure, only application configuration.

The discipline this requires is keeping both code paths correct and tested for as long as the flag exists, and removing the flag and the old path promptly once the new path is proven, rather than letting it become permanent conditional logic that nobody remembers the reason for.

What interviewers look for: the specific mechanism — decoupling activation from deployment, instant toggle instead of rollback — rather than a vague "flags make it safer"; awareness that both code paths must stay correct while the flag exists; a plan to retire the flag.

Common mistakes: using a flag to gate the migration itself (migrations are not naturally toggleable at runtime) rather than the application behavior that depends on the migrated schema; leaving stale flags in the codebase indefinitely, which accumulates untested code paths.

Q7 What's your rollback plan when a migration causes a production incident?#

Short answer: Prefer rolling the application back over rolling the schema back — with expand-contract, the previous application version already works against the expanded schema, so reverting code is instant and safe — and reserve schema rollback (running Down methods or a bundle targeting an earlier migration) for cases where the schema change itself, not just the application code, is the problem.

Rolling the database schema backward is riskier than rolling it forward, because a Down method can lose data written since the migration ran, and not every change is meaningfully reversible — a dropped column's Down can recreate the column but not the data it held. For an irreversible step, the honest answer is to make Down throw and document that recovery means restoring from a backup or a point-in-time restore, not pretending a Down method fixes it. Because of that asymmetry, the practical rollback plan is usually: first, revert the application to the previous version if the schema still supports it; second, if the schema itself must change, roll forward with a corrective migration rather than backward, since forward fixes do not risk losing writes made in between; and only reach for Down, a bundle targeted at an earlier migration, or a restore when neither of those is enough. Take a backup or confirm a tested point-in-time restore path before any migration that can destroy data, particularly contract-phase drops.

What interviewers look for: the preference for rolling the app back before the schema; understanding why Down is not always safe or even meaningful; a concrete answer for irreversible steps instead of assuming every migration can be undone.

Common mistakes: treating every migration as trivially reversible via Down; rolling the schema backward as the first response to an incident when rolling the application back would have been faster and safer.

Q8 Should EF Core migrations run automatically at application startup? How do you run them from CI/CD instead?#

Short answer: No, not for production systems that need availability and least privilege — calling Database.MigrateAsync() in Program.cs gives the application identity schema-altering permissions, skips human review of the SQL, and turns a slow or failing migration into an outage across every replica at once. Generate a migration bundle or idempotent script in CI and apply it as a separate, gated pipeline step, using a distinct, schema-privileged identity, before the application rollout begins.

migrate:
  needs: build
  environment: production   # approval gate lives here
  steps:
    - run: chmod +x efbundle && ./efbundle --connection "$MIGRATOR_CONNECTION"
      env:
        MIGRATOR_CONNECTION: ${{ secrets.DB_MIGRATOR_CONNECTION }}

deploy-app:
  needs: migrate
  steps:
    - run: echo "roll out the application only after the schema is ready"

A migration bundle (dotnet ef migrations bundle) is a self-contained executable that applies pending migrations exactly as database update would, using EF Core's own migration lock so two processes cannot apply concurrently, without needing the SDK, the EF tools or your source on the deployment agent. Pair it with an idempotent script (dotnet ef migrations script --idempotent) generated in the same build when a human, such as a DBA, needs to read the SQL before it runs. On Kubernetes, run the bundle as a Job or a pre-upgrade hook rather than from every pod's entrypoint, and see CI/CD for .NET with GitHub Actions and Azure DevOps for the surrounding pipeline structure, and the EF Core guide for how migrations are generated in the first place.

What interviewers look for: specific reasons startup migration is risky (privilege, review, fleet-wide outage risk), not just "it's a bad practice"; knowing the bundle-versus-idempotent-script distinction and when to use each; placing the migration step before, and gating, the application rollout.

Common mistakes: believing EF Core 9's migration lock alone makes startup migrations production-safe — it prevents corruption from concurrent applies, but does not address privilege, review or fleet-wide startup delay; running the bundle with the same identity the application uses at runtime.

Q9 How do you change a column's data type on a large, high-traffic table with zero downtime?#

Short answer: Treat it exactly like a rename under expand-contract: add a new column with the target type, dual-write and backfill with an explicit, validated conversion, switch reads and writes over, and drop the old column later — never an in-place ALTER COLUMN type change on a large table, which typically rewrites the entire table and holds a long lock, and can also fail outright on rows that do not convert cleanly.

The backfill step is where type changes differ from a plain rename: converting, say, a varchar price to a decimal requires validating every historical value, and rows that fail to parse need an explicit decision — skip and log, default and flag for review, or block the migration until data is cleaned up — made before the switch release, not discovered by it. Run the conversion through the same batched, idempotent backfill job used for any other large backfill, and add a validation query that confirms zero unconverted or unparseable rows remain before flipping the application to read the new column.

What interviewers look for: recognizing that a type change is expand-contract with an extra data validation step, not a special case that needs a different pattern; a concrete plan for rows that fail conversion, rather than assuming the data is always clean.

Common mistakes: running a direct ALTER COLUMN type change on a large, live table and being surprised by the lock duration or a mid-migration conversion failure; skipping validation and discovering bad data only after the switch release is already live.

Q10 How do you safely drop a column or table that's no longer used?#

Short answer: Remove it from the EF Core model — and therefore from the application's mapped columns — in one release first, confirm nothing still reads or writes it, and only drop the actual column or table in a later release, because a currently-deployed application version will otherwise error the instant the column it still maps disappears underneath it.

EF Core maps every property on an entity unless you tell it otherwise, so simply deleting the migration Down method or the physical column while the entity still has the property breaks any request that touches that entity. The safe sequence mirrors contract in expand-contract: first, a release that removes the CLR property (or marks it unused, with any reads pointed elsewhere), deployed and confirmed running everywhere; second, a later release whose migration issues the actual DropColumn or DropTable, once you are confident no older version is still in the fleet, including long-lived workers and scheduled jobs. Before the second release, take a backup or confirm a tested restore path, since a drop is not reversible by any Down method that also wants the data back.

What interviewers look for: the two-release separation between "the application stops using it" and "the database stops storing it"; awareness that EF Core maps every property by default, which is what makes premature drops break the running application specifically.

Common mistakes: dropping a column in the same release that removes it from the model, reintroducing the two-versions-at-once problem for any pod still on the old version; assuming an unused-looking column has no readers, including reporting jobs, without checking.