Schema Migrations in Production: Zero-Downtime Deployments

Table of Contents

• Introduction
• Why Production Migrations Are Risky
• The Expand and Contract Pattern
• A Practical Zero-Downtime Migration Flow
• Example: Renaming a Column Safely
• Backfills Without Taking the System Down
• Rollback Planning
• Common Mistakes
• Checklist
• FAQ
• Conclusion

Introduction

Schema migrations look simple in local development. You add a column, rename a field, change a type, run the migration, and move on. Production is different. The database is serving real traffic, old application instances may still be running, background jobs may be writing data, and large tables may contain millions of rows.

A safe production migration is not only about changing the schema. It is about changing the schema while the application keeps working.

Zero-downtime migration does not mean “nothing risky can happen.” It means the migration is designed so reads and writes can continue while the system moves from the old shape to the new shape.

Why Production Migrations Are Risky

Most migration failures come from a mismatch between application code and database shape.

For example, a deployment may remove a column while older app instances still read it. Or a migration may rename a column before all writers have been updated. Sometimes the SQL itself is correct, but it locks a large table for too long and blocks requests.

Common risks include:

• long-running table locks
• old and new app versions running at the same time
• background jobs using older assumptions
• large backfills slowing down the database
• missing indexes on newly introduced query paths
• rollback plans that only cover code, not data

The core idea is simple: production migrations should be compatible across versions.

The Expand and Contract Pattern

The safest pattern for many schema changes is expand and contract.

Expand means you add the new schema shape without removing the old one. The application can read and write both shapes during the transition.

Contract means you remove the old schema shape only after the system no longer depends on it.

Phase	What happens	Example
Expand	Add the new structure	Add display_name while keeping name
Migrate	Move or duplicate data	Backfill display_name from name
Switch	Update reads and writes	App reads display_name
Contract	Remove old structure	Drop name after verification

This pattern feels slower than a single migration, but it is much safer because every step can be verified.

A Practical Zero-Downtime Migration Flow

A good production migration usually happens in small steps.

1. Add the new schema in a backward-compatible way.
2. Deploy code that can write to both old and new fields.
3. Backfill existing data in small batches.
4. Verify counts, nulls, and application behavior.
5. Switch reads to the new field behind a feature flag or configuration.
6. Stop writing the old field.
7. Remove the old schema after the system is stable.

The important part is that each deployment can run safely even if another service or worker is still on the previous version.

Example: Renaming a Column Safely

Renaming a column directly can be risky because older code may still reference the old column name.

Unsafe:

ALTER TABLE users RENAME COLUMN name TO display_name;

Safer approach:

ALTER TABLE users ADD COLUMN display_name text;

Then backfill:

UPDATE users
SET display_name = name
WHERE display_name IS NULL;

During the transition, the application can write both columns:

Action	Old column name	New column display_name
User updates profile	write	write
Old app version reads	read	ignore
New app version reads	fallback	read
After cutover	stop writing	read/write

Only after verification should you drop the old column:

ALTER TABLE users DROP COLUMN name;

That final step should happen in a later deployment, not in the same deployment that introduces the new field.

Backfills Without Taking the System Down

Backfills are often the dangerous part. Updating millions of rows in one transaction can create locks, replication lag, high CPU usage, and noisy incident alerts.

Prefer batch updates:

UPDATE users
SET display_name = name
WHERE display_name IS NULL
  AND id > 10000
  AND id <= 20000;

Then repeat with small ranges.

Example progress tracking:

Batch	ID range	Rows updated	Duration
1	1-10,000	9,842	1.2s
2	10,001-20,000	9,911	1.3s
3	20,001-30,000	9,877	1.2s

Keep batches small enough that normal traffic remains healthy. Add pauses between batches if needed.

Rollback Planning

Rollback is not only “redeploy the previous code.” Database changes can be harder to reverse than application changes.

Before migrating, ask:

• Can old code still run after this migration?
• If the new code fails, can it ignore the new column?
• Is data being copied, transformed, or deleted?
• Can a failed backfill be resumed safely?
• What metrics tell us to stop?

For risky changes, prefer a rollback plan that disables usage of the new path instead of trying to undo every schema change immediately.

Common Mistakes

Doing schema and code changes in one step

If the schema change and the code change must land at the exact same time, the deployment is fragile. Split the change into compatible steps.

Dropping old columns too early

Old columns should stay until you are confident no application version, worker, report, or script still depends on them.

Ignoring background jobs

Jobs often run older code paths or process delayed data. Include them in the migration plan.

Backfilling too aggressively

A fast backfill that slows production is not a success. Prefer steady progress over noisy speed.

Forgetting observability

Track database load, query latency, lock waits, replication lag, error rate, and application behavior during the migration.

Checklist

• Make the first schema change backward-compatible.
• Avoid destructive changes in the same deployment as new code.
• Use expand and contract for renames, splits, and type changes.
• Backfill in small batches.
• Make backfills resumable.
• Verify data with counts and spot checks.
• Include workers and scheduled jobs in the plan.
• Decide rollback behavior before running the migration.
• Monitor locks, latency, and replication lag.
• Drop old schema only after the new path is stable.

FAQ

Does every migration need expand and contract?

No. Small additive changes, such as adding a nullable column, are often safe. Use expand and contract when old and new code may disagree about the schema.

Is adding an index safe in production?

It depends on the database and command. In PostgreSQL, CREATE INDEX CONCURRENTLY is often safer for large tables because it avoids blocking writes, but it has its own rules and failure modes.

Should migrations run automatically during deploy?

For simple additive migrations, automation can work well. For risky migrations, many teams prefer a controlled manual step with monitoring and a clear stop condition.

How do I know a column is safe to drop?

Check application code, background jobs, reports, dashboards, ad hoc scripts, logs, and query monitoring. If possible, keep the old column for one release cycle before dropping it.

Conclusion

Production migrations are engineering work, not just SQL files. The goal is to move the database and application together without forcing a risky all-at-once change.

Use backward-compatible steps, batch large data changes, verify each phase, and delay destructive cleanup until the system proves it no longer needs the old shape.