gh-ost: GitHub's online migration tool for MySQL

Ideas you will meet

Databases

Schemas, indexes, query plans and choosing a store.

Deployment

Releases, feature flags, rollbacks and continuous delivery.

What we already know about this

Our own lessons on these ideas. Read them here before you start, so it makes sense.

• Storage, Queues and Real-time · 11 minChanging a live database: online indexes and schema changesHow to add an index or alter a large table without blocking traffic: concurrent index builds, shadow-table copies with change capture, cut-over, and how to roll back.Read hereClose

  The problem

  Tables grow, queries change, and sooner or later you must add an index or change a column on a table that is serving live traffic. A naive change can lock the table for minutes or hours. For a 2 billion row table that is an outage.

  The goal of an online change: keep reads and writes working, limit extra load, and be able to back out.

  Pattern 1: build the index concurrently

  Some databases can build an index while writes continue. PostgreSQL's CREATE INDEX CONCURRENTLY scans the table twice and waits for existing transactions, so it does not block writes.

  Trade-offs: it takes longer and uses more I/O; it cannot run inside a transaction block; if it fails it can leave an invalid index that you must drop before retrying. Watch for long-running transactions that make it wait.

  Pattern 2: shadow table plus change capture

  For schema changes that rewrite the table, many teams use a copy approach, used by tools such as gh-ost and pt-online-schema-change:

  1. Create a shadow table with the new structure.
  2. Backfill by copying rows in small chunks.
  3. Meanwhile capture every change to the original (from the replication log or with triggers) and apply it to the shadow table.
  4. When the copy has caught up, cut over: a brief swap that renames the shadow table into place.
  5. Keep the old table for a while for rollback.

  100%

  Figure SDA-SQR-02-1 · Online schema change with a shadow table · © forcsguy.com

  Safety checklist

  • Throttle by replica lag, CPU and connection counts; the migration must back off when production suffers.
  • Test on a copy of production-sized data first; time and I/O surprise people.
  • Verify with row counts and sampled checksums before cut-over.
  • Plan the cut-over window for low traffic; make it short and reversible.
  • Keep the old table for rollback until you are confident.
  • Watch replicas: large changes can cause replication lag, which in turn breaks read-after-write assumptions.

  Backward-compatible steps (expand and contract)

  Schema and code must change in an order that never breaks either:

  1. Expand: add the new column or index; old code still works.
  2. Deploy code that writes both shapes and reads the new one when available.
  3. Backfill old rows in batches.
  4. Switch reads fully to the new shape.
  5. Contract: remove the old column in a later release, once nothing reads it.

  100%

  Figure SDA-SQR-02-2 · Expand and contract · © forcsguy.com

  Real-world reading

  Related reading

  • PostgreSQL: CREATE INDEX (CONCURRENTLY)PostgreSQL Global Development Group · PostgreSQLDocumentation
  • gh-ost: GitHub's online schema migration tool for MySQLGitHubGitHub
  • gh-ost: GitHub's online migration tool for MySQLGitHub Engineering · GitHubCase Studies
  • Online migrations at scaleJacqueline Xu · StripeCase Studies

  Show 1 moreShow fewer

  • Use The Index, LukeMarkus Winand · use-the-index-luke.comCourses

  Open this lesson with its quiz and exercise

