---
topic: system-design
author: Crashtech Editorial
published: Nov 14, 2026 · read: 5 min
---

Running a Petabyte MySQL Fleet: Pods, Replica Read Consistency and 30-Minute Restores

Shard by tenant, move tenants with a log-tailing mover, route related reads to one replica, and back up with disk snapshots instead of file copies.

– –

At small scale a database problem is a slow query. At Shopify’s scale it is a merchant who cannot take orders during a flash sale. The interesting part of their approach is how ordinary the techniques are: a sharding key, a log tailer, a hash and a snapshot, applied with discipline.

Pods: a shard plus a copy of the app

Each pod is an isolated copy of the core application and one MySQL shard. A request arrives at a load balancer that looks the shop up in a routing table and forwards it to that shop’s pod. This resembles a cell-based architecture: the cells are identical code, but each holds different tenants’ data.

The model works because nearly every table has a shop_id. That column is a natural sharding key, and it keeps one merchant’s data together, so queries rarely cross shards.

The problem: tenants are not equal

Over time some shards fill with large, busy shops and others sit nearly idle. Both are expensive:

  • Over-used shards risk failure, and a flash sale makes it worse.
  • Under-used shards waste money.

Even distribution by shop count does not work, because one shop can outweigh a hundred. The rebalancing decision is data driven, using:

InputWhy it matters
Historical utilisation per shardShows who is actually hot, and when
Shop sizeLarge shops are harder and slower to move
Time to moveFits moves into maintenance windows
Flash-sale patternsA quiet shop may spike predictably

Moving a shop with no downtime

The move must satisfy three constraints at once: the storefront stays available, no write is lost, and it finishes in reasonable time. The tool Shopify built and open-sourced, Ghostferry, works in three phases.

  1. Copy and tail, together

    Iterate over each table, select that shop’s rows and write them to the target in small separate transactions. At the same moment, tail the source’s binary log, the authoritative record of every change, and replay changes on the target so late writes are not missed.

  2. Cut over briefly

    When the replay queue is nearly empty, stop writes for that shop on the source, record the final binary log position as the stopping point, and replay the remaining events up to it. Source and target are now identical. This is the only moment of possible visible pause, so it is kept short.

  3. Switch routing, verify, prune

    Update the routing table to point the shop at its new pod. Verify the move, and only then delete the shop’s stale data from the old pod.

This is the same snapshot-then-tail-the-log shape used by most online migrations, with the routing table as the final switch.

Delete last

Keeping the old copy until verification passes costs a little disk and buys a rollback path. Pruning stale data is the very last step, never part of the cutover.

Advertisement

Reading from replicas without lying to users

Read replicas offload reads from the primary, but they lag. Time-sensitive reads, such as a user checking their just-saved profile, still go to the primary. The harder case is a request assembled from several reads.

Order for Item A and Item B
  read inventory(A) -> replica 1 (up to date)
  read inventory(B) -> replica 2 (lagging; still shows stock after B sold out)

Each answer is individually plausible, together they are inconsistent. Two textbook fixes were rejected:

OptionWhy Shopify did not use it
Tight consistency (every replica fully current)Removes the point of replicas and lets one down replica fail writes
Causal consistency with transaction idsNeeds special software on every replica to report its position

They chose monotonic read consistency: reads in a series follow a consistent timeline, even if that timeline is slightly behind. The mechanism is cheap:

  1. The application attaches a unique id to related requests, passed in a query comment as consistent_read_id.
  2. The database proxy extracts the id and hashes it to choose a replica.
  3. Every request with that id goes to the same replica, so successive reads never go backwards.

The trade-off, which they accepted, is that a replica outage can briefly break the guarantee for the requests that were pinned to it.

Backups that restore in minutes

The first design was conventional: run Percona XtraBackup, write files, archive them to cloud storage. It was robust and slow. Backing up a petabyte took long, and restoring one shard took over six hours, which meant a very high recovery time objective.

The redesign used what the platform already offered. The servers run on cloud VMs with persistent disks, and the platform can snapshot a disk at the block level.

Back up: a 15-minute job frequent snapshots
A scheduled job calls the cloud API to snapshot each MySQL disk across all clusters and regions, nearly 100 times a day per shard, thousands of snapshots daily.
Retain: a second job cost control
A separate retention job deletes snapshots by policy, keeping the important ones and keeping storage cost bounded.
Restore: mount, start minutes
Create a new disk from the latest snapshot, start MySQL on it, let replication catch up. Reported recovery time is under 30 minutes including lag.
Bonus: fast replicas same mechanism
A recent snapshot is also the quickest way to clone a new replica with almost current data.

Whatever storage you use, a backup you have not restored is a hope. Practise the restore.

Lessons you can use at any size

Do

Pick a shard key your data already has (tenant id), keep a routing table you can change, copy-and-tail for every move, route related reads together, and measure recovery time by actually restoring.

Don't

Demand perfectly consistent replicas, or assume a backup works because the job reports success. Both ideas fail at exactly the moment you need them.

Takeaway

Large-scale database operation is mostly three unglamorous capabilities done well: moving tenants live, reading from replicas without surprising users, and restoring fast. Tenant-keyed shards, a log-tailing mover, a hash that pins related reads, and storage-level snapshots are all simple, and together they are what makes zero visible downtime a routine property instead of a heroic one.

Advertisement

Frequently asked questions

Why shard a multi-tenant database by tenant id?

When almost every table belongs to one tenant, the tenant id is a natural shard key: one tenant's data stays together, queries rarely cross shards, and tenants can be moved as a unit. The risk is uneven tenant sizes, which is why shards need rebalancing.

What is replication lag and why does it cause bugs?

Replicas apply the primary's changes slightly later, so a read from a replica may not show a write that just happened. A user can save a change and not see it, or an app can combine reads from replicas at different points in time and get an inconsistent picture.

What is monotonic read consistency?

A guarantee that successive reads in a sequence never go backwards in time. You can achieve it cheaply by routing all related reads to the same replica, chosen by hashing a request-group id, so they see a consistent, if slightly stale, timeline.

Why are disk snapshots faster to restore than file-based backups?

A snapshot is a block-level copy managed by the storage layer, so restoring means creating a disk from it and starting the database on top, with no data to stream or replay at the file level. File-based backups must be downloaded, unpacked and often prepared, which scales with data size.

Sources & further reading

/* Comments */