Apps Artificial Intelligence Cloud Computing CSS Cybersecurity Data Science Database Go JavaScript Linux Python Rust Software Engineering Web Development

Safe Database Migrations with the Expand-and-Contract Pattern

4 min read .
Published on
Safe Database Migrations with the Expand-and-Contract Pattern

A schema migration can be syntactically correct and still cause an outage. Production deployments often run old and new application versions at the same time, background workers may lag behind, and a large table can turn a simple-looking DDL statement into a long lock.

The expand-and-contract pattern reduces those risks by splitting an incompatible change into compatible stages.

Why one-step schema changes are risky

Suppose an application wants to rename users.full_name to users.display_name.

A direct rename is attractive:

ALTER TABLE users RENAME COLUMN full_name TO display_name;

But any old application instance still selecting full_name can fail immediately. Rolling deployment and fast application rollback are no longer safe.

The database has become the coordination point between versions.

Phase 1: expand the schema

Add the new representation without removing the old one:

ALTER TABLE users ADD COLUMN display_name TEXT;

At this point, old code continues using full_name. New code must tolerate display_name being absent for existing rows until data is copied.

Schema details differ across database engines and versions. Before running DDL on a large production table, check the target database’s locking and rewrite behavior and test with realistic data volume.

Phase 2: deploy compatible application code

During the transition, the application may need dual-write behavior:

write full_name
write display_name

Alternatively, one value may be derived in the database or by a migration process. The important property is that both old and new application versions can operate correctly while they overlap.

Dual writes introduce their own consistency risk. Keep the transition short, monitor mismatches, and make one component clearly responsible for maintaining both fields.

Phase 3: backfill existing rows

Historical rows need the new value:

UPDATE users
SET display_name = full_name
WHERE display_name IS NULL;

On a small table, one statement may be fine. On a large table, update in bounded batches to reduce long transactions, replication pressure, lock duration, and transaction-log growth.

A backfill should be restartable. Use a predicate that identifies incomplete rows so rerunning a batch does not corrupt already migrated data.

Validate the backfill

Do not assume that “the job finished” means the data is correct. Check invariants, for example:

SELECT COUNT(*)
FROM users
WHERE display_name IS NULL;

If the fields should be identical during the transition, compare them as well, accounting for your database’s NULL semantics.

Phase 4: switch reads to the new schema

After the new field is populated and maintained, deploy code that reads display_name.

Keep writing the old field temporarily if older workers or rollback versions still depend on it. Observe the system long enough to know that the new read path is stable.

This delay is what preserves rollback: moving application reads back to the old version still works because the old representation remains valid.

Phase 5: contract only when nothing needs the old schema

Once deployed code, workers, scripts, reports, and operational tooling no longer depend on full_name, remove the compatibility behavior and eventually drop the old column:

ALTER TABLE users DROP COLUMN full_name;

Treat this as a separate release. After the destructive step, rolling back to code that expects full_name is no longer safe.

The same pattern applies beyond renames

Expand-and-contract is useful for:

  • splitting one column into several fields;
  • changing a serialized format;
  • replacing a foreign-key relationship;
  • moving data to a new table;
  • introducing a stricter constraint;
  • changing an identifier representation.

For a new NOT NULL rule, for example, first deploy code that always writes a value, backfill old nulls, validate the invariant, and only then enforce the constraint.

Indexes need migration planning too

Creating an index can be expensive and may lock writes depending on database engine, version, table size, and chosen command.

Some systems provide online or concurrent index-building options with specific limitations. Use the mechanism supported by your exact database rather than copying syntax from another engine.

After creation, verify that representative queries actually use the index. An index that exists but does not match the query’s filters and ordering still adds write cost without delivering the intended benefit.

Make migrations observable and restartable

For long-running data migrations, record progress such as the last processed primary key, rows changed, failures, and elapsed time.

Prefer idempotent batches:

select next incomplete range
update bounded rows
commit
record progress
repeat

If the process stops halfway through, operators should know whether rerunning it is safe and where work will resume.

Common pitfalls

Combining schema, backfill, and cleanup in one release

This removes the compatibility window and makes rollback difficult. Separate reversible expansion from destructive contraction.

Assuming DDL is instant

Metadata-only operations on one database version may rewrite data or acquire stronger locks on another. Verify behavior on the engine and version you operate.

Backfilling without production-like tests

A query that completes instantly on a development table may create hours of load in production. Test representative row counts and monitor replication lag, lock waits, and resource usage.

Forgetting non-web consumers

Old queue workers, scheduled jobs, analytics queries, data exports, and support scripts may depend on a column after the web deployment has moved on. Inventory consumers before contracting the schema.

Making rollback depend on reverse migrations

A hurried reverse migration during an incident can be riskier than the original release. Prefer forward-compatible stages that allow application rollback without immediate schema reversal.

A migration review checklist

Before approving a production schema change, ask:

  • Can old and new application versions run against the expanded schema?
  • Is the backfill bounded and restartable?
  • Are locks and table rewrites understood for the actual database version?
  • Can the application roll back before contraction?
  • Have background jobs and external consumers been considered?
  • Is the destructive step delayed until dependency removal is verified?

Database migrations are application compatibility changes, not merely SQL files. Designing the overlap explicitly is what turns a fragile one-step alteration into a controlled production rollout.

Databases SQL Backend Migrations

Related Posts

Keyset Pagination in SQL for Fast, Stable APIs
Published on
Keyset Pagination in SQL for Fast, Stable APIs
Pagination looks simple until a table becomes large or new rows are inserted while a client is paging through results. LIMIT and OFFSET are easy to understand, but deep offsets can become expensive and changing data can make rows appear twice or disappear between requests. Keyset pagination, also called seek pagination, avoids those problems by asking for rows after a known position instead of asking the database to skip a number of rows.
SQL Databases Backend API
Atomic File Writes in Go: Prevent Partial and Corrupted Files
Published on
Atomic File Writes in Go: Prevent Partial and Corrupted Files
Writing a file with os.WriteFile is simple, but it is not always the safest choice for configuration files, generated metadata, caches, state files, or other data that must never be left half-written. If a process crashes or the machine loses power while a file is being replaced, readers may observe incomplete content. A common way to reduce this risk is an atomic file write: write the new content to a temporary file first, then replace the destination with a rename.
Golang Files Backend Reliability
Bounded Concurrency in Go with a Worker Pool
Published on
Bounded Concurrency in Go with a Worker Pool
Goroutines are cheap, but the resources they call are often not. Starting one goroutine for every item in a large batch can overwhelm a database connection pool, trigger API rate limits, exhaust file descriptors, or create avoidable memory pressure. Bounded concurrency solves this by allowing only a fixed number of operations to run at the same time. A worker pool is one of the simplest standard-library patterns for implementing that limit in Go.
Golang Concurrency Backend Performance
Exponential Backoff with Jitter in Go
Published on
Exponential Backoff with Jitter in Go
Retries can make distributed systems more resilient, but immediate retries can also make an outage worse. If thousands of clients retry at the same moment, a recovering dependency receives another synchronized burst of traffic before it has time to stabilize. A common solution is exponential backoff with jitter: increase the maximum delay after each failure, then randomize the actual wait. This article builds that pattern with Go’s standard library and shows where retry logic belongs—and where it does not.
Golang Retries Reliability Backend
Go Context Timeouts and Request Cancellation
Published on
Go Context Timeouts and Request Cancellation
A Go HTTP handler can outlive the request that started it unless the work inside the handler pays attention to cancellation. That matters when a client disconnects, an upstream request takes too long, or a database query is no longer useful. Go solves this with context.Context. Every incoming *http.Request already has a context, and that context is canceled when the client connection closes, the request is canceled by HTTP/2, or the handler returns. You can also derive a shorter deadline for work that should not consume the entire request lifetime.
Golang Backend HTTP
Go Error Wrapping with errors.Is and errors.As
Published on
Go Error Wrapping with errors.Is and errors.As
Errors often cross several layers of a Go application. A low-level function may know that a file is missing, while a higher-level function needs to add context about which operation failed. Go error wrapping lets you add that context without losing information callers need for reliable handling. Why error strings are fragile Do not make program logic depend on error wording. Adding a filename or changing punctuation can break string comparisons even when the underlying condition is unchanged. Prefer semantic checks:
Golang Errors Backend Best Practices
chevron-up