Migrations at scale: gh-ost
This course covers schema migrations on large tables and during deploys. It explains which Postgres migrations only touch the catalogue and which rewrite or scan a table under an ACCESS EXCLUSIVE lock. It shows how gh-ost copies rows into a shadow table while reading the MySQL binary log, then compares the Postgres tools pg_repack, pg-osc and pgroll. It also covers the deploy window where old and new code share one schema, expand and contract, batched backfills, lock_timeout, and a review of this application's current setup.
What you'll learn
- ✓Which Postgres migrations are instant and which rewrite or scan the table
- ✓How gh-ost uses a shadow table, batched copies and the binary log
- ✓Built-in Postgres options: CREATE INDEX CONCURRENTLY, NOT VALID constraints and lock_timeout
- ✓What pg_repack, pg-osc and pgroll do and how they capture changes
- ✓Expand and contract for changes that break the deployed code
- ✓Batched backfills with in_batches, run in a job instead of a migration
The migration that locks the table
Most migrations are instant, which is why the ones that aren't come as a surprise. What decides it isn't the size of the table. It's whether the database has to rewrite it.
Read the full lesson free →Curriculum
7 lessonsThe migration that locks the table
Which migrations only touch the catalogue, which rewrite the table, and why the ACCESS EXCLUSIVE lock causes outages.
How gh-ost works
How gh-ost builds a shadow table, copies in batches, reads the binary log and swaps names.
The Postgres tools
Built-in Postgres features for safe migrations, plus what pg_repack, pg-osc and pgroll each do.
The deploy is the hard part
Why old and new code share one schema during a deploy, and how expand and contract handles it.
Backfilling without a lock
Filling a new column in short batched transactions, outside migrations, and adding NOT NULL without a long lock.
What this application does today
A review of this application's migrations, its new five-second lock_timeout, and the risk of renaming a column.
Putting it together
Separating slow migrations from breaking ones, plus a challenge to classify schema changes with migration_plan.