Learn the theory
What Happens When 100 Reads Hit the Database: how it works
4 min read · Updated
When 100 requests hit your application in the same second, they don't all hit the database at once — and they can't. A real database has limited connections and limited CPU, so requests queue, execute a few at a time, and finish in waves. Understanding where requests wait — the connection pool, the CPU, or nowhere because a cache answered — is the key to diagnosing slow apps and choosing the right fix.
In short
- Each request needs a connection from a pool; when the pool is empty, requests wait in line (and may time out).
- A connection holder still needs a CPU worker to run the query; with few cores, queries run in waves.
- Total time ≈ (requests ÷ parallelism) × query time. Cut query time, raise parallelism, or send fewer requests.
- Cache hits never reach the database; replicas multiply read capacity; an index shrinks every query.
- A pool that is too big just moves the queue from the pool to the CPU — and can make things worse.
The path of one read request
- The request reaches an application server thread or event-loop task.
- The app may check a cache (Redis, memcached or in-process). A hit returns immediately.
- On a miss it asks the connection pool for a database connection. If none is free, it waits.
- With a connection, it sends the SQL. The database parses and plans the query, then waits for a free CPU worker/process.
- The query reads from the buffer cache (memory) or disk, builds the result and returns it. The connection goes back to the pool.
Connection pools: the first queue
Opening a database connection is expensive (a TCP and auth handshake, plus a server process or thread), so applications keep a pool of reusable connections. If the pool has 10 connections and 100 requests arrive, 10 proceed and 90 queue. If a request waits longer than the pool timeout it fails with errors like “timeout acquiring connection”.
CPU workers: the second queue
Having a connection isn't enough. A database with 4 cores can run about 4 CPU-bound queries at a time, so the rest wait holding their connection. Slow queries make this worse: 100 queries of 200 ms on 4 cores take about 5 seconds, and every waiting query keeps a pool slot occupied — which is how a few slow queries can exhaust the entire pool and take the application down.
Four levers that cope with the load
| Fix | What it does | Trade-off |
|---|---|---|
| Add an index / fix the query | Shrinks every query from hundreds of ms to a few ms — often a 100× win. | Slower writes, more disk space. |
| Cache the reads | Answers repeated reads from memory; only misses hit the database. | Staleness, invalidation, extra moving part. |
| Add read replicas | Spreads reads over several servers. | Replication lag, stale reads, cost. |
| Tune pool / add CPU | More parallelism for the same query cost. | Limited by hardware; a too-big pool hurts. |
Fix the query first (cheapest, biggest), then cache, then replicate, then scale hardware.
Bursts vs steady traffic
The same 100 requests are harmless if they arrive evenly over a second and painful if they arrive in the same millisecond. Spikes (cache expiry, a popular post, a cron job) are what exhaust pools. Defences: queue or rate-limit at the edge, add jitter to cache TTLs, pre-warm caches, and use timeouts and circuit breakers so one slow dependency can't stall everything.
What to monitor
- Pool wait time and utilization — the earliest warning.
- Active vs idle connections on the database.
- p95/p99 query latency, not the average.
- Slow query log and
EXPLAIN (ANALYZE)for the top offenders. - Cache hit ratio and replica lag.
Frequently asked questions
- What happens when 100 requests hit a database at once?
- They compete for a limited number of pooled connections and CPU cores. Some run immediately, the rest wait in the connection pool queue or for a free CPU worker, and finish in waves. If waiting exceeds a timeout, requests fail.
- What is a database connection pool?
- A set of pre-opened, reusable database connections shared by the application's threads or tasks. It avoids the cost of opening a connection per request and caps how many requests may hit the database simultaneously.
- Why do slow queries cause 'connection pool exhausted' errors?
- Each running query holds a connection for its entire duration. When queries get slow, connections stay busy longer, the pool empties, and new requests queue until they time out.
- How many database connections should I use?
- Fewer than most people think. Roughly (cores × 2) plus disk parallelism per database server is a common starting point; beyond that extra connections add contention rather than throughput.
- Do read replicas help with write-heavy load?
- No. Replicas only serve reads; every write still goes to the primary. They also add replication lag, so very recent writes might not be visible on a replica yet.
- Should I cache or add an index first?
- Check the query first: an index or a rewrite fixes the root cause for all callers. Caching then removes repeated work, but it can't help the first request or a cache miss.