Pinterest MySQL Manual Sharding
Pinterest manually sharded MySQL by embedding shard location directly into 64-bit primary keys, eliminating the need for a shard routing table: which itself would become a single point of failure: and creating one of the earliest and most widely cited MySQL sharding case studies in the industry.
Pinterest reached MySQL scalability limits in 2012 when their single master database could no longer absorb the write rate from pin creation, repinning, and board updates across 10+ million users. Rather than adopt a distributed database (which they considered operationally immature), Pinterest designed a manual MySQL sharding scheme with a key architectural insight: encode the shard identifier directly into the primary key, so that any service receiving a Pinterest ID can determine the target shard without a lookup. Pinterest IDs are 64-bit integers structured as shard_id (16 bits) + type_id (10 bits) + local_id (36 bits). This design eliminates the shard routing table: a centralized lookup store that would itself require high availability and would become a bottleneck. The scheme traded future flexibility (moving an entity between shards requires ID reissuance) for operational simplicity and deterministic routing that has scaled Pinterest to billions of pins without architectural replacement.
Scale at Decision Point
Users
~10 million users in 2012; growing 40% month-over-month at the time of the sharding decision
Data Volume
~100 million pins at sharding time; growing to billions over subsequent years
Request Rate
Peak write rate saturating a single MySQL master; specific RPS not publicly disclosed
MySQL 5.5; 8 shards initially, expandable to 65,536 (16-bit shard ID space); Memcached for read caching; HBase for derived data and analytics
Architecture Evolution
Initial Architecture
Single MySQL master with read replicas, backed by Memcached for read caching. Pinterest launched on a monolithic schema with a single master handling all writes for pins, boards, users, and follow relationships. By late 2011, write throughput on the MySQL master reached hardware limits: the largest available instance could not absorb the write rate from pin creation and repin events. Vertical scaling was exhausted. The team evaluated: MySQL replication (only helps reads, not writes), NoSQL databases (Cassandra, MongoDB: rejected for operational immaturity and replication concerns), and manual MySQL sharding. Manual sharding was selected because MySQL was a known quantity and the operational model was fully understood by the team.
- Single MySQL master write ceiling: no horizontal write scaling
- Any master failure causes full write unavailability during failover
- Schema migrations require maintenance windows on the single shared master
- All Pinterest data shares one failure domain: a MySQL bug or corruption event affects everything
Evolved Architecture
Manually sharded MySQL with 4096 virtual shards (later expandable to 16 shards of physical MySQL instances). Every Pinterest entity (Pin, Board, User, Comment) receives a globally unique 64-bit ID at creation. The ID encodes: shard_id in the top 16 bits (identifying the physical MySQL shard), type_id in the next 10 bits (distinguishing Pins from Boards from Users), and local_id in the remaining 36 bits (a per-shard auto-increment sequence). Any service that receives a Pinterest ID can determine the target shard by reading the top 16 bits: no routing table required. Memcached provides a read cache layer across all shards. HBase stores derived data (follower counts, board pin counts) that require aggregation across shards, since cross-shard joins in MySQL are not supported. Separate lookup tables map human-readable usernames and URLs to Pinterest IDs.
- Shard ID is immutable once assigned: moving an entity to a different shard requires creating a new ID and migrating all references
- Cross-shard queries (find all pins on boards by users I follow) require application-level scatter-gather
- Adding a new shard requires a migration plan: simply adding a shard does not rebalance existing data
- Global aggregations (total pin count, top pins globally) are expensive scatter-gather operations across all shards
Key Transitions
Trigger
MySQL master write saturation was limiting pin creation rates during periods of viral growth. The engineering team had a one-month runway to implement and deploy a sharding solution before the single master would be unable to keep up with write volume. The design constraint was: zero application downtime, no foreign key constraints (MySQL FK enforcement across shards is impossible), and no centralized routing component that could fail.
Before
Single MySQL master; all entity IDs are MySQL auto-increment integers with no shard information
After
64-bit Pinterest IDs with embedded shard_id; routing decisions made at ID parse time
Outcome
ID format deployed and all new entity creation used the structured Pinterest ID. Shard routing was implemented as a pure function on the ID: zero network calls, zero database lookups for routing decisions. Initial deployment used 8 physical MySQL shards; the 16-bit shard ID space allowed future expansion to 65,536 physical shards without ID format changes.
Lessons
- Embed routing information in the primary key: it eliminates the routing table as a single point of failure and reduces routing to a bitwise operation
- Design the ID format for future scale, not current scale: Pinterest's 16-bit shard field allows 65,536 shards even though only 8 were needed initially
- The absence of foreign key constraints across shards must be accepted upfront: referential integrity must be enforced at the application layer
Trigger
ID format was deployed for new entities; existing entities retained their old MySQL auto-increment IDs. A migration was required to move all existing pins, boards, and users to the new sharded layout with new-format IDs before the old master reached capacity.
Before
Old auto-increment IDs for existing entities; new Pinterest IDs for entities created after cutover
After
All entities with Pinterest IDs; lookup tables mapping old IDs to new IDs for API compatibility
Outcome
Migration completed over several weeks with dual-ID support maintained throughout. API endpoints continued accepting old-format IDs via lookup table translation during the transition period. Old ID lookup tables eventually retired after client traffic confirmed no old-format IDs were in circulation.
Lessons
- Live ID migration requires maintaining translation tables: deprecating old IDs immediately breaks clients; a 6-12 month parallel period is realistic
- Dual-ID support adds application complexity that should be time-bounded from the start: set a retirement date for the old ID format at migration start
- Bulk migration jobs should run as background processes with throttling: competing with production write traffic causes latency spikes
Trigger
Follower relationship queries: "find all users following user X": are inherently cross-shard in Pinterest's data model since followers are distributed across shards. Application-level scatter-gather across 8+ MySQL shards for every follower query was creating unacceptable latency for high-follower-count users.
Before
Cross-shard follower queries via MySQL scatter-gather; O(shard_count) latency per query
After
HBase stores denormalized follower lists; single HBase lookup for follower set
Outcome
Follower list reads reduced from O(shard_count) MySQL scatter-gather to O(1) HBase point lookup. Write path maintained consistency between MySQL shard records and HBase follower lists via dual-write. Pinterest published this architecture in a widely-read 2013 engineering blog post that influenced how other companies approached cross-shard aggregations.
Lessons
- Cross-shard aggregations are fundamentally incompatible with the sharding model: move them to a separate store optimized for fan-out reads
- HBase's wide-row model is a natural fit for adjacency lists (follower sets): each user is a row key with follower IDs as column qualifiers
- Dual-write consistency between the sharded primary and the aggregation store requires careful transaction design: eventual consistency is usually acceptable for social graph reads
Key Lessons
ID design is a sharding decision: building shard location into the primary key is the most important single choice in a manual sharding architecture
A shard routing table is a centralized component that requires its own high availability, creates an additional network hop on every database operation, and becomes a bottleneck as request rate grows. Encoding shard location in the primary key eliminates all three problems at the cost of flexibility: the shard assignment is permanent. Pinterest accepted this tradeoff explicitly, noting that cross-shard entity migration is a rare operational event that can be handled with careful tooling.
Applicable when: You are designing a sharded database architecture and choosing between routing table and key-embedded shard routing
Cross-shard aggregations and cross-shard relationships require separate data stores: scatter-gather across many shards is not a sustainable pattern
Social relationships (followers, repins) inherently span shards. Pinterest's solution was to maintain denormalized copies in HBase for the aggregated views needed at read time, with the MySQL shards holding the authoritative records. This is a form of CQRS where the write model is normalized (sharded MySQL) and the read model is denormalized (HBase wide rows). The consistency lag between them is acceptable for social data.
Applicable when: You are building a sharded social platform and need to support follower/following relationship queries across shard boundaries
Manual sharding with a small team is operationally viable when the operational model is simple and well-understood
Pinterest chose MySQL sharding over NoSQL alternatives in 2012 partly because MySQL's operational model was known: the team understood how to monitor it, back it up, handle failover, and debug slow queries. The operational familiarity was worth significant feature tradeoffs. A complex, operationally opaque distributed database with better theoretical properties would have introduced failure modes the team could not diagnose under production pressure.
Applicable when: You are choosing a database architecture for a small engineering team and must balance capability against operational complexity
Technologies
Patterns
Failure Modes Encountered
Related Scenarios
Sources
3 sources are pending verification and have been hidden until a followable citation is available.