Zero-Downtime Schema Changes (Expand/Contract)

Scenario

Example app: an API deployed with rolling updates (old and new pods run together for ~10 minutes). The team renames users.name to users.full_name and makes email_verified NOT NULL with a default, in one migration that runs before the new code rolls out.

What you see in production

During the deploy, old pods crash with column "name" does not exist. The NOT NULL change took an exclusive lock while it checked 40 million rows. A new index built with plain CREATE INDEX blocked writes to users for 6 minutes.

The naive code

ALTER TABLE users RENAME COLUMN name TO full_name;                       -- old code breaks instantly
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;              -- full table scan under lock
CREATE INDEX users_email_idx ON users (email);                           -- blocks writes while building

operations = [
    migrations.RenameField("user", "name", "full_name"),
    migrations.AlterField("user", "email_verified", models.BooleanField(default=False)),   # null=False
    migrations.AddIndex("user", models.Index(fields=["email"], name="users_email_idx")),
]

Reproduce it

A compatibility check of each schema step against the app versions that are running at that moment:

def compatible(schema, app):
    needs = {"v1": {"name"}, "v2": {"full_name"}, "v2-dual": {"name", "full_name"}}[app]
    return needs <= schema

plans = {
    "rename in one step": [({"full_name"}, ["v1", "v2"])],
    "expand / contract": [
        ({"name", "full_name"}, ["v1", "v2-dual"]),     # 1. add column, deploy dual-writing code, backfill
        ({"name", "full_name"}, ["v2-dual", "v2"]),     # 2. switch reads to full_name
        ({"full_name"}, ["v2"]),                        # 3. drop old column after all v1/v2-dual are gone
    ],
}
for name, steps in plans.items():
    ok = all(compatible(schema, app) for schema, apps in steps for app in apps)
    broken = [(sorted(schema), app) for schema, apps in steps for app in apps if not compatible(schema, app)]
    print(f"{name:20} -> {'safe' if ok else 'BREAKS ' + str(broken)}")

rename in one step   -> BREAKS [(['full_name'], 'v1')]
expand / contract    -> safe

Root cause

During a rolling deploy, old and new application versions run against the same schema. A change that is incompatible with either version breaks requests. Separately, some DDL rewrites or validates the whole table under a strong lock, blocking traffic for the duration.

The fix

Expand / contract (also called parallel change): make every step backwards compatible.

1. Expand — add the new column (nullable, no rewrite).
2. Migrate — deploy code that writes both, backfill in batches (next lesson), then read the new one.
3. Contract — once no running code uses the old column, drop it.

And use the online variant of each operation:

SET lock_timeout = '2s';
-- NOT NULL without a long lock: add a NOT VALID check, validate later (validation doesn't block writes)
ALTER TABLE users ADD CONSTRAINT email_verified_not_null CHECK (email_verified IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT email_verified_not_null;
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;   -- PG12+: uses the validated check, no scan
-- indexes without blocking writes (can't run inside a transaction block)
CREATE INDEX CONCURRENTLY users_email_idx ON users (email);

ALTER TABLE users ADD COLUMN full_name VARCHAR(200) NULL, ALGORITHM=INSTANT;
ALTER TABLE users ADD INDEX users_email_idx (email), ALGORITHM=INPLACE, LOCK=NONE;
-- big table rewrites: gh-ost / pt-online-schema-change copy into a shadow table and swap

operations = [migrations.AddField("user", "full_name", models.CharField(max_length=200, null=True))]
# model: write both fields in save(); later migration removes `name` after reads switched

@PrePersist @PreUpdate
void syncNames() { this.fullName = this.name; }   // remove after the backfill and read switch

For MySQL tables too large or busy for online DDL, GitHub's gh-ost performs schema changes by copying data into a shadow table while tailing the binlog, then swapping tables (github/gh-ost).

Trade-offs

• Expand/contract takes several deploys and some temporary code — the price of zero downtime.
• CREATE INDEX CONCURRENTLY is slower, can fail and leave an INVALID index to drop and retry, and can't run in a transaction (configure your migration tool accordingly).
• Online schema-change tools need disk space for a full copy and add replication load.

Detect it in production

• Migration linting in CI (e.g. squawk for Postgres, strong_migrations for Rails) to flag locking operations.
• Lock-wait and error-rate monitoring during deploys; every migration runs with lock_timeout.

Key points

• Old and new code run together during deploys — every schema step must work with both.
• Expand → migrate (dual write, backfill, switch reads) → contract.
• Use online DDL: CONCURRENTLY, NOT VALID + VALIDATE, ALGORITHM=INSTANT/INPLACE, gh-ost.