Database Migrations at Scale: How to Move Fast Without Breaking Things

What you actually need to know

• Use CREATE INDEX CONCURRENTLY for indexes on large tables.
• Add NOT NULL columns as nullable first. Backfill. Add the constraint with NOT VALID.
• Dual write during column or table renames.
• Backfill in batches of a few thousand rows.
• Test on production sized data before the production migration.

The core argument

Database migrations are the kind of work that goes wrong only at scale. The naive ALTER TABLE that ran in milliseconds on a one thousand row table holds a lock for an hour on a one hundred million row table. The team that has not made the transition learns the patterns by causing an outage.

The fix is to learn the patterns before the table gets large. The Postgres patterns are well documented and consistent. Every long running schema change has a safe alternative that uses concurrent operations, batched backfills, or dual writes. The team that adopts these patterns ships migrations that never block the application.

The discipline is in always reaching for the safe pattern, even when the table is small. The team that uses CREATE INDEX CONCURRENTLY by default never has to remember to switch when the table grows. The team that uses the unsafe pattern by default has to remember and often forgets at the worst time.

The other discipline is testing. The migration that took thirty seconds on a development dataset might take three hours on production. The right approach is to test on a production sized copy of the database before the production migration. The test surfaces the timing reality and any blocking behavior.

The patterns reference

How much does this cost

The cost of the discipline is small. A few extra hours per migration to think through the sequence. The cost of skipping is the outage that the unsafe migration causes. The math is favorable for the discipline.

Features the migration framework must have

• Concurrent operations as the default.
• Batched backfill helpers.
• Dual write support in the application layer.
• Migration testing on production sized data.
• Migration timing measurement.
• Rollback path for every migration.
• Documentation of the safe patterns.
• A review process for migrations on large tables.

Expert opinion

The teams that have caused a production outage with a database migration never forget. The patterns are well known. The discipline is to apply them by default rather than by exception. The teams that learn this early move fast and break nothing. The teams that learn it through pain spend a quarter rebuilding trust with customers who experienced the outage.

>

Yashveer Singh, founder of Yashveer Labs

How this played out on a real project

A client SaaS was preparing a migration that added a NOT NULL column to their largest table. The naive ALTER TABLE would have held an exclusive lock for an estimated three hours. The team was prepared to do the migration in a maintenance window.

We restructured the migration. Add the column as nullable. Backfill in batches over a few hours, with each batch under a hundred milliseconds. Add the NOT NULL constraint with NOT VALID. Run VALIDATE in the background.

The new sequence took twelve hours of total wall clock time but never blocked the application. The customers experienced no downtime. The team adopted the pattern as standard for all migrations on large tables.

For more on the related work, see zero downtime database migrations a step by step guide and schema evolution adding columns without downtime.

Common mistakes teams make

1. Naive ALTER TABLE on large tables.
2. CREATE INDEX without CONCURRENTLY.
3. Long migrations during business hours.
4. No testing on production sized data.
5. No batched backfill.
6. No dual write during column rename.
7. No rollback plan.
8. Treating migrations as solved. New patterns appear with new Postgres versions.

A pre migration checklist

1. Step one. Estimate the table size and migration duration.
2. Step two. Pick the safe pattern from the reference.
3. Step three. Test on production sized data.
4. Step four. Review with another engineer.
5. Step five. Plan the rollback.
6. Step six. Run during a quiet window with monitoring.

For more on the related work, read zero downtime database migrations a step by step guide and PostgreSQL performance at scale the tweaks that move the needle. On the broader change management side, big bang vs gradual migration a decision map is the natural next read.