The Database Is the Bottleneck — Read Replicas, Sharding, Partitioning and Query Optimization Explained

System Design Series — Part 3 of 5

🎯 Practice This Before Your Next System Design Interview

Reading about database bottlenecks, read replicas and sharding is one thing. Explaining them out loud, under pressure, in a 45-minute interview is a completely different skill.

→ intervues.club — the AI voice interview platform for real technical interviews. → candidates.intervues.club — practise system design and backend interviews right now, speak your answer, get a report.

Practice the interview before the interview.

1. Where We Left Off

In Part 2 we established the single most important idea in scalable system design — the same ladder we walk through on engineering at Intervues:

Adding more application servers won't save you if the actual bottleneck is somewhere else.

We scaled the application layer horizontally. We put a load balancer in front. We made the servers stateless.

And then this happened:

The bottleneck migrated.

It almost always migrates to the same place.

In the vast majority of real production systems, the database is the bottleneck.

This post is about what to do when it does.

2. Why the Database Is Almost Always the Constraint

Application servers are easy to scale because they are stateless. Any instance can serve any request. You add a node, the load balancer sends it traffic, you're done.

Databases are hard to scale because they are stateful.

The database holds the truth. It must maintain:

• Durability
• Consistency
• Isolation between concurrent transactions
• A single coherent view of data

You can't just clone a database the way you clone an app server and expect it to work.

Ten app servers can share one database.

One database cannot easily become ten databases.

That asymmetry is the entire subject of this article.

3. Start With the Workload: Read-Heavy vs Write-Heavy

Before you touch anything, answer one question:

Is this workload read-heavy or write-heavy?

Every database scaling decision flows from this. The techniques that fix a read-heavy workload are completely different from the techniques that fix a write-heavy workload.

The Read/Write Ratio

The read/write ratio is a number you should be able to state about any system you design.

Read/Write Ratio = Read Queries per second : Write Queries per second

Typical examples:

Why does this matter so much?

Because reads can be replicated. Writes cannot.

You can serve a read from ten copies of the data. A write has to land somewhere authoritative.

This single diagram explains why read scaling is a solved problem and write scaling is an architecture problem.

4. Which Database Resource Is Actually Saturated?

"The database is slow" is not a diagnosis. It's a symptom.

Before you reach for read replicas or sharding, identify the limiting resource. A database can be constrained by any of five different things, and each one has a different fix.

Let's take each one.

Database CPU

Database CPU is burned by parsing, planning, sorting, joining, aggregating and comparing rows.

High CPU usually does not mean "buy a bigger machine." It usually means the database is doing far more work per query than it needs to.

The exact same query. A 1,000,000x difference in work.

Fix CPU with query optimization before you fix it with hardware.

Memory Pressure

Databases keep hot data in RAM — the buffer pool, page cache, or shared buffers depending on the engine.

Memory pressure happens when your working set (the data actively being queried) no longer fits in memory.

A cache hit and a cache miss can differ by 50x to 100x in latency. When memory pressure starts, your p99 latency doesn't degrade gracefully — it falls off a cliff, because a growing fraction of queries move from the fast path to the slow path.

Symptoms of memory pressure:

• Buffer cache hit ratio dropping below ~95%
• Sorts and hash joins spilling to temporary disk files
• Sudden non-linear latency increases as the dataset grows

Disk I/O

Disk I/O is the physical read and write throughput of your storage.

Notice something important: one logical write is many physical writes.

If your orders table has six indexes, an INSERT writes the row once and updates six index structures. This is write amplification, and it's why adding indexes to fix read performance can quietly destroy write performance.

Indexes make reads faster and writes slower. Always.

Database Connections and Connection Pools

This is the bottleneck that catches the most engineers by surprise, because the CPU graph looks fine.

Every database connection costs memory and, in some engines, an operating system process. PostgreSQL might comfortably handle a few hundred; it will not handle 10,000.

With a connection pool:

