The Same Query, A Thousand Times

The problem

A book catalog API. The homepage shows the top 10 books. Every visitor sees the same list. One hundred visitors per minute means 100 identical database queries per minute:

SELECT books.*, authors.name AS author_name, AVG(reviews.rating) AS avg_rating
FROM books
JOIN authors ON books.author_id = authors.id
LEFT JOIN reviews ON reviews.book_id = books.id
GROUP BY books.id
ORDER BY avg_rating DESC
LIMIT 10;

This query joins three tables, computes an average, sorts, and limits. It takes 15ms. At 100 requests per minute, the database spends 1.5 seconds per minute running the exact same query returning the exact same result.

The top 10 books change when someone posts a new review — maybe once every few hours. Between reviews, every query returns identical data.

What caching does

A cache stores the result of an expensive operation so subsequent requests skip the operation entirely:

// Without caching: 15ms per request
route.get("/books/top", {
  resolve: () => {
    const books = db.prepare("SELECT ... complex query ...").all();
    return Response.json(books);
  },
});

// With caching: 15ms first request, <1ms subsequent requests
let cachedTopBooks: any = null;
let cachedAt = 0;

route.get("/books/top", {
  resolve: () => {
    const now = Date.now();
    if (cachedTopBooks && now - cachedAt < 60_000) {
      return Response.json(cachedTopBooks); // <1ms — from memory
    }

    const books = db.prepare("SELECT ... complex query ...").all();
    cachedTopBooks = books;
    cachedAt = now;
    return Response.json(books);
  },
});

The first request runs the query and stores the result. The next 99 requests in that minute read from memory — no database query, no JOIN, no aggregation. The response time drops from 15ms to under 1ms.

Where caching happens

Caching can happen at multiple levels, each closer to the user:

Browser cache — The user’s browser stores responses. No network request at all. Controlled by HTTP headers (Cache-Control, ETag). Covered in the next section.

CDN cache — A server geographically close to the user stores responses. Faster than reaching your server. Controlled by the same HTTP headers.

Server-side cache — Your application stores query results in memory. Faster than querying the database. Covered in Section 3.

Database cache — The database caches query plans and recently accessed pages. Automatic, not directly controlled by you.

This course covers browser caching (HTTP headers), server-side caching (in-memory), and the patterns that make both work correctly.

The tradeoff

Caching trades freshness for speed. Cached data might be stale — a review was posted but the top 10 list still shows the old averages. The question is: how stale is acceptable?

For a book catalog: minutes of staleness is fine. For a stock price: seconds matter. For a bank balance: staleness is unacceptable.

This course teaches how to control staleness at every level.

Exercises

Exercise 1: Add timing to a database query. Run it 100 times. Calculate the total time. Now cache the result and run 100 requests against the cache. Compare.

Exercise 2: Think about the APIs from previous courses (auth, orders, products). Which endpoints return the same data for most requests? Which return user-specific data?