Course contentsShow
← Software Engineering
Lesson 891 of 1,93327. Database Design in PracticeFree lesson

Writing Safe Migrations

Creating migrations that avoid locking tables, minimize downtime, and can execute on large datasets without timing out.

Writing Safe Migrations

You'll learn how to design database migrations that execute reliably on production systems without causing outages or timeouts.

The Core Problem

Migrations often modify schema or data on live databases. If a migration locks a table for several minutes, your application can't serve requests. If it times out halfway through, you're left with inconsistent state. Safe migrations execute quickly, avoid blocking operations, and gracefully handle large datasets.

Key Principles

Avoid Long-Running Locks

Operations like ALTER TABLE ADD COLUMN can lock an entire table while restructuring it. On a million-row table, this might take minutes. Instead, break changes into non-blocking steps:

  • Add nullable columns first (fast)
  • Backfill data in small batches
  • Add constraints or make NOT NULL later

Work in Small Batches

Never update a million rows in one transaction. Process records in chunks of 1,000-10,000, committing between batches. This prevents long-running transactions from holding locks or filling transaction logs.

Use Non-Blocking Techniques

Some databases offer CREATE INDEX CONCURRENTLY or ALTER TABLE ... ALGORITHM=INPLACE. These allow reads/writes to continue during schema changes. Know your database's specific capabilities.

Test Against Production-Scale Data

A migration that runs in 2 seconds on 1,000 test rows might take 20 minutes on 10 million production rows. Always benchmark migrations against realistic data volumes.

Analogy

Think of renovating a busy restaurant. You don't close the kitchen for three hours to install new equipment—you'd lose customers. Instead, you install it section by section during slow periods, keeping most of the kitchen operational. Safe migrations work the same way.

Key Takeaway: Safe migrations avoid locking, process data incrementally, and are tested at production scale to ensure they complete quickly without disrupting service.