Data in system design series: 1. Choosing a database · 2. Sharding: shard keys and resharding
In a system design round, “which database?” often gets answered with someone’s favorite product. This post gives a process instead: estimate the load, know the ceiling of one relational node, then pick a database family by data shape, queries and contention. Indexing and partitioning on a single node are covered in JOINs and partitioning and index types beyond the B-tree.
1. The ceiling of a relational database
Key idea: before talking about NoSQL or sharding, know how much one PostgreSQL/MySQL node can take. Most systems never go past it.
Numbers for estimating (rough, they depend on hardware and queries):
| Setup | Key-lookup read QPS | Write TPS, low contention | Write TPS, high contention |
|---|---|---|---|
| Mid-range machine, recent DB version | ~20,000 | ~1,000 | ~200 |
| Cloud 4 cores, 8 GB, MySQL 5.7 | ~10,000 | ~500 | even lower |
Here TPS means multi-statement business transactions in the TPC-C style (orders, payments), not a single INSERT. Contention means many transactions need a lock on the same row, for example all decrementing stock for one hot product.
How to use it in an interview: work out peak QPS/TPS from the requirements, divide by the ceiling, and you get the number of nodes, or you know right away that “one node is enough”. Example: 50 million orders/day ≈ 580 orders/s on average; with a ×5 peak that’s ≈ 2,900 TPS. That’s past one node, so writes need to be split.
What to do with a big table: measure first (size, read/write ratio, main queries), then climb one step at a time: rewrite the query → index → archive old data → cache → use parallelism (parallel query, more workers) → primary-replica → partition/shard → a different database. Each step and a case study are in JOINs and partitioning.
2. Why an RDBMS is hard to scale out
Key idea: an RDBMS is designed for one machine with strong consistency. Scaling out is your job, and you lose exactly what makes it easy to use (joins, transactions).
| Reason | Short explanation |
|---|---|
| Strong consistency | Every commit must be visible to every later reader right away; across many machines that needs consensus, which is expensive. |
| Monolithic design | Joins, foreign keys and transactions assume all data is in one place. Split it and you lose them. |
| Random I/O on writes | Every insert has to find the right B+tree leaf (for the PK and every secondary index) and modify the page, which means many random reads and writes. |
| No built-in sharding | Splitting data, routing and rebalancing are done by the application or by middleware (Vitess, Citus). |
NoSQL goes the other way: distributed from day one. Many systems use an LSM tree (write to memory and a sequential log, then merge files in batches) to turn random writes into sequential ones, and they ship with auto-sharding (MongoDB, Cassandra). How LSM works is in index types beyond the B-tree.
3. Five NoSQL families, NewSQL, and how to score them
Key idea: score every family on the same criteria (ease of learning, ease of modeling, throughput, availability, consistency, ecosystem, read- or write-leaning), then choose by the criterion your problem needs most.
On a 1–5 star scale, based on common ratings (a guide, not absolute):
| Family | Examples | Ease of modeling | Throughput | Availability | Consistency | Leans toward |
|---|---|---|---|---|---|---|
| Relational | PostgreSQL, MySQL | 3 | 2 | 1 | 5 | reads, balanced |
| Key-value | Redis, DynamoDB | 1 | 4 | 4 | 2 | reads |
| Document | MongoDB, Couchbase | 3 | 2 | 3 | 2 | reads |
| Wide-column | Cassandra, ScyllaDB | 1 | 4 | 4 | 1 | writes |
| Graph | Neo4j, JanusGraph | 2 | 3 | 3 | 3 | reads |
| Time-series | TimescaleDB, InfluxDB | 2 | 4 | 2 | 3 | writes |
| NewSQL | TiDB, CockroachDB | 3 | 3 | 3 | high | balanced |
When to think about NoSQL: you need high read/write throughput, you need easy horizontal scaling, you do not need strong consistency across records, and you don’t need special SQL features (complex joins, constraints). Two common choices:
- MongoDB when the data model is flexible and you still need rich queries: IoT device info, a product catalog with many attributes.
- ScyllaDB/Cassandra when you write a lot and query by keys known in advance: messages, posts, timelines.
NewSQL (TiDB, CockroachDB) keeps SQL and ACID transactions but shards and replicates itself with Raft (a consensus algorithm: a commit happens only when a majority of nodes agree). The price: not full ANSI SQL, higher latency per transaction than one node because of consensus, and weak OLAP (TiDB makes up for it with TiFlash). Middleware (Vitess for MySQL, Citus for PostgreSQL) keeps the database you know but adds one more layer to operate.
CAP, PACELC, and why the “pick two” triangle misleads
The CAP triangle is often drawn with MySQL on the CA edge, MongoDB/HBase on CP and Cassandra/DynamoDB on AP. In reality:
- P is not a choice: every distributed system will see network partitions. The right question is “during a partition, do you give up C or A?”
- A single-node database like MySQL isn’t “CA”; it simply isn’t distributed. Add async replicas and it becomes a system that can serve stale reads.
- Many systems are tunable per query: Cassandra with
QUORUMis close to CP, withONEit’s AP; MongoDB withwriteConcern: majorityandreadConcern: majoritybehaves very differently from the defaults. - PACELC adds: when there is no partition (E, else), the system still chooses between latency (L) and consistency (C). That’s the trade-off you face every day.
Consistency in ACID is not consistency in CAP: ACID-C means “data satisfies all constraints”, CAP-C means “every read sees the latest write” (linearizability).
flowchart TD
A[Data problem] --> B{Need multi-record transactions?}
B -->|Yes| C{Fits one node, replicas included?}
C -->|Yes| D[PostgreSQL / MySQL]
C -->|No| E[NewSQL or Vitess/Citus]
B -->|No| F{Main query}
F -->|By key, very write-heavy| G[Cassandra / ScyllaDB]
F -->|By key, must be very fast| H[Redis / DynamoDB]
F -->|Flexible, many attributes| I[MongoDB]
F -->|Scan many rows to aggregate| J[ClickHouse]
F -->|Metrics over time| K[TimescaleDB]
F -->|Full-text| L[Elasticsearch]
4. Columnar storage for reporting
Key idea: OLAP (analytics: scan millions of rows, need only a few columns) runs fast when data is stored by column instead of by row, because you read only the columns you need and they compress very well.
Row storage: one page holds complete rows. Column storage: each column is its own file, and the i-th value in every file belongs to row i. The query SELECT sum(net_price) FROM fact_sales WHERE date_key = 140102 reads two column files instead of the whole 20-column table.
Two techniques make it fast:
- Bitmap per value: a column with few distinct values (low cardinality), such as
product_sk, gets one bitmap per value (bit i = 1 if row i has that value).WHERE product_sk IN (29, 30, 74)is an OR of three bitmaps followed by counting the 1 bits, which the CPU does with a popcount instruction, extremely fast. - Run-length encoding (RLE): a sparse bitmap is compressed into “9 zeros, 1 one, the rest zeros” → many times less space.
The price: changing one row in the middle of compressed data is hard. The fix: write to memory first (like an LSM memtable), then merge to disk in batches. ClickHouse recommends inserting in large batches (thousands to hundreds of thousands of rows at a time), not row by row.
5. Time-series and search
Key idea: data that is time-stamped, append-only and deleted by age (metrics, device logs) fits a time-series database; searching by text content fits a search engine.
TimescaleDB is a PostgreSQL extension. You create a hypertable and it splits itself into many child tables (chunks) by time range (say 1 day), and optionally by space (device_id). The benefits:
- Inserts always land in the newest chunk, so the index is small and fits in RAM.
- Deleting old data = dropping a chunk (instant), not
DELETErow by row. - Old chunks are compressed by column, and there are continuous aggregates (summary tables that update themselves).
Tips for write-heavy workloads: batch in memory, commit in batches, write in parallel with several workers, and consider synchronous_commit = off (the commit returns before the WAL hits disk; a crash loses at most a few hundred ms of data, but nothing gets corrupted). Use it only for data where losing a little is fine, like metrics.
Elasticsearch is fast thanks to an inverted index (term → list of documents), parallel execution across many shards/segments, reliance on the filesystem cache, and relevance scoring that allows early termination. Details in Elasticsearch indexing basics.
6. Choosing by contention
Key idea: even when both cases “need strong consistency”, the answer differs depending on whether requests fight over the same row. High contention is a limit of the problem, not of the database.
| Situation | Suggestion |
|---|---|
| Strong consistency + high concurrency + low contention (each user edits their own data) | NewSQL (TiDB) or a sharded RDBMS: spreading over more nodes raises TPS almost linearly. |
| Strong consistency + moderate concurrency + high contention (e.g. holding seats, decrementing stock) | One RDBMS node + optimistic locking / conditional updates, keep transactions short. See optimistic and pessimistic locking. |
| Strong consistency + very high concurrency + high contention (flash sale) | Change the approach: decrement stock in Redis with Lua, queue requests, split stock into many “buckets”, then write to the DB asynchronously. |
Remember
- State the rough ceiling of one relational node (QPS, TPS at low/high contention) and use it to estimate the number of nodes.
- Explain the four reasons an RDBMS is hard to scale out, and how LSM trees help NoSQL write fast.
- Pick a database family for: chat messages, a product catalog, revenue reports, device metrics, product search.
- Explain why columnar is fast for OLAP but slow for updates.
- Tell apart the three contention situations and the answer for each.
Interview questions
Mid
When would you pick NoSQL over PostgreSQL?
When the main queries are known up front and go by key, when you need throughput or horizontal scale beyond one node, and when you don’t need transactions or constraints across records.
Example, chat messages: always read by channel_id, very write-heavy, no joins → Cassandra/ScyllaDB. On the other hand, orders + payments need transactions and flexible reporting → PostgreSQL, scaled with replicas/partitions first.
Why do inserts into a MySQL table with many indexes get slower as the table grows?
Each index is a B+tree; every insert has to find the right leaf page in every tree, and once the indexes outgrow RAM, each lookup is random I/O to disk.
Random keys (UUIDv4) make it worse because they insert into the middle of the tree and cause page splits. To reduce it: fewer secondary indexes, increasing keys (UUIDv7, Snowflake), batch inserts, or an LSM-based system for write-only workloads.
Where is a columnar database faster than a row store, and where is it slower?
Faster when scanning many rows but few columns (it reads only the needed column files, compresses well, uses bitmaps); slower when reading or changing one full row, because that row is scattered across every column file.
That’s why OLTP uses a row store, reporting uses columnar, and the two are connected with CDC or Kafka.
Senior
A system needs 3,000 write TPS with strong consistency. What do you pick?
First ask about contention: 3,000 TPS spread over millions of users is a completely different problem from 3,000 TPS on a handful of rows.
- Low contention: one node does about 1,000 TPS, so writes must be split. Shard the RDBMS by user (Vitess/Citus) if the team is strong in MySQL/PostgreSQL, or use TiDB/CockroachDB if you need cross-shard transactions and can accept consensus latency.
- High contention: more nodes won’t help; reduce contention by design (split the hot row, Redis + Lua as a gate, queueing), then write to the DB asynchronously.
Is NewSQL free? What’s the price?
No. Every commit needs a majority of replicas to agree (Raft), so write latency is higher than one node: a few ms, or tens of ms across regions. Contended transactions still have to retry.
On top of that: not full ANSI SQL, some PostgreSQL/MySQL features are missing, the cost of running many nodes, and OLAP needs a separate component. It’s worth it when data and write load outgrow one node and you still need transactions.
Design storage for metrics from 1 million devices, one point every 10 seconds, kept for 90 days. Which database, and why?
A time-series database (TimescaleDB if you need to join with business data), because the data is append-only over time, read by device + time range, and deleted by age.
Estimate: 1M / 10 s = 100K points/s, 8.64 billion points/day; at ~20 bytes/point that’s ~170 GB/day, ~15 TB for 90 days uncompressed; columnar compression (usually ×10) brings it to ~1.5 TB. Hypertable with 1-day chunks, compress chunks older than 2 days, a retention policy that drops chunks after 90 days, and continuous aggregates per minute/hour for dashboards. Write through Kafka, then batch insert.
What does CAP say, and why is “MySQL is CA” the wrong way to put it?
CAP: when the network is partitioned, a distributed system must choose between answering (A) and answering with the latest value (C). P is not optional.
A single MySQL node isn’t distributed, so it isn’t on the triangle at all. With async replicas, replica reads can be stale (giving up C) and failover can lose writes. PACELC adds: even in normal operation you still trade latency for consistency.
Useful phrases for interviews
- “The bottleneck isn’t the database, it’s contention on a single hot row.”
- “Relational databases scale up easily but scale out with a lot of effort.”
- “A back-of-the-envelope estimate says one node handles this load.”
- “Chat messages fit a wide-column store keyed by channel.”
- “In Cassandra we do query-first modeling, one table per access pattern.”
- “Columnar storage only reads the columns the query touches.”
- “Low-cardinality columns compress well with run-length encoding.”
- “A hypertable lets us drop a whole day of data instantly.”
- “Every commit in TiDB waits for a Raft consensus round.”
- “A like counter can live with eventual consistency.”
Notes based on the System Design course (Ronin Engineer), rewritten in my own words.