The N+1 Query

The N+1 Query

This service returns a list of orders.

• GET/orders returns the 12 latest orders, each with its customer's name and item count.
• POST/orders creates an order.

Clients ask for the list twice a second, and the service keeps up. But the database spends far too much CPU doing it.

Yet no query is slow, and every column is indexed.

System Architecture

Your task

Serve the same 4 lists a second, with the same data, from the same database, for a fraction of the database's work.

You edit only orders.py in the orders-api repo, already cloned in the editor:

• orders.py has both endpoints and all their SQL. db.py (the connection pool) and app.py (wiring) are done for you.
• Every order in the list must still have reference, customer_name and item_count.
• Don't change the database, and don't cache the list. The grader creates an order and expects to see it in the next list.
• Commit + push → the platform rebuilds the image and redeploys the API.

Success Criteria

When you submit, the Judge watches the next 60 seconds of live traffic. All five must pass:

• Lists served ≥ 3.96/s: of the 4/s offered. The starter already manages this; don't break it.
• List p95 latency ≤ 80 ms.
• Database CPU per list ≤ 0.004 s: the starter uses about 0.016.
• New orders show up in the next list: 100%.
• No failed requests, including orders missing a name or item count.

Where to look

• Metrics: the database is busy for a service answering four lists a second.

• Terminal: ask the database which queries run most often, not which are slowest:

  bash

  PGPASSWORD=sdl psql -h postgres -p 80 -U sdl -d sdl -c "
    SELECT calls, round(mean_exec_time::numeric, 3) AS ms, left(query, 60) AS query
    FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;"
  

Submit once database CPU per list has held under 0.004 s for a full minute.