Database Migrations (4 blogmarks)

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

During Gülçin Yıldırım Jelínek's talk "Anatomy of Table-Level Locks in PostgreSQL", she mentioned that the common "Expand and Contract" schema migration approach to applying not null to an existing column will still involve an Access Exclusive table lock. This is because PostgreSQL needs to do a full table scan to ensure there are no instances of a null.

She goes on to mention that we can do better than that. We can further reduce locking with an additional step. If we first add an invalid not null check constraint for the column and eventually validate it after backfilling the entire table, then the alter table statement for adding the not null constraint will only need a Share Update Exclusive table lock. This means we can generally still perform SQL operations that read and update data.

According to the alter table docs:

SET NOT NULL may only be applied to a column provided none of the records in the table contain a NULL value for the column. Ordinarily this is checked during the ALTER TABLE by scanning the entire table; however, if a valid CHECK constraint is found which proves no NULL can exist, then the table scan is skipped.

Here is the relevant commit and commit message:

If existing CHECK or NOT NULL constraints preclude the presence
of nulls, we need not look to see whether any are present.

Which was added in the PostgreSQL 12 release.

This approach is also recommended by the strong_migrations gem for Rails.

It's cool to read through the internals of different rake tasks that are available for interacting with a Rails database and database migrations.

For instance, you can see how db:migrate works:

  desc "Migrate the database (options: VERSION=x, VERBOSE=false, SCOPE=blog)."
  task migrate: :load_config do
    ActiveRecord::Tasks::DatabaseTasks.migrate_all
    db_namespace["_dump"].invoke
  end

First, it attempts to run all your migrations. Then it invokes _dump which is an internal task for re-generating your schema.rb (or structure.sql) based on the latest DB schema changes.

Meant to be deleted

I love this idea for a custom rake task (rails db:migrate:archive) to occasionally archive past migration files.

# lib/tasks/migration_archive.rake
namespace :db do
  namespace :migrate do
    desc 'Archives old DB migration files'
    task :archive do
      sh 'mkdir -p db/migrate/archive'
      sh 'mv db/migrate/*.rb db/migrate/archive'
    end
  end
end

That way you still have access to them as development artifacts. Meanwhile you remove the migration clutter and communicate a reliance on the schema file for standing up fresh database instances (in dev/test/staging).

Data migrations

They don't go into much detail about data migrations. It's hard to prescribe a one-size-fits-all because sometimes the easiest thing to do is embed a bit of data manipulation in a standard schema migration, sometimes you want to manually run a SQL file against each database, or maybe you want to set up a process for these changes with a tool like the after_party gem.

Reversible migrations

For standard migrations, it is great to rely on the change method to ensure migrations are reversible. It's important to recognize what kinds of migrations are and aren't reversible. Sometimes we need to write some raw SQL and for that we are going to want up and down methods.