A connection pool decouples "number of application threads" from "number of database connections." Requests queue for a connection instead of creating a new one.

Now the counter-intuitive part:

A bigger connection pool is often slower than a smaller one.

If your database has 8 cores, it can genuinely execute roughly 8 queries at a time. A pool of 500 doesn't create more parallelism — it creates more contention. The queue moves from your pool (where waiting is cheap and observable) into the database (where waiting is expensive and invisible).

Signature symptom of a connection bottleneck: application latency is high, request queue is deep, and database CPU is at 40%.

Lock Contention

Lock contention is when transactions wait on each other rather than on hardware.

This is the classic flash-sale failure. Ten thousand users buy the same product. Every one of those transactions needs the same row. Your throughput on that row is not determined by your server count or your CPU — it's determined by how long each transaction holds the lock.

Max throughput on a hot row = 1 / (lock hold time)

Lock held 10ms → 100 updates/sec, no matter how many servers you have.

Lock contention fixes:

• Keep transactions short — never hold a lock across a network call to an external API
• Move non-essential work out of the transaction
• Use optimistic concurrency where appropriate
• Shard the hot row (e.g. split one counter into 20 counters and sum them)
• Choose the correct isolation level rather than defaulting to the strictest

5. Slow Queries: Read the Query Plan

Before any architectural change, look at the queries. In my experience most "we need to shard" conversations end after someone finally runs EXPLAIN ANALYZE.

A query plan is the database telling you exactly how it intends to find your data.

The three things you are looking for in a plan:

Full Table Scans

A full table scan (sequential scan) reads every row in the table to find the ones you want.

versus:

To find one row in 10 million:

Important nuance for interviews: a full table scan is not always wrong. If you're reading 80% of a table, a scan is genuinely faster than millions of random index lookups. The planner knows this. The problem is scanning a huge table to return five rows.

Indexes

An index is a separate, sorted data structure that maps column values to row locations.

Rules that actually matter:

1. Index what you filter, join and sort on. Not everything.
2. Composite index column order matters. An index on (customer_id, created_at) serves WHERE customer_id = ? and WHERE customer_id = ? ORDER BY created_at. It does not efficiently serve WHERE created_at > ? alone. Think of a phone book sorted by last name then first name.
3. Functions on the column kill the index. WHERE LOWER(email) = 'x' cannot use a plain index on email. This is called a non-sargable predicate.
4. Every index slows writes and consumes disk and memory.
5. Low-cardinality indexes are near-useless. An index on a boolean column that's 50/50 rarely helps.

N+1 Queries

The N+1 query problem is the single most common performance bug in application code, and it's invisible in the database logs because every individual query is fast.

101 queries, each taking 1ms, plus network round-trip each way. The database CPU looks fine. The endpoint takes 400ms.

The fix:

Or a single JOIN. Every ORM has an eager-loading mechanism — includes, preload, select_related, JOIN FETCH, Repo.preload. Learn yours.

Now scale that thought: your endpoint does 25 queries per request. At 5,000 RPS that's 125,000 queries per second hitting one database. You didn't have a traffic problem. You had a query-count problem.

Query Optimization Order of Operations

Query optimization is almost always cheaper than architecture. A missing index costs one migration. Sharding costs six months.

6. Read Replicas: Scaling the Read Path

You've optimized the queries. You've fixed the N+1s. You've added the indexes. And you are still read-bound.

Now you add read replicas.

Primary/Replica Architecture

In a primary/replica architecture:

• The primary accepts all writes and is the source of truth
• Replicas receive a stream of changes and serve read queries
• Read capacity scales roughly linearly with replica count
• Write capacity does not increase at all

That last point is the one people miss in interviews.

Adding replicas actually adds a small amount of extra load to the primary, since it must ship the replication stream to each one.

Asynchronous Replication and Replication Lag

Most systems use asynchronous replication: the primary commits the write and acknowledges it to the client before replicas have applied it.

That gap is replication lag. Under normal load it might be 5–50ms. Under a bulk import, a long-running migration, or heavy write bursts, it can become seconds or minutes.

