[DBEng] How Instagram Sharded PostgreSQL With 2.5 Engineers
In September 2011, Instagram was serving 10 million+ users on EC2 with two-and-a-half engineers and no ops team.
Table of Contents
Constraint That Shaped Everything
64-Bit ID: Time + Shard + Sequence
Routing: Two Lookups, One Python Dict
Logical Shards via PostgreSQL Schemas
Moving Shards With Streaming Replication
Why Not Redis, HBase, or Composite Keys?
What Any PostgreSQL Team Can Apply Today
📨 If this was useful, share it with one engineer on your team who manages production databases.
1. Constraint That Shaped Everything
Instagram launched in October 2010 on a single PostgreSQL server on EC2. By September 2011, they had over 10 million users. Team? Two-and-a-half engineers. No ops team. No DBA.
From co-founder Mike Krieger on Hacker News:
We’re on EC2, which has its set of limitations but means we can run a 10 million+ user system with two-and-a-half engineers (and no ops team / overhead).
Photos go to S3. PostgreSQL stores metadata, social graph, likes, comments, feed relationships. Almost every query is keyed by user. If you can guarantee a query touches exactly one user, you can guarantee it touches exactly one shard.
That’s the insight they exploited.
2. 64-Bit ID: Time + Shard + Sequence
Instagram designed a custom 64-bit ID generated entirely inside PostgreSQL via a PL/pgSQL function:
41 bits — milliseconds since a custom epoch (~69 years of runway)
13 bits — logical shard ID (0–8191)
10 bits — auto-increment sequence mod 1024
Function fires on DEFAULT nextval(). No application code generates IDs. No external service. Database itself produces globally-unique, time-sortable identifiers.
Why time-sortable? “Newest first” feeds become a simple index scan no secondary sort on a timestamp column.
Why 64-bit single integer instead of a composite key? Krieger’s answer:
“We often have to store keys in other systems like Redis, where having a single 64-bit integer makes it more portable and stored compactly (Redis, for example, has an optimization when storing integer values vs string values in its lists).”
One integer. Portable across every cache layer. No serialization overhead.
3. Routing: Two Lookups, One Python Dict
Routing is surprisingly simple. From Krieger’s HN response:
“Each lookup is: user_id % 1000 → schema ID, schema ID → database ID, then SELECT FROM schemaID.tablename on that particular database.”
That mapping lives as a Python dictionary inside their Django application. Not a service. Not a database call. A dict.
# Conceptual representation of their routing
SHARD_MAP = {
0: 'db-01',
1: 'db-01',
2: 'db-03',
# ... through 999
}
def get_db(user_id):
schema_id = user_id % NUM_SHARDS
return SHARD_MAP[schema_id]When asked about routing tables vs algorithmic hashing, Krieger said:
“Right now, it’s a lookup dict in our Django app which involves brief downtime just to update the shard map when moving the data.”
O(1). Zero network cost. Tradeoff: updating the dict requires a brief deploy.
4. Logical Shards via PostgreSQL Schemas
Instagram pre-creates all logical shards as PostgreSQL schemas. Each physical server hosts many schemas:
-- Each schema is a complete logical shard
CREATE SCHEMA insta5;
CREATE TABLE insta5.photos (
id bigint PRIMARY KEY DEFAULT insta5.next_id(),
user_id bigint NOT NULL,
...
);
CREATE SCHEMA insta6;
CREATE TABLE insta6.photos (
id bigint PRIMARY KEY DEFAULT insta6.next_id(),
user_id bigint NOT NULL,
...
);Why schemas instead of separate databases or table-name prefixes? Krieger explained the PostgreSQL advantage:
“One huge benefit to PostgreSQL is the schema/tablespace feature, since it means all our logical shards all live inside one database.”
Each schema has its own sequence. Database’s own nextval() guarantees no duplicate IDs within a shard. 13-bit shard ID in the key guarantees no duplicates across shards.
When a physical server gets hot, you move entire schemas to a new machine. The application dict update is the only code change. No data re-keying. No ID rewrites.
This is the detail most sharding projects get wrong. They shard physically first and cannot rebalance without rewriting keys.
🚀 PostgreSQL Health Report — One SQL file. 60+ diagnostic checks. Detects vacuum lag, bloat, lock chains, replication slot danger, unindexed FK columns, and more.
5. Moving Shards With Streaming Replication
How do you move a logical shard from one physical server to another without extended downtime?
Krieger described their process:
“The way we move shards is to use PostgreSQL’s built-in streaming replication to create an exact, in-sync copy of a set of tablespaces, then ‘fail over’ to a new machine and start reading/writing to a subset of those tablespaces.”
The process:
Set up streaming replication from source to destination server
Wait for the replica to be fully in sync
Briefly stop writes (update the Django dict to point to the new server)
Start reading/writing to the new machine
Downtime window is the dict update seconds, not minutes. Data copy happens in the background via PostgreSQL’s native replication. No custom migration tooling.
Clock Drift and Duplicate Keys
41-bit timestamp relies on clocks being roughly correct. Instagram uses ntpd in gradual-adjustment mode (no sudden jumps). But what if a clock is slightly behind after a shard move?
Krieger’s response:
“At worst, you’d have a duplicate key when trying to insert, and can re-try without the risk of having a duplicate ID floating around your system.”
Failure mode is a caught exception on insert, not silent data corruption. 10 bits of sequence give 1,024 IDs per millisecond per shard a shard would need to be creating more than 128 entries per second with clocks 8+ seconds off to trigger this.
6. Why Not Redis, HBase, or Composite Keys?
Instagram uses Redis heavily for other systems. They explicitly considered it for ID generation and rejected it:
“[Redis] would have introduced a single point of failure, unless we split the load between several Redis instances, at which point it would be hard to make the IDs time-sortable. Also, most of our Redis systems are durable within a minute (we write to disk on a slave every minute), but if we were to lose the master and slave simultaneously (imagine an EC2 network issue), then it would be hard to know what the last known ‘good’ ID was.”
On HBase and NoSQL solutions:
“The data we shard out is more suited to an RDBMS, and since we’re way more familiar with that world than with HBase and similar, it was the choice that let us make the most progress in a short time with a small team.”
This is the underrated lesson. They didn’t pick PostgreSQL because it was technically optimal. They picked it because 2.5 engineers can’t afford the learning curve of a system they don’t deeply understand. Familiarity is a scaling strategy when your team is small.
7. What Any PostgreSQL Team Can Apply Today
Encode shard identity into your primary keys. Single highest-leverage decision in a sharded system. It eliminates routing services, lookup tables, and entire categories of cross-shard bugs.
Shard logically, not physically. Pre-create thousands of schemas on a handful of machines. When you need to rebalance, move schemas don’t re-key data.
Use stock database features. PL/pgSQL functions, schemas, sequences, streaming replication Instagram’s entire sharding infrastructure used features that ship with PostgreSQL. No custom extensions. No external ID services.
Choose what your team already knows deeply. Instagram explicitly rejected technologies they were less familiar with. At 2.5 engineers, the cost of learning a new system is measured in months of shipping velocity.
Keep the routing layer dumb. A Python dict that maps shard→server is less elegant than consistent hashing. It’s also something any engineer can debug in 30 seconds at 3am during an incident.
📧 Want the complete PostgreSQL Health Report? 60+ diagnostic queries — statistics freshness, extended stats coverage, bloat, vacuum health. Ready-to-run fixes.
📌 Upgrade to Premium ($8/month) — full SQL diagnostic packs, incident playbooks, and the queries I actually run during a Sev-1.
Sources
Instagram Engineering — Sharding & IDs at Instagram (September 2011) — Original post (now dead)
Hacker News discussion with author responses from mikeyk (Mike Krieger) — news.ycombinator.com/item?id=3058327
Instagram Engineering — Storing hundreds of millions of simple key-value pairs in Redis — instagram-engineering.com