Phase 4-bis: BulkWriter (binary COPY) for INSERT-only rebuild batches

[jeremydmiller]

Parent

Child of CritterWatch/CritterWatch#208 — the master tracking issue for rebuild optimization work. Composes with Phase 4 (deferred flush via identity-map accumulation) and Phase E (measurement #4684).

Background

Rebuild has a property continuous catch-up doesn't: post-TRUNCATE, the entire rebuild write path is INSERT-only. There's no need for UPSERT machinery, no ON CONFLICT path, no concurrency checking — rebuild is authoritative by definition.

Binary COPY (via Npgsql's BeginBinaryImport) is typically 3-10× faster than per-row INSERT for bulk loads in PostgreSQL. On a 20M-event rebuild producing 5M aggregate rows, this could be 4-8 minutes of wall-clock saving on the write side alone.

Marten already has the COPY machinery: IDocumentStore.BulkInsertAsync uses BeginBinaryImport with the document binder pipeline. The rebuild path should reuse those binders.

Scope

Add a BulkWriter-based flush path to ProjectionUpdateBatch that activates when all operations in the batch are inserts (which rebuild always satisfies post-TRUNCATE). Fall back to per-row UPSERT otherwise (continuous mode).

Concrete shape

Per (tenant, projection) rebuild:
  1. TRUNCATE the projection's rows for this tenant
     - Partition-aware: TRUNCATE the per-tenant partition when UseTenantPartitionedEvents is on (cheap DDL)
     - Conjoined-only: DELETE FROM mt_doc_xxx WHERE tenant_id = $1 (slower but still cheaper than per-row INSERT)
  2. Replay events through normal batch machinery (same EvolveAsync, same aggregate cache)
  3. At batch flush:
     - If batch is INSERT-only (true for rebuild post-truncate):
         open BulkWriter via BeginBinaryImport within the open transaction,
         stream all rows, end COPY
     - Otherwise: fall back to per-row UPSERT (continuous mode)
  4. Progression row UPDATE at batch end (single statement; optional COPY-into-temp optimization deferred to a separate phase if measurement shows it dominates)

The insert-only detection is critical for graceful degradation. If a rebuild somehow ends up with mixed inserts/updates (e.g., partial-rebuild resume where TRUNCATE didn't happen), we don't silently corrupt — we just don't get the COPY win for that batch.

Constraints

1. Transaction scope. BeginBinaryImport runs within an open transaction. ProjectionUpdateBatch.ExecuteAsync already wraps each batch in one transaction. Composes cleanly.

2. Hierarchical / typed storage. If the projection's table has mt_doc_type (subclass projection), COPY must set it correctly per row. Reuse the binder machinery from BulkInsertAsync — already handles this.

3. Metadata columns. created / updated / version / tenant_id all need to be in the COPY stream. Same as BulkInsertAsync.

4. Partition awareness under UseTenantPartitionedEvents. COPY into the partitioned table works (PG routes to the right partition). Optionally COPY directly into the per-tenant child partition for marginal throughput — defer that complexity to v2.

5. Composite stage propagation. Stage 2's EvolveAsync may read stage 1's aggregates via Load. If we batch-buffer stage 1's COPY until batch end, stage 2 (running after stage 1 completes per batch) can't see stage 1's new rows in the DB yet. Two options:

   • Flush stage 1's COPY before stage 2 starts (smaller batches per stage, less COPY benefit)
   • Propagate stage 1's updates through the existing in-memory Upstream cache mechanism (ExecutionStage.ExecuteDownstreamAsync already does this for the synthetic-event propagation) — stage 2 reads from cache, not DB, so the un-flushed stage 1 COPY isn't visible-but-also-isn't-needed

   Preferred: the second option. Stage 2 reads via the propagated Upstream cache (already there); the DB-side COPY happens at batch end. This composes with Phase F (cache-integration work). Confirm during implementation that the existing propagation hits all the read paths stage 2 might use.

6. Recovery. COPY rollback is atomic with the transaction; partial COPY doesn't leak rows. Failed rebuild leaves the truncated projection table empty + the progression row in "tried, didn't finish" state. Operator retries the rebuild. Same correctness story as today — just a larger replay window per failed attempt.

7. UseTenantPartitionedEvents + per-tenant rebuild. Each per-tenant rebuild truncates only its own partition, runs its own COPY. Per-tenant isolation preserved.

Reuse vs. new code

• Reuse: BulkInsertAsync column binders, BeginBinaryImport orchestration, partition resolution. Most of the COPY plumbing exists.
• New: ProjectionUpdateBatch flush-mode detection (insert-only vs. mixed), TRUNCATE step at rebuild start, fallback path.

Acceptance

• New ProjectionUpdateBatch flush path that uses BulkWriter when all operations are inserts.
• Rebuild path TRUNCATEs the projection's rows for the affected (tenant, projection) at start.
• Fallback to per-row UPSERT works correctly for continuous catch-up (no behavior change).
• ScaleTesting harness (Marten.ScaleTesting: composite-projection rebuild load test harness on a Telehealth-derived domain #4666) shows measurable wall-clock improvement on a 20M-event Telehealth composite rebuild — target ≥30% reduction on the write-side time (per Phase E breakdown).
• All existing rebuild tests pass.
• Documentation: new section in docs/events/projections/rebuilding.md covering the BulkWriter optimization, when it activates, and the per-row fallback.

Non-goals

• Not changing the continuous catch-up write path. Per-row UPSERT stays the default.
• Not COPY for progression-row updates. Separate phase if Phase E shows progression writes dominate; defer until measured.
• Not COPY into per-tenant child partitions directly. Use the partitioned parent and let PG route. Optimization for v2.
• Not Polecat side. Marten-only in v1; Polecat gets symmetric treatment when it catches up.

Effort

~1-2 weeks. The COPY plumbing reuses existing machinery; the bulk of work is ProjectionUpdateBatch mode detection + the TRUNCATE step + harness validation.

Dependencies

• Phase E (Phase E: Per-batch measurement instrumentation in Marten.ScaleTesting harness #4684) for the measurement infrastructure — needed to validate the win.

Related

• Parent: CritterWatch#208
• CritterWatch#208 Phase 4 (deferred flush via identity-map accumulation) — composes: defer + then COPY the deferred batch
• Phase F (TBD jasperfx issue, cache integration) — composes via the Upstream cache propagation, which is how stage 2 reads stage 1's un-flushed COPY data
• Harness: Marten.ScaleTesting: composite-projection rebuild load test harness on a Telehealth-derived domain #4666 / #4666 Phase C — validate + stress subcommands #4676

[jeremydmiller]

Implementation finding: PR 2 (INSERT-only routing) is blocked on Phase 4, proven empirically

While implementing the INSERT-only rebuild routing (PR 2 of this issue), I hit a correctness blocker that the "INSERT-only post-TRUNCATE" framing glosses over, and proved it with a focused test. Capturing here so the phase ordering is on the record.

The premise that doesn't hold yet: "rebuild is INSERT-only" assumes each aggregate id is written exactly once across the rebuild. Marten's rebuild flushes page-by-page — one ProjectionUpdateBatch per EventPage (GroupedProjectionExecution.processRangeAsync), and the aggregate cache spans pages. So any stream whose events span more than one page has its aggregate re-stored once per page. With today's UPSERT/OVERWRITE that's correct (later pages overwrite); flip it to INSERT and the second page throws:

JasperFx.DocumentAlreadyExistsException: Document already exists <Projection>: <id>
  at Marten.Events.Daemon.Internals.ProjectionBatch.ExecuteAsync
  at JasperFx.Events.Daemon.GroupedProjectionExecution.applyBatchOperationsToDatabaseAsync

That's Marten's transform of PostgreSQL 23505 — same duplicate-key family as #4679, here on the projection document PK. A 30-event single stream at BatchSize=10 (3 pages) reproduces it deterministically.

So PR 2 depends on Phase 4 (deferred flush via identity-map accumulation, CritterWatch#208 Phase 4) — the single-flush mechanism that guarantees one write per aggregate. PR 2 cannot be enabled by default until Phase 4 lands. (The composition was noted in the issue body; this confirms it's a hard prerequisite, not just a "composes nicely.")

Landed in #4699 (draft):

• The ExecutionMode session seam (rebuild-vs-continuous signal) that PR 3's BulkWriter dispatch will key off.
• The actual Store→Insert routing in ProjectionStorage, gated behind an experimental default-off flag (ProjectionOptions.RebuildWithInsertOnly) so there's zero behavior change.
• Bug_4685_rebuild_insert_only_multipage pinning the boundary (default routing rebuilds correctly; INSERT-only routing cannot, until Phase 4). When Phase 4 lands, that test flips to expect success and the flag promotes to default-on.

Also worth a follow-up: DocumentStore.EventStore.cs:350 constructs the ProjectionUpdateBatch with a hardcoded ShardExecutionMode.Rebuild regardless of the actual mode — currently harmless (ProjectionUpdateBatch.Mode is unused) but a latent trap for anything that later keys off it. #4699 deliberately uses the session's ExecutionMode instead.