Every outbox processor, background job, or batch reconciliation script eventually runs into the same wall: you need to mark a large set of rows as processed, and each row needs its own value written back, not a single shared value. A simple UPDATE … WHERE id IN (…) will not do because every row gets a different timestamp. I ran into exactly this while tuning an outbox processor recently, and I ended up benchmarking seven different ways to solve it against PostgreSQL.
I tested each approach at 1,000, 10,000, and 25,000 rows. At 10,000 rows, the slowest approach took 2,414 milliseconds and the fastest took 41 milliseconds. That is roughly a 59x gap between the naive version and the fast one, and none of it comes from PostgreSQL being slow. It comes entirely from how many times your application talks to the database.
The scenario
Picture a table of orders. Each row needs a status change to Processed and a unique processed_at timestamp. Here is the table definition used for the benchmark.
CREATE TABLE orders (
id UUID NOT NULL PRIMARY KEY,
customer_name TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'Pending',
processed_at TIMESTAMPTZ
);The update payload is a plain record pairing each order id with its own timestamp.
record OrderUpdate(Guid Id, DateTime ProcessedAt);With 10,000 of these records in hand, here is how each of the seven approaches actually performs.
Approach 1: naive Dapper, one UPDATE per row
This is what most of us reach for first: loop through the list and fire one UPDATE per row inside a transaction.
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
foreach (var update in updates)
{
await connection.ExecuteAsync(
"""
UPDATE orders
SET processed_at = @ProcessedAt,
status = 'Processed'
WHERE id = @Id
""",
new { update.Id, update.ProcessedAt },
transaction: transaction);
}
await transaction.CommitAsync();10,000 rows means 10,000 round-trips to the database. Every ExecuteAsync call sends the SQL, waits for PostgreSQL to parse, plan, execute and respond, and only then moves to the next row. In the benchmark this took 2,414ms at 10,000 rows and crossed 6 seconds at 25,000 rows. On a real production network with any latency at all, this gets worse, not better. The lesson here is not that PostgreSQL is slow, it is that talking to the database 10,000 times in sequence is expensive no matter how fast the database itself is.
Approach 2: EF Core SaveChanges with batching
If you are using EF Core, SaveChanges already batches the generated SQL statements to cut down on round-trips, and you can push the batch size higher than the default of 42 statements per round-trip.
var options = new DbContextOptionsBuilder<AppDbContext>()
.UseNpgsql(connectionString, o => o.MinBatchSize(5000).MaxBatchSize(10000))
.Options;
await using var db = new AppDbContext(options);
var ids = updates.Select(u => u.Id).ToHashSet();
var orders = await db.Orders
.Where(o => ids.Contains(o.Id))
.ToListAsync();
var updateMap = updates.ToDictionary(u => u.Id);
foreach (var order in orders)
{
order.Status = "Processed";
order.ProcessedAt = updateMap[order.Id].ProcessedAt;
}
await db.SaveChangesAsync();This is a real improvement over the naive loop, coming in at 1,030ms at 10,000 rows, roughly half of Approach 1. But there are two hidden costs people often miss. First, you need an upfront SELECT to load all 10,000 entities into the change tracker before you can even start updating them. Second, EF Core still generates 10,000 individual UPDATE statements under the hood, it just packs them into fewer network round-trips. The SQL workload is still there, only the transport got more efficient. Do not mistake SaveChanges batching for a genuine bulk update mechanism.
Approach 3: Dapper with a VALUES table, one statement, one round-trip
This is where the real jump happens. Instead of sending 10,000 separate statements, send a single UPDATE that supplies every new value inline through a derived VALUES table.
UPDATE orders
SET processed_at = v.processed_at,
status = 'Processed'
FROM (VALUES
(@Id0, @ProcessedAt0),
(@Id1, @ProcessedAt1),
...
) AS v(id, processed_at)
WHERE orders.id = v.id::uuidAnd the C# to build and run it looks like this.
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
const string updateTemplate =
"""
UPDATE orders
SET processed_at = v.processed_at,
status = 'Processed'
FROM (VALUES
{0}
) AS v(id, processed_at)
WHERE orders.id = v.id::uuid
""";
var paramNames = string.Join(
",\n ",
updates.Select((_, i) => $"(@Id{i}, @ProcessedAt{i})"));
var sql = string.Format(updateTemplate, paramNames);
var parameters = new DynamicParameters();
for (int i = 0; i < updates.Count; i++)
{
parameters.Add($"Id{i}", updates[i].Id.ToString());
parameters.Add($"ProcessedAt{i}", updates[i].ProcessedAt);
}
await connection.ExecuteAsync(sql, parameters, transaction: transaction);
await transaction.CommitAsync();PostgreSQL receives one statement, builds one execution plan, and updates every row in a single pass. That single round-trip dropped the time from 2,414ms down to 89ms at 10,000 rows, which is the single biggest jump anywhere in this benchmark. There is a real trade-off worth knowing about though: the SQL string itself grows with the batch size, so at 10,000 rows you are sending 10,000 parameter pairs inline in the query text. PostgreSQL caps you at 65,535 parameters per statement, so you have headroom here, but it is worth keeping in mind if your batch sizes grow much larger. Approaches 6 and 7 sidestep this limit entirely.
Approach 4: EF Core ExecuteSqlRaw with the same SQL
The SQL here is identical to Approach 3. The reason to reach for this version instead is that you are already working inside an EF Core DbContext and want the bulk update to share a transaction with other EF Core operations, without dropping down to a raw Npgsql connection.
await using var db = new AppDbContext(options);
await using var transaction = await db.Database.BeginTransactionAsync();
var paramEntries = new List<NpgsqlParameter>();
var valueClauses = new List<string>();
for (int i = 0; i < updates.Count; i++)
{
valueClauses.Add($"(@Id{i}::uuid, @ProcessedAt{i})");
paramEntries.Add(new NpgsqlParameter($"Id{i}", updates[i].Id.ToString()));
paramEntries.Add(new NpgsqlParameter($"ProcessedAt{i}", updates[i].ProcessedAt));
}
var sql = string.Format(updateTemplate, string.Join(",\n ", valueClauses));
await db.Database.ExecuteSqlRawAsync(sql, paramEntries);
await transaction.CommitAsync();Performance is essentially the same as Approach 3 since the exact same SQL is hitting the database, coming in at 166ms versus 89ms at 10,000 rows. That small gap is most likely overhead from EF Core’s transaction wrapper rather than anything to do with the SQL itself. What you gain is the ability to mix change-tracked EF Core operations with raw SQL bulk updates inside one transaction. One gotcha worth calling out: ExecuteSqlRawAsync does not accept Dapper’s DynamicParameters, so you have to build NpgsqlParameter objects directly instead. Dapper and EF Core are not an either-or choice, and this approach is a good example of using both where each one fits best.
Approach 5: Dapper with a CTE
This is a variation on Approach 3 that wraps the same VALUES data inside a named common table expression instead of an inline derived table.
WITH updates(id, processed_at) AS (
VALUES
(@Id0::uuid, @ProcessedAt0),
(@Id1::uuid, @ProcessedAt1),
...
)
UPDATE orders
SET processed_at = updates.processed_at,
status = 'Processed'
FROM updates
WHERE orders.id = updates.idThe C# side looks almost identical to Approach 3, just with the SQL text swapped for the CTE form.
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
var valueClauses = string.Join(
",\n ",
updates.Select((_, i) => $"(@Id{i}::uuid, @ProcessedAt{i})"));
var sql =
$"""
WITH updates(id, processed_at) AS (
VALUES
{valueClauses}
)
UPDATE orders
SET processed_at = updates.processed_at,
status = 'Processed'
FROM updates
WHERE orders.id = updates.id
""";
var parameters = new DynamicParameters();
for (int i = 0; i < updates.Count; i++)
{
parameters.Add($"Id{i}", updates[i].Id.ToString());
parameters.Add($"ProcessedAt{i}", updates[i].ProcessedAt);
}
await connection.ExecuteAsync(sql, parameters, transaction: transaction);
await transaction.CommitAsync();Still one statement, still one round-trip. PostgreSQL materializes the CTE once and joins against it for the update. In the benchmark this came in at 103ms at 10,000 rows, a touch behind the plain VALUES form at 89ms. That gap is small enough to call this a style choice rather than a performance decision. Some teams prefer the CTE form purely for readability when the update logic gets more involved and they want to name the data source explicitly in the query.
Approach 6: Dapper with UNNEST
If you are on PostgreSQL specifically, UNNEST is a cleaner option than either VALUES form. Instead of dynamically building @Id0 through @Id9999 in the query text, you pass two arrays as parameters and let PostgreSQL expand them server-side.
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
var ids = updates.Select(u => u.Id).ToArray();
var processedAts = updates.Select(u => u.ProcessedAt).ToArray();
await connection.ExecuteAsync(
"""
UPDATE orders
SET processed_at = v.processed_at,
status = 'Processed'
FROM UNNEST(@Ids, @ProcessedAts) AS v(id, processed_at)
WHERE orders.id = v.id
""",
new { Ids = ids, ProcessedAts = processedAts },
transaction: transaction);
await transaction.CommitAsync();This has become my default choice for PostgreSQL workloads, for a few concrete reasons. There is no dynamic SQL involved, the query text never changes regardless of batch size, so there is no string.Format and no growing parameter list to manage. There are only two parameters total, Ids and ProcessedAts, and Npgsql maps Guid[] and DateTime[] straight to native PostgreSQL array types. Because the query text is fixed, PostgreSQL can cache and reuse the same execution plan no matter how many rows you are updating, and the whole thing scales cleanly since only the array payload grows while the SQL stays constant. The catch is that UNNEST is PostgreSQL-specific. If you need to support multiple database engines, stick with the VALUES form from Approaches 3 through 5, or look for the equivalent array-expansion function your target database offers.
Approach 7: temp table plus binary COPY
Every approach so far passes data through SQL parameters. At extreme batch sizes that starts to matter, both because of the 65,535 parameter ceiling and the overhead of constructing very large parameter lists. Approach 7 skips parameters entirely: create a temporary staging table, bulk-load into it using Npgsql’s binary COPY, then run one UPDATE … FROM against it.
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync(
"""
CREATE TEMP TABLE temp_updates (
id UUID NOT NULL,
processed_at TIMESTAMPTZ NOT NULL
) ON COMMIT DROP
""",
transaction: transaction);
await using (var writer = await connection.BeginBinaryImportAsync(
"COPY temp_updates (id, processed_at) FROM STDIN (FORMAT BINARY)"))
{
foreach (var u in updates)
{
await writer.StartRowAsync();
await writer.WriteAsync(u.Id, NpgsqlTypes.NpgsqlDbType.Uuid);
await writer.WriteAsync(u.ProcessedAt, NpgsqlTypes.NpgsqlDbType.TimestampTz);
}
await writer.CompleteAsync();
}
await connection.ExecuteAsync(
"""
UPDATE orders
SET processed_at = t.processed_at,
status = 'Processed'
FROM temp_updates t
WHERE orders.id = t.id
""",
transaction: transaction);
await transaction.CommitAsync();Binary COPY is the most efficient data-loading path Npgsql offers. It bypasses the SQL parameter system altogether and streams rows directly using PostgreSQL’s binary wire protocol. The ON COMMIT DROP clause means the temp table cleans itself up automatically once the transaction ends, so you are not left with staging tables lying around. The trade-off is added code complexity, since you are technically running two operations, a COPY followed by an UPDATE, instead of one statement. At 10,000 rows this came in at 41ms, matching UNNEST exactly. At larger batch sizes, say 100,000 rows and beyond, I would expect binary COPY to pull further ahead, since the query text stays fixed regardless of row count and there is no parameter limit to run into.
Benchmark results
Here are the full numbers across all three batch sizes, measured against PostgreSQL.
Approach 1,000 rows 10,000 rows 25,000 rows
-------------------------------------------------------------------------------
1. Naive Dapper (loop) 317ms 2,414ms 6,283ms
2. EF Core SaveChanges 575ms 1,030ms 1,767ms
3. Dapper + VALUES table 19ms 89ms 233ms
4. EF Core ExecuteSqlRaw + VALUES 58ms 166ms 282ms
5. Dapper + CTE 13ms 103ms 251ms
6. Dapper + UNNEST 12ms 41ms 92ms
7. Temp table + binary COPY 11ms 41ms 93msA few things are worth pulling out of this table. EF Core SaveChanges actually beats naive Dapper at 10,000 rows and above, purely because its batching cuts down round-trips, but both approaches trail far behind every single-statement option. The single biggest jump in the whole table is between Approach 2 and Approach 3, which is the point where you move from N statements to 1 statement. Approach 5’s CTE form performs essentially identically to Approach 3’s VALUES form, confirming that choice is about readability, not speed. UNNEST and binary COPY are the fastest at every scale tested, and the gap between them and the VALUES or CTE approaches widens as batch size grows.
What I would actually use in production
The core takeaway is simple: round-trips are what cost you, not the SQL itself. Cutting 10,000 database calls down to a single call is where essentially all the performance gain comes from. Everything past that point is a smaller, secondary optimization.
A few practical points worth carrying into your own codebase. EF Core SaveChanges is not a bulk update mechanism, even though it feels like one. It reduces round-trips through batching, but it still generates one UPDATE statement per row internally, and it needs an upfront SELECT to populate the change tracker before it can do anything. For genuine bulk mutations, raw SQL through Dapper or ExecuteSqlRaw is the better tool for the job, and the same reasoning applies to bulk inserts, not just updates.
If you are specifically targeting PostgreSQL, UNNEST and binary COPY are the two approaches worth defaulting to at scale. Both keep the query text fixed regardless of batch size and carry no parameter count ceiling. The VALUES and CTE forms are perfectly solid choices for smaller batches, or when you need SQL that is more portable across database engines rather than PostgreSQL-specific syntax.
Do not feel like you have to pick either Dapper or EF Core and stick with it everywhere. It is completely reasonable to query with EF Core for your regular application code and drop into raw SQL through Dapper or ExecuteSqlRaw specifically for bulk mutations, sharing the same underlying transaction across both. That combination gives you the ergonomics of EF Core for regular CRUD and the throughput of hand-written SQL exactly where you need it, which is usually a handful of hot paths rather than your entire codebase.
One production caveat worth adding: before reaching for binary COPY on a hot path, check that your team is comfortable maintaining it. It is the fastest option here, but it is also the one with the most moving parts, a temp table, a binary writer, and a separate UPDATE statement, so weigh that operational complexity against how often this code path actually runs at the batch sizes that would justify it.
Leave a Reply