A benchmark with no network in it measures the floor

A cascade delete written the way an ORM writes it — destroy the parent, walk the children, destroy each one — is the same work as three set-based statements. It is not the same number of round trips, and in production that difference was the whole of a seven-minute request.

I wanted to write that up with a reproduction rather than a war story, so I built the smallest faithful version: a documents/blocks/attributes tree, foreign keys, indexes on the referencing side, 2,000 blocks on the document under test. The per-row arm loops in PL/pgSQL issuing a DELETE per child, the way a library would. The set-based arm does three statements.

Here is what the arms measured.

Arm	Median	Best	Buffers	Plan shape
row-at-a-timeone DELETE per child row, per level — the shape an ORM cascade produces	125 ms	125 ms	n/a	no plan — utility statement
set-basedthree statements in total, following the same dependency order	79 ms	79 ms	18,868	ModifyTable -> Hash Join -> Seq Scan -> Hash \| ModifyTable -> Bitmap Heap Scan -> Bitmap Index Scan(blocks_document_id_idx) \| ModifyTable -> Index Scan(documents_pkey)

Every arm returned the same 1 row (result hash 6cfe8d532fb34381), so the comparison is between ways of asking one question rather than between two questions.

1.586x. The incident this is modelled on was a 387-second delete going to 8.7 seconds.

The honest first conclusion

A local reproduction of a round-trip problem measures the floor of it, not the problem.

Deleting 4,001 small rows against a database on the same machine costs about 33 microseconds per statement. Every statement in the real incident crossed a network and a connection pool and cost about 2.9 milliseconds. Same code, same statement counts, roughly ninety times the per-statement cost.

That factor is not something I can measure from here, so I am not going to put it in a table as though I had. What I can do is measure the thing underneath it — the per-statement overhead itself — and show that it is what both versions are actually competing on.

What the cost is made of

The sweep below deletes five trees of increasing size, each in its own rolled-back transaction, with both forms run against each one.

The same delete, at five tree sizes
Blocks	Statements	One row at a time	Set-based	µs per statement
250	501	16 ms	10 ms	32.4
500	1,001	33 ms	29 ms	32.6
1,000	2,001	65 ms	45 ms	32.5
2,000	4,001	123 ms	79 ms	30.8
4,000	8,001	237 ms	141 ms	29.6

Sixteen times the work, and the last column does not move: 1.10x across the whole range. That is the cost of issuing a statement, and it is what the row-at-a-time form is really paying for — not the rows.

Read the last column. Going from 250 blocks to 4,000 — sixteen times the tree, 8,001 statements — the cost of one statement does not move. It sits at roughly 32 microseconds whether it is deleting five rows or five hundred thousand.

That is the shape of the problem. The row-at-a-time form is not paying for the rows. It is paying for the statements, at a fixed price each, and the price is set by the transport rather than by the database. Which is exactly why this is so much worse over a network and barely visible in process.

Put the two facts together and the incident stops being mysterious. 133,973 statements at the 2.9ms per statement those statements actually cost is 388 seconds. The request took 387 seconds. Almost none of that time was the database doing anything; it was 133,973 statements’ worth of getting there and back.

The numbers the arms do not show

Two things about this case are worth stating plainly rather than leaving in a result file.

The per-row arm has no plan. It is a DO block — a utility statement — and EXPLAIN rejects it, because there is no plan: it is a loop, and the statements inside it are 4,001 separate statements, none of which has a plan either. The buffers column for that arm reads n/a rather than a number, because a zero there would have read as “read nothing”, which is the most reassuring answer available and the one nobody questions.

I got this benchmark wrong twice before it was right, and both times it failed quietly rather than loudly.

The first version put the sweep’s statements in separate rolled-back transactions, so the second of the three set-based statements ran against a database where the first had been undone. That one did fail loudly, on a foreign key. The second version ran each statement list twice inside a single transaction as a warmup — except that the first pass deleted the tree, so the timed pass had nothing to do. It reported 8,001 statements in 0.81ms. The only reason I caught that is that the number was impossible.

What I would take from it

If a fix is about how many times something crosses a boundary, a benchmark with no boundary in it will understate the fix by the cost of the boundary. That is not a reason to skip the benchmark — the local run is what told me the per-statement cost is flat, which is the fact that makes the fix worth making. It is a reason to say which part you measured and which part you did not.

Reproducing this

npm run bench -- deleting-a-tree-one-row-at-a-time

The fixture is built in SQL rather than row by row, the deleting arms run in transactions that are rolled back so every round starts from a whole tree, and the harness refuses to report a ratio unless both arms left exactly the same rows behind — compared on a digest of the surviving ids, because a count alone would pass two arms that deleted the same number of different rows.