Zero-downtime schema changes

Databases are the hard part of a safe deploy. During a rolling release, old and new versions of the app read and write the same tables. The expand and contract pattern keeps both working. You add compatible changes first, move the code over, and remove the old shape last.

Walking through the five steps

Here is the safe sequence for adding an email_verified flag to a live users table, while the old code keeps running.

Step 1  Migration: ADD COLUMN email_verified BOOLEAN DEFAULT false
Step 2  Deploy app: dual-write email_verified; read new with fallback
Step 3  Backfill: UPDATE ... WHERE email_verified IS NULL in batches of 1k
Step 4  Deploy app: read and write only email_verified
Step 5  Migration: drop the unused legacy column and stop dual-write

Each step is safe on its own. The old code still works after step 1, because the new column has a default. The new code still works after step 2, because it reads the old data when the new value is missing.

Large tables and locks

Online schema tools such as pt-online-schema-change, gh-ost and pgroll rewrite large tables without long locks. They build a shadow copy and sync the changes into it. Still rehearse on a production-sized copy, because disk, replication lag and trigger overhead surprise people.

On Postgres, build indexes on large tables with CREATE INDEX CONCURRENTLY. It can fail and leave an invalid index that you must clean up. Do not change a primary key in the same change as an app deploy.

Migration rules

1. Use the same migration tool everywhere. Pick one, such as Flyway, Liquibase, golang-migrate or Prisma Migrate, and run the same ordered files from CI.
2. Rehearse on production-sized data when a migration locks or rewrites rows.
3. Record versions. Long-lived databases must never skip a version. Ephemeral databases can apply all of them from scratch.
4. Pair with flags. Ship the schema change first, then enable the flag that reads the new column. This stops instances on the new code from reading data that is not ready.

Common mistakes

• A single rename in one release. Old pods fail during the rollout.
• Expand and contract in one shot. Old code cannot be used during the gap.
• Building an index without CONCURRENTLY. The table is locked while it builds.

Check yourself

1. Why is a single RENAME COLUMN risky during a rolling deploy? Answer: Pods still running the old code expect the old name, so they start failing partway through the rollout.
2. What is the first step of expand and contract? Answer: Add the new column as a compatible change, nullable or with a default, so the old code keeps working.

Key takeaways

• Expand first, switch the code second, and contract last.
• Backfill in batches, and dual-write until every reader has moved.
• Use online schema tools and CREATE INDEX CONCURRENTLY on large tables.
• Pair schema changes with flags, and rehearse them on production-sized data.

Further learning

The rollback side of the same problem is covered in rollback and roll-forward. For the broader idea of keeping config and state consistent across environments, see configuration and environments.