My page makes 50 database calls and takes four seconds

System Design · Foundations
10 min read, about 40 min with practice

Concept
Asked often: medium
Reviewed 2026-11

See the problem

A product list page takes 4 seconds. You look at the database: each query takes 1 ms. You look at the app server: it is nearly idle. Nothing is busy, yet everything is slow.

Then you notice two things. The code runs one query per product, 50 in a loop. And the app runs in Europe, while the database is in the US.

Define the task

What you have: a request that touches memory, disks, a database and the network.
What you want: a quick, rough answer to “where does the time go?”, worked out in your head before you change anything.
The hard part: the speeds differ by a million times or more. Guessing without numbers sends you to fix the wrong thing.

Pause and think

Hint 1 of 3

Each query must travel to the database and back before the next one starts.

Hint 2 of 3

50 × 80 ms is already 4,000 ms.

Hint 3 of 3

Could you ask once for all 50? Could the app and database be closer?

Understand the idea

Picture the numbers at human speed. If reading from memory took about 1.5 minutes, an SSD read would take a day. A round trip across the Atlantic would take 2.5 years.

What	Rough time	If 1 ns were 1 second
Read from memory	100 ns	about 1.5 minutes
Read 4 KB at random from an SSD	100 µs (0.1 ms)	about 1 day
Round trip inside one data centre	0.5 ms	about 6 days
Simple indexed query on a nearby database	1 ms	about 12 days
Read 1 MB in a row from an SSD	1 ms	about 12 days
Disk seek on a spinning hard drive	10 ms	about 4 months
Round trip, Europe to the US east coast	80 ms	about 2.5 years
Round trip, California to Europe	150 ms	about 5 years

These are rough; real machines differ. Learn the gaps, not the digits: memory is about 1,000 times faster than an SSD read, and an ocean is about 100 times slower than a data centre.

Watch the product page three ways. Change the number of products or the ocean’s round trip below the player.

Where 50 queries spend their time

v1: one query per product, far away

Step 1 of 6. A page lists 50 products. The app server runs in Europe; the database is in the US. A round trip across the ocean takes about 80 ms.

Speed Ask me first Show as text

The table and the sums come from this file, which the tests check:

"""Rough latency numbers, and the time a product page spends waiting.

The numbers are orders of magnitude for today's hardware. They change from
machine to machine, but the gaps between them (100x, 1,000x) stay.
"""

# Seconds.
NS = 1e-9
US = 1e-6
MS = 1e-3

LATENCY = {
    "read from memory": 100 * NS,
    "read 4 KB at random from an SSD": 100 * US,
    "round trip inside one data centre": 500 * US,
    "simple indexed query on a nearby database": 1 * MS,
    "read 1 MB in a row from an SSD": 1 * MS,
    "disk seek on a spinning hard drive": 10 * MS,
    "round trip from Europe to the US east coast": 80 * MS,
    "round trip from California to Europe": 150 * MS,
}


def human_scale(seconds: float) -> float:
    """If 1 nanosecond took 1 second, how many seconds would this take?"""
    return seconds / NS


def page_ms(queries: int, round_trip_ms: float, query_ms: float = 1.0) -> float:
    """Time for queries sent one after another: each pays the round trip and the query."""
    return queries * (round_trip_ms + query_ms)


def transfer_seconds(megabytes: float, mb_per_second: float) -> float:
    """Time to move data at a steady speed, like reading a big file."""
    return megabytes / mb_per_second


def calls_ms(times_ms: list[float], parallel: bool) -> float:
    """Calls to other services: one after another adds up; all at once waits for the slowest."""
    return max(times_ms) if parallel else sum(times_ms)

Climb the ladder

Each rung loads the same 50 products. Each query costs 1 ms on the database.

The idea: Loop over the ids and run SELECT ... WHERE id = ? for each one, from Europe to the US.

It is the natural first code, and it is fast on your laptop, where the database is 0.1 ms away.

Waiting: about 4 s
Weak spot: 50 ocean crossings per page
Why: 50 × (80 ms round trip + 1 ms query) = 4,050 ms.

Where it breaks: distance multiplies every call. The same loop that took 50 ms next to the database takes 4 seconds across the ocean.

The idea: Ask once: SELECT ... WHERE id IN (...), still across the ocean.

The waste in Okay: 49 extra round trips that each carried one row.

Waiting: about 81 ms
Weak spot: every page still crosses the ocean
Why: 1 × (80 ms + 1 ms). The work is the same; the waiting is cut 50 times.

