Engineering story · PostgreSQL Fabric

Twenty thousand rows a second

A batch update that took twenty-five seconds became a third of a second: one SQL rewrite that makes parameter count independent of row count, and nearly 400 GB of indexes that nobody had ever read

PostgreSQL and RustPostgreSQL Fabric3.4-million-item run
Three nearest-neighbour indexes on one table · scan counts Maintained on every update, read by no query.
what the table kept
3.7 GBof index storage left, from 399
  • 0.3 sper batch update, down from 25 s, with more rows in each
  • 20,000rows per second, up from 50, read from the database's own counters
  • 0.19 msper row at most after the rewrite, and as low as 0.06 ms

My research pipeline writes its results back to PostgreSQL through one governed write path, and in a 3.4-million-item run that path was the bottleneck. Each batch update of about 1,350 rows cost roughly 19 milliseconds a row, and the queue behind it grew past 140,000 messages. Two changes fixed it: a different shape of UPDATE statement, and the removal of indexes that had never been used.

The statement

The original update built a table of values inline, joined it to the target and bound one parameter per cell. PostgreSQL allows 65,535 bind parameters per statement, so a twenty-column payload capped a batch at a few thousand rows, and the planner materialised the whole derived table before it could join. The rewrite binds each column as one array and unnests the arrays into a row set: the parameter count equals the column count whatever the row count, the planner joins directly on the key. The old form is kept as an automatic fallback for types the array path cannot bind. Array-valued columns needed a trick of their own, because unnesting flattens a two-dimensional array; they bind as text arrays of array literals and are cast back in the assignment, and a binary encoder handles two-dimensional arrays where the text path will not do. Vector columns are refused on this path rather than silently mishandled.

Bind parameters for one statement · twenty columns
inline values table: one per cell, so rows hit the cap65,535
unnested arrays: one per column, at any row count20

The indexes

The same investigation found three approximate-nearest-neighbour indexes on the table that no query had ever scanned, and every update had been paying to maintain them. They were dropped, with the concurrent recreation commands kept beside the change for rollback. The detection is two catalog queries, and the second is how 196 deadlocks to zero found its missing update key.

index size against index scanslarge, never scanned:pure write costscans0
sequential against index scans per keyneeds an indexindex scansseq
Points illustrative. The first question removes work; the second adds the one index that pays for itself.

The result

Measured at the database rather than at the queue, batch time improved around fifty-fold and per-row cost by one hundred to three hundred times, and the backlog drained.

A queue message is not a row.

One message can carry a bulk update of thousands of rows or fail and write none, so throughput is read from the database's own tuple counters and commit counts, never from queue length.

Two messages off the queue · the rows each one wrote
bulk updatethousands failed batchnone

A collector bug that divided a decimal by a float had been silently dropping samples; the fix casts to an integer, and the ground-truth formula is written down.

What this demonstrates

Database performance work done from the engine's point of view: a statement shaped for the planner and the protocol limit, index cost counted as write cost, and results measured where they are true.

The rewrite and the index decision were mine; AI coding agents, a mix of frontier and open-source models, implemented them under my governance system.