Large scale migrations: gh-ost
Large schema changes behave differently from small ones because lock duration grows with table size. This course covers Postgres lock modes and the lock queue, the safe subset of changes such as concurrent indexes and NOT VALID constraints, and lock_timeout with strong_migrations as guardrails. It explains how gh-ost copies a MySQL table into a shadow table by reading the binary log, and how to run it with throttling and a postponed cut-over. It closes with pg-osc, expand/contract, batched backfills and a migration runbook.
What you'll learn
- ✓Postgres lock modes, lock duration and the lock queue
- ✓Non-blocking rewrites of indexes, constraints and NOT NULL, enforced by strong_migrations
- ✓How gh-ost uses a shadow table and the MySQL binary log
- ✓Running gh-ost with throttling, chunk sizing and a postponed cut-over
- ✓Expand/contract and batched backfills on Postgres
- ✓A runbook for rehearsing, watching and aborting a migration
The migration that locked the table
A deploy goes out at 14:02. It carries one migration:
Read the full lesson free →Curriculum
7 lessonsThe migration that locked the table
A deploy that locked a 240-million-row table for eleven minutes shows why lock duration grows with table size.
Lock modes, and the queue behind them
ShareLock versus AccessExclusiveLock, why lock strictness and duration differ, how the lock queue blocks reads, and lock_timeout.
The safe subset: changes that never block
Concurrent index builds, NOT VALID constraints, a scan-free SET NOT NULL, changes that stay expensive, and strong_migrations in CI.
Shadow tables: how gh-ost works
How shadow-table copying works, why pt-online-schema-change triggers add write latency, and how gh-ost reads the MySQL binary log.
Running gh-ost safely
Dry runs, load and lag throttling, chunk size, postponed cut-over, hooks, the control socket and cases gh-ost cannot handle.
Postgres, pg-osc, and expand/contract
pg-osc on Postgres, the five steps of expand/contract, safe column removal and batched, resumable backfills outside migrations.
The migration runbook
A runbook covering table size, locks, rehearsal on a snapshot, monitoring queries, abort commands and finishing the contract step.