Same Request, Same Data — One Is 200× Faster

We recorded this live: a real API endpoint aggregating 20 million Postgres rows took ~1 second on every call — the database recomputed the full answer each time. We added five lines of Redis caching. The same call: 6 milliseconds (and the next ones under 2ms).

That’s what a cache is: compute the answer once, store it in fast memory, serve the copy. Here’s the exact setup — you need Python 3 and Docker, about 10 minutes.

1. The database (with real weight)

docker run -d --name pg-demo -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16
docker run -d --name redis-demo -p 6379:6379 redis:7

docker exec -it pg-demo psql -U postgres -c "
CREATE TABLE orders AS
SELECT g AS id,
       'product-' || (g % 2000) AS product,
       (random()*500)::numeric(10,2) AS amount
FROM generate_series(1, 20000000) g;"

docker exec -it pg-demo psql -U postgres -c "SELECT count(*) FROM orders;"
# → 20000000

2. A tiny real API (no cache yet)

# app.py — top products by revenue
from fastapi import FastAPI
import psycopg2

app = FastAPI()
db = psycopg2.connect("postgresql://postgres:secret@localhost:5432/postgres")

SQL = """SELECT product, count(*) AS orders, round(sum(amount)) AS revenue
         FROM orders GROUP BY product ORDER BY revenue DESC LIMIT 5"""

@app.get("/top-products")
def top_products():
    with db.cursor() as cur:
        cur.execute(SQL)
        rows = cur.fetchall()
    return {"top": [{"product": p, "orders": o, "revenue": float(r)} for p, o, r in rows]}

pip install fastapi "uvicorn[standard]" psycopg2-binary redis
uvicorn app:app --port 8000

# feel the pain — every call repeats the full 20M-row aggregation:
curl -s -o /dev/null -w "real %{time_total}s\n" localhost:8000/top-products
# → real 1.24s … real 0.94s … real 0.99s  (ours, measured live)

Why is it slow every time? Postgres caches pages, not answers — the GROUP BY over 20M rows is recomputed on each call.

3. The five lines

import redis, json                                   # +1
cache = redis.Redis()                                # +2

@app.get("/top-products")
def top_products():
    hit = cache.get("top-products")                  # +3
    if hit: return json.loads(hit)                   # +4  check Redis FIRST
    with db.cursor() as cur:
        cur.execute(SQL)
        rows = cur.fetchall()
    result = {"top": [{"product": p, "orders": o, "revenue": float(r)} for p, o, r in rows]}
    cache.set("top-products", json.dumps(result), ex=60)   # +5  store for 60s
    return result

Restart and measure:

curl -s -o /dev/null -w "MISS real %{time_total}s\n" localhost:8000/top-products
# → MISS real 1.28s      (Redis is empty — DB does the work ONCE)
curl -s -o /dev/null -w "HIT  real %{time_total}s\n" localhost:8000/top-products
# → HIT  real 0.006s     (and 0.001s after that — instant, every time)

Ours, measured live: 1.28 s miss → 6 ms hit. Over 200× faster, and the database does nothing.

The honest part (this is the interview answer)

1. Cached answers go stale. Our ex=60 means the answer can be up to 60 seconds old — new orders won’t show until the cache expires. Knowing when to refresh is the famously hard part: cache invalidation.
2. Caching helps reads, not writes. Every write still hits the database — and now also has to think about the cache.
3. It only pays when the same question repeats. A query that’s different every time (per-user, per-filter) caches poorly — key design matters.

Clean up

docker rm -f pg-demo redis-demo && docker volume prune -f