Blogmark
Expand and Contract Pattern
via jbranchaud@gmail.com
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