Refactoring Legacy Schemas

In this lesson, you’ll learn how to modernize a monolithic, unstructured legacy database without triggering system-wide downtime or catastrophic data loss.

Refactoring a legacy schema—often described as "replacing the engine while the plane is flying"—requires a cautious, incremental approach. The biggest risk is the "Big Bang" migration, where you attempt to move everything at once. Instead, we use the Expand and Contract pattern to manage risk.

The Expand and Contract Pattern

This strategy allows you to transition from an old schema structure to a new one while maintaining full backward compatibility. It follows three distinct phases:

1. Expand: You modify the schema to support both the old and new structures simultaneously. If you are splitting a bloated Users table into Users and UserProfiles, you create the new table but keep the data synchronized.
2. Migrate: You update your application code to write to both locations, then use background processes to backfill historical data.
3. Contract: Once you verify that the new schema is stable and the old structure is no longer receiving traffic, you safely drop the legacy columns or tables.

Illustration: Refactoring an Email Column

Imagine you have a legacy Users table where email is a simple string, but you now need to support multiple email addresses. You shouldn't just ALTER TABLE and delete the old column. Instead:

-- Step 1: Expand (Add new table, keep original column for now)
CREATE TABLE UserEmails (
    user_id INT,
    email_address VARCHAR(255),
    is_primary BOOLEAN
);

-- Step 2: Migrate (Dual-write in app code, sync back data)
-- Use a Trigger or Application-level logic to copy current 'email' to 'UserEmails'
INSERT INTO UserEmails (user_id, email_address, is_primary)
SELECT id, email, TRUE FROM Users;

-- Step 3: Contract (Only after verifying application reads from UserEmails)
ALTER TABLE Users DROP COLUMN email;

By decoupling the database change from the application deployment, you ensure that if the new schema causes performance issues or data integrity errors, you can immediately revert the application code without needing to restore a massive, outdated database dump.

Key Takeaway: Modernize legacy schemas by using the Expand and Contract pattern to ensure continuous availability and seamless rollbacks.