Online DDL and Zero Downtime Schema Migrations in Distributed Databases

---
title: "Zero Downtime Schema Migrations in Distributed Databases"
published: true
description: "A practical walkthrough of how CockroachDB, YugabyteDB, TiDB, and Spanner handle online DDL vs PostgreSQL — and migration strategies that actually work on sharded clusters."
tags: postgresql, devops, architecture, cloud
canonical_url: https://blog.mvpfactory.co/zero-downtime-schema-migrations-distributed-dbs
---

## What You Will Learn

By the end of this tutorial, you will understand how distributed databases like CockroachDB, YugabyteDB, TiDB, and Spanner propagate schema changes across nodes without downtime — and why your single-node PostgreSQL intuitions will mislead you. I will walk you through the expand-contract migration pattern, show you how to recover from stuck DDLs, and give you a compatibility matrix you can pin to your team's wall.

## Prerequisites

- Familiarity with PostgreSQL `ALTER TABLE` and locking behavior
- Basic understanding of sharded/distributed database architecture
- A healthy fear of running migrations on production clusters

## Step 1: Understand Why Single-Node Thinking Breaks

On single-node PostgreSQL, `ALTER TABLE ... ADD COLUMN` with a default acquired an `ACCESS EXCLUSIVE` lock until [PostgreSQL 11 introduced heap rewrite avoidance](https://www.postgresql.org/docs/11/release-11.html) for non-volatile defaults. Even today, adding a `NOT NULL` constraint with validation or creating an index without `CONCURRENTLY` blocks concurrent queries.

Now multiply that across a 50-node cluster. Every node holds a subset of data, and they all need to agree on the schema. A naive global table lock means seconds or minutes of downtime. Unacceptable for five-nines systems.

## Step 2: Learn the Schema Versioning Model

Here is the core insight. Instead of a single atomic flip, distributed databases introduce the new schema gradually. Google Spanner's protocol ensures no more than two schema versions are active across the cluster at any time. CockroachDB adopted a similar model.

A typical online `ADD COLUMN` progresses through three phases:

Phase 1: DELETE-ONLY → New column exists but only deletes consider it
Phase 2: DELETE-AND-WRITE-ONLY → New column accepts writes but reads ignore it
Phase 3: PUBLIC → Column is fully visible to all operations

Each phase propagates via a lease-based protocol. Nodes on the prior version keep operating correctly — the protocol guarantees correctness across both versions.

## Step 3: Know Your Database's DDL Support

| Feature | PostgreSQL | CockroachDB | YugabyteDB | TiDB | Spanner |
|---|---|---|---|---|---|
| Add column | Lock (fast since v11) | Online | Online | Online | Online |
| Add index | `CONCURRENTLY` | Online (backfill) | Online | Online (internal backfill worker)¹ | Online |
| Drop column | `ACCESS EXCLUSIVE` | Online, multi-phase | Online | Online | Online |
| Add NOT NULL | Full scan + lock | Online validation | Online | Online | Online |
| Change column type | Rewrite + lock | Limited | Limited | Supported | Limited² |

> ¹ TiDB uses an internal DDL worker. See [TiDB DDL documentation](https://docs.pingcap.com/tidb/stable/ddl-introduction).
> ² Spanner supports some in-place type promotions (e.g., widening `STRING` length) but not arbitrary type changes.

Let me show you a pattern I use in every project: check this matrix for your specific database version before writing any migration. CockroachDB [v23.1 introduced the declarative schema changer](https://www.cockroachlabs.com/docs/v23.1/online-schema-changes) as default, and TiDB [v6.2 added concurrent DDL execution](https://docs.pingcap.com/tidb/v6.2/release-6.2.0). What required downtime two versions ago may be fully online today.

## Step 4: Use the Expand-Contract Pattern

The most battle-tested pattern across all distributed databases:

sql
-- Phase 1: Expand — add the new column
ALTER TABLE orders ADD COLUMN status_v2 INT;

-- Phase 2: Dual-write — application writes to both columns
-- Phase 3: Backfill — migrate existing data
UPDATE orders SET status_v2 = status WHERE status_v2 IS NULL;

-- Phase 4: Contract — drop the old column
ALTER TABLE orders DROP COLUMN status;

This decouples the schema change from the application deploy. That matters when cluster nodes might be running different binary versions during a rolling update. Tools like `atlas` or CockroachDB's built-in schema change jobs let you declare desired state and let the system figure out the migration path — this scales better than hand-written migration files once you pass 100+ migrations.

## Gotchas

**Node restarts mid-migration** can leave schema changes in an intermediate state. In CockroachDB, schema change jobs are resumable:

sql
SHOW JOBS WHEN COMPLETE (
SELECT job_id FROM crdb_internal.jobs
WHERE job_type = 'SCHEMA CHANGE' AND status = 'reverting'
);
RESUME JOB ;

**Stuck concurrent DDLs in TiDB** happen when multiple DDLs target the same table:

sql
ADMIN SHOW DDL JOBS;
ADMIN CANCEL DDL JOBS job_id;

**Unthrottled backfills will ruin your day.** On a 500GB table across 30 nodes, an index backfill can take hours. Both CockroachDB and TiDB support throttling backfills to limit I/O impact. I have seen unthrottled backfills consume enough I/O to cause latency spikes that triggered cascading timeouts.

**Clock skew** on non-Spanner systems without TrueTime can cause version propagation delays. Monitor schema lease durations and alert when lease renewal latency exceeds your threshold.

**Testing on a single-node dev instance tells you nothing.** Always test migrations against a multi-node staging cluster with failure injection — kill a node mid-backfill, introduce network partitions. The docs do not mention this, but your first time running `RESUME JOB` should not be during a 2 AM incident.

Speaking of 2 AM incidents — long migration sessions are exactly the kind of deep-focus work where I rely on [HealthyDesk](https://play.google.com/store/apps/details?id=com.healthydesk) to remind me to stand up. Hard to debug a stuck DDL when your back is locked up from three hours in the same chair.

## Wrapping Up

Here is the minimal setup to get this working safely: use expand-contract for any non-trivial change, build a DDL compatibility matrix for your specific database version, throttle backfills, instrument migrations with metrics, and rehearse failure recovery before you need it. These patterns work universally across PostgreSQL and every distributed database covered here. Practice the recovery workflows now — your future self will thank you.