Migration Strategies

Database migrations are routine in single-tenant systems. In multi-tenant SaaS platforms they become an operational risk surface.

A migration that succeeds in development may break production when hundreds or thousands of tenants share the same infrastructure. Schema changes can block queries, corrupt tenant isolation guarantees, or create version skew between services and databases.

Migration design must therefore be treated as part of system architecture rather than a deployment afterthought.

If you’re building a SaaS product, this is the point where tenant boundaries and release sequencing need to be planned together. Teams that need to build a system like this usually treat migrations as an architecture decision, not an operations afterthought.

This article analyzes how database migrations behave inside multi-tenant SaaS systems, the architectural patterns used to control migration risk, and the operational strategies required to execute them safely at scale.

The focus is on relational databases such as PostgreSQL used with ASP.NET Core and EF Core, although the principles apply across most database platforms.

This topic pairs closely with SaaS Database Schema Patterns for Multi-Tenant Systems and Preventing Cross-Tenant Data Leakage in Multi-Tenant SaaS Systems.

Problem Definition and System Boundary

A migration is a structural change to persistent storage.

Examples include:

• adding or removing columns
• changing column types
• creating or modifying indexes
• splitting or merging tables
• backfilling derived data

In a multi-tenant SaaS system, these operations interact with several architectural constraints:

• tenant isolation
• concurrent production traffic
• large shared datasets
• multiple application instances

Unlike single-tenant systems, migrations cannot assume:

• downtime
• isolated databases
• uniform schema state

The operational boundary of a migration includes the entire runtime stack:

Application instances
↓
ORM migration engine
↓
Database cluster
↓
Tenant data partitions

A migration that locks shared tables or changes query assumptions can impact every tenant simultaneously.

The architectural question is therefore not how to run migrations.

The question is how to change data structures without violating tenant isolation or system availability.

Multi-Tenant Schema Topologies and Migration Complexity

Migration complexity depends heavily on the chosen tenancy model.

Shared Schema with Tenant Column

Example table:

Orders TenantId OrderId CustomerId Total Status

All tenants share tables. Isolation relies on a TenantId column enforced by query filters.

Advantages for migrations:

• only one schema must be updated
• schema state remains globally consistent

Risks:

• large tables increase migration time
• table locks affect all tenants simultaneously

Example risk:

ALTER TABLE Orders ADD COLUMN DiscountCode TEXT;

On a table with hundreds of millions of rows this operation may block writes for significant periods.

Schema Per Tenant

Each tenant has its own schema.

tenant_a.orders tenant_b.orders tenant_c.orders

Advantages:

• tenant data physically separated
• reduced blast radius for schema corruption

Migration cost:

Every tenant schema must be migrated independently.

A system with 2,000 tenants requires 2,000 migration executions.

Operational complexity becomes the dominant challenge.

Database Per Tenant

Each tenant runs in a separate database.

Advantages:

• strong isolation
• independent upgrades

Tradeoffs:

• migration orchestration becomes a distributed system problem
• infrastructure costs increase
• version drift between tenants becomes common

Migration architecture must match the tenancy model.

Migration Patterns for Shared-Schema Systems

Shared-schema systems require migrations that minimize table locks and maintain backward compatibility.

Three patterns are commonly used.

Expand and Contract Migration

The safest approach is a staged migration.

Phase 1 expands the schema.
Phase 2 removes obsolete structures.

Initial schema:

Users Id Name

New requirement: split Name into FirstName and LastName.

Phase 1 migration:

ALTER TABLE Users ADD COLUMN FirstName TEXT;
ALTER TABLE Users ADD COLUMN LastName TEXT;

Application version update:

• write to both fields
• read old field

Background job backfills data.

Phase 2 migration:

ALTER TABLE Users DROP COLUMN Name;

This prevents downtime and maintains compatibility between application versions.

Online Index Creation

Index creation can block writes if executed incorrectly.

PostgreSQL supports concurrent index creation.

CREATE INDEX CONCURRENTLY idx_orders_tenant
ON Orders(TenantId);

Benefits:

• avoids write locks
• allows normal operations during indexing

Constraints:

