Shard or Do Not Shard: Pinterest's Hand-Built MySQL Fleet
By September 2011, Pinterest was growing faster than any startup before it, and every piece of its infrastructure was over capacity. The NoSQL systems it had bet on for automatic scaling all eventually broke catastrophically, and a large fleet of MySQL read replicas caused bugs whenever they lagged behind. The rebuild launched in early 2012 and was still the core store three and a half years later. It is hand-sharding in its clearest form: the data is split into 4,096 small databases spread across pairs of machines, with a shared config (in ZooKeeper) recording which machine holds which shards. Every object also gets a 64-bit ID that carries its own address: 16 bits of shard, 10 of type, 36 of row. Joins moved up into the application, schemas moved into JSON, production reads never touch a standby copy, and capacity grows by moving some of a busy machine's shards onto a new machine. Finding any object became a matter of simple math.
Ride the 2011 growth curve on one box until it hits the ceiling, then bet on auto-scaling clustering and watch it break the way it broke on Pinterest. Then shard by hand: build a real Pin ID bit by bit, run a join in the application, and split a hot machine's shard range when the load comes back.
Problem
The dataset is the largest human-curated interest graph in the world: more than 50 billion Pins saved to over a billion boards, plus likes, follows, and the home feed those relations feed. In 2011 the growth curve went vertical, and by that September every piece of infrastructure was over capacity at once. The failures came in two flavors. The auto-scaling bets, several NoSQL databases running at once, eventually broke catastrophically; the team's best-known advice from that era was simple: try really hard to just use MySQL. The usual MySQL workaround, adding lots of read replicas, caused its own kind of bug: replicas fall behind the primary, and that delay plus caching produces errors that are occasional, impossible to reproduce, and everywhere.
The requirements for the rebuild are notable for what they exclude:
- Stability, easy operation, and room to scale to the moon from a small start.
- All Pinner-created content reachable at all times.
- A predictable order, like 'give me N Pins of this board by newest first'.
- Best-effort updates only: no cross-shard all-or-nothing writes and no global consistency, with eventual consistency left as extra machinery to add on top later.
Once data spans databases, joins, foreign keys, and global indexes are gone; load balancing has to move whole virtual shards, never row by row; and production must only ever talk to the primary. The team wrote down what a distributed MySQL could actually promise, and built within those limits instead of pretending they weren't there.
Solution
The fleet started as eight rented Amazon servers (EC2), each carrying a slice of the 4,096 virtual shards. Each shard is an ordinary MySQL database, named db00000 through db04095, with several databases per server. Every server has a standby twin kept in sync (master-master replication) that production never reads from. A small config table maps ranges of shards to machines, for example {range: (0,511), master: MySQL001A, standby: MySQL001B}. It lives in a shared config service (ZooKeeper) and changes only when shards move or a host dies. Failover is a human running a script to promote the standby; even three and a half years in, there was still no automatic failover.
Every object gets a 64-bit ID that is also its address: 16 bits for the shard, 10 for the type, 36 for the row within that shard, plus two spare bits the author keeps out of chip-design habit. Reading an ID tells you where the data is (shard 3429, type Pin, row 7075733). Creating an object builds an ID: pick a shard (preferably the board's, so related data sits together), insert the row, and MySQL's built-in counter hands back the row number that completes the address. Globally unique IDs come for free. The paired decision is that placement is permanent: once data lands in a shard it never leaves, and extra capacity comes from moving shards between machines, not data between shards.
Each shard holds the same tables in two shapes. Object tables (pins, boards, users) are a row number plus a JSON blob, a bag of fields stored as text. New fields are added by telling the services to read them, using a default when an older record doesn't have the field yet. That is how a company this size did only about one schema change (an ALTER) in three years. Mapping tables (like board_has_pins) are three columns, from-ID, to-ID, and a sequence number, indexed together and stored on the shard of the object the mapping starts from (here, the board), using unix timestamps as an easy always-increasing sequence. Reading a board is a join done in the application: ask the mapping on the board's shard for 50 pin IDs, then fetch those 50 pin objects. Because the join lives in the application, each half gets its own cache: the pin objects in memcache, the board-to-pins lists in Redis.
Capacity grows three ways, in rising order of effort:
- Upgrade the machines (more space, faster disks, more memory).
- Open dormant ranges: only 4,096 of the 65,536 possible shards existed at launch, and new servers later opened shards 4,096 through 8,191.
- Split a hot machine's range: copy MySQL001A to a new pair, then flip the config so each machine carries half the shards.
For lookups that arrive by something other than an ID (Facebook IDs, emails, IP addresses), a parallel 'mod shard' hashes any key to a shard number, giving up the ID system's nicer properties in return. The system shipped only after a brutal migration: a script copies the data, and you run it again and again until nothing is left behind, because some data always is. The closing line is the whole design in one breath: no all-or-nothing writes, no isolation, no guaranteed consistency in every case, and reliability through simplicity, because the thing just works.
Tradeoffs
- Choosing boring, proven technology flips the usual trade-off. Choosing mature MySQL over auto-scaling stores meant taking on distribution (sharding, placement, rebalancing) as application-level work, in exchange for never again debugging an immature storage engine's catastrophic failure at 3 a.m. Pinterest took on predictable complexity it created itself and got rid of unpredictable complexity it would have inherited, and the author's comment about having 'the scars to prove it' suggests they would choose the same way again.
- Encoding placement in the ID makes location free and permanent in the same stroke. Any client can find any object with a little bit math, with no lookup service and no hash ring to consult, and no object can ever change shards, because its address is baked into every reference to it. Hot spots can only be relieved a whole shard at a time (move or split the range), so a single viral board stays wherever it was born.
- Application-layer joins trade the database's guarantees for the architecture's freedom. Every relation costs two queries, and the application itself has to keep references valid, since the post is frank that the system offers no all-or-nothing writes, no isolation, and no guaranteed consistency in every case. In exchange, each half of a join scales on its own and gets its own cache: objects in memcache, mappings in Redis, chosen to fit each access pattern rather than dictated by the engine.
- JSON blobs make the schema a convention the services agree on, not a rule the database enforces. Roughly one schema change in three years, new fields shipped as read-time defaults, and no locked tables at 50-billion-row scale, but also no database-level validation, typing, or indexing of anything inside the blob. The schema still exists; it just lives in the code, enforced by whichever services remember it.
- Reading only from the primary spends hardware to buy predictability. The standby twin of every pair serves nothing in production; it exists for failover, backups, and dumps to storage, so read capacity equals the primary's capacity, and scaling reads means splitting shards. The post treats this as a firm rule: standby copies fall behind, that delay causes strange bugs, and once you are sharded there is no benefit left to justify the risk.
- Declining automatic failover keeps a human between the alarm and the fix. When a primary dies, scripts promote the standby, but a person runs them, so a few minutes of downtime is accepted on every failure, in exchange for never letting an ambiguous signal trigger a wrong promotion on its own. It is the cautious end of the automation dial, chosen at design time rather than learned from a painful incident.
Patterns in this article
- Application-Layer Sharding
This is the earliest instance of the pattern, designed in 2012, years before the other systems here hit the same wall. Discord routes messages to Elasticsearch shards in application code (2017); Figma and Notion partitioned an existing single Postgres database under duress; Pinterest built the virtual-shards-over-machines architecture from scratch mid-hypergrowth. The shared skeleton is exact: many small logical shards (4,096 here), a config mapping ranges to machines, and capacity added by remapping. So are the shared renunciations: no cross-shard joins, foreign keys, or global indexes.
- ID-Encoded Placement
The post's distinctive contribution: the object ID carries its shard (16 bits), type (10), and row (36), so finding any object is simple bit math no service needs to mediate, and globally unique IDs fall out for free. The flip-side cost is a deliberate design decision: data never moves between shards, because every reference to it would then point to the wrong place. The post even includes the contrasting scheme in miniature: the mod shard hashes arbitrary keys and loses these nice properties.
- Master-Only Reads
The other system in this collection with the same rule is Airbnb's Orpheus. Pinterest states it as operational law: standbys lag, lag breeds strange caching bugs, and production never reads from a standby. Airbnb reaches the same rule from a sharper wound, where reading a lagged replica turns a correct, safe-to-repeat retry into a double charge. Same conclusion from two directions: replicas are for disaster, not for reads.
Also solving this
Other systems in behindscale's Single-table scaling ceiling class: