bulk-submit: performance roadmap back to ~30 min without configuration (multi-row INSERT, index write-volume audit)

[angela-helios]

Context

With #932 (search-index allowlist) closed by design decision, the out-of-the-box bulk import time regressed to the pre-campaign baseline. Measured today on the real 31 GB / 954,288-entry export, PostgreSQL 16, HFS_BULK_SUBMIT_FILE_CONCURRENCY=8:

Configuration	Rate	Full import
PostgreSQL ×8, indexing inline (out of the box)	95/s	~2h 45m
PostgreSQL ×8, HFS_BULK_SUBMIT_DEFER_INDEXING=true	133/s ingest	~2h + deferred reindex
The former ~30 min figure	527/s	required the #932 allowlist, now closed

This issue tracks the remaining levers to get back to ~30 minutes without configuration knobs. Ordered by expected impact.

1. Multi-row INSERT / batched statements on the PostgreSQL write path

The strongest lever, confirmed live during today's import: sampling pg_stat_activity wait events while ingesting shows 11 of 19 samples in Client:ClientRead — the server spends most of its time waiting for HFS to send the next statement. HFS is nearly idle too (1.8% CPU), the disk is idle (0.2%); the pipeline is round-trip-bound. Each resource issues ~25 individual index-row INSERTs, one round-trip each.

Collapsing the index rows for a resource (or a whole batch) into one multi-row INSERT ... VALUES (...), (...) — or COPY for the bulk path — turns those waits into work. This is Lever 4 / "Problem D" in the "Cutting Bulk Import to 30 Minutes" analysis: PostgreSQL currently needs 8 workers to compensate a per-worker handicap (~110/s single, round-trip-bound).

2. Audit the duplicate _recent sort-covering indexes (PostgreSQL)

Each populated index family maintains two partial indexes per row: the value index and a _recent variant covering ORDER BY last_updated DESC. Measured at 470k resources into today's import:

idx_search_token_code_recent  351 MB   (tenant, type, param, last_updated DESC, resource_id) INCLUDE (code, system)
idx_search_token_code         345 MB   (tenant, type, param, code, last_updated DESC, resource_id) INCLUDE (system)
idx_search_date_recent         91 MB   same pattern for dates
idx_search_date                67 MB

That is ~2× the write volume per token/date row. The _recent variants exist for _sort=-_lastUpdated listings; worth measuring whether they earn their write cost or whether the primary family index (which already carries last_updated DESC after the value column) covers those plans well enough. Same spirit as the SQLite index audit that produced #944.

3. SQLite: partial family indexes — PR #944 (in review)

Rebuilds the nine family indexes as partial so each row maintains only its own family's structures. Measured 302 s → 181 s (1.67×) on the 10k inline benchmark, no configuration. PostgreSQL already follows this design (all its family indexes carry WHERE value_X IS NOT NULL); #944 brings SQLite to parity.

4. SQLite: statement batching inside the write lock

The same round-trip logic applies in-process: ~25 prepared-statement executions per resource inside BEGIN IMMEDIATE. Multi-row INSERTs into search_index would shorten lock holds — which is also the mitigation path for #942 (database is locked at high fan-out).

5. Backend-aware fan-out defaults + resilient manifest bookkeeping — #942

Filed separately; owned by Edson. Fan-out ×8 on SQLite with the full registry aborts the import today.

Non-goals (measured, ruled out — do not re-litigate)

• FHIRPath extraction is ~1–2% of import time in release builds (the earlier "90%" figure was a debug-build measurement scope error). A compiled fast-path exists on perf/fhirpath-fast-path and is e2e-neutral.
• SQLite pragma tuning (synchronous, pool size), extraction hoisting (1.24×), prepare_cached — all measured neutral or marginal; see the analysis document and perf(search): index only queried parameters and skip unused full-text #932's closing discussion.

Measurement hygiene note

Long-lived local file servers degrade: the Synthea static server had accumulated days of half-closed sockets from interrupted runs and was serving at 26 KB/s (vs 240 MB/s after restart). It turned out not to be the limiter for the import itself, but restart the fixture server before benchmarking.

Refs: #904 (defer indexing), #933 (fan-out), #932 (closed allowlist), #942, #944, Edson's "Cutting Bulk Import to 30 Minutes" analysis.