Fourteen Rows at a Time: Batching Math and the Deletes That Dodge You

Part 2 ended on a boast: the physical DELETE, the archive INSERTs, and the snapshot ticket all ship in one round trip. That boast has a load limit, and the limit has a number: fourteen. This post is about where that number comes from, what happens when you exceed it, and — because an interception library owes you this — a complete inventory of the deletions the interceptor will never see.

The arithmetic of a batch

EF Core doesn't send one statement per command; it batches. Each provider has a MaxBatchSize — for SQL Server the default is 42 (genuinely; it's in the provider). Stay under it and your save is one round trip. Exceed it and EF silently splits the work across multiple batches, which still executes correctly inside one transaction but is no longer the single-trip operation you sized for.

Now count what one archived deletion costs. The entity's DELETE is one statement. The archive INSERT is another. The snapshot ticket, when requested, is a third. Call it three statements per archived entity, and the arithmetic writes itself:

MaxBatchSize 42 ÷ ~3 statements per entity ≈ 14 entities per save

That is precisely the default in the interceptor:

/// <summary>
/// Maximum number of entities to archive inline in a single SaveChanges.
/// Beyond this, archive entries are written to ArchiveOutbox and processed
/// asynchronously to avoid splitting EF's command batch across round-trips.
/// Tune in proportion to your provider's MaxBatchSize / StatementsPerEntity.
/// SQL Server default MaxBatchSize=42, statements per entity=3 → limit≈14.
/// Setting MaxBatchSize higher (e.g. 200) allows a higher inline limit.
/// </summary>
public int InlineArchiveLimit { get; set; } = 14;

The limit is configurable through AddArchiveKit, and it should move whenever your provider settings do. Npgsql tolerates much larger batches than SQL Server's default; the sample app runs Postgres and could comfortably set the limit to 50 or beyond. Raise MaxBatchSize on the provider, raise InlineArchiveLimit to match — the two numbers are a ratio, not independent knobs:

builder.Services.AddDbContext<AppDbContext>((sp, opt) =>
{
    opt.UseNpgsql(conn, npgsql => npgsql.MaxBatchSize(200));
    opt.AddInterceptors(sp.GetRequiredService<ArchiveInterceptor>());
});

builder.Services.AddArchiveKit(o => o.InlineArchiveLimit = 60);  // ≈ 200 ÷ 3

To be clear about what's at stake when the ratio is wrong: nothing corrupts. EF splits the work into multiple batches inside the same transaction, so atomicity holds; you're spending extra round trips, not correctness. The limit is a performance contract, which is exactly why breaching it warns instead of throws.

An honest confession about the outbox

Read that doc comment again: "Beyond this, archive entries are written to ArchiveOutbox and processed asynchronously." Now here is what the code actually does past the limit:

if (archivable.Count > InlineArchiveLimit)
{
    _logger.LogWarning(
        "ArchiveKit: {Count} entities exceed inline limit of {Limit}. " +
        "Consider using the outbox pattern for bulk operations.",
        archivable.Count, InlineArchiveLimit);
}

It logs a warning and archives everything inline anyway. The outbox the comment describes does not exist yet. I'm leaving the comment in the codebase because it documents the destination, but the honest current state is: the limit is a smoke alarm, not a sprinkler system. Blow past it and your save still succeeds, still archives, still commits atomically — it just stops being one round trip, and at genuinely bulk scale (thousands of soft-deletes in one save) you're also paying reflection costs per entity and inflating one transaction with the entire archive write.

Is warning-only the wrong call? I went back and forth. Silently switching strategies at a threshold — inline below fourteen, deferred outbox above — changes observable semantics: below the line your archive row exists the instant the save returns; above it, eventually. A library that quietly moves your data from “consistent” to “eventually consistent” based on a count you didn't check is a library that generates 2 a.m. tickets. Until the outbox exists with an explicit opt-in, loud-and-inline is the least surprising behavior. That's the trade I made; the warning tells you when to revisit it.

The deletes that dodge you

An interceptor is a checkpoint on one road. Here are the roads that don't pass it.

Synchronous SaveChanges. The interceptor overrides SavingChangesAsync only. Call the synchronous SaveChanges() and the interception simply doesn't happen — but notice how it fails: the entity was soft-deleted by your code and nothing flips it to Deleted, so it saves as a plain soft delete. The row stays in the table, flagged and filtered by the global query filter. No data is lost; no archive is written. It degrades to the pattern ArchiveKit was built to replace, which is the gentlest available failure — but if half your codebase calls sync saves, half your deletions won't archive, and nothing will tell you. Overriding the sync SavingChanges to match is a small, planned addition; today, async saves are the contract.

ExecuteDelete. EF Core's bulk operations are the big one:

await db.Orders
    .Where(o => o.CreatedAt < cutoff)
    .ExecuteDeleteAsync();   // no tracking, no SaveChanges, no interceptor

ExecuteDelete and ExecuteUpdate translate straight to SQL and never touch the change tracker or the save pipeline. That's their entire value proposition — and it means they are invisible to any SaveChangesInterceptor, not just mine. Rows deleted this way are simply gone. The same applies to ExecuteUpdateAsync(s => s.SetProperty(o => o.IsDeleted, true)): the rows are soft-deleted, but no tracked entities existed, so no archive rows were written, and no later save will notice them.

Raw SQL and everyone else. DELETE FROM orders WHERE ... in a migration script, a DBA in a maintenance window, another service sharing the database, a cascade fired inside the database itself. The interceptor lives in your application process; anything that doesn't is out of scope by construction.

Living with the boundary

You can read that list as a flaw or as a boundary, and I've made my peace with it as a boundary: ArchiveKit archives intentional, application-level deletions. That covers the case the series opened with — a user deletes an order, and the business later asks what it looked like. It does not cover bulk data hygiene, and I think that's correct: if you're ExecuteDelete-ing a million stale rows, you do not want three million archive statements riding along; you want a set-based INSERT INTO ... SELECT into the archive table, written deliberately, run in batches you control. The typed archive tables from part 4 are perfectly good targets for that SQL — the library just won't write it for you.

Two mitigations close most of the remaining gap in practice. First, convention-test the escape hatches: an architecture test that fails on ExecuteDeleteAsync usage against [Archivable] entity types is twenty lines with a library like NetArchTest and turns a silent policy hole into a build break. Second, watch the warning: InlineArchiveLimit breaches in your logs are the early signal that someone is doing bulk work through the tracked path, which is usually the moment to hand them the set-based script instead.

Everything so far has treated the archive row as a given — a thing the interceptor conjures and inserts. But the shape of that row is where half the library's opinions live: which properties become real queryable columns, why every scalar also lands in a jsonb blob, and why the archive's link back to the entity is deliberately not a foreign key.

Next up: the archive table that outlives its source.