The Cost of Round Trips
Every statement carries fixed overhead: a network round trip, parsing, planning, and transaction bookkeeping. Inserting a thousand rows one statement at a time pays that overhead a thousand times. Batching groups many rows into a single multi row insert or a bulk copy so the fixed cost is amortized.
Ways to Batch
- A multi row insert sends many value tuples in one statement.
- A bulk copy path streams rows with minimal per row overhead, the fastest option for large loads.
- Wrapping many writes in one transaction avoids a commit and its flush per row.
Finding the Right Size
Bigger batches are not always better. Very large batches hold locks longer, grow memory, and lengthen the transaction, which can block other writers and inflate rollback cost on failure. A few hundred to a few thousand rows per batch is a common sweet spot found by testing.
Key idea
Batching amortizes fixed per call cost across many rows, raising write throughput, but oversized batches hold locks and memory, so tune the batch size.