Where it breaks: 81 ms is still almost all ocean. Any page that needs two or three dependent queries pays it two or three times.

The idea: Run the app in the same data centre as the database, and batch the query.

The waste in Better: the chattiest link, app to database, crossed the ocean. Now it stays inside one building. The browser crosses the ocean to reach the app, but only a few times per page.

Waiting: about 1.5 ms
Weak spot: the browser may be further from the app
Why: 1 × (0.5 ms round trip + 1 ms query).

When Best isn’t best: if most users are far away, serve static files from a CDN near them, and copy read-only data to a region near them. Then writes still go to one place.

Draw it yourself

Step 1 puts memory between the app and the database. Step 2 serves big photos from near the users.

Step 1: Stop asking the database for the same thing

A product page reads the same 50 popular products thousands of times a minute.
Memory answers in well under a millisecond; the database takes about 1 ms plus the trip.
Keep the database as the place where data is saved.

Solid arrow: sends requests toDashed arrow: copies data to

Watch the traffic

Send 10 requests through your board and see where each one goes. Then make a part fail and send them again.

SendReadsWritesBackground jobs

Make a part fail

How requests move

Requests start at the Client and follow your arrows. Reads try a cache first (4 in 5 are found there), then a read replica, then the database. Writes go to the database. Background jobs go on a queue, and a worker saves them. A load balancer skips a part that is down; nothing else can.

Compare

Way	Round trips to the database	Waiting	Use it when
One query per row, far away	50	about 4 s	Never, past a demo
One batched query, far away	1	about 81 ms	You can’t move the app yet
One batched query, nearby	1	about 1.5 ms	Almost always
From the app’s memory	0	well under 1 ms	Data that changes rarely

Common mistakes and edge cases

Mixing up ms and µs. A µs (microsecond) is a thousandth of a ms. An SSD read is about 0.1 ms, not 100 ms.
Forgetting that waits add up. Calls one after another add their times. Calls at the same time cost the slowest one.
Bandwidth is not latency. A bigger pipe moves more data a second; it doesn’t make one round trip shorter.
Average hides the slow ones. Users feel the slowest requests (the p95 and p99), and one slow call in a chain slows the whole request.
The speed of light is a hard limit. No hardware makes a round trip across an ocean much shorter than about 60 ms.

Say it in the interview

Questions to ask first

Where are the users, and where are the servers and the database?
How many calls does one request make, and are they one after another?
Is the data in memory, on an SSD, or on another machine?

A 60-second walkthrough

“I’d add up where the time goes. Memory is about 100 nanoseconds and an SSD read about 0.1 ms. A round trip is about 0.5 ms in a data centre, and about 80 ms across the Atlantic. This page runs 50 queries one after another from Europe to a US database: 50 times 80 ms is 4 seconds. So I’d batch them into one query, which gives about 80 ms. Then I’d move the app next to the database, which gives about 1.5 ms. Hot data can live in memory, well under a millisecond.”

Likely follow-ups

How long to read 10 GB from an SSD? At about 1 GB a second in a row, about 10 seconds.
Why is a CDN faster? Its servers are near users, so each round trip is a few ms instead of 100 or more.
Calls to six services? In a row they add up; at the same time they cost the slowest one. Run independent calls in parallel, with timeouts.

Red flags interviewers notice

No numbers at all, or numbers off by 1,000 times.
Calling “the database” slow without asking where it is.
Adding servers to fix a request that is waiting, not working.

Practice

1. Fastest to slowest (easy)

Put these in order, fastest first: a round trip from London to New York; reading a value from memory; reading 4 KB from an SSD; a round trip between two servers in one data centre; a seek on a spinning hard drive.

Hint 1 of 3

Memory is measured in nanoseconds; everything else is much slower.

Hint 2 of 3

An SSD read is about 0.1 ms; a round trip in a data centre about 0.5 ms.

Hint 3 of 3

A hard drive seek is about 10 ms; an ocean is tens of ms or more.

Show the answers

The idea: Memory first, network last, the rest by guess.

Many people put the data centre round trip before the SSD read.

Gives: gets the ends right
Weak spot: the middle is a guess
Why: The middle three are close enough that feel is not enough.

The idea: Memory (100 ns), SSD (0.1 ms), data centre (0.5 ms), hard drive seek (10 ms), London to New York (about 70 ms).

London to New York is a bit shorter than Europe to the US east coast in general, but still tens of ms.

Gives: the right order
Weak spot: no sense of the gaps
Why: Each number is rough, but the order is clear.

The idea: Same order, plus the gaps: memory to SSD is about 1,000 times, data centre to ocean about 100 times.

