Zero-Downtime Database Migrations: The Expand/Contract Pattern That Lets You Change Your Schema Any Day of the Week

It is 4:55 p.m. on a Thursday, and a product manager asks for one small change: split the full_name column into first_name and last_name. The developer writes a four-line migration, runs it on staging in two seconds, and ships it. In production, the same statement takes an exclusive lock on a table with 180 million rows, every write queues behind it, connection pools saturate, and the API returns 503s for eleven minutes. Nobody wrote bad code. They simply treated a database migration as a code change, when it is really a live operation on shared, stateful infrastructure.

At AEGONTECH LLC, we see this pattern in almost every engagement where a team has outgrown "deploy at 2 a.m. and hope." This guide explains how zero-downtime database migrations actually work, which changes are dangerous, and how to build a process that lets you change your schema any day of the week.

Key Takeaways

• Schema changes fail in production because of locks and long-running rewrites, not because of syntax errors. Staging data volumes hide the problem.
• The expand/contract pattern splits one risky change into several small, individually reversible steps, so the application and the schema never need to change at the same instant.
• Backfills must run in small batches, throttled and resumable, never as one giant UPDATE.
• Treat migrations as first-class deployments with their own review checklist, lock timeouts, and rollback plan.

Why do database migrations cause downtime in the first place?

Database migrations cause downtime because many schema changes acquire locks that block reads or writes for as long as the operation runs, and that duration grows with table size. A change that finishes in milliseconds on a 10,000-row staging table can hold a lock for many minutes on a production table with hundreds of millions of rows.

Consider what happens in PostgreSQL. An ALTER TABLE needs an ACCESS EXCLUSIVE lock, the strictest lock available, which conflicts with every other operation on that table. Even if the statement itself is fast, it must first wait for any in-flight query to finish. While it waits, every new query lines up behind it. A single forgotten long-running analytics query can turn a "fast" migration into a full outage. This is called lock queueing, and it is the most common cause of migration incidents we investigate.

The second cause is the table rewrite: an operation that copies every row into a new physical file. Changing a column type, or (in older PostgreSQL versions) adding a column with a default value, rewrites the whole table. Since PostgreSQL 11, adding a column with a constant default is metadata-only and instant, but many other changes still rewrite.

The uncomfortable truth is that a migration that has never been tested against production-scale data has never really been tested.

What is the expand/contract pattern?

The expand/contract pattern (also called parallel change) is a technique that replaces one breaking schema change with a sequence of backward-compatible steps: first expand the schema to support both old and new shapes, migrate the data and code, then contract by removing the old shape. At no point does the running application encounter a schema it cannot handle.

Take the full_name split from our opening example. The safe version looks like this:

1. Expand: add nullable first_name and last_name columns. Adding a nullable column without a default is a metadata-only change and takes a lock for milliseconds.
2. Dual write: deploy application code that writes to both the old and new columns on every insert and update. Reads still use full_name.
3. Backfill: copy historical data into the new columns in small batches.
4. Switch reads: deploy code that reads from the new columns. Keep dual writes running so you can roll back instantly.
5. Contract: once you are confident, stop writing full_name, deploy, and finally drop the column in a separate release.

That is five deployments instead of one, and it feels slow. It is also the difference between a change you can revert in seconds and one that needs a restore from backup. The cost of expand/contract is a few extra deploys; the cost of skipping it is measured in outages.

How do you backfill data without taking the database down?

AEGONTECH engineers backfill safely by processing rows in small, throttled batches, committing between each batch, and making the job resumable so it can be paused or restarted at any point. A single UPDATE across a large table holds locks, generates enormous write-ahead log volume, and can starve replicas.

A dependable backfill job has these properties:

• Batch size of roughly 1,000 to 10,000 rows, tuned so each transaction completes in well under a second.
• Keyset pagination (WHERE id > last_id ORDER BY id LIMIT n) rather than OFFSET, which gets slower with every page.
• Throttling tied to replica lag or database CPU, so the job slows down when the system is busy.
• Idempotency: running the same batch twice produces the same result, so a crash never corrupts data.
• Progress tracking stored outside the job process, so you can see how far along it is.

As a rough benchmark, a well-tuned batched backfill can move 50 to 100 million rows in a few hours with no user-visible impact, while the same volume in a single statement can cause replication lag measured in hours.

Which schema changes are safe, and which are dangerous?

Safe changes are those that only touch metadata or take very short locks, while dangerous changes rewrite tables, scan them under an exclusive lock, or break running application code. Knowing which category a statement belongs to is the most valuable habit a team can build.

Generally safe (in PostgreSQL 11+):

• Adding a nullable column, or a column with a constant default.
• Creating an index with CREATE INDEX CONCURRENTLY (slower, but does not block writes).
• Adding a NOT VALID foreign key or check constraint, then running VALIDATE CONSTRAINT separately, which takes a much weaker lock.

Dangerous without special handling:

