bulk-submit(sqlite): per-entry pipeline dominates ingest (~84%) — instrument and cut the non-index write volume

[angela-helios]

Context

SQLite counterpart of #945, from an instrumented run of the real 31 GB manifest (single worker, the safe default per #942). Binary = #944's partial indexes plus per-phase timing counters on the write path (cumulative, dumped every 1,000 resources). Windows host, WAL mode.

Host profile during ingest: the writer pegs one core at ~99% while the disk sits at ~12 MB/s. SQLite ingest is CPU-bound on the single writer thread — the exact opposite of PostgreSQL, which is round-trip-bound with idle CPU (#945). The two backends need different levers.

Measured phase breakdown (real data)

At 29,000 resources into the manifest (CarePlan / AllergyIntolerance segment, ~1.5 KB average resource, 13.8 index rows per resource):

Phase	Per resource	Share of wall
Wall clock (52/s in this window)	19.2 ms	100%
FHIRPath extraction	0.26 ms	1.4%
search_index row INSERTs	2.66 ms	13.9%
FTS (exists check + insert)	0.14 ms	0.7%
Everything else (unattributed)	~16 ms	~84%

Two conclusions:

1. perf(sqlite): rebuild search_index family indexes as partial (schema v20) #944's partial indexes already paid off on real data: 0.19 ms per index row, down from 0.40 ms measured with the full-width indexes. Indexing is now a ~14% item, not the dominant cost.
2. The dominant cost (~84%) is the per-entry pipeline outside indexing, and it is currently unmeasured. Known occupants, from a row-count audit of the same database:

resources                31,555     the resource row
resource_history         31,555     a FULL COPY of the data blob, written on create
bulk_entry_results       30,176     one bookkeeping row per manifest entry
bulk_submission_changes  30,176     one more bookkeeping row per manifest entry
search_index            437,723     ~14 rows per resource (instrumented above)

Every ingested entry writes four table rows (one of them duplicating the entire resource blob) plus its index rows, and the remainder of the 16 ms budget goes to the typed FHIR serde round-trip, savepoint/transaction bookkeeping, and NDJSON parsing — none of it yet individually timed.

Proposed work, in order

1. Extend the phase instrumentation to the rest of the per-entry pipeline (resource INSERT, history INSERT, bookkeeping rows, serde round-trip, batch commit) and attribute the ~16 ms. This is a day of work and decides everything below.
2. Stop double-writing the blob on create — evaluate deriving history v1 from the current resources row instead of materializing a full copy into resource_history at create time. On a fresh bulk load this halves the largest write.
3. Batch the bookkeeping writes — bulk_entry_results and bulk_submission_changes are one INSERT each per entry; multi-row INSERT per batch is mechanical.
4. Multi-row INSERT for search_index rows — now a ~14% item after perf(sqlite): rebuild search_index family indexes as partial (schema v20) #944, still worth taking inside the same pass as (3), and it shortens the write-lock hold (mitigates bulk-submit(sqlite): import aborts with 'database is locked' at high file concurrency with the full search-parameter registry #942).
5. Skip the typed serde round-trip where possible — if the stored representation is the canonical JSON bytes and validation is off, parsing NDJSON into the full typed FHIR model and re-serializing may be avoidable on the bulk path.

Known context (do not re-derive)

• HFS_BULK_SUBMIT_DEFER_INDEXING=true already gives ~5× on the ingest phase (indexing relocated, not removed) — perf(bulk-submit): fast-load mode — defer search indexing to a post-manifest reindex #904.
• Fan-out ceiling on SQLite is ~1.1–1.4× (single writer); at 8 workers without pruning the import aborts — bulk-submit(sqlite): import aborts with 'database is locked' at high file concurrency with the full search-parameter registry #942 (owned by Edson).
• Extraction hoisting measured 1.24× — stability fix, not a perf fix.
• FHIRPath is 1.4% of wall on real data; not a lever (see bulk-submit: performance roadmap back to ~30 min without configuration (multi-row INSERT, index write-volume audit) #945 non-goals).
• Cross-day rate comparisons are contaminated by host state (see the fixture-server note in bulk-submit: performance roadmap back to ~30 min without configuration (multi-row INSERT, index write-volume audit) #945); the ratios within this instrumented run are internally consistent.

Refs: #945 (PostgreSQL roadmap), #944 (partial indexes, in review), #942, #904, #933.

[angela-helios]

Step 1 (attribute the ~84%) is done, and the answer was better than expected: it was almost entirely one statement — the per-entry DELETE FROM resource_fts, a full scan of the FTS5 virtual table on every ingested entry (FTS5 can't index plain-column WHEREs). Quadratic over the import; it also explains the throughput decay.

Full instrumented attribution on the real manifest (single worker, partial indexes applied):

Phase	@25k resources (before fix)	@99k resources (after fix)
FTS delete scan	12.4 ms/entry (72%)	0.05 ms/entry
Batch commit (fsync)	0.9 ms	1.95 ms (now the leader)
search_index row INSERTs	2.8 ms	1.71 ms
FHIRPath extraction	0.27 ms	0.21 ms
Receipts (entry_results + changes)	0.19 ms	0.20 ms
resources + history INSERTs	0.12 ms	0.11 ms
Everything else (read-check, clone, serialize, bookkeeping, contained)	<0.3 ms	<0.3 ms

Throughput in the identical window: 56/s → 232/s (4.1×), stable instead of decaying. Fix is up as #949.

Consequences for this issue's list: the history double-write and the bookkeeping rows measured innocent (0.11 / 0.20 ms) — dropping items 2–3 as perf work. Remaining levers, reordered: (1) external-content FTS5 so updates stop paying the scan too, (2) batch-commit cost (1.95 ms/entry — batch size / wal_autocheckpoint), (3) multi-row INSERT for index rows (1.71 ms/entry).

[angela-helios]

Full-run verification of the stacked fixes, completed tonight on the real 31 GB manifest:

989,101 resources in 51 minutes (318/s sustained), zero errors — SQLite, single worker, indexing inline (searchable as it loads), zero configuration.

That is the combination of the three open PRs — #944 (partial indexes), #949 (FTS scan fix), #950 (64 MiB page cache) — on an otherwise stock server. For scale: today's main starts at ~110/s and decays quadratically (#949), and the PostgreSQL fan-out ×8 full run measured 2 h 53 m on the same host and dataset. The throughput held stable across the entire run — the decay is gone.

The ~30-minute target is now within reach of the remaining #947 levers (external-content FTS5, multi-row index INSERTs) or a modest fan-out once #946 lands.