Zero-Downtime Database Migrations in High-Volume Multi-Tenant Systems

In the early days of a software startup, database migrations are simple: you schedule a "maintenance window" at midnight on a Sunday, take the application offline for 20 minutes, run your schema migration scripts, and bring the site back up.

In enterprise enterprise cloud platform, maintenance windows do not exist. Your customers operate across all global time zones from Tokyo to London and New York. Contractual Service Level Agreements (SLAs) mandate 99.9% or 99.99% availability, with severe financial penalties for unannounced downtime.

Furthermore, multi-tenant tables contain tens of millions of records. If you execute a naive SQL migration—such as running ALTER TABLE orders ADD COLUMN status_code INT NOT NULL DEFAULT 1 or CREATE INDEX on a 50-million row table—PostgreSQL acquires an ACCESS EXCLUSIVE lock.

While waiting for that lock:

1. Every incoming client SELECT query queues behind the migration.
2. Connection pools in PgBouncer exhaust within 5 seconds.
3. Every web server pod runs out of database connections, and your entire application crashes with HTTP 504 Gateway Timeouts.

Executing migrations in high-volume multi-tenant cloud systems requires achieving true zero downtime. This guide presents the four-phase Expand and Contract Pattern, non-blocking index creation, safe constraint enforcement, and throttled backfill techniques.

The Lock Hierarchy: What Causes Table Freezes?

PostgreSQL utilizes a sophisticated lock hierarchy. Understanding which DDL statements acquire conflicting locks is the first step to zero downtime:

Rule #1: Always Set a Statement Timeout

Never run a DDL migration without an aggressive statement timeout:

SET statement_timeout = '3s';
SET lock_timeout = '2s';

-- If this table cannot acquire the lock within 2 seconds, ABORT immediately!
ALTER TABLE invoices ADD COLUMN notes TEXT;

If a long-running analytical query holds an open lock on invoices, your migration will safely abort after 2 seconds rather than queuing hundreds of customer requests behind it.

The Four-Phase Expand and Contract Pattern

How do you make breaking schema changes—such as renaming a column, changing a data type, or splitting a table—when hundreds of application servers are running active queries?

You execute the change across four distinct, decoupled deployment phases:

Phase 1: Expand (Database DDL)
Add new nullable column or table alongside the old one.
Both exist simultaneously.
        |
        v
Phase 2: Dual-Writing (Application Deployment V1)
Application reads from old_column, but writes to BOTH old_column and new_column.
        |
        v
Phase 3: Backfill (Throttled Background Worker)
Worker copies and transforms historical data from old_column into new_column.
        |
        v
Phase 4: Contract (Application Deployment V2 & Database DDL)
Application reads from new_column exclusively. Old column is safely dropped.

Worked Scenario: Renaming email to email_address

Phase 1: Expand (Database Migration)

Add the new column as NULLABLE:

ALTER TABLE users ADD COLUMN email_address TEXT;

Execution time: 3 milliseconds. Zero table locks.

Phase 2: Dual-Writing (Deploy Application Code V1)

Deploy application code that reads the old field, but writes to both fields on every mutation:

func (r *UserRepo) Update(ctx context.Context, u *User) error {
	// DUAL WRITE: Write to both old and new columns
	query := `
		UPDATE users 
		SET email = $1, email_address = $1, updated_at = NOW() 
		WHERE id = $2;
	`
	_, err := r.db.ExecContext(ctx, query, u.Email, u.ID)
	return err
}

Now, all newly created and updated records are synchronized in real-time. If you need to roll back the code deployment, the old column is still completely intact.

Phase 3: Throttled Historical Backfill

Now backfill the millions of historical records where email_address IS NULL.

Never run a single massive query: UPDATE users SET email_address = email;. Doing so will generate gigabytes of write-ahead logs (WAL), spike replication lag to your read replicas, and lock table rows.

Run a throttled background script in micro-batches:

-- Execute in a loop, sleeping 50ms between chunks:
UPDATE users
SET email_address = email
WHERE id IN (
    SELECT id FROM users
    WHERE email_address IS NULL
    ORDER BY id
    LIMIT 2000 -- Small, non-blocking chunk size
);

Check replication lag on read replicas after every chunk:

SELECT EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())) AS lag_seconds;

If replica lag exceeds 100ms, pause the backfill script for 5 seconds to let replicas catch up.

Phase 4: Contract (Deploy Application Code V2)

Once 100% of rows are backfilled:

1. Deploy Application Code V2, which reads and writes to email_address exclusively.
2. Once all old application pods have terminated, drop the old column:

ALTER TABLE users DROP COLUMN email;

The column was renamed across 50 million customer records with zero downtime, zero lock contention, and zero customer impact.

Non-Blocking Index Creation: CONCURRENTLY

Never execute standard CREATE INDEX on a live production table. Standard index creation acquires a SHARE lock, blocking all INSERT, UPDATE, and DELETE queries until the entire index build finishes.

Always use CONCURRENTLY:

-- Safe: Builds index using two non-blocking table scans
CREATE INDEX CONCURRENTLY idx_users_tenant_email ON users (tenant_id, email_address);

Handling Invalid Indexes

CREATE INDEX CONCURRENTLY runs in two transactions outside of standard DDL transaction blocks. If a transient deadlock occurs during index creation, PostgreSQL marks the index as INVALID:

SELECT relname, indisvalid FROM pg_index JOIN pg_class ON pg_index.indexrelid = pg_class.oid WHERE indisvalid = false;

An invalid index consumes disk space and slows down writes, but cannot be used for queries. Check for invalid indexes in your CI/CD pipeline, and drop them before re-running the migration:

DROP INDEX CONCURRENTLY IF EXISTS idx_users_tenant_email;

Adding NOT NULL Constraints Without Table Rewrites

Adding a standard NOT NULL constraint to an existing column scans the entire table while holding an exclusive lock.

The zero-downtime technique uses PostgreSQL's NOT VALID syntax:

-- Step 1: Add check constraint with NOT VALID (instantly completes in 2ms)
ALTER TABLE users ADD CONSTRAINT check_email_not_null CHECK (email_address IS NOT NULL) NOT VALID;

-- Step 2: Validate existing rows in background (weak lock, reads/writes continue uninterrupted)
ALTER TABLE users VALIDATE CONSTRAINT check_email_not_null;

Step 1 validates only newly inserted rows. Step 2 verifies historical rows while holding only a weak SHARE UPDATE EXCLUSIVE lock that does not block user traffic.

Summary Checklist for Production

• Always Set Lock Timeouts: Set lock_timeout = '2s' on all migration runner connections.
• Use the Expand-Contract Pattern: Decouple schema expansion from schema contraction across separate deployments.
• Always Use CREATE INDEX CONCURRENTLY: Never block table writes with standard index builds.
• Chunk Historical Backfills: Update rows in batches of 1,000 to 2,000 rows with pauses to protect replication lag.
• Validate Constraints Asynchronously: Use ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT.