Technology · Discipline

PostgreSQL bulk-update engineering

Statement shape, batch width and index cost, each judged by what the database actually does with a write.

PostgreSQL write pathFAFO Core and Inference FabricRust service, SQL functions
Bind parameters in one batch
Inline values · one per cell rows × columns; grows until the limit
Unnest · one per column each array holds every row
  • 3.4Mitems in one write-back run
  • 65,535bind parameters, the ceiling no batch reaches
  • ~400 GBof never-scanned vector indexes dropped

My research pipeline writes millions of rows back to PostgreSQL through one service, and the shape of each statement decides whether that service keeps up. The work lives in the write path of FAFO Core and Function Forge and serves the Inference Fabric's completion side.

Unnest instead of inline values

Bulk updates bind each column as one array and unnest the arrays into a row set joined to the target on its key. The parameter count then equals the column count whatever the row count, which keeps every batch under PostgreSQL's bind-parameter limit, and the planner joins directly instead of materialising a derived table. The run that proved it is told in Twenty thousand rows a second.

Time per batch
Inline values25 s+Unnest arrays~0.3 s
Rows per batch
Inline values~1,350Unnest arrays3,276

The inline-values form is kept as an automatic fallback for types the array path cannot bind; array-valued columns bind as text arrays of literals and are cast back in the assignment, a binary encoder covers two-dimensional arrays, and vector columns are refused on this path rather than mishandled.

Batch width decided where the width is known

Batch width is derived per destination from the parameter limit and the payload's own width, stamped by the producer, with the library re-chunking by the same rule. Upserts carry a conflict strategy. Writes that span parent and child tables arrive as one ordered message and commit in one transaction. Lattice-wide operations run as SQL functions inside the database, next to their indexes.

One batch = the parameter limitwide rows · fewer per batchnarrow rows · more per batchthe producer stamps the width; the library re-chunks by the same rule
Conflict strategy on an upsert
Do nothing
Update named columns
Replacedelete and insert, one transaction
Custom clause
Row counts are illustrative.

The limits are plain: no partitioning or trigger machinery is involved, and duplicate keys inside one batch are not deduplicated.

Indexes counted as write cost

Every index is maintained on every write, so index cost is write cost, and unused indexes are found from the catalog's scan counts. A missing unique index on an update key, found from sequential-scan counts, was the cause of the deadlocks in 196 deadlocks to zero.

Indexes on the write path · read from the catalog's scan counters
vector index · 0 scans · dropped
vector index · 0 scans · dropped
vector index · 0 scans · dropped
unique index on the update key · added
Concurrent recreation commands for the three were kept for rollback.

Throughput is measured from the database's own tuple and commit counters, never from queue length, because one queue message can be thousands of rows or none.