DEV Community

Young Gao
Young Gao

Posted on

Database Migrations in Production: Zero-Downtime Schema Changes (2026 Guide)

#postgres #database #backend #devops

You deploy a migration that adds a NOT NULL column. The old code is still running. Every INSERT fails. Your API returns 500s for 3 minutes until the new code deploys.

The Expand-Contract Pattern

Never make breaking schema changes in one step. Instead:

  1. Expand: Add the new column as NULLABLE (or with a default)
  2. Migrate code: Deploy code that writes to both old and new columns
  3. Backfill: Populate existing rows in batches
  4. Contract: Remove old column, add NOT NULL constraint

Safe Migration Examples

Adding a Column 

ALTER TABLE users ADD COLUMN phone TEXT;
UPDATE users SET phone = legacy_phone WHERE phone IS NULL AND id BETWEEN 1 AND 10000;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Renaming a Column

Never rename directly. Instead: add new column, dual-write, backfill, switch reads, drop old column. Four deploys, zero downtime.

Dangerous Operations

These lock tables and block queries on large tables:
Adding index without CONCURRENTLY locks the table. Adding NOT NULL requires full table scan. Changing column type rewrites the entire table.

Use CREATE INDEX CONCURRENTLY in PostgreSQL. Use pt-online-schema-change or gh-ost for MySQL.

Rollback Strategy

Every migration needs a rollback plan. If you add a column, the rollback drops it. If you rename, the rollback is: stop dual-write, drop new column. Test rollbacks in staging before production.

Part of my Production Backend Patterns series. Follow for more practical backend engineering.

If this was useful, consider:

Top comments (0)