> ## Content Index
> Fetch the complete content index at: https://devinside.cloud/llms.txt
> Use this file to discover other available public pages before exploring further.

# A checkpoint must follow the effect.
- URL: https://devinside.cloud/bounded-batches-moving-large-datasets-through-an-ef-core-service/
- Published: 2026-11-12T15:49:29.000Z
- Updated: 2026-11-13T00:58:13.000Z
- Description: The export has reached row 8,000 when the worker stops. The destination contains the last page, but the source checkpoint still says 6,000. Restarting from 6,000 repeats work.
- Author: Marcin Borecki
- Tags: Engineering, Performance & Reliability, #m04, #devinside-review

Historical edition · Sources and examples scoped to 2025-07-10.

*Retrospective technical scope: 10 July 2025\. The worked job is a design example; its calculated counts are not database measurements.*

The export has reached row 8,000 when the worker stops. The destination contains the last page, but the source checkpoint still says 6,000\. Restarting from 6,000 repeats work. Restarting from 8,000 might skip work if the destination only appeared to accept the page. The problem is no longer how quickly EF Core can read a table. It is what the service can honestly claim after interruption.

I would start a large-data design with that uncomfortable moment. It forces the size of a batch, the lifetime of memory and the meaning of progress into the same conversation. A service that only works while every step succeeds is not yet an export protocol.

The example throughout this article moves a finite selection of immutable records to a destination that can recognize an already accepted record. The source has a unique, increasing identifier. We process in identifier order and retain the highest identifier whose destination effect is durably known. A bounded queue may overlap reading and writing, but there is one ordered destination consumer. Those restrictions are deliberate: they make the progress invariant understandable before adding concurrency.

The code uses a historical baseline of .NET 9 and EF Core 9.0.0\. It is illustrative source, not a claim that this complete service was compiled or run in July. The later executable companion is a separate investigation and does not supply evidence for the counts or conclusions in this edition.

Try the failure first. The model has twenty-four immutable records. Leave the batch at four and crash on batch two, then choose **Show final state**. Change checkpoint order or turn off destination receipts to see omission and repeated effects emerge from the same input.

## Decide which records belong to the job

“Export the table” is underspecified when the table changes during the export. A cursor over increasing identifiers can avoid revisiting earlier identifiers, but it does not by itself create a stable snapshot of the row contents. A row can be updated after it was read. A deletion can remove a row before its turn. A newly inserted row can extend the job indefinitely if the selection has no upper boundary.

For this worked design, the job captures an upper identifier, `upperId`, when it is created. It includes records where `Id <= upperId`, and it assumes those selected records are immutable for the job's lifetime. If the real source is mutable, that assumption must be replaced with a mechanism such as a database snapshot, a versioned export view or a materialized list of version identities. A high-water mark alone is not snapshot isolation.

This choice affects recovery. A checkpoint can identify the next record only if the meaning of the preceding records is stable enough to replay. If identifier 42 now represents different bytes, a receipt saying “42 was accepted” is insufficient. The receipt needs a content version or hash, or the source must guarantee immutability.

