Schema changes without downtime: Expand and contract

In short: In expand-and-contract a database schema is changed in several backward-compatible steps so that old and new program versions work in the meantime and no downtime is needed.

In more detail: Renaming a column sounds simple. But if the app runs in several instances or there are rollbacks, a hard switch can wreck everything: during deployment old and new versions work at the same time. So changes are split into compatible steps.

In Depth

The pattern

1. Expand: add a new column/table (nullable or with default), remove nothing old.
2. Write to both: the application writes old and new (dual writing) and keeps reading from the old.
3. Backfill: copy existing data in stages (batches) into the new structure.
4. Switch reads: the application reads from the new (feature flag, verify).
5. Contract (clean-up): only when no version uses the old any more is the old column removed.

Example: splitting column name into first_name and last_name

-- 1. Expand
ALTER TABLE users ADD COLUMN first_name text, ADD COLUMN last_name text;
-- 2. App writes name, first_name, last_name
-- 3. Backfill in batches
UPDATE users SET first_name = split_part(name,' ',1), last_name = split_part(name,' ',2)
  WHERE id BETWEEN 1 AND 10000 AND first_name IS NULL;
-- 4. App reads first_name/last_name
-- 5. Contract (later, separate)
ALTER TABLE users DROP COLUMN name;

Dangerous operations

• Renaming or deleting a column while old program versions use it.
• NOT NULL without default on a large table (can lock the table).
• Creating an index without CONCURRENTLY (PostgreSQL) blocks writes.
• Changing a type (rewrites the table).
• Large updates in one transaction (locks, replication lag). Always in batches.
• Migrations and deployment must be timed together.

Practice

Test migrations on a copy with realistic data volumes. Backups before critical steps, rollback plan, version migrations and keep them in the repository. Tools: Flyway, Liquibase, Alembic, Prisma Migrate, Drizzle Kit, gh-ost/pg-osc for large tables. Check whether a maintenance window is simpler for small systems than elaborate multi-step procedures.

See also: Database migration, Blue-green and canary deployment, Feature flags, Backup strategy