• Databases Deep Dive · 12 minReplication topologies and multi-region databasesSingle-leader, multi-leader and leaderless replication, how they handle conflicts and lag, and what changes across regions.Read hereClose

  Why replicate

  Replication keeps copies of data on several machines for three reasons: availability (survive a node failure), read scaling (serve reads from replicas) and latency (put data near users). The hard part is keeping copies consistent while writes continue. (Basics are in the replication and sharding lesson of the foundations course; this lesson compares the topologies.)

  Single-leader (primary and replicas)

  One node, the leader (primary or master), accepts writes and ships its changes to followers (replicas), which serve reads.

  100%

  Figure SDA-DDV-04-1 · Single-leader replication · © forcsguy.com

  • Synchronous replication waits for a follower before acknowledging: safer, slower, and a stalled follower can block writes. Asynchronous is faster but a failover can lose the latest writes. A common compromise: one synchronous follower, others asynchronous.
  • Failover must detect failure, choose the most up-to-date replica, redirect clients and ensure the old leader does not come back believing it still leads (split brain).
  • Replication lag causes stale reads. Techniques: read-your-writes (route a user's reads to the leader shortly after they write), monotonic reads (keep a user on one replica), and checking replica position.

  Multi-leader

  Several nodes accept writes (often one per region) and replicate to each other. Benefits: local write latency and continued operation if a region is cut off. Cost: write conflicts.

  Conflict handling options:

  • Avoid them: route all writes for one record to one leader (for example by user or region).
  • Last write wins by timestamp: simple, but silently drops one of two concurrent updates and depends on clocks.
  • Merge values (union of sets, counters) using mergeable data types (CRDTs; see the collaborative editing lesson).
  • Custom resolution in application code, or ask the user.

  Multi-leader also appears in offline-capable apps (each device is a leader) and collaborative editing.

  Before: single leader

  100%

  After: multi-leader across regions

  100%

  What changed

  • Users in each region write to a nearby database
  • Concurrent edits to the same row need conflict handling
  • More moving parts to run and monitor

  Leaderless (Dynamo-style)

  Any replica accepts writes; clients write to several replicas and read from several, using quorums (W + R > N) to get fresh data (see the key-value store lesson). Background anti-entropy and read repair fix stale copies; versions or vector clocks detect conflicts. It favours availability and is typical of Cassandra and DynamoDB-style stores.

  100%

  Figure SDA-DDV-04-2 · Choosing a replication model · © forcsguy.com

  Multi-region designs

  • Active-passive: one region serves writes; another is a warm standby. Simple; failover loses a little data (the replication lag) and takes minutes.
  • Active-active: several regions serve traffic. Requires either partitioning data by home region (each record has one home, cross-region reads are replicas) or true multi-leader conflict handling.
  • Global databases use consensus across regions (see the consensus lesson) for strong consistency at the cost of cross-region write latency; some offer tunable consistency per query.
  • Latency numbers matter: a cross-ocean round trip is around 100 ms or more, so synchronous cross-region replication adds that to every write.
  • Data residency laws may require keeping certain users' data in a region.

  Partitioning and replication together

  Real systems shard data into partitions and replicate each partition (a leader per partition spreads write load across nodes). Rebalancing, hot partitions and cross-partition transactions are the new problems (see sharding and the consistent-hashing lesson).

  Master/slave naming

  You will see "master/slave", "primary/replica" and "leader/follower" for the same single-leader idea; most current documentation prefers the last two.

  Checklist

  • What happens to writes if the leader or a region is lost, and how long?
  • How much data can we lose (RPO) and how long can we be down (RTO)?
  • How do we handle stale reads from replicas?
  • Who resolves conflicts, and how?
  • Have we practised failover?

  Real-world reading

  Related reading

  • Active-Active for Multi-Regional ResiliencyNetflix Engineering · NetflixCase Studies
  • Designing Data-Intensive ApplicationsMartin Kleppmann · O'ReillyBooks
  • Read Consistency with Database Replicas at ShopifyShopify engineers · ShopifyCase Studies
  • Uber Schemaless: A Scalable MySQL DatastoreUber Engineering · UberCase Studies

  Show 6 moreShow fewer

  • Mitigating MySQL Replication Lag and Reducing Read Load at GithubGithub engineers · GithubCase Studies
  • Cassandra architecture overviewApache Cassandra · Apache Software FoundationDocumentation
  • How Discord Stores Trillions of MessagesBo Ingram · DiscordBlogs
  • Spanner: Google's Globally Distributed Database (paper)Corbett et al. · GooglePapers
  • Scaling Multitenant Architecture Across Multiple Data Centres at Shopify - Weingarten, Engineering Lead at ShopifyShopifyYouTube
  • MIT 6.824: Distributed SystemsRobert Morris · MITCourses

  Open this lesson with its quiz and exercise

• Core Concepts: How Things Actually Work · 8 minDeployment strategiesRolling, blue-green, canary and feature flags: how to release changes with less risk, including database changes.Read hereClose

  Why strategy matters

  Most outages follow a change. A good deployment strategy limits the blast radius and makes rollback fast.

  The main strategies

  Recreate (big bang)

  Stop the old version, start the new one. Simple, but there is downtime and no easy rollback.

  Rolling update

  Replace instances a few at a time. No downtime and little extra capacity, but for a while both versions serve traffic, so they must be compatible. Rollback means rolling back the same way.

  Blue-green

  Run two identical environments. Blue serves live traffic; deploy to green, test it, then switch the router to green. Rollback is flipping back. Cost: double the capacity during the switch, and shared state (databases) still needs care.

  Canary

  Send a small share (1%, 5%, 25%...) of traffic to the new version, watch the metrics, then widen. If errors rise, roll back automatically. Needs good monitoring and traffic splitting.

  100%

  Figure SDA-CORE-12-1 · Canary · © forcsguy.com

  Feature flags

  Ship code switched off, and enable it for chosen users (or a percentage) at runtime. It separates deploying from releasing, and gives an instant kill switch. Remove old flags to avoid clutter.

  100%

  Figure SDA-CORE-12-2 · Four ways to put a new version live · © forcsguy.com

  Before: release to everyone at once

  100%

  After: canary: a few users first

  100%

  What changed

  • A bad release reaches few users before it is caught
  • Rolling back means shifting traffic back, not redeploying
  • You compare error rates between old and new

  Databases are the hard part

  Code can roll back instantly; data cannot. Use expand and contract:

  1. Expand: add new columns or tables, compatible with the old code.
  2. Deploy code that works with both shapes and writes to both if needed.
  3. Migrate data (backfill in batches).
  4. Contract: remove the old structure in a later release.

  Never combine a destructive schema change with the code that depends on it in one step.

  Checklist for safer releases

  • Automated tests and a staging environment that resembles production.
  • Health checks and automatic rollback on failure.
  • Monitoring on error rate and latency, watched during rollout.
  • Small, frequent changes over large, rare ones.
  • A written, practised rollback plan.

  More on this site

  Related reading

  • Blue Green DeploymentMartin FowlerBlogs
  • Ensuring rollback safety during deploymentsAmazon Builders' Library · Amazon Web ServicesBlogs
  • Feature Toggles (aka Feature Flags)Pete Hodgson · Martin FowlerBlogs
  • Going faster with continuous deliveryAmazon Builders' Library · Amazon Web ServicesBlogs

  Show 6 moreShow fewer

  • Online migrations at scaleJacqueline Xu · StripeCase Studies
  • Continuous Integration and Deployment with Bazel at DropboxDropbox engineers · DropboxCase Studies
  • HashiCorp BlogHashiCorp engineers · HashiCorpBlogs
  • Software Engineering at GoogleTitus Winters, Tom Manshreck and Hyrum Wright · O'Reilly / GoogleBooks
  • Scaling iOS CI with Anka at ShopifyShopify engineers · ShopifyCase Studies
  • Mozilla Release EngineeringMozilla Release Engineering engineers · Mozilla Release EngineeringBlogs

  Open this lesson with its quiz and exercise