Batching: Turning a Thousand Round Trips Into One

There is a number that explains most of database latency complaints, and it is not any number that appears in an execution plan. A query that executes in 200 microseconds and takes 3 milliseconds to arrive is a query spending 93% of its time on the network, and no index will change that.

Batch the round trips and the arithmetic changes shape. The interesting part is not the API call; it is which semantics remain after latency, transaction scope, server concurrency, and failure behavior change.

Batching also changes the two numbers most teams size around. Fewer round trips can reduce connection hold time, which changes connection-pool sizing. A batch created to hide lazy loading is usually an N+1 wearing a transport fix; the repeated database work remains, as described in EF Core and the N+1 query problem. Write batching also changes commit and WAL behavior, so its durability cost belongs beside fsync, group commit, and durability.

What a round trip costs

  one round trip, same region

    client -> server      0.4 ms
    server work           0.2 ms
    server -> client      0.4 ms
                          ------
                           1.0 ms, of which 0.2 ms is work

  a thousand of them, sequentially

    1000 x 1.0 ms = 1000 ms
    server busy    200 ms  (20%)
    waiting        800 ms  (80%)

Now the same work in one round trip, which is what a bulk insert is.

  one round trip, 1000 rows

    client -> server      0.4 ms
    server work         180.0 ms    (it has something to do now)
    server -> client      0.4 ms
                          ------
                           181 ms, of which 180 ms is work

The database went from twenty percent utilised to fully utilised, and the wall clock went from a second to a fifth of a second. Nothing about the rows changed. The only difference is that the server was given enough work to stay awake while the previous one was in flight.

This is why the optimization is worth the effort on bulk work and mostly not worth it on small ones. The benefit scales with how much idle network time you had.

The decision is latency against parallelism

Batching has a cost that is easy to miss, because it only shows up in the case where you would have wanted concurrency.

Statements in a batch execute in order, one after another, on one connection. A batch of eight independent reads has replaced eight round trips with one, and it has also replaced the possibility of running those eight reads at the same time.

  eight independent reads, one connection, batched

    t=0.0   round trip out
    t=0.4   R1 (0.2ms) R2 (0.2ms) R3 (0.2ms) R4 (0.2ms) R5 R6 R7 R8
    t=1.8   round trip back
            total 1.8 ms

  the same eight reads, four connections in parallel

    t=0.0   round trip out  (x4, simultaneously)
    t=0.4   R1 R2 R3 R4  |  R5 R6 R7 R8
    t=0.9   round trips back
            total 0.9 ms

In this illustrative arithmetic the parallel version is faster because independent queries overlap. A real plan may already use parallel workers, may be I/O-bound, or may contend on the same pages; four client connections do not guarantee four useful cores.

  independent work
    -> compare one batch with bounded concurrency
    -> reserve pool and database headroom for other requests

  dependent work
    -> express the dependency in one SQL statement where possible
    -> otherwise the later client command needs the earlier result

So the rule is not “batch more” or “parallelize reads.” Batch when round trips dominate and serial server execution is acceptable. Use bounded concurrency only when independent latency can overlap without taking capacity from other requests. An N+1 is still best removed with a projection, join, or set-based query; transporting the same N statements in one packet reduces latency but preserves repeated database work.

Writing in bulk

One useful bulk-write shape is an insert from unnest. It accepts typed arrays and keeps SQL text stable as row count changes. It still has parameter, message, memory, and transaction-size limits, so chunk it from measurement rather than describing it as unlimited.

INSERT INTO order_items (order_id, sku, quantity, unit_price)
SELECT *
FROM unnest(@ids::bigint[], @skus::text[],
            @quantities::int[], @prices::numeric[])
  AS t(order_id, sku, quantity, unit_price);

await using var cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.Add(new NpgsqlParameter("ids",         NpgsqlDbType.Array | NpgsqlDbType.Bigint) { Value = ids });
cmd.Parameters.Add(new NpgsqlParameter("skus",        NpgsqlDbType.Array | NpgsqlDbType.Text)    { Value = skus });
cmd.Parameters.Add(new NpgsqlParameter("quantities",  NpgsqlDbType.Array | NpgsqlDbType.Integer) { Value = quantities });
cmd.Parameters.Add(new NpgsqlParameter("prices",      NpgsqlDbType.Array | NpgsqlDbType.Numeric) { Value = prices });

await cmd.ExecuteNonQueryAsync(ct);

The same shape works for updates, which is more useful because batch updates are where the naive loop hurts most.

UPDATE order_items
SET quantity = s.quantity,
    unit_price = s.unit_price
FROM unnest(@ids::bigint[], @quantities::int[], @prices::numeric[])
  AS s(id, quantity, unit_price)
WHERE order_items.id = s.id;

And a delete in the same form. All three take one round trip, one parse, one plan.

For a genuinely large load, Npgsql’s binary COPY path is faster again, because it skips the parse and plan entirely and streams rows in the binary format.

await using var importer = await conn.BeginBinaryImportAsync(
    "COPY order_items (order_id, sku, quantity, unit_price) " +
    "FROM STDIN (FORMAT BINARY)");

foreach (var item in items)
{
    await importer.StartRowAsync();
    await importer.WriteAsync(item.OrderId, NpgsqlDbType.Bigint);
    await importer.WriteAsync(item.Sku, NpgsqlDbType.Text);
    await importer.WriteAsync(item.Quantity, NpgsqlDbType.Integer);
    await importer.WriteAsync(item.UnitPrice, NpgsqlDbType.Numeric);
}

await importer.CompleteAsync();

Outside an explicit transaction, one COPY operation is atomic. Ten million rows can therefore mean one large transaction, a large WAL burst, long lock ownership, replica lag, and expensive recovery after failure. Plain inserts do not create dead tuples merely by committing; bulk updates and deletes do. Chunk size should be chosen from WAL, replication, lock, restart, and throughput measurements.

EF Core already batches SaveChanges, but batching behavior and useful limits are provider-specific. Do not copy SQL Server’s documented thresholds into Npgsql configuration. Measure the Npgsql provider and prefer a set-based ExecuteUpdate/ExecuteDelete or PostgreSQL bulk path when the operation is naturally set-based.

options.UseNpgsql(cs, npgsql => npgsql.MaxBatchSize(1000));

Reading in bulk

Multiple independent reads can share a round trip, and this is the least used feature in the client for no good reason.

await using var batch = new NpgsqlBatch(conn);

var orders = new NpgsqlBatchCommand(
    "SELECT * FROM orders WHERE customer_id = @id");
orders.Parameters.Add(new NpgsqlParameter("id", customerId));
batch.BatchCommands.Add(orders);

var stats = new NpgsqlBatchCommand(
    "SELECT count(*), sum(total) FROM orders WHERE customer_id = @id");
stats.Parameters.Add(new NpgsqlParameter("id", customerId));
batch.BatchCommands.Add(stats);

var recent = new NpgsqlBatchCommand(
    "SELECT * FROM payments WHERE customer_id = @id ORDER BY created_at DESC LIMIT 5");
recent.Parameters.Add(new NpgsqlParameter("id", customerId));
batch.BatchCommands.Add(recent);

await using var reader = await batch.ExecuteReaderAsync(ct);

One round trip, three results. The catch is that a reader for a multi-statement batch has to be stepped through in order, which is awkward if you want the results as separate sets, and the win over four parallel connections is that you have given up the parallelism. For a small number of statements on a hot path it is a genuine improvement; for a large number it is usually not the right tool.

Sequential dependent reads cannot be batched at all, because the second query needs the first result. That case needs two round trips and there is no way around it, which is the real reason to reduce the number of queries rather than to make each one faster.

Know the batch’s transaction and error-barrier behavior

Modern NpgsqlBatch does have an atomic default. When no explicit transaction exists, Npgsql wraps the batch in an implicit transaction. If one command fails, later commands are skipped and earlier commands are rolled back.

await using var batch = new NpgsqlBatch(conn);

batch.BatchCommands.Add(new("UPDATE accounts SET balance = balance - 100 WHERE id = 1"));
batch.BatchCommands.Add(new("UPDATE accounts SET balance = balance + 100 WHERE id = 2"));
batch.BatchCommands.Add(new("INSERT INTO transfers (from_id, to_id) VALUES (1, 2)"));
batch.BatchCommands.Add(new("UPDATE ledger SET reconciled = true WHERE id = 88123")); // fails

await batch.ExecuteNonQueryAsync(ct);

With default options, the fourth statement fails and the implicit transaction rolls the first three back. That is the behavior to pin in an integration test because older APIs, other providers, and explicitly enabled error barriers can behave differently.

  default NpgsqlBatch, no existing transaction

    statement 1  executed provisionally
    statement 2  executed provisionally
    statement 3  executed provisionally
    statement 4  failed

    implicit transaction rolls back statements 1-3

EnableErrorBarriers = true changes this boundary. Npgsql inserts protocol synchronization barriers so one command’s error does not prevent independent later commands from running. Without an explicit transaction, successful commands on other sides of a barrier can commit independently. That is useful for best-effort independent work and wrong for a money transfer.

I still prefer an explicit transaction when atomicity is a business requirement. It makes the boundary visible, permits an intentional isolation level, and can include other commands around the batch.

// explicit business transaction around the batch
await using var tx = await conn.BeginTransactionAsync(ct);

await using (var batch = new NpgsqlBatch(conn, tx))
{
    batch.BatchCommands.Add(new("UPDATE accounts SET balance = balance - 100 WHERE id = 1"));
    batch.BatchCommands.Add(new("UPDATE accounts SET balance = balance + 100 WHERE id = 2"));
    batch.BatchCommands.Add(new("INSERT INTO transfers (from_id, to_id) VALUES (1, 2)"));
}

await tx.CommitAsync(ct);

Now the business operation is explicitly one transaction. Do not enable error barriers inside it expecting partial success: PostgreSQL marks the transaction failed after an error until it is rolled back to a savepoint or ended.

SaveChanges is transactional for providers that support transactions, but multiple SaveChanges calls are separate units unless the application opens a larger transaction. The rule is to test the exact client/provider/version and make the business transaction explicit when it spans more than one API call.

Preparation has two different “five” values

PostgreSQL and Npgsql each have a setting that people summarize as “five,” but neither means PostgreSQL has a five-entry plan cache.

SHOW plan_cache_mode;      -- auto by default

  PostgreSQL plan_cache_mode = auto
    prepared statement starts with custom plans
    after five custom plans, PostgreSQL compares their average cost
    with a generic plan and chooses under its documented heuristic

  Npgsql Auto Prepare Min Usages = 5
    only relevant when automatic preparation is enabled

  Npgsql Max Auto Prepare = 0 by default
    zero means automatic preparation is disabled

Calling Prepare() explicitly prepares the batch commands on the physical connection. Automatic preparation instead uses an Npgsql LRU whose capacity is the configured Max Auto Prepare; it is not five unless somebody configured five. A dynamic SQL shape can still prevent preparation reuse and create parse/plan overhead, but the mechanism must be described correctly before tuning it.

  static command text
    stable shape per command
    explicit or configured automatic preparation can be reused

  dynamic command text
    interpolated values or variable IN lists
    distinct SQL identities cannot reuse one prepared statement

Keep command text stable: use parameters for values and ANY or unnest with arrays for variable input counts. Turn on automatic preparation only after measuring parse/plan cost and choosing a capacity that matches the statement working set. A generic plan is not automatically better: skewed parameter distributions can make custom plans materially cheaper.

-- one stable statement for any number of rows
SELECT * FROM orders WHERE id = ANY(@ids);

