The database is where most systems live or die. You can scale stateless app servers all day by adding boxes, but the database holds state, and state is stubborn — it's the part that doesn't parallelize for free. So this post is about the two questions that matter most: which kind of database to pick, and what to do when one machine can't hold your data anymore.
This is part five of my system design fundamentals series.
SQL vs NoSQL: the choice everyone overthinks
SQL (relational) databases — Postgres, MySQL — store data in tables with a fixed schema and relationships between them. Their superpower is ACID transactions: strong guarantees that a set of changes either all happen or none do, leaving the data consistent. They're brilliant when your data is structured and relationships matter, and when correctness is non-negotiable — anything involving money is a relational database until proven otherwise.
NoSQL databases trade some of those guarantees for flexibility and scale, and they come in flavors:
- Document stores (MongoDB) hold flexible, JSON-like documents. Great when your data doesn't fit neat rows or the schema keeps evolving.
- Key-value stores (Redis, DynamoDB) are simple and extremely fast for lookups by key. Perfect for caching, sessions, and simple high-throughput access.
- Column-family stores (Cassandra) are built for enormous write volumes across many machines. The choice for time-series and event data at scale.
- Graph databases (Neo4j) treat relationships as first-class. When your queries are mostly about connections — social graphs, recommendations — they shine.
Here's the honest truth most "SQL vs NoSQL" debates miss: start with a relational database. It's flexible, well understood, and handles far more scale than people assume. Reach for NoSQL when you have a specific reason — a scale requirement SQL genuinely can't meet, or a data shape (documents, graphs, massive writes) that a specialized store handles much better. Picking NoSQL because it sounds modern is how teams end up rebuilding joins by hand in application code.
Making a single database faster: indexing
Before you distribute anything, make sure your one database is actually working hard for you. The biggest lever is indexing. An index is a separate data structure that lets the database find rows without scanning the entire table — like the index at the back of a book instead of reading every page. Query on a column a lot? Index it, and a slow query can become instant.
The catch is that indexes aren't free: each one takes storage and slows down writes, because every insert or update has to maintain the index too. So you index the columns you query and filter on, not every column. Over-indexing a write-heavy table is a classic self-inflicted wound.
When one machine isn't enough: partitioning and sharding
Eventually your data outgrows a single server. Partitioning splits it into pieces. There are two directions.
Vertical partitioning splits by columns — put rarely used or huge columns in a separate table. Horizontal partitioning (sharding) splits by rows — put some rows on one machine and others on another. Sharding is the one that unlocks real scale, because it spreads both data and load across many machines.
The whole game in sharding is choosing a good shard key — the value that decides which machine a row lives on. The strategies:
- Range-based — rows 1–1000 here, 1001–2000 there. Simple, and great for range queries, but prone to hot spots if activity clusters in one range (think: everyone piling onto the newest records).
- Hash-based — hash the key to pick a shard. Spreads data evenly and avoids hot spots, but you lose efficient range queries, and naive hashing reshuffles everything when you add a machine (which is exactly the problem consistent hashing solves).
- Geographic — shard by region, keeping users' data near them. Lower latency, but uneven if one region dominates.
- Directory-based — a lookup service maps keys to shards. Flexible, but the lookup service becomes a critical dependency you have to keep fast and available.
Why sharding hurts
Sharding is powerful and genuinely painful, and it's worth being honest about the costs before you commit. Cross-shard queries are the big one: a query that needs data from multiple shards (especially a join) is slow and awkward, because the database can no longer do it in one place. Rebalancing — moving data when you add or remove shards — is complex and risky. And transactions across shards largely give up the clean ACID guarantees you had on a single box.
The practical advice: delay sharding as long as you reasonably can. Squeeze your single database with indexing, caching, and read replicas first. Shard when you truly must, and choose the shard key carefully, because changing it later is one of the most painful migrations in the business.
Scaling reads: replication
Sharding scales writes and storage. Replication scales reads and buys you availability, and it's usually the move you make before sharding.
Master-slave (primary-replica) replication is the common pattern: one primary handles all writes, and its data is copied to one or more read replicas that handle reads. Since most systems read far more than they write, this alone scales you enormously — point your read traffic at the replicas and the primary breathes easier. Bonus: if the primary fails, a replica can be promoted to take over, improving availability.
Master-master (multi-primary) replication lets multiple nodes accept writes. It sounds better — no single write bottleneck — but it introduces a nasty problem: two masters can accept conflicting writes to the same data at the same time, and now you have to resolve that conflict. It's more complex than it looks, and most teams are better served by a single primary until they truly can't be.
Replication lag: the gotcha that gets everyone
Replication isn't instant. There's a small delay — replication lag — between a write landing on the primary and showing up on the replicas. This causes a classic bug: a user updates their profile (write to primary), the page reloads and reads from a replica that hasn't caught up yet, and they see their old data. It looks like the save failed. It didn't.
You handle this by knowing it exists. For data where a user must immediately see their own writes, read it from the primary right after writing (read-your-writes consistency). For data where a second of staleness is harmless — a like count, a feed — replicas are perfectly fine. Matching each read to how fresh it truly needs to be is the skill.
When conflicts do happen in multi-primary setups, teams reach for strategies like Last-Write-Wins (the most recent timestamp wins — simple, but silently discards the other write) or application-specific merge logic. None are free; the cleanest option is usually to avoid needing them.
Putting it together
The database is the hardest thing to scale, so scale it in order: pick relational unless you have a real reason not to, index well, cache in front, add read replicas to handle read load, and only shard when a single primary genuinely can't hold the data or the write volume. Each step is more complex than the last, so climb the ladder deliberately rather than jumping to the bottom rung because it sounds impressive.
In the final part of this series, we'll zoom out to the properties that govern all of this once data is spread across machines: distributed systems — the CAP theorem, consistency models, message queues, and microservices.
