Databases
Choosing between SQL and NoSQL, how indexes work, and the difference between replication and sharding
The database is usually where a design gets hard, because it is the one component that cannot simply be duplicated. Every other layer can be copied; data must agree with itself.
SQL or NoSQL
The honest answer is that this choice matters less than people think, and relational is the right default for most systems. But you should be able to say why.
| Relational (SQL) | Document / key-value (NoSQL) | |
|---|---|---|
| Schema | Fixed, enforced | Flexible, per record |
| Joins | Built in | Usually absent — you denormalise |
| Transactions | Strong, across tables | Often limited to one record |
| Scaling writes | Hard — sharding is manual | Designed in from the start |
| Query flexibility | Very high — ad-hoc queries | Limited to planned access patterns |
| Best when | Relationships matter, correctness matters | One access pattern, enormous scale |
Pick based on access patterns, not on fashion
Ask two questions:
- Do entities relate to each other in ways you will query across? Orders belong to users belong to companies — that is relational.
- Do you know the one way you will read the data, and is the volume enormous? That is when a key-value store earns its place.
"NoSQL scales better" is true and usually irrelevant. Most systems never reach the point where it matters, and by then you know far more about your access patterns.
The family of NoSQL stores
| Type | Shape | Fits |
|---|---|---|
| Key-value (Redis, DynamoDB) | key → blob | Sessions, caches, counters |
| Document (MongoDB) | key → JSON | Content with varying fields |
| Wide-column (Cassandra) | row key → many columns | Time series, very heavy writes |
| Graph (Neo4j) | Nodes and edges | Social graphs, recommendations |
| Search (Elasticsearch) | Inverted index | Full-text search, log analysis |
Indexes
An index is what turns a full table scan into a lookup. Without one, finding a row means reading every row.
No index: scan 10,000,000 rows → seconds
With index: ~23 steps of a B-tree → under a millisecondMost indexes are B-trees, which stay balanced and keep values sorted — so they serve range queries (WHERE age BETWEEN 20 AND 30) as well as exact matches. This is binary search applied to disk pages.
Indexes are not free
Every index must be updated on every insert, update and delete. Five indexes mean five extra writes per row change.
A table with many indexes reads quickly and writes slowly. On a write-heavy table, an unused index is a pure cost — and most databases can tell you which indexes are never used.
What to index
| Index this | Reason |
|---|---|
Columns in WHERE | The whole point |
Columns in JOIN | Otherwise every join is a scan |
Columns in ORDER BY | Avoids sorting the result |
| Foreign keys | Almost always queried |
| Do not index | Reason |
|---|---|
| Low-cardinality columns | An index on a boolean rarely helps |
| Rarely queried columns | Cost with no benefit |
| Very wide columns | The index becomes huge |
Composite indexes are order-sensitive
An index on (country, city) can serve a query filtering on country, or on country AND city.
It cannot serve a query filtering on city alone — the same way a phone book sorted by surname then first name cannot help you find everyone called "Sara".
Put the column you always filter on first.
Replication: Copies of the Same Data
Replication makes copies. It gives you read capacity and survivability. It does not help with writes.
Read-after-write is the bug users actually notice
A user edits their profile, the write goes to the primary, the page reloads, the read hits a replica that has not caught up — and their change is gone. They edit it again. Now there is a support ticket.
Three standard fixes:
- Read your own writes from the primary for a few seconds after a write.
- Sticky reads — pin that user to the primary briefly.
- Wait for the replica to confirm it has caught up before reading.
The first is the most common and the simplest.
Synchronous or asynchronous
| Synchronous | Asynchronous | |
|---|---|---|
| Write confirmed when | Replicas have it too | The primary has it |
| Write latency | Higher | Lower |
| Data loss if the primary dies | None | Recent writes are lost |
| Default in practice | Rare | Common |
Most systems use asynchronous replication and accept a small window of possible loss, because the latency cost of synchronous replication is paid on every single write.
Sharding: Splitting the Data
When writes exceed what one primary can take, the data itself must be split. Each shard holds a different slice — not a copy.
| Strategy | How | Watch out for |
|---|---|---|
| Range | Users A–H, I–P, Q–Z | Uneven — surnames are not uniformly distributed |
| Hash | hash(id) % shards | Even, but adding a shard reshuffles everything |
| Consistent hashing | Hash onto a ring | Adding a shard moves only a small fraction |
| Directory | A lookup table of key → shard | Flexible; the directory becomes a bottleneck |
What sharding takes away
Joins across shards stop working. A query touching two users on different shards must fetch both and join in application code.
Transactions across shards stop working. Atomicity within one shard is fine; across shards you need distributed transactions, which are slow and complicated.
Unique constraints across shards stop working. "This email must be unique" needs a separate global check.
Rebalancing is an operational project, not a configuration change.
This is why sharding is last on the ladder. Exhaust vertical scaling, caching and replicas first.
Choosing the shard key
This is the decision you cannot easily undo.
| The key should be | Why |
|---|---|
| High cardinality | Many distinct values to spread across |
| Evenly distributed | No shard should get disproportionate traffic |
| Present in most queries | Otherwise every query hits every shard |
Hot shards are the classic failure
Shard a social network by user id and it spreads evenly — until one account with 100 million followers puts all of its traffic on one shard.
Sharding by timestamp is worse: every new write lands on the newest shard while the others sit idle.
Watch for keys where activity is not uniform, which in practice is most human-generated data.
Choosing, in Order
1. One database, indexed properly → further than most people expect
2. Add a cache → removes most read load
3. Add read replicas → read-heavy workloads
4. Split by feature (users / orders / logs) → independent scaling
5. Shard → only when writes exceed one primaryProper indexing beats most architecture changes
A missing index on a large table can make a query a thousand times slower. Adding it takes minutes.
Before reaching for replicas or shards, confirm the queries are actually indexed. It is unglamorous and it is frequently the whole problem.
Related Reading
- Consistency and CAP — what replication does to correctness
- Caching — the layer that keeps load off the database
- Scaling — where the database sits in the growth ladder
- Binary Search — the idea underneath a B-tree index