catalogue / databases
Where most real production incidents actually live.
9 of 9 topics live on this page
One page with 3 lists on it, where every row needs a related record (say, each order's customer). Lazy loading fetches that record separately for every row: the N+1 query an ORM makes easy to write. The other three fetch the same data in a handful of trips, whatever the row count.
Assumes each database query costs a 0.8 ms round trip plus 0.15 ms to run, one after another
round trip 0.8 ms query 0.15 ms lists 3
| Approach | Round trips so far | Page load time |
|---|---|---|
| Lazy loading | 97.9 ms | |
| Batched IN | 5.7 ms | |
| Dataloader | 3.8 ms | |
| One join per list | 2.85 ms |
Batched IN and the dataloader both make one extra trip for related rows; the difference is when. Batched IN makes it after each list (3 extra trips); the dataloader waits until every list has loaded and makes it once for the whole page. Lazy loading waits 34× longer than the joins for the same page.
The same query on an orders table, run two ways. Without an index the database reads every row to find one customer's 8 orders, so the work grows with the table; with an index on customer_id it walks a B-tree only 2 to 4 pages deep and jumps straight to them, so a table 300 times bigger costs one more page read.
Assumes 100 rows per 8 KB table page, ~300 keys per index page, 8 orders per customer each on its own page, 10 µs to read the next page in order and 100 µs to jump to any page (nothing cached). Playback time grows with the log of real time.
SELECT * FROM orders WHERE customer_id = 4217; -- 8 rows of 4,000,000, each dot ≈ 285,714 rows
rows/page 100 fan-out 300 orders/customer 8 in order 10 µs jump 100 µs
At 4,000,000 rows the index reads 11 pages instead of 40,000: about 364× less time for the same answer.
| Plan | 1,000 rows | 100,000 rows | 4,000,000 rows | 100,000,000 rows |
|---|---|---|---|---|
| No index: sequential scan | 1,000 rows 10 pages, 100 µs | 100,000 rows 1,000 pages, 10 ms | 4,000,000 rows 40,000 pages, 400 ms | 100,000,000 rows 1,000,000 pages, 10 s |
| Index on customer_id | 8 rows 10 pages, 1 ms | 8 rows 11 pages, 1.1 ms | 8 rows 11 pages, 1.1 ms | 8 rows 12 pages, 1.2 ms |
A reader has paged deep into a list sorted by id, 20 rows a page. With OFFSET the database has to walk past every row on every earlier page and throw them away before it reaches the 20 it returns; keyset pagination remembers the last id it showed and lets the index jump straight there. The top lanes play each query in real time; the chart shows the rows read for every page.
Assumes an index on id, 1 µs per row read, 0.1 ms fixed cost per query, and a 2,000,000-row table
page size 20 rows table 2,000,000 rows per row 1 µs per query 0.1 ms
| Query | Rows read | Thrown away | Time | Status |
|---|---|---|---|---|
| OFFSET | 200,000 | 199,980 | 200 ms | noticeable |
| Keyset | 20 | 0 | 120 µs | instant |
On page 10,000, OFFSET reads 200,000 rows and throws away 199,980 of them to show 20: 10,000× the work of the keyset query, and 20 rows more for every page deeper. The keyset query costs the same on every page; the price is that it can only step to the next page, not jump to page 10,000 by number.
A list page shows 20 rows and a “showing 1-20 of N” badge. The rows come from an index and cost the same however many match; the exact total has to visit every matching row, so past 4,000 rows the badge is the slow part of the page.
Assumes Postgres, where MVCC means an exact count must check every matching row: 0.3 ms per round trip, 10 µs per row the page fetches through its index, 50 ns per row the count checks (in memory, one core, no parallel workers)
page 20 rows round trip 0.3 ms page row 10 µs count row 50 ns
| Request | SQL | Rows read | Time |
|---|---|---|---|
| Page query | ORDER BY id DESC LIMIT 20 | 20 | 500 µs |
| Exact count | SELECT count(*) | 4,265,795 | 214 ms |
| Estimate | reltuples from pg_class | 1 | 300 µs |
At 4,265,795 matching rows the exact count takes 214 ms, 427× the page it sits on noticeable. The estimate is one catalogue lookup whatever the size. Or drop the total: fetch 21 rows, show 20, and offer “next” only if the extra one came back (“1-20 of many”), which costs nothing beyond the page query.
A product grid that renders a name, a price and a thumbnail, loaded with SELECT * and with those three columns named. The top bars are one row drawn to scale by bytes; below, the whole page crosses the wire. SELECT * also drags along a 2 KB description and a 5.8 KB specs blob that the database first has to fetch from its TOAST table, and both costs grow in a straight line with the rows.
Assumes the column sizes shown, the Postgres wire format (7 B per row, 4 B per value), a 1 Gbit/s link, and 5 µs to fetch and decompress each 1,996 B TOAST chunk, done before the row is sent; the round trip, the same for both, is left out
description 2,000 B specs 5,800 B link 1,000 Mbit/s TOAST chunk 5 µs
| Query | Page | Shown | Wire | TOAST | Total | Received | Status |
|---|---|---|---|---|---|---|---|
| SELECT * | 802 KB | 1.6% | 6.42 ms | 1.5 ms | 7.92 ms | instant | |
| SELECT name, price, thumbnail_url | 14.7 KB | 87% | 118 µs | none | 118 µs | instant |
At 100 rows, SELECT * sends 802 KB to put 12.8 KB on the page, and takes 67× as long as naming the three columns; 19% of that time is the database fetching specs_json from TOAST. Both lines are straight: ten times the rows, ten times the bytes.
Orders joined to their shipments, running for real on 24 rows each, one line per row the query returns. With the join condition each order meets only its own shipment; mistype one alias so the condition compares the shipment with itself and it is true for every pair, so every order is joined to every shipment and the rows returned grow with the square of the table size.
Real joins over 24 orders with one shipment each, counted as rows come out, both at the same rows per second. The table assumes 200 bytes and 1 µs per returned row
10,000 × 10,000 = 100,000,000 rows where 10,000 were wanted
SELECT o.id, s.tracking_no
FROM orders o
JOIN shipments s
ON s.order_id = o.idSELECT o.id, s.tracking_no
FROM orders o
JOIN shipments s
ON s.order_id = s.order_idsample 24 rows row 200 B per row 1 µs
| Join condition | Rows returned | Data sent | Time | Status |
|---|---|---|---|---|
| ON s.order_id = o.id | 10,000 | 2 MB | 10 ms | instant |
| ON s.order_id = s.order_id | 100,000,000 | 20 GB | 1.67 min | never ships |
Every request runs the same UPDATE on one page's view counter. The database lets only one transaction hold that row's lock at a time, so the row commits at most 500 increments a second however many writers pile on, and past about 26 writers each extra one only lengthens the queue. Splitting the counter over 8 rows and summing them on read raises the cap 8-fold: the same cliff, further out.
Assumes each writer does 50 ms of other work, then holds the row lock 2 ms per increment including the commit (both vary randomly around those means). The lanes run that simulation for real, slowed 40×; the chart is its exact average.
UPDATE stats SET views = views + 1 WHERE page_id = 1UPDATE stats SET views = views + 1 WHERE page_id = 1 AND shard = floor(random() * 8)lock hold 2 ms other work 50 ms shards 8 rows
Slowed 40×: each dot is one writer; a 2 ms lock hold lasts 80 ms on screen.
| Counter | Max increments | Increments | UPDATE latency | Writers waiting | Status |
|---|---|---|---|---|---|
| One counter row | 500/s | 500/s | 350 ms | 174 | at the cap |
| 8 counter rows, summed on read | 4,000/s | 3,298/s | 10.6 ms | 28 | queue forming |
One orders table taking as many INSERTs as reads. Each index you add lets another kind of query jump straight to its row instead of scanning the whole table, but every INSERT now writes one more index page. Read work falls, write work climbs, and the total bottoms out at 6 indexes.
Assumes a 10,000-row table (100 pages), 50% reads and 50% INSERTs, 12 query shapes each 0.5× as common as the last, index k serving shape k; a scan reads all 100 pages, an index lookup 2, an INSERT writes 1 page plus 1 per index
| Indexes | Reads that scan | Pages per read | Pages per INSERT | Pages per 100 ops | Status | |
|---|---|---|---|---|---|---|
| This setting | 6 | 1.5% | 3.5 | 7 | 525 | lowest total |
| No indexes | 0 | 100% | 100.0 | 1 | 5,050 | reads scan |
| Best | 6 | 1.5% | 3.5 | 7 | 525 | lowest total |
| Every column | 12 | 0% | 2.0 | 13 | 750 | over-indexed |
Index number 7 would serve a query that is only 0.78% of reads, and every INSERT would pay one more page write for it. The best count is not where the two lines cross (between 4 and 5): it is where one more index saves less read work than it adds in writes.
500 request threads share 100 database connections, simulated live. Each connection frees up about every 1 s, so the pool can serve 100 requests a second. Past that, threads queue inside your app, not in the database, and any thread that waits 2 s fails with an acquire timeout, not a clear 'pool full' error. At 95 requests/s the average wait is 101 ms; at 105 it is 1.81 s.
Simulated for real: random arrivals, each request holds its connection for 1 s on average, first come first served, 2 s acquire timeout. Curves: the same queue solved exactly (M/M/100 with a 2 s deadline)
threads 500 pool 100 hold 1 s timeout 2 s
The pool never refuses a request. A thread that asks for a connection while all 100 are busy is parked and blocks; nothing fails until its 2 s are up, so what the on-call engineer sees is an acquire timeout, while the database runs exactly 100 queries and looks healthy. Past 100 requests/s every extra request waits the full 2 s and then fails: at 150/s that is 33% of all requests.