Schema Versioning Patterns

In this lesson, you will learn how to maintain multiple schema versions to ensure your database remains compatible while your application rolls out new updates.

The Problem: Rolling Deployments

When you deploy a new version of an application, you often need to change the database schema (e.g., adding a column or splitting a table). If you have multiple application instances running, you might have some servers running "Version 1" code while others run "Version 2." If your schema change is destructive, the "Version 1" servers will crash. Schema versioning allows your database to support both versions simultaneously during the transition period.

The Pattern: Expand and Contract

The most effective way to handle this is the Expand and Contract pattern. Instead of making one breaking change, you break the migration into three distinct phases:

1. Expand: Add new schema elements (like a new column) while keeping the old ones. At this point, the application can write to both the old and new structures.
2. Migrate: Update the application code to use the new structure and backfill any missing data from old rows to new rows.
3. Contract: Once all instances are updated, remove the old, deprecated columns or tables.

For example, if you are renaming a column user_name to full_name, you wouldn't just rename it. You would:

• Step 1: Add the full_name column.
• Step 2: Deploy code that writes to both user_name and full_name.
• Step 3: Deploy code that reads from full_name.
• Step 4: Drop the user_name column.

-- Phase 1: Expand
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Phase 2: Application logic writes to both columns
-- Phase 3: Contract (only after confirming the app no longer uses the old column)
ALTER TABLE users DROP COLUMN user_name;

By decoupling the database change from the application deployment, you prevent downtime and ensure that your database can accommodate any server in your cluster, regardless of which version of the code it is running.

Key Takeaway: Never perform a destructive schema change in a single step; use an "Expand and Contract" approach to keep multiple versions of your application code compatible with your database.