Project · Rank your database's slow queries in both engines

project

hard

module project

Ship something real. Submit your work when you're done.

Brief

Produce a slow-query report for the seeded dataset in both PostgreSQL and MongoDB that a colleague could act on without talking to you. It must rank candidates by recoverable database time rather than by duration, name the single highest-value fix in each engine, and identify at least one query whose recoverable time is near zero despite consuming significant total time — with an explanation of where its real fix lives.

Deliverables

A `docker-compose.yml` and seed scripts that bring both engines up with the shared dataset, runnable by someone else with one command.
`report.md` containing two ranked tables — one per engine — with columns for calls, total time, mean, docs/rows examined per returned, and estimated recoverable time.
The raw output of the pg_stat_statements prioritisation query and the MongoDB profiler aggregation, pasted unedited.
A named top candidate per engine with a one-paragraph justification in terms of recovered database time and whether a user waits on it.
One identified query with high total time and near-zero headroom, with an explanation of why no index will help it.

How we grade it

Both engines report the same three collections with 200,000 users, 2,000,000 orders and 5,000,000 events — verified by a count in the report.
The ranking is by recoverable time, not by mean or max duration, and the report states the headroom proxy used.
Every number in the report can be reproduced by running a query included in the report.
The near-zero-headroom query is correctly diagnosed as an application-side problem, naming a specific mechanism (N+1, missing cache, or unbatched loop).
The MongoDB profiler settings used are stated explicitly, including slowms and sampleRate, and the report notes that sampled counts are scaled.

Hints

Reset pg_stat_statements and drop system.profile immediately before your measurement window, so the report describes a known period rather than the whole history of the container. Generate load with a short script that mixes the query shapes you care about at realistic ratios — a single hand-run query will not produce a leaderboard worth ranking. If a MongoDB shape appears twice with different planSummary values, that is not a bug in your aggregation: it means the same query sometimes uses an index and sometimes does not, and it belongs in the report.

Expected output

$ docker compose up -d && ./seed.sh pg: users 200000, orders 2000000, events 5000000 mongo: users 200000, orders 2000000, events 5000000 $ ./generate-load.sh --duration 300 generated 412,004 operations across 6 shapes $ psql -f prioritise.sql qp query | calls | mean_ms | total_sec | pct | rows/call | blocks/call ----------------------------------+---------+---------+-----------+------+-----------+------------ SELECT * FROM users WHERE id = $1| 312004 | 0.48 | 150 | 41.2 | 1.0 | 0.1 SELECT * FROM orders WHERE use...| 62011 | 71.90 | 446 | 38.1 | 0.6 | 3120.0 SELECT count(*) FROM events WH...| 12 | 4102.30 | 49 | 4.2 | 1.0 | 88400.0 top candidate: orders by (user_id, status) 446s of 1170s total, 3120 disk blocks per call to return 0.6 rows headroom ~99%, synchronous (order history page) -> fix first near-zero headroom: users by id 41.2% of database time, 1 row per call, 0.1 blocks per call already optimal; 312k calls in 300s is an N+1 in the order-list endpoint. Fix belongs in the application, not the database.

Stretch goals

Add a third arm to the report using auto_explain's JSON output, ranking captured plans by the ratio of actual to estimated rows — a direct preview of module 7.

Write the load generator so the query mix is configurable, then show how the ranking changes when you shift the ratio between the cheap-and-frequent and expensive-and-rare shapes.

Compute real p95 values per shape by merging raw timings, and show how far the answer is from averaging per-shape p95s.

Script the whole report so it runs in CI against a fresh container and fails if any shape's waste ratio exceeds a threshold you choose.

Extend the MongoDB aggregation to group by planSummary and flag any shape that has more than one plan in the window.