Stale Reads

Replication lag produces stale reads — and stale reads produce bug reports that sound insane.

The write succeeded. The data is safe. The user just read from a replica that hadn't caught up.

Consistency Trade-Offs

There is no free lunch here. You are choosing where on this spectrum to sit:

The practical approach used by nearly every large system is routing by query intent, not by a global setting:

A useful pattern: after a user writes, pin that user's reads to the primary for a short window (sticky reads). Everyone else reads from replicas. You get read-your-writes semantics without giving up read scaling.

Where Replicas Stop Helping

7. Partitioning: Splitting a Table

Partitioning splits one logical table into multiple physical pieces — usually within the same database instance.

Why it helps:

• Queries with a date filter touch one partition instead of 800 GB (partition pruning)
• Each partition has smaller indexes that are more likely to fit in memory
• You can drop old data by dropping a partition instead of running a DELETE of 200 million rows
• Maintenance operations like VACUUM and ANALYZE run per-partition

Partition Keys

The partition key determines which partition a row lands in. Choosing it well is the whole game.

The rule:

The partition key must appear in the WHERE clause of your most common queries.

If you partition orders by created_at but 90% of your queries are WHERE customer_id = ?, the database has to check every partition. You've made things worse.

Hot Partitions

A hot partition is what happens when your data is partitioned but your traffic isn't.

This is the classic failure of time-based partitioning in write-heavy systems: every insert goes into the current time bucket. You have twelve partitions and one of them is doing all the work.

Other hot partition causes:

• Partitioning by tenant_id when one enterprise customer generates 60% of the data
• Partitioning by country for an app that's 90% one country
• Any monotonically increasing key (auto-increment IDs, timestamps) as the partition key in a write-heavy system

8. Sharding: Splitting the Database

Sharding is partitioning taken across machines. Each shard is a separate database server holding a distinct subset of the data.

This is the only technique on this list that genuinely scales writes.

Now:

• Total write capacity = sum of all shards
• Total storage = sum of all shards
• Each shard's working set is small enough to fit in memory again

And you can still put replicas behind each shard:

Shard Keys

The shard key is the most consequential decision in the entire system. It is extremely difficult to change later, because changing it means physically moving every row in your database.

A good shard key has three properties:

Concrete examples:

Bad shard keys:

Hot Shards

A hot shard is a hot partition with worse consequences, because now it's an entire server that can't keep up while its siblings idle.

This is the celebrity problem. You sharded by user_id, which is perfectly even in terms of row count. Then one user got 40 million followers and every write about them lands on one machine.

Mitigations:

Cross-Shard Queries

This is the real cost of sharding, and the thing candidates consistently underestimate.

Any query that doesn't include the shard key must hit every shard:

Things that become genuinely hard once you shard:

• JOINs across shards — you have to join in the application layer
• Global ORDER BY with LIMIT — you must over-fetch from every shard and re-sort
• COUNT(*) across all data — scatter-gather every time, or maintain a counter
• Transactions spanning shards — you now need two-phase commit or sagas
• Unique constraints across shards — the database can't enforce them for you
• Foreign keys across shards — they don't exist

If more than a small percentage of your queries are scatter-gather, you chose the wrong shard key.

9. Write Bottlenecks vs Read Bottlenecks — The Decision Tree

Here is the whole article compressed into one diagram. This is what you should be able to draw on a whiteboard.

The Cost Ladder

Always climb this in order. Each rung is roughly 10x the cost of the one below it.

Engineers reach for level 7 in interviews because it sounds impressive. Senior engineers reach for level 1 first, and say why — that's what actually signals seniority.

10. A Worked Example

E-commerce backend. 5,000 RPS. Read/write ratio roughly 50:1.

Observed state:

Step 1 — Count queries per request. Average is 22. The product listing page loads 50 products, then queries the sellers table once per product. Classic N+1. Fixed with eager loading: 22 → 4 queries per request.

