Instagram PostgreSQL Sharding
Instagram built a logical sharding layer on PostgreSQL to scale from a single database to thousands of logical shards mapped across physical servers, routing by user ID without cross-shard joins: allowing server additions to be live migrations of logical shards rather than full resharding events.
Instagram launched in 2010 and was acquired by Facebook in 2012 with roughly 30 million users. By that point their PostgreSQL infrastructure was being stretched by photo storage, feed queries, and follow-graph traversals. Rather than migrate to a different storage system, Instagram built a logical sharding scheme on top of PostgreSQL: they defined thousands of logical shards (e.g., 2000), assigned each user to a shard by hashing their user ID, and mapped logical shards to physical PostgreSQL instances. When capacity grew, new physical servers were added and a subset of logical shards was migrated to them : a targeted data movement, not a global rehash. Django's ORM was wrapped with a shard-routing layer that computed the target shard from the user ID in the request context. This pattern, many logical shards, few physical servers, shard-to-server mapping stored in a routing table: became a widely referenced architecture for relational database horizontal scaling.
Scale at Decision Point
Users
~30M users at acquisition (April 2012); ~150M by end of 2012
Data Volume
Hundreds of millions of photos; the follow-graph and feed tables grew proportionally
Request Rate
~10,000 API requests/second at the time of public discussion (2012)
Django application servers; PostgreSQL with streaming replication; logical shard routing in application layer
Architecture Evolution
Initial Architecture
Single PostgreSQL primary with streaming replication replicas. Django ORM with direct database connections. No sharding or partitioning: all user data in a single database schema. Read replicas absorbed read load but the primary remained the single write point.
- Single write primary cannot scale write throughput beyond the machine's CPU and I/O ceiling
- All user data on one schema means a single hot table (photos, follows) becomes the write bottleneck for all users simultaneously: no isolation between user workloads
- Adding read replicas addresses read throughput but does nothing for write throughput
- Schema migrations (adding columns, new indexes) require full table locks or slow online operations on tables with hundreds of millions of rows
Evolved Architecture
PostgreSQL with logical sharding: 2000 logical shards, each a separate PostgreSQL schema (or database). Users assigned to shards by consistent hash of user ID. A shard routing table maps logical shard IDs to physical PostgreSQL instances. Django's database router intercepts ORM queries and routes to the correct shard based on user ID extracted from request context. Physical servers initially 12, growing as traffic increased. Shard migration involves copying one logical shard's data to a new physical host and updating the routing table: a targeted operation affecting ~0.05% of users rather than a full rehash. Redis used for session storage, feed caching, and follow-graph caching to reduce read pressure on PostgreSQL shards.
- Cross-shard queries are not supported: operations that span multiple users (e.g., "find all users who follow both X and Y") require in-application aggregation across multiple shard connections
- Hot users (celebrities with millions of followers) can make a single logical shard disproportionately hot; a shard containing a high-follower user will see significantly more read traffic than adjacent shards
- The shard routing layer adds application complexity: developers must always have a user ID in context to route queries; operations without a clear sharding key require fan-out across all shards
Key Transitions
Trigger
PostgreSQL primary write throughput was approaching the machine's ceiling as Instagram's user base grew. A full migration to a distributed database would require rewriting the Django application. The team chose to build a sharding abstraction on PostgreSQL: a system they understood deeply: rather than adopt a new storage technology with unknown operational characteristics under their specific workload.
Before
Single PostgreSQL primary with read replicas; write bottleneck approaching
After
2000 logical shards on 12+ physical PostgreSQL instances; shard-routing Django layer
Outcome
Write throughput scaled horizontally: each physical server handled approximately 1/N of the write load. Adding capacity required migrating a subset of logical shards to new hardware: a targeted operation rather than a global resharding. The application-layer routing added minimal latency (single hash computation per request). The sharding model supported Instagram's growth through the Facebook acquisition and subsequent scale to hundreds of millions of users.
Lessons
- Logical sharding (many logical shards, few physical hosts) decouples the partitioning scheme from the physical topology. When you need to add capacity, you move logical shards: you do not re-partition user data.
- Incremental over revolutionary: building a sharding layer on a known, trusted technology (PostgreSQL) is lower risk than migrating to an unfamiliar distributed database. The team retained operational familiarity and existing tooling.
Trigger
The direct messaging feature introduced a workload that did not fit the user-ID sharding model: a conversation between two users spans two shards. Cross-shard consistency and routing complexity made the PostgreSQL sharding model awkward for message storage. Cassandra's partition-by-conversation model fit the access pattern directly.
Before
All data on PostgreSQL shards; DM queries requiring cross-shard joins
After
Direct messages on Cassandra; PostgreSQL shards retain user profile and photo data
Outcome
Message storage on Cassandra eliminated the cross-shard join problem for DMs. The two stores coexisted: PostgreSQL shards for user-centric data, Cassandra for conversation-centric data. This is a common polyglot persistence outcome : different access patterns route to purpose-fit storage engines.
Lessons
- No single sharding key works for all access patterns. User-ID sharding is optimal for user-centric data but creates cross-shard complexity for relationship-centric data (conversations, shared content). Recognize when a new access pattern is fundamentally different from the existing model.
Key Lessons
Logical sharding separates the partitioning model from the physical deployment topology
Instagram defined 2000 logical shards but initially ran only 12 physical PostgreSQL hosts. The ratio of logical to physical shards (167:1) meant that adding a new physical server required migrating ~167 logical shards: roughly 8% of users : rather than rehashing all users. This made capacity additions incremental and reversible. Choosing a fixed large number of logical shards at design time is a deliberate architecture decision: it avoids the rehashing problem that plagues systems with one logical shard per physical host.
Applicable when: You are designing a sharding scheme for a relational database and anticipate adding physical capacity over time. Use many logical shards (10–100× physical hosts) from the start. The routing table is cheap; rehashing at scale is not.
Sharding key selection determines which queries are fast and which are impossible
Instagram's user-ID sharding key made all user-centric operations efficient (single-shard lookups) but made cross-user operations expensive or impossible without fan-out. The DM case: where a conversation belongs to two users : was architecturally awkward enough to warrant a separate storage system. The sharding key is a load-bearing architectural decision; changing it later requires a full data migration.
Applicable when: You are selecting a sharding key. Enumerate all access patterns first. Identify which queries require single-shard execution (latency-sensitive) vs. multi-shard fan-out (acceptable for background jobs). Choose the key that minimizes hot-path cross-shard operations.
Fan-out on write for feeds does not scale indefinitely for high-follower accounts
Instagram used fan-out on write for feed updates: when a user posts a photo, the post is written to each follower's feed. This works well when follower counts are bounded. Celebrities with millions of followers produce millions of feed writes per post. Instagram eventually moved to a hybrid model: fan-out on write for normal accounts, on-read aggregation for high-follower accounts. This is a well-documented scaling inflection point for social feed architectures.
Applicable when: You are building a social feed using fan-out on write and your user base includes accounts with 10,000+ followers. Model the write amplification at p99 follower count, not average follower count.
Technologies
Patterns
Failure Modes Encountered
Related Scenarios
Sources
2 sources are pending verification and have been hidden until a followable citation is available.