A stock dashboard that takes twelve seconds
A pharmacy chain's stock dashboard queries a PostgreSQL sales table with 4.2 million rows, growing by about 900 rows per minute at peak. The product page lists 200 items, and the application issues one query per item to sum the quantity sold since the last stock count. Each query takes 55–65 ms, so the page takes about 12 s. A separate monthly report, which filters by branch and date range, runs for 3.8 s. There is an index on product_id but none on sold_at, and the report uses SELECT *. A contractor proposes replacing live queries with a summary table rebuilt nightly, quoting €4,000. Management requires stock figures no older than five minutes and will not extend the maintenance window, currently 02:00–02:30.
1. Diagnose the two slow paths and quantify where the time goes.
Read the worked answer
Product page – N+1 query pattern. The page issues 200 separate round trips. At 55–65 ms each, that is 200 × 60 ms ≈ 12 s, matching the symptom. The per-query time is not catastrophic; the multiplication is. Each round trip also incurs parsing, planning and network latency, so the application is paying fixed overheads 200 times.
Monthly report – sequential scan and over-reading. SELECT * forces PostgreSQL to read every column of 4.2 million rows, roughly 1.1 GB, because no index covers the filter on sold_at; the existing product_id index does not help a branch-and-date predicate. At typical disk throughput this scan plus row assembly explains the 3.8 s.
Reasoning check: the two problems are independent. Batching the product page will not speed the report, and adding a date index will not remove the 200 round trips. A correct answer separates them rather than applying one fix to both.
2. Evaluate the contractor's summary-table proposal against the five-minute freshness requirement, and recommend an alternative with its trade-offs.
Read the worked answer
The proposal fails the requirement. A nightly rebuild leaves data up to 24 hours old, which is far outside five minutes. Even if the rebuild were moved into the 30-minute window, it would still be stale for most of the day. Making the summary acceptable would require refreshing it every few minutes, which turns it into a materialised view with its own refresh cost, locking questions and a second source of truth to reconcile. At €4,000 it is expensive for a problem that is largely a query-shape issue.
Recommended alternative:
- Replace the 200 queries with one
GROUP BY product_idquery using anINlist or a join to the product list; expected latency around 80–150 ms instead of 12 s. - Add a composite index on
(branch_id, sold_at) INCLUDE (product_id, quantity)and stop selecting unused columns. This targets the report directly.
Trade-offs: the extra index slows inserts. At 900 rows/minute (15 per second), one additional index is usually a small percentage overhead and well within capacity, but it must be measured on a replica first. Index build time on 4.2 million rows should be scheduled, and the query plan checked with EXPLAIN ANALYZE before and after. This keeps data live rather than trading freshness for speed.