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.

7 min read

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

QuestionLeans SQL (Postgres, MySQL, SQL Server)Leans NoSQL (DynamoDB, Cassandra, MongoDB, Cosmos DB)
Need multi-row/multi-table ACID transactions?Yes: orders, payments, inventoryRarely, or only within one partition
Relationships and ad-hoc queries/joins?Yes: reporting, admin tools, evolving queriesNo: access is by known key patterns
Write volumeUp to ~10K writes/s per primary100K-millions writes/s, scales by adding nodes
Data sizeUp to a few TB per node comfortablyTens of TB to PB across many nodes
SchemaStable, enforced, migrationsFlexible / sparse attributes
ConsistencyStrong by defaultOften eventual/tunable; strong per key available
Team familiarityUniversalRequires 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

FamilyModelExamplesGreat for
Key-valuekey → blobRedis, DynamoDB, RiakSessions, carts, URL mappings, counters
DocumentJSON documents, secondary indexesDocumentDB, Cosmos DB, Firestore, MongoDBCatalogs, user profiles, CMS content
Wide-columnPartition key + sorted clustering columnsCassandra, ScyllaDB, Bigtable, HBaseMessages, time-series, activity logs, very high writes
GraphNodes + edgesNeo4j, NeptuneDeep relationship traversal (fraud rings, recommendations)
SearchInverted indexElasticsearch, OpenSearchFull-text search, faceting (as a secondary store)
Time-seriesTime-ordered pointsInfluxDB, TimescaleDB, PrometheusMetrics, 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) serves WHERE user_id = ? ORDER BY created_at DESC, but not WHERE 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
ModeHowProsCons
Leader-follower, asyncLeader acks before followers applyFast writesFailover can lose the last seconds of writes; replica lag
Leader-follower, sync / semi-syncAt least one follower acksNo data loss on failoverHigher write latency; a stuck follower blocks writes
Multi-leaderSeveral nodes accept writes (e.g. per region)Local writes in each regionWrite conflicts need resolution (LWW, CRDTs)
Leaderless (quorum)Write to W of N, read from R of NNo failover needed, tunableConflict 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.

StrategyHowProsCons
RangeKey ranges (A-F, G-M…, or by date)Efficient range scansHot spots (all new writes go to the "latest" range)
Hashhash(key) mod NEven spreadRange queries hit all shards; changing N remaps nearly all keys
Consistent hashingKeys and nodes on a ringAdding a node moves only ~1/N of keysNeeds virtual nodes for balance
Directory / lookupA table maps key → shardFlexible, can move tenants individuallyLookup service is critical path + SPOF risk
Geo / tenantBy region or customerData residency, isolationUneven 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:

  1. Has high cardinality and spreads load evenly (user_id, conversation_id, order_id).
  2. Matches the main access pattern, so most queries hit exactly one shard ("all messages for conversation X").
  3. 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..#9 to 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