110,000 queries/sec → 20,000 queries/sec
Database CPU: 97% → 61%

Step 2 — EXPLAIN ANALYZE the slowest remaining query. WHERE category_id = ? AND status = 'active' ORDER BY created_at DESC is doing a full table scan on 40 million rows. Add a composite index on (category_id, status, created_at DESC).

Database CPU: 61% → 38%
p99 latency: 1,400ms → 90ms

Step 3 — Fix the connection pool. 480 connections for an 8-core database is pure contention. Reduce to 25 per app server.

Latency variance collapses. Throughput rises.

Step 4 — Now scale reads. Product catalog is stale-tolerant. Add two read replicas, route catalog browsing, search and recommendations to them. Keep cart, checkout, inventory and payments on the primary.

Step 5 — Partition the growing tables. orders is 900 GB. Range-partition by created_at with monthly partitions; 95% of order queries are for the last 90 days.

Step 6 — Shard only if writes saturate the primary. Not yet. And note what we avoided by doing steps 1–3 first.

We got a ~2.5x capacity improvement from two code changes before spending a rupee on infrastructure.

11. What Interviewers Are Actually Listening For

When you're asked "the database is slow, what do you do?", the interviewer is not checking whether you know the word "sharding." They're checking your diagnostic discipline.

Strong answers do this:

Weak answers jump straight to "I'd shard it."

Phrases that signal seniority:

• "What's the read/write ratio?"
• "Which resource is saturated — is the CPU high, or are we just out of connections?"
• "Can this read tolerate 50ms of staleness? If yes, it goes to a replica."
• "What's the shard key, and what percentage of queries would become scatter-gather?"
• "I'd add the index first and measure — sharding is a six-month commitment."

12. Key Terms Recap

13. Final Takeaway

The database becomes the bottleneck in almost every system that grows, because it's the one layer you can't make stateless.

But "the database is the bottleneck" is where the analysis starts, not where it ends.

Work in this order:

1. Measure — find the saturated resource, not the highest percentage
2. Fix the queries — N+1s, indexes, query plans, unbounded fetches
3. Cache — the cheapest read capacity you will ever buy
4. Replicate — scale reads, accept bounded staleness where safe
5. Partition — smaller indexes, cheaper maintenance
6. Shard — only when writes genuinely exceed one machine

And know what each one costs you. Replicas cost you consistency. Sharding costs you JOINs, transactions and simplicity. Indexes cost you write throughput.

Adding a database replica won't help if your bottleneck is a missing index. And sharding won't help if your bottleneck is an N+1 query.

Find the constraint. Fix the constraint. Measure again.

Next in the series — Part 4: Caching, Queues and Asynchronous Processing.

Practice saying this out loud via the interview engine or see pricing.

🎯 Now Go Explain This Out Loud

You can read every article on database scaling, read replicas, sharding and query optimization ever written and still freeze when an interviewer asks "walk me through how you'd scale the write path."

The gap between knowing and explaining under pressure is where most technical interviews are lost.

→ candidates.intervues.club — start a real AI voice interview right now. Speak your system design answer out loud, get a structured report on your reasoning, clarity and depth. Free to start.

→ intervues.club — hiring? Run AI voice technical interviews at scale, with candidates who can actually explain their architecture decisions.

No AI copilot feeding you answers. Just you, the question, and your ability to reason.

Practice the interview before the interview. → candidates.intervues.club

Part 3 of 5 in the System Design Fundamentals series. Part 1: Load, Latency and Throughput. Part 2: Horizontal Scaling and Bottleneck Migration. Part 4: Caching, Queues and Async Processing. Part 5: Consistency, Availability and Failure.

Tags: system design interview, database bottleneck, read replicas, database sharding, database partitioning, query optimization, N+1 query problem, connection pooling, replication lag, eventual consistency, scalable system design, backend engineering, distributed systems, SQL indexing, hot partition, shard key, read-heavy vs write-heavy, lock contention, full table scan, database scaling