• Changing a column's data type, which usually rewrites the table.
• Adding NOT NULL to an existing column in one step, which forces a full-table scan under an exclusive lock.
• Renaming a column or table that running application instances still reference.
• Creating an index without CONCURRENTLY, which blocks all writes for the duration.
• Dropping a column that any deployed code version still reads.

A useful rule of thumb: never rename in place, always add and migrate. A rename looks harmless but breaks every application instance running the previous release during a rolling deploy.

How does this compare: online schema-change tools vs. hand-written expand/contract?

Hand-written expand/contract gives you full control and works with any database, while online schema-change tools such as pt-online-schema-change, gh-ost (for MySQL), or pg_repack and pgroll (for PostgreSQL) automate the shadow-table dance for you. The right choice depends on table size and team maturity.

In an automated tool approach, the tool creates a shadow copy of the table with the new schema, copies rows in the background, keeps it in sync through triggers or the binary log, then swaps the tables atomically. This is excellent for huge tables and for changes that genuinely require a rewrite, but it needs extra disk space (often 2x the table size), and the final swap still takes a brief lock.

With hand-written expand/contract, you use plain SQL and application code. It is simpler to reason about, requires no extra tooling, and doubles as documentation of intent. It demands discipline from developers, since every step is a separate deploy.

Our default recommendation at AEGONTECH is hand-written expand/contract for most changes, and a dedicated online tool for the small number of large-table rewrites that cannot be decomposed. Whichever you choose, the tool never replaces the process.

What guardrails should every migration pipeline have?

Every migration pipeline should enforce a lock timeout, run through CI against production-like data, require review by someone who understands locking, and have a documented rollback path. Guardrails matter because they make the safe path the default one.

At AEGONTECH, the migration pipelines we build for products such as Dolfy.ai and Mimicall.app follow a consistent checklist:

• Set lock_timeout (for example 3 seconds) and statement_timeout on migration connections. If a migration cannot acquire its lock quickly, it fails fast and retries instead of blocking production traffic behind it.
• Lint migrations automatically. Tools like squawk or strong_migrations flag dangerous operations in CI before a human ever reviews them.
• Test against a recent, anonymized production snapshot so lock durations and rewrite times reflect reality.
• Separate schema deploys from code deploys. Run migrations as their own step in your CI/CD (continuous integration and continuous delivery) pipeline, so a failing migration never leaves half-deployed application code.
• Decouple destructive steps. Dropping a column or table happens at least one full release after the code stopped using it, ideally after a waiting period of a week or more.
• Monitor during the change. Watch lock waits, replica lag, error rate, and p99 latency, and abort automatically if they cross thresholds.

In our experience at AEGONTECH LLC, teams that adopt even three of these controls typically see migration-related incidents drop sharply, because most outages come from the same handful of avoidable mistakes.

What are the most common mistakes teams make?

The most common mistakes are testing only on small datasets, combining schema and data changes in one migration, forgetting about long-running queries and open transactions, and dropping columns before every application version has stopped using them.

Two deserve special attention. First, mixing DDL (schema changes) and DML (data changes) in one transaction creates long-held locks and makes rollbacks unpredictable. Keep them separate. Second, ignoring the rolling deploy window: during any deploy, old and new application versions run simultaneously against the same schema. Every migration must be compatible with both. If it is not, you have an outage window whether you planned one or not.

Frequently Asked Questions

Can we do zero-downtime migrations on MySQL as well as PostgreSQL?

Yes. The expand/contract pattern is database-agnostic. MySQL offers ALGORITHM=INPLACE and INSTANT for many operations, and tools like gh-ost and pt-online-schema-change handle large rewrites. The details of which operations lock differ by engine and version, so always check the documentation for your exact version.

How much longer does expand/contract take compared to a normal migration?

The engineering effort is typically 1.5 to 3 times higher for a breaking change, spread across several small releases. In exchange, each step is independently reversible and carries almost no outage risk. For non-breaking changes such as adding a nullable column, there is no extra cost at all.

Do we need this if our database is small?

If a table has under a few hundred thousand rows and your traffic is low, a simple migration is usually fine. But building the habit early is cheap, and tables grow faster than teams expect. It is far easier to adopt safe practices at 1 million rows than to retrofit them at 500 million.

What about NoSQL databases like MongoDB?

Schemaless databases move the problem rather than remove it. Documents in the old shape still exist, so application code must handle multiple versions of a document. The same expand/contract thinking applies: write the new shape, lazily or in batches migrate old documents, then remove the compatibility code.

Making schema changes boring

The goal of a mature migration process is not heroics; it is boredom. When a schema change is a routine, reviewed, reversible step rather than a late-night event, your team ships faster and sleeps better. Speed in software delivery comes from making risky things safe, not from taking more risks.

If your team is still scheduling maintenance windows for database changes, or if a migration on a growing table is starting to worry you, AEGONTECH LLC can help review your current process and design a safer path. Visit aegontech.dev to book a consultation with our engineering team.