Scaling Postgres to Planet Scale: The Instagram Playbook

By JB (Muke Johnbaptist) · July 2026. A practical engineering guide based on how Instagram scaled Postgres from 3 engineers and one database (2010) to 27M users and 2TB (2012) — and beyond.

Why this matters

Instagram, Reddit, Notion, Discord, Stripe, and GitHub all run (or ran) massive workloads on Postgres. They evaluated Cassandra, MongoDB, and DynamoDB — and stayed with Postgres anyway. The core insight: most scaling problems are not database problems, they're architecture problems. You almost never need to switch databases; you need to fix how you're using the one you have.

This guide breaks down every concept, explains how to implement each one, and tells you at what scale you actually need it — so you don't over-engineer too early or panic-fix too late.

Part 1: The four walls (in the order you hit them)

Wall 1 — Connection exhaustion

What breaks: each Postgres connection costs ~1.3 MB of RAM. If you have 50 app servers each holding 30 idle connections, that's 1,500 connections — roughly 2GB of RAM burned before a single query runs.

Root cause: every application process (a Django worker, a Node process) keeps its own connection pool. These pools don't coordinate with each other.

The fix: a connection pooler — PgBouncer — sits between your app and Postgres. Your app thinks it's talking to Postgres directly, but PgBouncer multiplexes hundreds or thousands of app-side connections down to a small number of real backend connections (e.g. 30).

When you need it: this is the first bottleneck almost everyone hits — often well before 1 million users, sometimes with just a handful of app servers. If you don't have PgBouncer (or a pooler like Supavisor, RDS Proxy, or pgcat) in front of Postgres today, this is your highest-leverage fix this week, regardless of scale.

Wall 2 — The vertical scaling ceiling

What breaks: you've maxed out RAM and disk I/O on the biggest instance your cloud provider will rent you. There's no bigger box.

Root cause: a single Postgres primary can only vertically scale so far. Instagram hit this around 2TB of data.

The industry's wrong answer: "switch to a NoSQL database." Instagram evaluated this and rejected it — horizontal partitioning problems don't disappear when you switch databases; they move behind a different abstraction, and you lose relational guarantees (joins, transactions, constraints) permanently.

The right answer: make Postgres itself horizontal — shard it.

When you need it: only once you've genuinely exhausted vertical scaling and connection pooling and query/index optimisation. Instagram didn't shard until 27 million users. Sharding is the most expensive complexity you can add — don't reach for it first.

Wall 3 — The sharding key problem

What breaks: once you decide to shard, you must pick a shard key (the column you partition by). For Instagram, that was user_id.

The trade-off this creates:

• Queries scoped to one user ("show my profile") → fast, single-shard lookup.
• Queries that span many users ("show my feed from 200 people I follow") → must fan out to potentially dozens of shards, merge results in the application layer, and are only as fast as your slowest shard.

Why the decision is permanent: once hundreds of terabytes are partitioned by user, resharding means rewriting every row. There is no clean "change our mind later."

When you need to decide: before you shard — and you only get one real shot. Pick your shard key based on your dominant query pattern, not your simplest one.

Wall 4 — Unique ID generation across shards

What breaks: Postgres auto-increment IDs are per-database. If shard 0 and shard 1 both generate 1, 2, 3…, you get collisions across your whole system.

Options Instagram evaluated and rejected:

• UUIDs — never collide, but are large, random, and have no time-ordering, making "show latest photos" queries expensive.
• Ticket server (Flickr's approach) — a dedicated ID-issuing service. Rejected as a single point of failure.
• Twitter Snowflake — time-ordered distributed IDs, but requires running a separate service alongside Postgres.

What Instagram actually built: Snowflake-style IDs generated inside Postgres itself via a database function — no external service. A 64-bit ID is composed of:

Because the timestamp occupies the high-order bits, IDs are roughly sortable by time — "show me the latest photos" becomes ORDER BY id DESC LIMIT 20, with no separate timestamp index needed.

When you need it: from day one, even before you're sharded and even if you never plan to shard. Migrating billions of existing auto-increment rows to Snowflake-style IDs later is extremely painful. This is the cheapest insurance policy in the whole guide.

Part 2: The underused Postgres features

Partial indexes

A normal index has one entry per row. A partial index only indexes rows matching a condition.

• Effect: if only 10% of your billion-row table is "hot," the index can be ~10× smaller and faster to scan, and it naturally stays small as old data ages out.
• Implement: CREATE INDEX idx_recent_photos ON photos (created_at) WHERE created_at > now() - interval '30 days';
• When you need it: as soon as you have a large table where queries mostly touch a recent/active subset — commonly the hundreds-of-millions-of-rows range, but no harm doing it earlier.

Functional indexes

Indexes the result of a function applied to a column, rather than the raw value.

• Use case: instead of indexing a full 64-character token, index just the first 8 characters — cutting index size roughly 10× while keeping lookups fast (8-char prefixes are usually unique enough).
• Implement: CREATE INDEX idx_token_prefix ON tokens (substring(token, 1, 8));
• When you need it: when you have long string columns (tokens, hashes, URLs) indexed in full — matters at billions of rows, harmless at any scale.

Logical replication

Streams every insert/update/delete on a table to downstream systems in real time — built into Postgres, no Kafka or dual-writes required.

• Use cases: a search index subscribes and stays current; a cache layer subscribes and invalidates entries; an analytics warehouse subscribes and gets a live copy.
• Implement: CREATE PUBLICATION my_pub FOR TABLE photos; on the primary, then a subscriber connects with CREATE SUBSCRIPTION.
• When you need it: as soon as more than one system needs to stay in sync with your source-of-truth table. Many teams reinvent this badly with manual event publishing — check whether logical replication solves it first.

Part 3: The sharding architecture pattern (the most important idea)

The single most valuable idea in the whole case study: separate how data is partitioned from where it physically lives.

• Instagram created thousands of logical shards (implemented as Postgres schemas), independent of physical hardware.
• Early on, all those logical shards lived on one physical machine — the application didn't know or care.
• When a physical machine approached capacity, Instagram used Postgres's built-in streaming replication to copy a subset of logical shards to a new machine, then flipped a mapping table (logical shard → physical node). No data rewrite. No application code changes.

Why this matters: if you hardcode "we have 16 shards" into your application, growing means resharding — rewriting data. If you hardcode "we have 4,000 logical shards mapped onto some number of physical nodes," growing is just a mapping update.

When you need it: only once you're sharding at all — but if you are sharding, build the logical/physical separation from the start. Retrofitting it later is as painful as retrofitting Snowflake IDs.

Part 4: The "boring technology" principle

Every team has a finite complexity budget. Each new piece of infrastructure (a new database, a new queue, a new service) costs real, ongoing tax: learning its quirks, building operational expertise, training and hiring for it.

Boring technology (Postgres, Linux, Memcached, Nginx) costs less to adopt because your team likely already knows it, its failure modes are documented extensively, and operational tooling has existed for 15+ years.

Spend your complexity budget when you have a genuinely new problem the boring stack can't solve — vector search at scale, real-time time-series analytics, graph traversal at planet scale. "We have a lot of users" is not a new problem; it's the most common problem in software, and Postgres has a well-documented playbook for it.

Part 5: The developer checklist

Use this in order — don't skip ahead to sharding just because it sounds impressive.

Tier 0 — do this regardless of scale

• Connection pooler installed (PgBouncer, RDS Proxy, Supavisor, or pgcat) between app and Postgres.
• Snowflake-style or time-sortable IDs instead of plain auto-increment — even if you're nowhere near sharding.
• Confirm your app-side pool size × number of app instances isn't silently creating thousands of idle Postgres connections.

Tier 1 — query & index hygiene (before hardware upgrades)

• Identify your "hot" data subset and add partial indexes scoped to it.
• Audit indexes on long string/token/hash columns — consider functional indexes on truncated prefixes.
• Run EXPLAIN ANALYZE on your slowest and most frequent queries before assuming you need more hardware.
• Check for missing indexes on foreign keys and frequently filtered columns.

Tier 2 — vertical scaling (before sharding)

• Confirm you've actually maxed out RAM/disk I/O on the largest instance available.
• Consider read replicas for read-heavy workloads before sharding writes.
• Consider native table partitioning (not sharding) for very large single-table workloads — a lighter-weight step.

Tier 3 — sharding (only once Tiers 0–2 are exhausted)

• Identify your dominant query pattern before picking a shard key.
• Design logical shards (e.g. schemas) decoupled from physical nodes from day one.
• Build/choose a mapping layer (logical shard → physical node) the application queries dynamically, never hardcoded.
• Plan for cross-shard fan-out (application-layer merging, caching, precomputed feeds) — accept this complexity upfront.

Tier 4 — keeping systems in sync

• If you're manually syncing a search index, cache, or analytics warehouse with hand-rolled events or dual writes, evaluate logical replication as a built-in replacement.

Ongoing — complexity-budget discipline

• Before adopting any new database/infra, ask: is this solving a problem boring tech genuinely cannot solve (vector search, graph traversal, time-series at extreme scale), or is this just "we have a lot of users" — which Postgres already handles?

Quick reference: what requires what scale

The one-sentence takeaway

Understand and exhaust what your existing database can do — with connection pooling, proper indexing, and smart ID design — before reaching for a new one. The database is rarely the bottleneck; the architecture around it is.