Say the gaps in the interview. That is what shows you can use the numbers.

Gives: order and size
Weak spot: none
Why: The gaps tell you which fix matters: removing one ocean crossing beats a hundred SSD reads.

2. Read 10 GB (medium)

A nightly job reads a 10 GB file. How long does it take from a local SSD, and from a file server over a 1 Gbit/s network?

Hint 1 of 3

An SSD reads about 1,000 MB a second when the data is in a row.

Hint 2 of 3

1 Gbit/s is about 125 MB a second (8 bits in a byte).

Hint 3 of 3

Time is size ÷ speed.

Show the answers

The idea: Treat it as many 4 KB random reads at 0.1 ms each.

Random reads and reads in a row are different numbers. Big files are read in a row.

Answer: about 4 minutes
Weak spot: the wrong model
Why: 2.5 million reads × 0.1 ms is 250 s, but a big file is read in a row, which is much faster.

The idea: 10,000 MB ÷ 1,000 MB a second.

Fast SSDs read several GB a second, so say “about 10 seconds, maybe less”.

Answer: about 10 s
Weak spot: forgets the network case
Why: Reading in a row is limited by the SSD’s speed, about 1 GB a second.

The idea: SSD: about 10 s. Network: 10,000 MB ÷ 125 MB a second = 80 s; the network is the limit there.

A 10 Gbit/s network would bring it to about 8 seconds, and then the disks may become the limit.

Answer: 10 s and 80 s
Weak spot: none
Why: Find the slowest link on the way: here, the 1 Gbit/s network.

3. Six calls in checkout (hard)

Checkout calls six services: stock, prices, tax, fraud, payment and shipping. Each takes about 20 ms. Checkout takes 120 ms, and sometimes 400 ms. Speed it up.

Hint 1 of 3

Are the six calls one after another?

Hint 2 of 3

Which calls don’t need the answer of another?

Hint 3 of 3

When calls run at the same time, the slowest one sets the time.

Show the answers

The idea: Ask each team to make their service faster.

The design, not the services, makes it 120 ms.

Gives: slow, and maybe small gains
Weak spot: the chain still adds up
Why: Six calls in a row cost 6 × 20 = 120 ms even when each is fine.

The idea: Stock, prices, tax and fraud don’t depend on each other: run them at once, then payment, then shipping.

The 400 ms checkouts remain: when one service is slow, everyone waits for it.

Gives: about 60 ms
Weak spot: one slow call still holds everyone
Why: Three steps of 20 ms each, instead of six.

The idea: Run independent calls at once, each with a timeout; move shipping to a queue after payment.

Decide each timeout’s fallback: for fraud, perhaps “hold the order for review”.

Gives: about 40 ms, and a cap on slow calls
Weak spot: needs a plan for a timed-out call
Why: The user waits for what they must see; a slow fraud check can time out to a safe default.

Check yourself

Cheat sheet

Memory 100 ns; SSD random read 0.1 ms; data centre round trip 0.5 ms; indexed query 1 ms; ocean round trip 80 to 150 ms.
Memory to SSD is about 1,000 times; data centre to ocean about 100 times.
An SSD reads about 1 GB a second in a row; 1 Gbit/s is about 125 MB a second.
Calls in a row add up; calls at once cost the slowest. Batch them, and keep chatty parts close.

Go deeper

Colin Scott: Latency numbers every programmer should know (by year)
Jeff Dean: Numbers everyone should know (talk slides, 2009)
Next: Back-of-the-envelope estimates use these numbers.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

Give the rough time for a memory read, an SSD read, a round trip in a data centre and one across an ocean
Find where a slow request spends its time by adding up round trips
Cut waiting by batching calls and placing chatty parts next to each other
Turn a size and a speed into a time, out loud

Status: Not started

My page makes 50 database calls and takes four seconds

See the problem

Define the task

Pause and think

Understand the idea

Climb the ladder

One query per product, across the ocean

One query for all 50

One query, with the app next to the database

Draw it yourself

Step 1: Stop asking the database for the same thing

Watch the traffic

Compare

Common mistakes and edge cases

Say it in the interview

Practice

1. Fastest to slowest (easy)

By feel

With the numbers

With the gaps

2. Read 10 GB (medium)

Use the round-trip numbers

Size ÷ speed for the SSD

Both, and what limits each

3. Six calls in checkout (hard)

Faster services

Run independent calls together

Parallel, with timeouts and later work

Check yourself

Cheat sheet

Go deeper

Can you do these now?