Scaling Relational Databases
High Level Design·advanced·~35 min read
- system-design
- postgres
- sharding
- distributed-sql
- cassandra
- hld
- 1. The Simple Levers (Before You Shard) - 2. Application-Level Sharding — The Instagram Approach - 3. Distributed SQL — The Tempting Alternative - 4. Full NoSQL + Optimistic Concurrency Control - 5. Comparison Table - 6. Decision Flow…
What you'll learn
1. The Simple Levers (Before You Shard)
1a. Read Replicas
Primary handles writes; N replicas serve reads with ~1 s lag. - Fixes read-heavy workloads (feeds, product pages, dashboards). - Does not reduce write pressure, grow storage, or raise per-row throughput. - Replication lag is a real corre…
1b. Native Declarative Partitioning (Same Postgres, Same Connection)
Postgres splits one logical posts table into physical child tables keyed by a column — usually time. - Writes auto-route to the right child based on the createdat value. - Reads with a WHERE createdat >= … prune partitions — Postgres onl…
2. Application-Level Sharding — The Instagram Approach
You run many physical Postgres servers. Each is a vanilla single-node Postgres. Your application decides which one to talk to.
2a. The Instagram ID Trick
Instagram bakes the shard ID into every Snowflake ID: The app receives postid = 42937491832910 → extracts bits 13..22 → shard = 437 → opens a connection to Physical Postgres #437. No central routing lookup. No metadata service in the hot…
2b. Logical vs Physical Shards
Instagram started with ~4 096 logical shards across ~32 physical servers — ~128 logical shards per server. When they outgrew a server, they moved logical shards (not individual rows) off it. The ID layout never changed; only the mapping…
2c. The Operational Pay-off
- Blast radius isolation — Shard #14 crashes → 1/32 of users degraded; the other 31/32 are fine. - Per-shard tuning — upgrade Postgres version on one shard first; test; roll forward. - No consensus overhead — every write is a plain Postg…
2d. The Cost
- Your app code owns routing. Every ORM query must know its shard. - Cross-shard queries ("count all posts last hour") become fan-out jobs → map-reduce or ETL to an OLAP store. - Transactions across shards do not exist. Use the saga patt…
3. Distributed SQL — The Tempting Alternative
CockroachDB, YugabyteDB, Citus (Postgres extension), TiDB. The sales pitch: "Postgres compatibility + out-of-the-box horizontal scaling, no app changes." The reality is more nuanced. Below is the honest trade-off. Many teams land here an…
3a. How It Works
Data is auto-sharded into ranges (CockroachDB) or tablets (Yugabyte). Writes commit via a consensus protocol (Raft) across a majority of replicas. A Hybrid Logical Clock (HLC) orders transactions across nodes without a GPS-backed TrueTim…
3b. The Latency Tax
Every write round-trips to a majority of replicas for consensus. Even on a fast internal network: | Operation | Vanilla Postgres | Distributed SQL | |---------------------|------------------|-------------------| | Single-row insert | 1–2…
3c. Query Planner Abstraction Leaks
The storage engine is RocksDB-based, not Postgres heap. The planner behaves differently. A JOIN that ran in 10 ms on single-node Postgres may trigger a network shuffle across tablets: You end up with two SQL dialects in your head: "what…
3d. Operational Debuggability
- Postgres: EXPLAIN ANALYZE, pgstatstatements, VACUUM VERBOSE, decades of StackOverflow answers. - Distributed SQL: is it slow because of disk I/O, network partition, HLC drift, Raft leader election, hot range, or shuffle? You need distr…
3e. When Distributed SQL Is the Right Call
- You need strongly consistent cross-region transactions (e.g., ledger, inventory, auction). The latency tax is the cost of correctness. - Your team is small and the alternative is to build and run application sharding — Distributed SQL…
3f. When It's Wrong
- Write-heavy, latency-sensitive (feed inbox, chat, analytics ingest). Pick NoSQL (Cassandra, DynamoDB) or app-level sharding. - You already have Postgres expertise and no cross-region requirement. Sharding is easier than you think. ---
4. Full NoSQL + Optimistic Concurrency Control
If you don't want to shard Postgres or pay the Distributed SQL latency tax, go NoSQL. The hard part is replacing Postgres's SELECT … FOR UPDATE with a lock-free equivalent.
4a. The Problem FOR UPDATE Solved
Two media-processing workers finish at the same ms. Both read the manifest, both see "all tasks complete", both try to publish. See [[media-upload-pipeline]].
4b. OCC — Optimistic Concurrency Control
Add a version integer. Every update must specify the version it read. In DynamoDB this is a conditional expression:
4c. Where OCC Shines vs Falls Short
- Shines: low-conflict workloads (99 % of writes don't race). Scales horizontally forever. - Falls short: high-contention hotspots (counter on a viral tweet, inventory on Black Friday). Retry storm → p99 explodes. For those, you need eit…
4d. NoSQL for the Post DB — Concrete Setup
- Posts table (DynamoDB / Cassandra): partition = userid, sort = postid (Snowflake). Natural chronological scan, no secondary index needed. - Media jobs table: partition = postid, sort = taskid. Add version column. Workers do conditional…
5. Comparison Table
| Lever | Writes/s per cluster | p99 write | Blast radius | SQL coverage | Team skill required | |--------------------------|-----------------------|-----------|------------------|---------------------|--------------------------------| |…
6. Decision Flow
---
What to Say in an Interview
1. "Start with partitioning, not sharding." Native declarative partitioning buys you a lot before you need to shard. 2. "Prefer application-level sharding over Distributed SQL at write-heavy scale." Instagram, Slack, Pinterest all picked…