How to migrate databases in production without downtime

Why changing production databases usually causes headaches

Anyone working in software engineering knows that changing a production database schema is often fraught with anxiety. In high-availability systems, stopping service for a few minutes to run a table alteration command means losing revenue, frustrating users, and generating urgent support tickets. In practice, this happens because applications and databases speak a rigid language, where any sudden structural change makes old code spit out cascading errors.

To bypass this problem without displaying maintenance screens at dawn, software architects adopt strategies based on gradual versioning and backward compatibility. This care is similar to what we apply when planning how to migrate databases from a monolith without downtime, ensuring real traffic keeps flowing while system foundations are swapped underneath. The secret is to separate physical data changes from logical application code modifications.

What is the expand contract pattern and how it works

The expand contract pattern consists of dividing any complex database change into three distinct stages: expansion, transition, and contract. In the first phase, called expansion, you alter the database solely to add new elements, like a new column or table, without removing or modifying existing ones. Thus, the current application keeps running seamlessly because nothing it relies on was broken or moved around.

In the intermediate phase, you update the application to start writing data to both the old and new formats simultaneously. Finally, in the contract phase, after all code has been updated and legacy data has been migrated in the background, you remove the old schema with total safety. This flow ensures the system never goes down, as the database never stops fulfilling requests expected by the application.

Step-by-step to apply expansion without interrupting service

The first practical step to apply this technique is creating the new structure in the database without touching existing code. Imagine you need to rename the full_name column to first_name and last_name in a heavily accessed users table. If you simply alter the column directly, the current system will fail when trying to fetch a missing field.

To solve this, you run a simple SQL command to add the new columns allowing null values, as shown in the example below:

ALTER TABLE users ADD COLUMN first_name VARCHAR(100);
ALTER TABLE users ADD COLUMN last_name VARCHAR(100);

Once executed, the database gains new workspace, but the application continues using the old full_name field without noticing any change in the production environment.

Updating application code with dual writing

With new columns available in the database, the second step requires updating application code to adopt dual writing. In practice, whenever a user registers or updates their profile, the system populates both the old column and the newly created columns. This behavior ensures information remains redundant and safe in both places during the transition period.

To ensure complex queries remain performant during this duplication phase, it pays to review indexation on affected tables, following principles similar to those discussed when analyzing how to optimize queries with composite indexes. A good index prevents dual writing from generating performance bottlenecks and keeps API response fast for end users.

Below is an illustrative code example demonstrating how to perform this dual insertion safely within your application service layer:

async function updateUserProfile(userId, fullName) {
  const parts = fullName.split(' ');
  const firstName = parts[0];
  const lastName = parts.slice(1).join(' ');

  await db.query(
    'UPDATE users SET full_name = $1, first_name = $2, last_name = $3 WHERE id = $4',
    [fullName, firstName, lastName, userId]
  );
}

Migrating legacy historical data in the background

While the new application writes to both places, old records already in the database still have data populated only in the legacy column. To resolve this gap without freezing the system, you must run a background migration script, often called a backfill. This script reads records in small batches and gradually populates the new columns, respecting server CPU and disk consumption limits.

This approach prevents the database from locking up due to excessive locks on giant tables. When the script finishes scanning the entire table, it means 100% of historical data has the new format populated and ready for consumption by upcoming software versions.

Contracting and removing legacy code safely

The last step of the process is the contract, where you clean up clutter left by previous phases. After application code has been updated to read exclusively from the new first_name and last_name columns, and the migration script has finished its job, the old full_name column loses practical utility.

At this point, you can perform a new database alteration to remove the old column and clean up legacy application code with total peace of mind. This operational discipline turns a stressful task into a predictable engineering routine, enabling continuous value delivery without sacrificing business stability. To explore these architectural concepts further in production, see our related technical analysis on How to monitor containers in real-time with eBPF on Linux.

Final thoughts on zero-downtime migration

Adopting the expand contract pattern requires discipline from the engineering team, as we turn a task that would be done in a single risky command into a process split into controlled stages. Although it requires more temporary lines of code, the gain in resilience and peace of mind widely compensates for the extra effort.

By mastering this technique, your team gains autonomy to evolve complex system data models at any time of day, without depending on nighttime maintenance windows or extended weekends. The result is an agile, reliable product prepared to grow sustainably.