Blogmark

The Expand and Contract pattern is a general database schema migration pattern to minimize locking and downtime that occurs when irreversibly modifying a production database schema. This is also known as the Parallel Change pattern.

Certain schema changes and operations can involve full table rewrites and other locking that cannot be tolerated by a system with production workloads. These can sneak up on us in production if we don't know about them because the same schema changes against non-production data may be instantaneous.

This pattern is necessary for something as simple as set not null on an existing column. You'd use this pattern to migrate a primary key from int to bigint. This would also be used for more complex operations like splitting out and normalizing a monolithic table to multiple tables or retroactively partitioning a table.

So, what is the "Expand and Contract" pattern?

There is no exact recipe. Each migration can require devising a custom approach. The general pattern holds and looks like this:

1. Expand the schema

Expand the existing schema to accommodate the "new" thing. This is often adding a new column or table which we will eventually swap to.

2. Sync write operations

Ensure write operations are kept in sync between the existing and "new" thing. This can either be handled by setting up database triggers now or by updating app code in the next step to write both places.

3. Expand application code interface

Update all application code to start writing to the "new" column or table. If you didn't set up triggers in the previous step, then you'll need the app code to also continue to write to the existing column or table.

4. Backfill new schema

Backfill the "new" column or table with all the existing data. Depending on the nature of the migration some sort of normalization or default value may need to be applied for certain records during this operation.

5. Cut reads over to new schema

At this point, the app is still reading from the existing schema. If this is an in-place migration, then this step may primarily be a transaction double-renaming so that the app is still referencing the same-named column or table, but in actuality is the "new" entity. The works if you're using triggers to stay in sync which you'll update or remove in this same transaction. Be more careful if the app is still writing both places.

Alternatively, you may opt for deploying app changes that cut all reads over to the new schema. No app code should read from the existing schema at this point, though some sync code may still be in place.

6. Ensure app works as expected

Beyond any automated testing that was enforced during previous steps, you'll want to thoroughly smoke test your app. It is now running on the new schema, reading migrated, backfilled data. Is everything looking good and checking out?

7. Remove writes to existing entities

If you haven't already, this is the time to remove any triggers or app code that are still syncing old and new entities now.

8. Contract the schema

We no longer need to old column or table that we started with. It is already growing out of date as our system continues to field write operations. The column or table can now be dropped.

And that's it. That's generally how to apply the Expand and Contract pattern.

Note: you should definitely take a backup before getting started with this work or more surgically, right before you cut reads over.

There is a great resource from Prisma on this as well: Using the expand and contract pattern | Prisma's Data Guide