Zero-Downtime Database Migrations

Hello! Welcome to the next lesson in our journey to master production-ready microservices.

In our recent lessons, we've explored sophisticated zero-downtime deployment strategies like blue-green and canary releases. These are excellent for deploying new versions of your stateless application code. However, they share a critical assumption: that the database schema remains compatible. What happens when your new feature requires a database change that would break the currently running version of your application? This is a common and challenging scenario in real-world systems and a frequent topic in senior engineering interviews.

Today, we'll confront this challenge directly. Your learning outcome is to describe strategies for handling database schema migrations in a zero-downtime deployment context, with a special focus on the powerful expand/contract pattern. Mastering this decouples your application deployments from your database changes, enabling true continuous delivery even when your data model evolves.

1. The Fundamental Challenge: The Tyranny of a Shared Database

When you perform a rolling or canary deployment, you temporarily have two different versions of your microservice (let's call them V1 and V2) running at the same time and talking to the same database.

This creates a conflict:

• If you apply a "breaking" schema change (e.g., renaming a column) before deploying your new code, the old V1 instances will fail because they don't know about the change.
• If you deploy your new V2 code before applying the schema change, the V2 instances will fail because the database schema they expect doesn't exist yet.

This leads us to the Golden Rule of Zero-Downtime Database Migrations: application and database deployments must be decoupled and managed in a way that ensures both backward and forward compatibility at all times.

• Backward Compatibility: A new schema change must not break the old version of the application.
• Forward Compatibility: A new version of the application must be able to work with the old database schema.

To achieve this, we need a methodical, multi-step approach.

2. The Expand-Contract Pattern (Parallel Change)

The most robust and widely used strategy for handling breaking schema changes is the Expand-Contract Pattern. Instead of making a destructive change in one step, you break it down into a sequence of safe, non-breaking steps.

The process has three major phases: Expand, Transition, and Contract.

Let's make this concrete with a common interview scenario: renaming a column. Imagine we need to rename the user_name column to username in our users table.

Here is a step-by-step breakdown of how to apply the pattern.

Phase 1: Expand (Additive Schema Change)

The first step is to expand the database schema so it can support both the old and new application versions.

1. Action: Add the new username column, making it NULL-able.

   ALTER TABLE users ADD COLUMN username VARCHAR(255) NULL;
   

2. State: The users table now has both user_name and username columns.
3. Compatibility: This is a safe, backward-compatible change. The V1 application code is completely unaware of the new username column and continues to read from and write to user_name without issue.

Phase 2: Transition (Application & Data Migration)

This is the most complex phase, involving changes to the application code and migrating the data.

1. Action (Application Code): Deploy a new version of the application (V2) that understands both columns.
   • Writes: Any code that creates or updates a user now writes to both user_name and username. This is often called a "dual write."
   • Reads: Code that reads user data is changed to prioritize the new username column but falls back to the old user_name column if the new one is NULL. This ensures it can read both old and new records correctly.

   // Simplified Spring Boot Entity logic
   public class User {
       // ...
       private String userName; // old column
       private String username; // new column
   
       public String getEffectiveUsername() {
           // Prioritize new column, fall back to old
           return (this.username != null) ? this.username : this.userName;
       }
   
       public void setUsername(String name) {
           // Dual write
           this.username = name;
           this.userName = name;
       }
   }
   

2. Action (Data Migration): While V2 is running, run a background script to backfill the data for existing records. To avoid locking the table and impacting performance, this should be done in small batches.

   -- Run this repeatedly in batches until all rows are updated
   UPDATE users SET username = user_name WHERE username IS NULL LIMIT 1000;
   

3. State: At this point, all new writes are populating both columns, and old data is being progressively migrated. The system is fully functional, supporting both V1 (which might still be running during the rollout) and V2.

Phase 3: Contract (Cleanup)

Once the V2 application is fully rolled out and all historical data has been backfilled, you can begin removing the old structures. This is also done in multiple steps.

1. Action (Application Code): Deploy a new version (V3) that only reads from and writes to the new username column. The code's dependency on user_name is now completely removed. The dual-write logic and read fallback are gone.
2. State: The application logic is now clean and only references the new schema. The old user_name column is no longer being read from or written to by the application.
3. Action (Schema Cleanup): After V3 is verified to be stable in production, you can safely run the final schema migration to drop the old column.

   ALTER TABLE users DROP COLUMN user_name;
   

4. Final State: The migration is complete. The application and database are in a new, consistent state with zero downtime.

To see these steps laid out clearly, including an alternative method for syncing data with database triggers, please review the following resource.

Test your understanding!

In Phase 2 of the Expand-Contract pattern, why is it critical for the application to perform "dual writes" (writing to both the old and new columns)? What could go wrong if the new application version (V2) only wrote to the new column?

Show answer

Dual writes are critical to support seamless rollbacks and concurrent versions. If V2 only wrote to the new column, any data it creates would be invisible to the old application version (V1), which might still be serving traffic during a rolling update. Furthermore, if you needed to roll back from V2 to V1 due to a bug, all the data created by V2 would be effectively lost from the perspective of the now-active V1 code. By writing to both columns, you ensure data is preserved and visible to all running application versions.

3. Other Key Strategies and Considerations

The Expand-Contract pattern is your go-to for complex changes, but other techniques are essential parts of a robust migration toolkit.

Additive-Only Migrations

The simplest migrations are purely additive. Adding a new table or a new nullable column doesn't break old code. For adding a new NOT NULL field, you can use a multi-step "nullable column trick," which is essentially a mini expand-contract pattern:

1. Add the column as nullable.
2. Deploy code that starts writing to it.
3. Backfill the column for all existing records.
4. Add the NOT NULL constraint.

Online Schema Migration Tools

For tables with billions of rows, even a simple ALTER TABLE command can lock the table and cause an outage. For these scenarios at scale, specialized tools are used.

• How they work: They create a "ghost" table with the new schema, copy data in small chunks, use triggers or the database's replication log to capture live changes, and finally perform an atomic swap of the original and ghost tables.
• Examples: pt-online-schema-change and gh-ost for MySQL, or using CREATE INDEX CONCURRENTLY in PostgreSQL to avoid locking on index creation.

Blue-Green Database Deployments

This is a heavyweight strategy for major changes, like upgrading a database version.

• You set up a complete, replicated "Green" database alongside your "Blue" production one.
• You stop replication and apply the breaking schema changes to the Green database.
• You verify Green, and then switch application traffic over.
• This is very safe but also very expensive, as it requires duplicating your entire database infrastructure.

The Power of Feature Flags

You can enhance the Expand-Contract pattern with feature flags. In our example, the read behavior in V2 could be controlled by a flag:

if (featureFlags.isEnabled("use-new-username-schema")) {
    return user.getUsername();
} else {
    return user.getUserName();
}

This gives you an "emergency brake." If you discover a problem with the new column's logic, you can instantly flip the flag to force the application to read from the old column, mitigating the issue without needing a full rollback deployment.

4. Interview Practice: Putting It All Together

Let's simulate an interview question that requires you to apply these concepts.

Senior Engineer Interview Question

"Our e-commerce platform stores product prices in a DECIMAL column in the products table. We've decided to switch to storing prices as an INTEGER representing cents to avoid floating-point inaccuracies. This is a critical, high-traffic table. Outline the exact steps you would take to migrate this price column from DECIMAL to INTEGER with zero downtime."

Show detailed answer

"This is a classic breaking schema change, and I would use the Expand-Contract pattern to handle it safely with zero downtime. Here are the precise steps I'd follow, broken down into distinct deployments:

Deployment 1: Expand the Schema

1. Create and run a schema migration to add a new nullable integer column named price_in_cents to the products table.

   ALTER TABLE products ADD COLUMN price_in_cents INTEGER NULL;
   

   This change is backward compatible. The current application is unaffected.

Deployment 2: Transition the Application and Data

1. Modify the application code to handle both price representations.
   • Write Path: When a product price is updated, the application will write to both columns. It will save the integer value to price_in_cents and convert the cents value back to a decimal for the old price column.
   • Read Path: The application will be updated to read from price_in_cents. If that value is NULL (for an old record not yet migrated), it will read from the price column and convert it to cents in memory.
2. Deploy this new application version.
3. Once deployed, start a background job to backfill the price_in_cents column for all existing products. This must be done in small, throttled batches to avoid overloading the database.

   UPDATE products SET price_in_cents = price * 100 WHERE price_in_cents IS NULL LIMIT 500;
   

   We would monitor this job until it has processed all rows.

Deployment 3: Contract the Application Logic

1. Once the backfill is 100% complete and the application has been stable for a period, we'll do a cleanup deployment.
2. The application code is modified again to only read from and write to the price_in_cents column. All references and logic related to the old price column are removed.
3. Deploy this simplified application version.

Deployment 4: Contract the Schema

1. After the final application version is confirmed to be stable and running correctly, we can run the last migration.
2. Create and run a schema migration to drop the old price column.

   ALTER TABLE products DROP COLUMN price;
   

This multi-phase process, while complex, guarantees that at no point during the migration is there an incompatibility between the running code and the database schema, thus achieving zero downtime."

Conclusion

Handling database migrations gracefully is a hallmark of a mature engineering organization and a skill expected of senior engineers. You can't achieve true continuous delivery without it.

Key Takeaways:

• The core challenge of database migrations is maintaining compatibility while multiple application versions are live.
• The Expand-Contract Pattern is the most reliable strategy for handling breaking schema changes. It involves three phases:
  1. Expand: Additively change the schema to support both old and new versions.
  2. Transition: Deploy code that can handle both schemas (dual writes, read fallbacks) and migrate data.
  3. Contract: Clean up the code to remove dependency on the old schema, then clean up the database by dropping the old structures.
• Simpler changes can often be purely additive.
• Feature flags provide a critical safety net, allowing you to quickly toggle between old and new data paths without a full deployment.
• For very large tables, online schema migration tools are necessary to avoid database locks.

In this lesson, we discussed using feature flags as a safety mechanism for database migrations. But feature flags are much more powerful—they can be used to decouple feature releases from deployments entirely. In our next lesson, we will dive deep into this topic and implement feature flags within a Spring Boot application to decouple feature releases from deployments.