System design · Concept 5
Databases: SQL, NoSQL, Replication and Sharding
How to choose a datastore, use indexes well, replicate for reads and availability, and partition data without creating hot spots.
In short
- Default to SQL; choose NoSQL for known keys at huge scale
- Index for your top queries: leftmost prefix rule
- Replicas scale reads but lag: design read-your-writes
- Shard last, on a high-cardinality key that matches the main query
- Consistent hashing moves about 1/N of keys when you add a node
Most system design hinges on the data tier. Stateless services scale by adding boxes, but data has to be stored, replicated, partitioned and kept consistent. Choose the store from your access patterns and consistency needs. Fashion shouldn't come into it.
SQL vs NoSQL decision guide
| Question | Leans SQL (Postgres, MySQL, SQL Server) | Leans NoSQL (DynamoDB, Cassandra, MongoDB, Cosmos DB) |
|---|---|---|
| Need multi-row/multi-table ACID transactions? | Yes: orders, payments, inventory | Rarely, or only within one partition |
| Relationships and ad-hoc queries/joins? | Yes: reporting, admin tools, evolving queries | No: access is by known key patterns |
| Write volume | Up to ~10K writes/s per primary | 100K-millions writes/s, scales by adding nodes |
| Data size | Up to a few TB per node comfortably | Tens of TB to PB across many nodes |
| Schema | Stable, enforced, migrations | Flexible / sparse attributes |
| Consistency | Strong by default | Often eventual/tunable; strong per key available |
| Team familiarity | Universal | Requires modeling around access patterns up front |
Default to SQL unless you have a clear reason: massive write throughput, a simple key-based access pattern at huge scale, or a natural fit like time-series or wide rows. Many real systems use both (polyglot persistence): SQL for core entities, a wide-column store for events and messages, and a search index for text.
NoSQL families
| Family | Model | Examples | Great for |
|---|---|---|---|
| Key-value | key → blob | Redis, DynamoDB, Riak | Sessions, carts, URL mappings, counters |
| Document | JSON documents, secondary indexes | DocumentDB, Cosmos DB, Firestore, MongoDB | Catalogs, user profiles, CMS content |
| Wide-column | Partition key + sorted clustering columns | Cassandra, ScyllaDB, Bigtable, HBase | Messages, time-series, activity logs, very high writes |
| Graph | Nodes + edges | Neo4j, Neptune | Deep relationship traversal (fraud rings, recommendations) |
| Search | Inverted index | Elasticsearch, OpenSearch | Full-text search, faceting (as a secondary store) |
| Time-series | Time-ordered points | InfluxDB, TimescaleDB, Prometheus | Metrics, IoT |
Indexes
An index trades write cost and storage for fast reads.
- B-tree (the SQL default): O(log n) lookups, range scans, ordered results.
- LSM tree (Cassandra, RocksDB): writes append to memory and flush sequentially, so writes are very fast. Reads may check several files, which bloom filters and compaction mitigate.
- Composite indexes follow the leftmost prefix rule.
(user_id, created_at)servesWHERE user_id = ? ORDER BY created_at DESC, but notWHERE created_at > ?alone. - Covering index: includes every column the query needs, so the table is never touched.
- Cost: each index slows writes and uses memory. Index for your top queries and drop unused indexes.
- In distributed databases, a local secondary index lives with the partition (queries must hit all partitions unless filtered by partition key). A global secondary index is partitioned separately. In many stores (DynamoDB, for example) it's updated asynchronously and is eventually consistent; distributed SQL engines such as Spanner or CockroachDB keep it in step inside the transaction instead, at the price of a cross-partition commit on every write.
-- "Latest 20 orders for a user" -> composite index matches the access pattern
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
SELECT id, total, status FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20;
Replication
Replication keeps copies of data on multiple nodes for availability, read scaling and durability.
writes
App ───────────────► [ Leader ] ──async log──► [ Follower 1 ] ◄── reads
└─async log──► [ Follower 2 ] ◄── reads
| Mode | How | Pros | Cons |
|---|---|---|---|
| Leader-follower, async | Leader acks before followers apply | Fast writes | Failover can lose the last seconds of writes; replica lag |
| Leader-follower, sync / semi-sync | At least one follower acks | No data loss on failover | Higher write latency; a stuck follower blocks writes |
| Multi-leader | Several nodes accept writes (e.g. per region) | Local writes in each region | Write conflicts need resolution (LWW, CRDTs) |
| Leaderless (quorum) | Write to W of N, read from R of N | No failover needed, tunable | Conflict handling, read repair, complexity |
With quorums, R + W > N (e.g. N=3, W=2, R=2) means reads overlap the latest write.
Read replicas and replication lag
Replicas scale reads but serve slightly stale data (usually milliseconds, sometimes seconds under load). Watch for:
- Read-your-writes: a user updates their profile, refreshes, and sees the old value. Fix it by reading from the primary for that user for a short window after a write, or by tracking the write's log position.
- Monotonic reads: successive reads hitting different replicas can go back in time. Pin a session to one replica.
- Failover: promoting a follower must fence the old leader (so you don't get split brain) and update clients via DNS or a proxy.
Partitioning (sharding)
Shard when a single node can't hold the data or absorb the writes. Each shard holds a subset of the keys.
| Strategy | How | Pros | Cons |
|---|---|---|---|
| Range | Key ranges (A-F, G-M…, or by date) | Efficient range scans | Hot spots (all new writes go to the "latest" range) |
| Hash | hash(key) mod N | Even spread | Range queries hit all shards; changing N remaps nearly all keys |
| Consistent hashing | Keys and nodes on a ring | Adding a node moves only ~1/N of keys | Needs virtual nodes for balance |
| Directory / lookup | A table maps key → shard | Flexible, can move tenants individually | Lookup service is critical path + SPOF risk |
| Geo / tenant | By region or customer | Data residency, isolation | Uneven tenant sizes |
Consistent hashing
0
N3 ● ● N1 key k → hash(k) on the ring → walk clockwise
● ● to the first node.
N2(v) N3(v) Each physical node owns many virtual nodes (v)
● ● so load stays even and adding a node steals a
N1(v) ● ● N2 little from everyone.
Used by Cassandra, Riak, Amazon's original Dynamo design, many caches and load balancers. (DynamoDB the service hashes the partition key too, but splits the hash space into contiguous ranges that it splits and moves as load grows.)
Choosing a shard key
A good shard key:
- Has high cardinality and spreads load evenly (user_id, conversation_id, order_id).
- Matches the main access pattern, so most queries hit exactly one shard ("all messages for conversation X").
- Keeps related data together, so transactions stay single-shard (an order and its line items share
customer_id).
Avoid monotonically increasing keys (timestamps, auto-increment IDs) for range sharding, because every write lands on one shard.
Hot partitions
Even a good key can get hot: a celebrity user, a viral product, a large tenant.
- Salting / write sharding: append a suffix
key#0..#9to spread writes, and read all suffixes and merge. - Split hot partitions (range-based stores can split automatically).
- Cache hot reads in front of the partition.
- Isolate whales: give the biggest tenants dedicated shards.
- Rate limit per key to protect neighbours.
Resharding
Moving data while serving traffic usually follows these steps: 1) dual-write to old and new locations, 2) backfill historical data, 3) verify (checksums, shadow reads), 4) switch reads, 5) stop old writes. Design keys and a directory layer so resharding stays possible. Many teams pick a large fixed number of logical shards up front (e.g. 1,024) mapped to fewer physical nodes.
When to use / trade-offs
- SQL for transactional core data, relationships and ad-hoc queries. You'll need to scale it with replicas, partitioning, or a distributed SQL engine (CockroachDB, Spanner, Citus).
- Wide-column / key-value for massive write volume with known access patterns. You give up joins and flexible queries, and data has to be modeled per query.
- Read replicas are cheap read scaling, paid for with replication lag. Design read-your-writes where users notice.
- Sharding is the last resort for writes and size. It brings cross-shard queries and transactions, rebalancing work and operational load.
- Denormalization speeds up reads at the cost of write amplification and consistency work.
Checklist
- Access patterns listed; store chosen per pattern
- Primary key and indexes match the top queries
- Replication mode, failover and replica-lag handling
- Data size and write rate: does it need sharding? Which key?
- Hot partition mitigation
- Backups, point-in-time recovery, retention
Practise this concept
In the app: a short video and a 11-question concept check. Free with a GitHub account.
Open in the app