![Membership and progress are different cuts](https://devinside.cloud/content/images/2026/11/devinside/v4/M04/selection.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/M04/selection.svg)Swipe the drawing to inspect it.

The cut at `upperId` defines membership. The checkpoint defines completed progress within that membership. They are different boundaries and should be stored separately. Moving the upper bound during a restart quietly changes the job the operator is trying to recover.

The identifier must also have a deterministic order. If the desired order is a timestamp that can repeat, use a stable tie-breaker such as `(CreatedUtc, Id)` and make the cursor predicate match that compound order. A page boundary that cannot distinguish equal values invites omission or repetition even without a crash.

## Ask the database for a page, not a future heap problem

The first bound belongs in the query. Loading every matching entity and later splitting the resulting list into batches bounds the destination calls, not the amount of source material already retained in memory.

Here is the shape of the selected read. The entity and DTO are application-owned types. The page is projected into the fields the destination needs, and the query does not ask EF to retain change-tracking state for the read-only export.

```csharp
public sealed record ExportRow(long Id, int Version, string Name);

static Task<List<ExportRow>> ReadPageAsync(
    ExportDbContext db, long afterId, long upperId,
    int pageSize, CancellationToken cancellationToken)
{
    return db.SourceRows
        .AsNoTracking()
        .Where(row => row.Id > afterId && row.Id <= upperId)
        .OrderBy(row => row.Id)
        .Select(row => new ExportRow(row.Id, row.Version, row.Name))
        .Take(pageSize)
        .ToListAsync(cancellationToken);
}

```

`AsNoTracking` and `ToListAsync` are present in the tagged EF Core 9.0.0 query extensions. The same source documents the restriction on overlapping operations on one context. That is an API availability check, not proof of the SQL generated by this complete snippet or its execution plan. [EF Core 9.0.0 query extension source](https://raw.githubusercontent.com/dotnet/efcore/v9.0.0/src/EFCore/Extensions/EntityFrameworkQueryableExtensions.cs?ref=devinside.cloud).

The important property is the keyset predicate: resume after the last identifier, rather than skipping an ever-growing number of rows. That shape makes the recovery cursor explicit. It still needs an appropriate index and an actual plan review. The presence of `Take` in C# does not establish that a database query is cheap.

Projection narrows the object graph, but it does not establish a byte limit. A thousand short names and a thousand very large strings are different memory workloads. If the source can contain large values, the design needs a maximum projected record size, a separate large-object path, or byte-based admission in addition to the row limit.

## A queue bound is not a process-memory bound

Suppose pages contain four records and the queue can retain two pages. A reader can be holding the page it has just fetched while waiting to enqueue it. The destination consumer can be holding another page while it writes. The queue itself can contain two pages. Under that specific ownership arrangement, up to four pages can be retained by the application's page pipeline.

The useful bound is therefore:

```text
retained page slots <= queued pages + producer page + consumer page
retained records    <= (Q + 2) * B

```

For `Q = 2` and `B = 4`, the conservative count is sixteen records. That is arithmetic under the model, not a measured heap size. It excludes database-driver buffers, connection state, serializer buffers, the destination's own retained data and any references accidentally kept elsewhere.

![A two-page queue can coexist with four live pages](https://devinside.cloud/content/images/2026/11/devinside/v4/M04/ownership.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/M04/ownership.svg)Swipe the drawing to inspect it.

The drawing shows why naming only the channel capacity is misleading. A queue is one owner among several. The formula also gives a review question: can any stage retain more than one page outside the queue? A retry list, a closure captured by unfinished tasks or a diagnostic payload can invalidate the bound without changing the channel configuration.

A bounded channel is useful when its full mode waits for capacity. The producer's awaited write then participates in backpressure. A design that starts unbounded `WriteAsync` tasks without awaiting them has merely moved the queue into a collection of pending operations. A mode that drops data can also be appropriate for some telemetry, but it is not equivalent to a complete export.

The bound is easiest to reason about with one reader and one ordered consumer. More consumers can increase destination concurrency, but they also introduce out-of-order completion. A single scalar checkpoint must then represent a contiguous completed prefix, not the largest identifier any worker happened to finish.

## Move the checkpoint after the durable effect

The core recovery rule is small: the checkpoint must never claim an effect that the destination cannot prove it accepted. Everything else in the protocol follows from that sentence.

Consider a batch of identifiers 5 through 8\. The previous checkpoint is 4\. The consumer writes the batch and then saves checkpoint 8\. A crash after the write but before the checkpoint causes identifiers 5 through 8 to be delivered again. That is acceptable only if the destination can recognize the replay and avoid repeating the business effect.

Now reverse the order. Save checkpoint 8 first, then write the batch. A crash between those operations causes the restart to begin at 9, while 5 through 8 may never have reached the destination. That is not a duplicate-delivery issue. It is omission.

![The crash gap determines replay or omission](https://devinside.cloud/content/images/2026/11/devinside/v4/M04/checkpoint.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/M04/checkpoint.svg)Swipe the drawing to inspect it.

The distinction matters when someone proposes “exactly once” as a property of the loop. If the destination and checkpoint share one database transaction, the design can commit them atomically within that database's boundary. If they are separate systems, a local transaction around the checkpoint cannot make the external write atomic. The protocol must handle an uncertain result, typically through stable identity and a destination receipt or another explicitly designed reconciliation mechanism.

An acknowledgement is also not automatically durable acceptance. The destination contract must state what it acknowledges. A request accepted into volatile memory is not the same as a committed receipt. The export should not advance its durable checkpoint farther than the destination's durable promise.

## Make replay identity include meaning

A destination receipt can be keyed by the export identity and source record identity. When the same key arrives again, the destination compares the version or hash with the original receipt. Equal identity and equal content mean a replay. Equal identity and different content mean a conflict that requires a decision.

Silently treating every duplicate key as success can hide a changed source. Blindly applying every repeated request can duplicate the effect. The receiver needs enough information to distinguish those cases.

For a destination implemented in the same relational database as its receipt, the transaction contains the receipt and the corresponding change. The following is deliberately pseudocode around EF's real transaction API; the application-specific receipt checks and mutation are shown as named operations so their semantics are not hidden inside a pretend generic helper.

```csharp
await using var transaction =
    await destination.Database.BeginTransactionAsync(cancellationToken);

// Application contract:
// 1. Find receipt by (jobId, row.Id).
// 2. Existing equal version/hash: leave the effect unchanged.
// 3. Existing different version/hash: reject the conflict.
// 4. No receipt: apply effect and insert receipt together.

await destination.SaveChangesAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);

```

Transaction methods are available in the tagged EF Core relational source. Their existence does not solve cross-system atomicity, isolation choice, retry strategy or the uniqueness constraint needed to arbitrate simultaneous receipt insertion. [EF Core 9.0.0 relational transaction extensions](https://raw.githubusercontent.com/dotnet/efcore/v9.0.0/src/EFCore.Relational/Extensions/RelationalDatabaseFacadeExtensions.cs?ref=devinside.cloud).

If multiple consumers can race on a receipt, a “check then insert” sequence needs database enforcement, not just an application `if`. A unique key identifies the race; the code still needs a policy for the losing transaction. This article keeps one ordered consumer so that the checkpoint calculation stays the focus, but the destination contract should not rely on that restriction accidentally.

## Break the job where the promise is weakest

The opening interactive model contains twenty-four immutable synthetic records. It models a bounded page pipeline and a crash after a batch's destination commit but before its checkpoint is persisted. You can change page size, queue capacity and the batch at which the crash occurs. Restart uses the stored checkpoint and the same job selection.

> **Worked example.** With batches of four and a crash on batch two, the destination has eight unique records while the durable checkpoint remains four. Restart replays records 5 through 8\. An idempotent receiver reports four repeated deliveries and still ends with twenty-four unique accepted records. Those values are calculated by the browser model. No SQL command is executed.

Turn off receipt-based deduplication and replay becomes a duplicate-effect counterexample. Turn on the deliberately unsafe “checkpoint first” policy and the same crash boundary can skip records instead. The model should make both errors uncomfortable: a green progress number can be wrong in opposite directions.

Queue capacity changes the modeled ownership bound and producer blocking, not the fundamental recovery rule. A larger queue can hold more unfinished work; it does not make an acknowledgement durable or a receipt idempotent. Page size changes the amount of replay at the chosen boundary. It also changes how frequently the design pays per-page overhead, but the model does not assign a throughput advantage because it contains no database timings.

## Cancellation needs a reported state

Cancellation is another interruption, but it is easy to describe too optimistically. A request to cancel a query or a destination call does not prove that no effect occurred. An operation may complete while the caller is observing cancellation, or the caller may lose the acknowledgement after a commit.

The job should stop admitting new pages, observe the current operation's outcome as far as the contract permits, and leave a checkpoint that means only what has been established. If the destination outcome is uncertain, recovery needs the same identity-aware query or replay used after a process failure.

Avoid returning a single Boolean called `cancelled` if it hides whether the last effect committed. A useful state can distinguish “stop requested,” “no more work admitted,” “last destination outcome known” and “recovery required.” The operator does not need the internal implementation of every flag, but they do need to know whether restarting is safe and what it will repeat.

The same discipline applies to progress displays. “8,000 rows read” is not “8,000 rows durably accepted.” “Last identifier observed” is not a recovery checkpoint. If a status page collapses those counters into one number, it becomes difficult to tell whether the job is slow, blocked or lying about completion.

| State after interruption            | What is established           | Recovery decision              |
| ----------------------------------- | ----------------------------- | ------------------------------ |
| Commit acknowledged, checkpoint old | Destination accepted the page | Replay with receipt identity   |
| Commit outcome unknown              | Caller lacks an answer        | Query receipt or replay safely |
| Checkpoint advanced before commit   | Progress may omit effects     | Reconcile the skipped range    |

These are protocol states, not measured frequencies. A cancellation response must not flatten them into the same promise.

## Choose batch size from the decision, not a folklore number

A large batch can amortize round trips and per-transaction work. It can also retain more data, hold resources longer and enlarge the replay unit. A small batch can make progress more granular while increasing overhead. There is no universal page size that resolves those costs without a workload.

I would first identify a permitted record-size envelope and a memory budget for the page pipeline. Then I would choose a conservative starting batch, inspect the actual SQL and destination behavior, and measure the relevant interval. If the product cares about cancellation response or recovery work, those outcomes belong beside throughput.

The important constraint is that the benchmark cannot quietly discard responsibility. A version that returns before the destination has durably accepted the page is not a faster implementation of the same export contract. A version that omits receipt checks is doing less correctness work. A version that retains every failed page outside the bounded queue may have a different memory bound.

Before trusting a large-data job, I want one concrete answer: after a crash at any boundary, which identifiers can the service prove are complete, which may be repeated, and which must never be skipped? Once that answer is stable, performance work has a contract to preserve.

GO FURTHER

## Inspect the contemporary laboratory.

This is a newly reconstructed historical edition, not a claim of earlier authorship. Later executed experiments, their exact environments and original results are preserved in a separate notebook.

[Open the current laboratory notebook ↗](https://devinside.cloud/lab-notebooks/v4/M04.html)

[Download the current reconstruction sources ↗](https://devinside.cloud/content/files/v4/M04-reconstruction.zip)