Chapter 01 · Why cache?01 / 19

Every read hits the database

Meet an outdoor-gear shop: three shoppers, one app server and a Postgres database. Every product page runs a query that joins the product with its 12,000 reviews to get the average rating. It takes about 50 ms.

Each orange dot is one of those queries, and the bars inside Postgres are queries running right now. The database is busy, and every shopper waits the full 50 ms, longer when queries have to queue.

Now look at what it computes. The tally counts reads per product: the same few, over and over. Trail boots (product:42) alone gets about a third of all views. Postgres works out the same answer again and again.

Popularity like this is so common it has a name: a Zipf distribution. The second most popular item gets roughly half the views of the first, the third roughly a third, and so on down a long tail.

product page queryin the real world
-- runs on every product page view
SELECT p.name, p.price,
       avg(r.stars), count(r.id)
FROM products p
JOIN reviews r ON r.product_id = p.id
WHERE p.id = 42
GROUP BY p.id;
 
-- EXPLAIN ANALYZE says:
--   Planning Time: 0.3 ms
--   Execution Time: 48.7 ms