Database Migrations for Large Tables

In this lesson, you’ll learn how to modify the schema of tables containing millions of rows without locking your database and causing application downtime.

The Challenge of Large Tables

When you run a standard Add-Migration and Update-Database command, EF Core typically generates ALTER TABLE statements. On a table with 50 million rows, adding a new column or changing a constraint can cause a "Metadata Lock," making the table inaccessible to your application until the database engine finishes rewriting the structure. For high-traffic systems, this leads to an unacceptable outage.

The Expand and Contract Pattern

To avoid downtime, we shift from "in-place" updates to a multi-stage migration strategy. Instead of modifying the existing column or constraint directly, we follow these steps:

1. Expand: Add the new column as nullable or with a default value. Update your application code to start writing to both the old and new columns, while still reading from the old one.
2. Migrate: Run a background job—in small batches—to copy data from the old column to the new one. Never run a single update query on a massive table, as this will bloat the transaction log and block other operations.
3. Switch: Update the application to read from the new column.
4. Contract: Once confirmed, drop the old column or constraint.

Batching Updates

When moving data, avoid a heavy UPDATE statement. Instead, use a batching strategy to keep the transaction logs small and allow the database to breathe:

// Example of a batch-processing logic snippet
int batchSize = 1000;
bool hasMore = true;

while (hasMore) {
    var rowsUpdated = await context.Database.ExecuteSqlRawAsync(
        "UPDATE TOP ({0}) MyTable SET NewCol = OldCol WHERE NewCol IS NULL", batchSize);
    
    if (rowsUpdated == 0) hasMore = false;
    await Task.Delay(100); // Give the database engine a moment to breathe
}

By decoupling the schema change from the data migration, you ensure that the database remains responsive, and your application deployment is decoupled from the heavy lifting of data transformation.

Key Takeaway: Never run blocking schema migrations on massive tables; instead, use the Expand and Contract pattern with batched background data synchronization to ensure continuous availability.