Databases

• faint tick: one trip to fetch a list
• solid tick: one trip to fetch related rows

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.

SELECT * FROM orders WHERE customer_id = 4217; -- 8 rows of 4,000,000, each dot ≈ 285,714 rows

• a dot is a slice of the table's rows
• rows examined, in the plan's colour
• a row with customer_id = 4217

At 4,000,000 rows the index reads 11 pages instead of 40,000: about 364× less time for the same answer.

• read and thrown away
• the 20 rows returned
• never read

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.

• page query
• exact count
• estimate

At 4,265,795 matching rows the exact count takes 214 ms, 427× the page it sits on noticeable. The estimate comes from the planner's statistics, with no rows read, 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.

• SELECT *
• SELECT name, price, thumbnail_url
• bytes sent, never shown
• specs_json: fetched from TOAST, then sent
• not sent yet

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.

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.id

SELECT o.id, s.tracking_no
FROM orders o
JOIN shipments s
  ON s.order_id = s.order_id

• one line per row returned: each order with its own shipment
• one line per row returned: every order with every shipment

• one counter row
• 8 counter rows, summed on read

• a page written by an INSERT
• a page read by a SELECT
• triangle: an index on that column (dashed: not built)

• reads: pages per 100 operations
• writes: pages per 100 operations
• total: pages per 100 operations

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.

• free thread
• thread waiting for a connection
• thread holding a connection
• timed out

• exact model
• the simulation above, last 5 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. The app has its own limit too: once every thread is parked, new requests are refused before they reach the pool, which the curves leave out, so with few threads or a long timeout the simulation shows refusals where the curves show timeouts.