• cannot run inside a transaction
• may take longer to complete

For large multi-tenant tables this approach is essential.

Data Backfill Jobs

Schema expansion often requires computing derived data for historical rows.

Example:

ALTER TABLE Orders ADD COLUMN Region TEXT;

Existing rows must be populated.

Backfilling during migrations increases risk.

A safer pattern uses asynchronous workers.

Example background worker:

public class RegionBackfillWorker : BackgroundService
{
    private readonly IServiceProvider _services;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var scope = _services.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

        var orders = await db.Orders
            .Where(o => o.Region == null)
            .Take(1000)
            .ToListAsync();

        foreach (var order in orders)
        {
            order.Region = ResolveRegion(order.CustomerId);
        }

        await db.SaveChangesAsync();
    }
}

Backfill jobs reduce pressure on migration execution and allow throttling.

Migration Patterns for Schema-Per-Tenant Systems

Schema-per-tenant systems require migration orchestration.

Instead of a single operation, thousands of migrations may run.

Typical architecture:

Migration Service
↓
Tenant Registry
↓
Migration Queue
↓
Tenant Schemas

Example migration runner:

foreach (var tenant in tenants)
{
    var connection = BuildTenantConnection(tenant);

    using var context = new TenantDbContext(connection);

    context.Database.Migrate();
}

Key operational concerns:

• partial migrations
• failed tenants
• migration order consistency

Large systems track migration state.

Example table:

TenantMigrations TenantId MigrationId AppliedAt Status

This allows retries and monitoring.

Real Failure Scenario

A SaaS platform using shared schema introduces a column modification.

ALTER TABLE Users
ALTER COLUMN Email TYPE VARCHAR(100);

Existing column type: TEXT.

The Users table contains tens of millions of rows.

PostgreSQL performs a full table rewrite.

Consequences:

• write locks block transactions
• API timeouts increase
• retry storms increase database load

Root cause:

The migration assumed a small dataset and ignored rewrite cost.

Prevention strategy:

• avoid type changes on large tables
• use staged expand-contract migrations
• measure table rewrite cost before deployment

Operational Considerations for Production Migrations

Migration safety depends more on operations than code.

Migration Observability

Every migration should emit operational metrics.

Examples:

• migration duration
• lock wait time
• replication lag
• rewritten row counts

Monitoring should alert when thresholds exceed expected ranges.

Deployment Coordination

Application versions must remain compatible with both schema states.

Typical deployment order:

1. expand migration
2. deploy new application version
3. run backfill workers
4. contract migration

Breaking this order creates schema incompatibilities.

Traffic-Aware Scheduling

Large migrations should run during lower traffic periods.

However global SaaS systems rarely have true downtime.

Safer approach:

Throttle migration operations.

Example:

• backfill 500 rows per minute
• pause when replication lag increases

Rollback Strategy

Some migrations cannot be reversed.

Rollback planning may include:

• schema rollback scripts
• feature flags disabling new fields
• restoring data from backups

A migration without rollback planning introduces major operational risk.

Migration Strategy Tradeoffs

Migration architecture introduces tradeoffs.

Shared schema:

• simple coordination
• large blast radius

Schema per tenant:

• improved isolation
• operational complexity

Database per tenant:

• strongest isolation
• version drift between tenants

Most early-stage SaaS platforms adopt shared-schema models for simplicity.

As systems scale, migration orchestration infrastructure becomes necessary.

Relationship to Overall SaaS Architecture

Database migrations demonstrate a broader principle.

Infrastructure changes in multi-tenant systems affect shared resources.

Schema design, deployment pipelines, and background workers must operate with tenant awareness.

For a broader architectural overview of tenant isolation strategies and SaaS system design, see the pillar article:

Complete Guide to Multi-Tenant SaaS in ASP.NET Core

Need implementation support? Review the Agnite Scan case study or explore our services.

Related Articles

• Complete Guide to Multi-Tenant SaaS in ASP.NET Core
• SaaS Database Schema Patterns for Multi-Tenant Systems
• Tenant Context Propagation in ASP.NET Core
• Preventing Cross-Tenant Data Leakage in Multi-Tenant SaaS Systems