When a prepared statement has parameter-sensitive plans and the heuristic makes the wrong choice, plan_cache_mode is a diagnostic and narrowly scoped control—not the first repair for dynamic SQL.

WAL, fsync, and why writes benefit most

The network saving is the reason batching is worth doing. The durability saving is the reason it is worth doing for writes specifically.

A transaction commits by writing a commit record and flushing it. On storage that honours a flush, that flush is the expensive part, and it happens once per commit.

  a thousand single-row transactions

    1000 x BEGIN
    1000 x INSERT
    1000 x COMMIT
         -> 1000 commit records
         -> 1000 flushes          <- the dominant cost
         -> 1000 x round trips

  one transaction, a thousand rows

    1 x BEGIN
    1 x INSERT ... 1000 rows
    1 x COMMIT
         -> 1 commit record
         -> 1 flush
         -> 1 round trip

Group commit, which is the mechanism Postgres already uses to collapse concurrent commits, does part of this for you under concurrency, and a bulk statement makes it unnecessary. This is why COPY is measurably faster per row than a batch of individual inserts even from the same client, and why the difference is larger on slower storage than on a fast one.

Transaction size is the counterpoint. A huge UPDATE or DELETE creates dead row versions that vacuum cannot reclaim while an old snapshot can still see them; a huge INSERT creates no dead versions by itself but still produces WAL, holds locks, delays visibility until commit, and can create replication and recovery pressure. Chunking creates restart points and bounds those effects, at the cost of losing all-or-nothing semantics across the full load.

const int chunk = 50_000;

for (var i = 0; i < rows.Count; i += chunk)
{
    var slice = rows.GetRange(i, Math.Min(chunk, rows.Count - i));

    await using var tx = await conn.BeginTransactionAsync(ct);
    await WriteChunkAsync(conn, tx, slice, ct);
    await tx.CommitAsync(ct);

    // Optional rate shaping belongs here only if WAL, replica, or I/O
    // telemetry shows that the next chunk should wait.
}

A production-ready architecture

  a loop of database calls
        |
        v
  are the statements independent?
        |
   +----+-------------------------------------+
   | yes                                  | no
   v                                      v
  how latency-bound is it?            dependent results cannot be
   |                                    batched at all
   | server-bound                        |
   |   -> batch on one connection        |
   | latency-bound                       |
   |   -> parallel connections, up to the pool
   |                                     |
   +--------------+----------------------+
                  v
  what transaction boundary does the business require?
        |
   +----+------+
   | yes       | no
   v           v
  explicit transaction   default NpgsqlBatch still uses
  with chosen isolation  an implicit transaction; error
                         barriers change failure isolation
        |
        v
  +--------------------------------------------------+
  | keep batch text constant                          |
  |   parameters for values, unnest for variable      |
  |   counts, never an interpolated IN list           |
  | chunk very large transactions so vacuum can work  |
  | measure: round trips, not just total time         |
  +--------------------------------------------------+

A delivery checklist:

1. Count round trips per endpoint, not just latency. A loop of single-row writes is the common case.
2. Use unnest with arrays for variable-length bulk writes so the statement text never changes.
3. Use binary COPY for genuinely large loads, chunked with a commit between chunks.
4. Parallelise independent reads across connections rather than batching them into one.
5. Never batch statements that depend on each other’s results. There is no way to express that.
6. Rely on NpgsqlBatch’s implicit transaction only knowingly; use an explicit transaction when the business unit extends around it, and review EnableErrorBarriers separately.
7. Keep each command’s SQL text stable so preparation can be reused when it is enabled.
8. Avoid interpolated IN lists, which generate a new statement shape per list length.
9. Treat EF Core batching limits as provider-specific and benchmark before overriding MaxBatchSize.
10. Chunk large writes to bound WAL, locks, replica lag, recovery, and dead tuples from updates/deletes; document the resulting partial-progress contract.

Failure stories worth testing

Insert a thousand rows one at a time, then with unnest, and compare

The server utilisation trace is the interesting part. The first version shows the database idle most of the time, and the second shows it busy, and the difference is entirely the round trip.

Fail a command in the middle, with and without error barriers

The default implicit transaction should roll the batch back. Then enable error barriers and observe which independent commands complete without an explicit transaction. Pin both behaviors to the Npgsql version the service deploys.

Time a flush-bound workload with one transaction against a thousand

On storage where a flush is expensive, the difference is dramatic and it is the reason bulk loading is faster than a loop even from the same machine.

Enable automatic preparation, then vary command text

First verify that Max Auto Prepare is nonzero. Compare interpolated/variable SQL with stable parameterized commands and inspect prepared statements plus parse/plan time. Do not interpret Auto Prepare Min Usages = 5 as cache capacity.

Measure an independent read fan-out, batched against parallel

Batching wins on round trips, parallelism wins on wall clock, and the crossover depends on how server-bound each query is. Measuring both on your own queries is more useful than a rule of thumb.

Common mistakes

Mistake	What actually happens	Better decision
Assuming every batch API has the same failure semantics	NpgsqlBatch is implicitly transactional by default; barriers and other providers differ	Test the deployed provider and make business transactions explicit
Batching independent reads to fix an N+1	Round trips fall but repeated database work remains	Replace it with a projection or set-based query
Building command text dynamically	Stable preparation and statement identity are lost	Constant text, parameters for values
Interpolating an IN list	A different statement per list length	= ANY(@ids)
One enormous transaction for a bulk load	A burst of dead tuples vacuum cannot clean	Chunk with a commit between chunks
Optimising by index rather than round trips	Every query was already indexed and still numerous	Reduce the count of statements
Sending a dependent read in a batch	It cannot work, the result is not available yet	Two round trips, or a join
Copying EF batch-size folklore between providers	Provider behavior and useful thresholds differ	Benchmark the actual provider before setting MaxBatchSize
Measuring only total time	A batch and a parallel version can both be fast for different reasons	Count round trips and read utilisation
Forgetting to complete a binary COPY	Disposing the importer cancels and reverts the import	Call CompleteAsync; commit separately only when using an explicit transaction
Assuming batching removes parse cost	A dynamic batch is parsed and planned every time	Keep the statement shape stable

The complete story in one minute

The cost of a database call is the round trip, not the execution, so a thousand single-row inserts can spend eighty percent of their time waiting on the network while the database sits idle. Batch the work and the server gets something to do during the previous network delay, and both latency and utilisation improve. That is the whole argument, and it is arithmetic rather than opinion.

The subtle part is that statements in one batch execute on one connection. Independent queries might finish sooner with bounded concurrency, but only while the connection pool, database CPU, I/O, and other requests have headroom. An N+1 remains an N+1 inside a packet; a projection or set-based statement removes repeated work instead of only its network gaps.

The failure and plan behavior must be stated precisely. Modern NpgsqlBatch uses an implicit transaction when no explicit transaction exists, so a command failure rolls the default batch back. Error barriers deliberately isolate commands and can permit partial success outside an explicit transaction. PostgreSQL’s five-custom-plan heuristic is not a five-entry cache, and Npgsql auto-prepare is disabled by default even though its minimum-use threshold defaults to five when enabled. Keep command text stable, configure preparation from evidence, and test the deployed client version.

For writes, fewer commits can remove flush waits and set-based SQL or binary COPY can remove per-row parse and execution overhead. The new constraint is transaction size: WAL volume, locks, replication lag, restart cost, and dead tuples from updates or deletes. Removing a thousand round trips was one optimization. Preserving the intended transaction and recovery boundary while doing it was the database design.

Technical references

• Npgsql basic usage: batching and implicit transactions
• NpgsqlBatch.EnableErrorBarriers
• Npgsql prepared statements and automatic preparation
• Npgsql binary COPY
• PostgreSQL prepared statements and plan_cache_mode
• PostgreSQL COPY
• EF Core efficient updating and provider-specific batching