Database migrations in production — zero-downtime strategies

Advanced5 min read·eng-06-015

sqlinterview

Concept

Zero-downtime database migrations — how to change a database schema in production without taking the application offline. The challenge: your app is deployed in rolling fashion, so old and new app code run simultaneously during deployment. The schema must work with BOTH versions.

The expand-contract pattern (also called parallel change):

Expand: Add new columns/tables. Both old and new code work.
Migrate data: Backfill data asynchronously.
Contract: Remove old columns/tables once new code is fully deployed and old code is gone.

Safe operations (can be done at any time without downtime):

Adding a nullable column.
Adding a new table.
Adding an index (with ALGORITHM=INPLACE, LOCK=NONE in MySQL).
Adding a new column with a default value (PostgreSQL adds it as nullable, then sets default separately).

Dangerous operations (require careful sequencing):

Dropping a column — old code may still reference it.
Renaming a column — old code uses old name, new code uses new name.
Adding a NOT NULL column without a default — fails on INSERT from old code.
Adding a UNIQUE index on a large table — locks the table in some DB versions.

Renaming a column (3 deployments):

Deploy 1: Add new column, write to both old and new column.
Deploy 2: Read from new column, write to both.
Deploy 3: Stop writing to old column, drop it.

Large table migrations: Adding a column to a 100M-row table can lock the table for minutes. Use:

pt-online-schema-change (pt-osc): Creates a shadow table, copies data, swaps.
gh-ost (GitHub): Trigger-based, online migration.
PostgreSQL: ADD COLUMN is instant for nullable columns (just a catalog change in Postgres 11+).

Laravel best practices: Use $table->string('col')->nullable() for new columns. Use separate migrations for data backfill (queue jobs). Use $table->renameColumn() only after verifying old code is gone.

Code Example

php

<?php
// ============================================================
// EXAMPLE: Renaming 'username' to 'display_name' safely
// ============================================================

// STEP 1 — Migration (expand): add new column, keep old one
// php artisan make:migration add_display_name_to_users
public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('display_name')->nullable()->after('username');
    });
}

// STEP 1 — Backfill data (run as queued job or in migration)
// DB::statement('UPDATE users SET display_name = username WHERE display_name IS NULL');

// STEP 2 — Deploy new code that reads 'display_name', falls back to 'username'
// In User model:
public function getDisplayNameAttribute(): string
{
    return $this->attributes['display_name'] ?? $this->attributes['username'];
}

// STEP 2 — Write to BOTH columns in new code
public function setDisplayNameAttribute(string $value): void
{
    $this->attributes['display_name'] = $value;
    $this->attributes['username']     = $value; // keep old column in sync
}

// STEP 3 — Migration (contract): drop old column after all old code is gone
public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('username');
    });
}

// ============================================================
// SAFE: Adding a nullable column with a default
// ============================================================
Schema::table('orders', function (Blueprint $table) {
    $table->string('currency', 3)->nullable()->default('USD')->after('total');
    // MySQL: ADD COLUMN with default is fast (metadata-only in some cases)
    // PostgreSQL: instant for nullable columns
});

// ============================================================
// SAFE: Adding an index without locking (MySQL)
// ============================================================
DB::statement('ALTER TABLE orders ADD INDEX idx_orders_status (status), ALGORITHM=INPLACE, LOCK=NONE');

// Laravel migration alternative — uses ALGORITHM=INPLACE automatically in many cases
Schema::table('orders', function (Blueprint $table) {
    $table->index('status');
});

PreviousConnection pooling — PgBouncer, ProxySQL, why it mattersSQL Deep Dive NextArrays as hash maps — O(1) lookup, collision handlingData Structures in PHP