Skip to content

perf: benchmark-driven performance investigation (tracking issue: results, discarded experiments, PR index) #2180

Open
Open
perf: benchmark-driven performance investigation (tracking issue: results, discarded experiments, PR index)#2180
Assignees
eterceros

Description

@eterceros
eterceros
opened on Oct 20, 2026

Summary

Tracking issue for a benchmark-driven performance investigation of HFS (CRUD, Import and Search legs of the public FHIR server benchmark, fhir-benchmark.yml). It is not a work item: it records what was measured, what is proposed as a PR, and what was tried and discarded, so nobody repeats it.
It is written so a reviewer can decide from this page alone: start with the decision table, then the charts; the technical detail is collapsed. Each PR gets its own child issue linked in the "PR index". Related: #1902 (bulk-import suite in the benchmark), #1977.

Status (2026-10-21): 101 experiment rows, some never run (PostgreSQL 32, MongoDB 26, Elasticsearch 21, SQLite 22), 6 PR candidates open (the Mongo candidate was superseded by the merged #2144), 27 discarded, 16 superseded, 28 inconclusive or never run, 9 optional or opt-in (the rest are accepted parts of a candidate). Upstream PR #2144 (same Mongo optimisation, by another contributor) was merged on 2026-10-20 and cites our benchmark comment; our own Mongo child issue #2182 was closed as superseded. The Security Audit failure on main (#2189: new RustSec advisories, unrelated to any PR) was fixed by #2191. The first performance PR is open: #2199 (Postgres EXISTS removal, child issue #2195); the others are still to come. A CI run on matched hardware is a separate, later task.

Decision table (read this first)

Order = recommended review order. Index: before = 100 (the baseline build, see legend below). IMPROVES = better, WORSENS = worse. Bars here are 1 block per 10 index points (the charts below use a different scale: 16 blocks = 100; an arrow marks an off-scale bar).

# Candidate In plain words Metric Before -> After (index) Gain Cost / risk Evidence Recommendation
1 Mongo batched Bundle writes: upstream PR #2144 (MERGED 2026-10-20); our own candidate (#2182) closed as superseded Import of transaction Bundles: one existence query per type plus chunked inserts, not about 7 calls per entry Import resources/s, 30 Bundles 100 -> 167 █████████████████ IMPROVES +66.6 % (total time -39.3 %) hfs peak memory WORSENS +24.3 % (accepted); opt-in flag 4 pairs, every B beat every A; final DB state identical No rival PR. #2144 is merged (same speed within noise, lower memory in a 2-round indication). Follow-ups: offer our equivalence and rollback tests as a PR on top of main, and re-measure main to confirm the gain on #1977
2 Postgres: remove pre-LIMIT EXISTS Search page no longer re-checks "is the resource live" per candidate; the join already does DB CPU per request 100 -> 75 ████████ IMPROVES -24.8 % (plan+exec -31.6 %, req/s +26.8 %) Observation?date at 1 VU WORSENS +28 % (diagnosed, not blocking; 30 VU IMPROVES -5 %) 4 pairs, 4/4 each, every B above every A on req/s; 0 mismatches in 2,757 requests; 455 tests Open first Postgres PR (smallest, cleanest, no schema change)
3 Postgres: date index v50 One partial newest-first index for date range pages; no query change DB CPU per request 100 -> 61 ██████ IMPROVES -38.8 % (req/s +46.4 %, p99 -67.4 %, buffers -50.9 %) Schema change v49 to v50 (needs an issue). Write cost, CRUD 300 VU vs main, quiet box, 6 pairs plus 2 identical-binary control pairs: DB CPU per write request median -0.4 % (range -3.4 to +5.6 %), below the 9 % difference seen between two identical runs of main; WAL records per request +1.6 % (+0.5 to +4.8 %); req/s and p95 are not distinguishable from noise (+-25 to 35 % between identical runs). Cost is below about 3 % and not demonstrable; the migration takes milliseconds on an empty DB 4 blocks, all rung-runs beat main; 0 mismatches in 2,757 requests Own PR after an issue; second Postgres PR
4 Postgres: eligibility cache Caches "no search row has NULL last_updated" per process (stale up to 10 s) DB CPU per request (on top of v50) 100 -> 81 ████████ IMPROVES -19.4 % (buffers -12.5 %; req/s +12.5 % not conclusive) Touches write path. CRUD 300 VU vs main (stacked on v50, 4 pairs): DB CPU per request IMPROVES -13.3 % (4/4), req/s IMPROVES +15.2 % (4/4, but the slowest B only just ties the fastest A), p99 -7.4 %; WAL bytes WORSEN +2.4 % (about +1 WAL record per request) 4 blocks, 4/4 on efficiency metrics Modest; third Postgres PR
5 Elasticsearch: sweep limiter + backoff (robustness fix; the skip was dropped) Bound concurrent delete_by_query sweeps, back off on 429/503 and repeat a throttled sweep on its own; the sweep always runs, as on main Retry log lines per CRUD run 100 -> 5 ▏ IMPROVES -95 % to -99.5 % vs main (-99.5 % over 3 pairs earlier; -95.4 % and -98.0 % in the final 2-pair run). Sync failures IMPROVE 38 and 144 -> 0. req/s is not claimed None known: the optional skip was removed because it never fires in the benchmark and had a correctness gap (stale contained docs after a failed sync) ABAB, 0 client errors; 164 + 26 + 6 ES tests pass (incl. real ES 7.17 and a regression test that stale contained docs are removed) Open as a robustness PR (pr/es-sweep-limiter-backoff)
6 Postgres: SearchParameter fingerprint TTL (opt-in) Skip the COUNT, MAX(last_updated) probe (2 per write) for a short TTL; default off DB CPU per request 100 -> 76 ████████ IMPROVES -24.1 % (probe calls -99.4 %) Opt-in; with TTL above 0 another instance may index without a just-committed SearchParameter; req/s not proven 4 pairs, 4/4; req/s fails the rule (min B below max A) Low priority opt-in PR
7 SQLite package, 11 commits (migrations v42-v46) Covering and code-first token indexes, first-page probe, one-transaction create Search req/s at 30 VU, vs main 100 -> 3146 ████████████████████▶ (x31) IMPROVES +3046 % (CPU/request -96.8 %). Against our integration branch (not main): +92 % Index changes are not opt-in (only the probe has a switch); migrations lock writes for seconds on big DBs; CRUD neutral (+7.5 % +-17); the v46 step depends on v42 and the probe 4 pairs, every B beat every A; 22/22 URLs identical; 2,416 tests pass Last; split into stacked reviewable steps; issue first

Gains at a glance

All values are index numbers, before = 100, from the experiments below. One chart per direction so it is obvious which way is good.

HIGHER IS BETTER (throughput). Scale: 16 blocks = 100 = before.
Mongo import resources/s (ours vs main)        before ████████████████ 100 | after ███████████████████████████ 167
Mongo import, PR #2144 (2-round indication)    before ████████████████ 100 | after ████████████████████████████ 176
PG search req/s, EXISTS removal alone          before ████████████████ 100 | after ████████████████████ 127
PG search req/s, v50 index alone               before ████████████████ 100 | after ███████████████████████ 146
PG search req/s, ladder rungs 1-3 cumulative   before ████████████████ 100 | after ████████████████████████████████████████ 247
SQLite search req/s vs integration2 (NOT main) before ████████████████ 100 | after ███████████████████████████████ 192

SQLite search against main is off this scale: +3046 % (x31), 100 -> 3146. It is large because main has no covering indexes; it is not comparable with other backends and must not be mixed with the +92 % above (that one is against our integration branch).

LOWER IS BETTER (DB or host CPU per request). 16 blocks = 100 = before.
PG DB CPU/request, EXISTS removal alone        before ████████████████ 100 | after ████████████ 75
PG DB CPU/request, v50 index alone             before ████████████████ 100 | after ██████████ 61
PG DB CPU/request, cache (on top of v50)       before ████████████████ 100 | after █████████████ 81
PG DB CPU/request, ladder 1-3 cumulative       before ████████████████ 100 | after ████ 27
PG DB CPU/request, fingerprint TTL (opt-in)    before ████████████████ 100 | after ████████████ 76
SQLite CPU/request vs main (11 commits)        before ████████████████ 100 | after ▏ 3
SQLite CPU/request vs integration2             before ████████████████ 100 | after █████████ 59

LOWER IS BETTER (buffers per request, tail latency). 16 blocks = 100 = before.
PG buffers/request, EXISTS removal alone       before ████████████████ 100 | after ██████████ 65
PG buffers/request, v50 index alone            before ████████████████ 100 | after ████████ 49
PG buffers/request, cache (on top of v50)      before ████████████████ 100 | after ██████████████ 88
PG buffers/request, ladder 1-3 cumulative      before ████████████████ 100 | after ██ 14
PG p99 latency, v50 index alone                before ████████████████ 100 | after █████ 33
PG p99 latency, ladder 1-3 cumulative          before ████████████████ 100 | after ██ 13
SQLite p95 latency vs main (30 VU)             before ████████████████ 100 | after ▏ 3

LOWER IS BETTER (time, retries, calls, memory). 16 blocks = 100 = before.
Mongo total import time, ours vs main          before ████████████████ 100 | after ██████████ 61
Mongo p95 per Bundle, ours vs main             before ████████████████ 100 | after █████ 31
ES retry log lines per CRUD run (limiter+backoff) before ████████████████ 100 | after ▏ 1
PG fingerprint probe calls (TTL, opt-in)       before ████████████████ 100 | after ▏ 1
Mongo hfs peak memory, ours vs main (WORSE)    before ████████████████ 100 | after ████████████████████ 124

Mongo import, three arms (main, our branch, upstream PR #2144), same workload, 2 rounds with rotated order (an indication, not a validation):

Arm resources/s vs main total time vs main p95 per Bundle vs main hfs peak memory vs main
main 100 100 100 100
ours IMPROVES +67.3 % (rounds +55.0, +79.6) -> 167 -40.0 % -> 60 -54.3 % -> 46 WORSENS +22.3 % -> 122
PR #2144 IMPROVES +76.3 % (rounds +71.6, +81.0) -> 176 -43.1 % -> 57 -70.2 % -> 30 IMPROVES -14.4 % -> 86 
resources/s (higher is better)
  main      ████████████████ 100
  ours      ███████████████████████████ 167
  PR #2144  ████████████████████████████ 176
hfs peak memory (lower is better)
  main      ████████████████ 100
  ours      ████████████████████ 122
  PR #2144  ██████████████ 86

The p95 here comes from the 2-round run, so it differs from the 4-pair chart above. PR #2144 vs ours: +5.8 % on average (+10.7 % and +0.8 % per round), inside noise. Final database state main vs #2144 identical (37,974 resources, 670,663 search rows). Memory of #2144 is noisy between its two runs.

What each number means

  • Baseline tags. [main] = upstream origin/main (678fc50) or an equivalent build. [main-old] = hfs-base d3976b4 (0.2.5, earlier main snapshot; SQLite schema v41). [integration] = an internal branch carrying about 17 other unmerged changes: the gain on clean main can be bigger or smaller, so every [integration] figure needs a re-measurement on main before its PR. Most early A arms were integration, not main; only the Postgres ladder, the two prpg runs (EXISTS removal alone, fingerprint TTL), the Mongo import, the ES sweep and the final SQLite package have main as baseline.
  • IMPROVES / WORSENS are written in words on purpose. Lower time, latency, CPU, buffers, retries, memory = better; higher req/s = better.
  • Headline figures are ratios and index values; absolute values in the collapsed tables are local and only give context. All runs are on a shared 8-core development box (load 4-30, other jobs running, foreign CPU 1.8-4.9 cores in some runs). Absolute req/s or ms are not comparable with the public benchmark hardware, so the issue shows ratios and index values (before = 100, nearest integer). Data: 30 % slice of the benchmark corpus (about 490k resources) for Postgres and SQLite, 10 % slice for Mongo; warm cache.
  • ABAB method. A (baseline) and B (candidate) alternate on fresh data, with a load gate (aborted above load1 15; load and foreign CPU recorded; relaxed from the stricter quiet-window rule on 2026-10-19 because eight agents shared eight cores). Acceptance rule: every B run beats every A run and the mean gain exceeds the A-A spread. Throughput is CPU-bound and noisy on that box, so decisions lean on stable per-request metrics: DB CPU per request, shared buffers per request, plan+exec time, 1-VU latency, bytes written per request.
  • "Pairs". N pairs = N alternating A/B runs. With 4 pairs the bootstrap 95 % interval equals the min-max range. "Every B beat every A" with only 2 pairs happens by chance about 17 % of the time.
  • Honesty notes. Indicative = few pairs or noisy box; validated = 4 or more pairs, every B beat every A, equivalence checked. Nothing is validated on matched hardware or on the full 1.63M-resource corpus yet. Equivalence = same ids, order, _total and next links (Postgres 24 shapes, 2,757 requests; SQLite 22 URLs x 3 pages; Mongo and ES by canonical per-resource hashes).
  • Evidence is kept locally as per-experiment reports; it is available on request.

PostgreSQL

Result Before -> After (index) Gain Verdict
EXISTS removal alone on main (4 pairs): DB CPU/req 100 -> 75 ████████ IMPROVES -24.8 %; plan+exec -31.6 %, buffers -34.8 %, req/s +26.8 % PR CANDIDATE
v50 date index (4 blocks): DB CPU/req 100 -> 61 ██████ IMPROVES -38.8 %; req/s +46.4 %, buffers -50.9 %, p99 -67.4 % PR CANDIDATE
Eligibility cache on top of v50: DB CPU/req 100 -> 81 ████████ IMPROVES -19.4 %; buffers -12.5 %; req/s +12.5 % not conclusive PR CANDIDATE (modest)
Ladder rungs 1-3 cumulative vs main: DB CPU/req 100 -> 27 ███ IMPROVES -72.9 %; req/s +147.3 % (index 247), buffers -85.7 %, p99 -87.4 % Reference only: stacked, review as 3 PRs
Fingerprint TTL (opt-in): DB CPU/req 100 -> 76 ████████ IMPROVES -24.1 %; probe calls -99.4 % PR CANDIDATE (opt-in)
Ladder rung 4, probe-gated date arm DB CPU/req 100 -> 105 WORSENS +5.4 % (4/4 blocks), up to 11.5x slower near its 300-row threshold DISCARDED (dropped)
Observation?date at 1 VU after EXISTS removal 100 -> 128 WORSENS +28 % at 1 VU, IMPROVES -5 % at 30 VU Diagnosed (serial instead of parallel plan); not blocking
[integration] max_parallel_workers_per_gather = 0; search_auto planning 100 -> 72; 100 -> 74 IMPROVES planning -28 %; -26.5 % OPTIONAL, recheck on full corpus

Several ladder runs had 1.8-2.6 foreign CPU cores, so conclusions rest on per-request metrics. The ladder's equivalence check found 0 mismatches over 24 shapes and 2 seeds for all rungs.

All 32 experiments for PostgreSQL
Experiment What it changes Baseline Result (pairs) Verdict Why
PG-1 v50 index (rung 1) Schema v49 to v50: partial newest-first date index; no query change [main] 4 blocks, 30 VU: req/s +46.4 % (range +2.0 to +67.8), DB CPU/req -38.8 %, buffers -50.9 %, plan+exec -53.5 %, p95 -37.1 %, p99 -67.4 %, 1-VU -25.6 %; Observation?date avg -87.7 %, Encounter?date -43.9 %; all 16 rung-runs beat every main run; 0 mismatches / 2,757 PR CANDIDATE Date shapes were 81 % of PG search time in CI; CRUD pair owed
PG-2 Date rewrites R1-R4, EXPLAIN IS NOT NULL, EXISTS guard, UNION ALL, new index compared by EXPLAIN ANALYZE [main-old] Obs gt/ge dense 26-98 ms to 0.08 ms with R1, but Encounter gt/ge WORSENS 3-5x (6.1 to 30.3 ms); index alone fixes both; 804 comparisons, 0 diffs SUPERSEDED Index alone explains the gain; R2 was +2-7 ms slower
PG-3 IS NOT NULL SQL + index, end to end base vs index+SQL vs index only [main-old] req/s: index+SQL +18 to +40 %, index only +44 to +71 % (1 run per arm, A-A 19 %); Observation?date total 502/733 s to 144/124 s DISCARDED (SQL part) SQL change adds nothing, risks Encounter
PG-4 v50 M1 ABAB Same as PG-1, older base [main-old] 2 pairs: req/s +74 % (every B beat every A; base spread 12.6 %), DB CPU/req -40 %, 1-VU -24 %, p99 -50 to -65 % SUPERSEDED by PG-1 First independent confirmation
PG-5 v50 insert write cost Extra B-tree entry per date row [main-old] 2 pairs, insert 50 VU: creates/s -1.3 % (spreads 6.5 and 9.1 %), not detectable ACCEPTED (cost check) Partial index; 300 VU CRUD pair vs main owed
PG-6 v50 on bulk-import $bulk-submit, 10 % slice [main-old] 1 run per arm: +38.2 % resources/s, both runs load 12-25 INCONCLUSIVE Noise about +-40 %; only shows no slowdown
PG-7 Eligibility cache, first EXPLAIN Drop index_eligible CTE, cache per tenant [integration] CTE vs none: date eq -17 %, gt 98 to 81 ms, ge 3.0 to 0.57 ms; quantity 9.1 to 17.9 ms WORSENS; single clone SUPERSEDED by PG-8 CTE was not the 36 % pg_stat_statements suggested
PG-8 Eligibility cache (rung 2) Per-process cache of "no NULL last_updated"; stale up to 10 s; opt-out HFS_PG_ELIGIBLE_CACHE=0 [main], on rung 1 Marginal, 4 blocks: DB CPU/req -19.4 % (4/4), plan+exec -24.4 %, buffers -12.5 %, 1-VU -14.4 % (4/4), req/s +12.5 % (3 up, 1 down), p95 -10.3 %; cumulative vs main req/s +54.1 %, DB CPU -51.2 % PR CANDIDATE Writers pin NULL rows: CRUD pair owed
PG-9 EXISTS removal, stacked (rung 3) Drops per-candidate live-resource EXISTS in the fast-path page [main], on rungs 1-2 Marginal, 4 blocks: req/s +50.4 % (4/4), DB CPU -45.1 %, buffers -66.8 %, plan+exec -51.4 %, p95 -41.4 %, p99 -54.7 %, Encounter?date -72.4 %; cumulative req/s +147.3 %, DB CPU -72.9 %, buffers -85.7 %, p99 -87.4 %; 0 mismatches PR CANDIDATE (same change as PG-10) Flat 0.6-1.2 ms from 200 to 5,000 rows; never slower than main in 28 selectivity cells
PG-10 EXISTS removal alone Same change ported to plain main [main] 4 pairs, 30 VU: plan+exec -31.6 % (CI -37.7 to -24.8), buffers -34.8 %, DB CPU -24.8 % (4/4 each), HFS CPU -2.3 %, req/s +26.8 % (min B above max A; A-A 14 %), p95 -36 %, p99 -33.9 %; Encounter?date 1 VU -61 %; 22 of 24 shapes improve 12-58 %; 455 tests, 0 / 2,757 mismatches PR CANDIDATE (first) Smaller than stacked: main keeps the CTE and has no date index
PG-11 Observation?date side effect 1-VU tail after PG-10 [main] 1 VU avg WORSENS +28 % (18.7 to 24.0 ms; p95 about 134 to 185 ms; median unchanged); 30 VU avg -5 %; total work and buffers not worse INCONCLUSIVE (diagnosed) Serial plan without EXISTS; fix idea (EXISTS back for gt/ge) unmeasured
PG-12 Probe-gated date arm, EXPLAIN Bounded 300-row probe picks value-first or newest-first [integration] 584 queries replayed: Encounter?date -96 % (6.52 to 0.25 ms); Observation?date WORSENS (0.13 to 0.20 ms); no ABAB SUPERSEDED by PG-13 Looked good warm; lost under load
PG-13 Date probe arm (rung 4) Same arm, threshold 300, on rung 3 [main], on rungs 1-3 Marginal, 4 blocks: DB CPU/req WORSENS +5.4 % (4/4), req/s -2.9 %, plan+exec +5.9 %; 28-cell sweep: 20-40 % slower than rung 3; Observation lt at 380 rows 11.5x slower; gt at 371-600 rows 2.7-2.9x DISCARDED About 0.2 ms fixed overhead plus a plan flip at the threshold; only an unmeasured Encounter-only variant (about 600) survives
PG-14 Combined fast path Cache + EXISTS removal + probe arm merged [integration] 5 pairs: plan+exec -46 %, buffers -68 %, DB CPU -39 %, 1-VU -29 %, req/s +41 % (paired +2, +105, +21, +35, +42 %); Encounter?date 1 VU -81 %; Obs?date 1 VU +15 % (not conclusive); 0 / 3,837 mismatches SUPERSEDED (split into rungs) Baseline carried unmerged work; not reviewable as one PR
PG-15 EXPLAIN-wins branch First EXISTS removal and cache hardening [integration] 1,096 statements replayed: buffers 2.98M to 0.29M (-90 %); 8 VU exec 25.3 to 6.8-8.0 s; 0 / 504 mismatches; no ABAB SUPERSEDED by PG-9/10 Source of the idea
PG-16 Integration vs old base Whole binary, search and CRUD [main-old] Search 4 runs +54 % (inside 54 % spread); 1-VU -24 to -33 %; p99 -50 to -65 %; CRUD +47 % (base spread 63 %) INCONCLUSIVE Inside box noise
PG-17 Integration3b vs main, search Whole binary [main] 1 pair: req/s +102.7 %, p95 -58.7 %, p99 -83.8 %, plan+exec -79.4 %, buffers -86.0 %, DB CPU -71.5 %; 0 / 1,992 mismatches SUPERSEDED by ladder One pair; CRUD and insert never ran
PG-18 Fingerprint TTL, first (H13) Skip the SearchParameter probe for a TTL [integration] Calls -99.7 %; insert 4 pairs +17.7 % (A-A spread 86 %, B1 below A2); TTL-0 control as fast as B; CRUD vacuumed clone 2 pairs -3.4 % INCONCLUSIVE Measured on a vacuumed clone
PG-19 Fingerprint TTL v2 Fresh never-vacuumed DB [integration] 6 pairs, CRUD 300 VU: DB CPU/req -25 % (range -41 to -6 %, 6/6, p=0.03), exec+plan -32 % (5/6), calls -99.5 %; req/s inconclusive SUPERSEDED by PG-20 The earlier "no gain" came from the vacuumed clone
PG-20 Fingerprint TTL vs main Opt-in, TTL default 0, bulk submit always probes [main] 4 pairs, CRUD 300 VU: calls -99.4 % (4/4), DB CPU/req -24.1 % (-52.7 to -21.1 %, 4/4), plan+exec -33.3 %, HFS CPU -3.6 %, req/s +25.8 % (4/4 up but min B below max A) PR CANDIDATE (opt-in) Strictly less DB work; TTL above 0 risks another instance missing a just-committed SearchParameter
PG-21 max_parallel_workers_per_gather = 0 Deployment setting, no code [integration] 3 pairs: planning -28 %, exec+plan -18 %, DB CPU -13 % (3/3), 1-VU p50 -11 %, req/s median +4.7 %; buffers WORSEN +15 % OPTIONAL Small, free; recheck on full corpus
PG-22 plan_cache_mode = search_auto Opt-in cached plans for whitelisted statements [integration] 6 pairs: planning -26.5 % (6/6), 1-VU -10.3 %, DB CPU -12.6 % (5/6), req/s mean +7.7 % but 4 of 6 up, A-A 27.5 % (older 4-pair read: -1.0 %) OPTIONAL (off) CPU-bound host hides it; recheck full corpus
PG-23 plan_cache_mode auto / generic for all Generic plans from the 5th execution [integration] plan+exec 0.76 to 0.69 ms (auto), 0.96 (generic, WORSENS +26 %); auto: Observation?date exec 15x, Encounter?date 3x, quantity 4.9x; generic: category 54x, status 8x, date 21x DISCARDED Generic plans pathological for date, quantity, category
PG-24 Pipelined writes One round trip per write (8 to 1; delete 7 to 1), opt-in [integration] SQL calls -24 %, idle-in-tx -93 %, HFS CPU -11 %; CRUD over 12 pairs only 8 favour B (p about 0.19; series +26.9 %, +13.3 %, +15.1 %); insert req/s WORSENS -12.8 %, DB CPU/req +89 %; WALWrite waiters +9 to +20 % OPTIONAL (parked) CRUD is WAL-commit bound; committers only queue
PG-25 Fewer write round trips Prepared cache for locks and fingerprint; no SAVEPOINT [main-old] CRUD single run +10 % to -3 % vs base; SQL per request 8.0 to 7.0 DISCARDED Same WAL bound
PG-26 Pool 64 instead of 32 Doubles the pool [main-old] CRUD single run -11 % (inside 47 % spread); 51 of 64 connections wait on WALWrite DISCARDED Pool is not the limit
PG-27 synchronous_commit = off Diagnostic only [main-old] CRUD single run +13 % (+39 % vs mean), inside spread; WALWrite waiters vanish DISCARDED (diagnostic) WAL fsync is only 2-7 ms of 15-28 ms hold time; weakens durability
PG-28 Empty DB for CRUD Fresh never-vacuumed DB [main-old] 3 runs: no gain; the CI figure was not reproduced INCONCLUSIVE Fingerprint probe costs 2.4-6 ms there and cancels the gain
PG-29 WAL-diet: minimal index set + FTS writes off 12 instead of 22 search indexes (new DB), FTS writes off [integration] WAL records/create -24 %, update -15 %; CRUD valid pairs +33, +22, +41 % (3/3); insert 3 pairs -9.0, -6.7, +68.9 % (no effect) OPTIONAL (provisional) Bare-id reference search 125 vs 0.4 ms; FTS off breaks _text/_content; summary never written
PG-30 WAL-diet: diff-based search_index update Rewrite only changed search rows [integration] WAL records/update -76 %, bytes -62 %; CRUD arm never completed DISCARDED (NO-GO) Unchanged rows keep an old last_updated: keyset paging can skip or repeat
PG-31 EXPLAIN-audit ideas Ordered reference index, drop 10 indexes, STATISTICS 2000, optional FTS [integration] Reference index 14.4 to 0.43 ms cold but 0.4-1 ms warm (no gain); STATISTICS 18.7 to 10.0 ms but WORSENS value_quantity; dropping token or quantity indexes 11 to 333 ms, 49 to 267 ms DISCARDED Cold single queries; warm replay removed the gap
PG-32 Pre-campaign ingest knobs file_concurrency=4, batch_size=1000 [main-old] About 2x in a 2 % slice for pg-es; deferred indexing 2.6x slower with ES; not re-measured INCONCLUSIVE Old notes, other harness

MongoDB

Result Before -> After (index) Gain Verdict
Bundle batch vs main: resources/s (4 pairs) 100 -> 167 █████████████████ IMPROVES +66.6 % (per pair +58.8, +53.9, +74.3, +75.3 %) SUPERSEDED by merged #2144
Total import time; p95 per Bundle 100 -> 61; 100 -> 31 IMPROVES -39.3 %; p50 -64.1 %, p95 -68.8 %, p99 -45.9 %; existence queries 39.6k to 6.8k as above
hfs peak memory 100 -> 124 WORSENS +24.3 % (about +550 MB); mongod +0.7 % Accepted trade-off
Final DB state identical 37,974 resources, 37,974 history, 670,663 search, 39,336 contained rows Evidence
Upstream PR #2144, indication (2 rounds) 100 -> 176 IMPROVES +76.3 %; memory IMPROVES -14.4 % MERGED; re-measure main
Composite rows, explain-wins, write-trips, bundle fast path not measured end to end no valid ABAB on main OPTIONAL; issue first
All 26 experiments for MongoDB
Experiment What it changes Baseline Result (pairs) Verdict Why
M-01 Round-trip microbenchmark 1 DB call per document vs batched insertMany none (loopback) 1,600 insertOne 679 ms to insertMany x100 69 ms; 200 entries 470 ms to 58 ms (about 8x). 3 reps, not ABAB SUPERSEDED Motivated the design; ratios only
M-02 Row model vs embedded sp array One row per parameter value vs embedded array none (synthetic) Insert 2.4x faster; index 60-100 to 9-22 MB; code+date query 240 ms to 4 ms; single runs DISCARDED (deferred) About 6k-line rewrite; batching came first
M-03 Bundle batch, series q4a First prototype, time-capped runs [main-old] Base 576.9 and 273.8 resources/s depending on window; variant +114 % vs first base; 1 run per variant INCONCLUSIVE (invalid) mongod OOM-killed; rate depends on window length; +114 % withdrawn
M-04 Bundle batch, series q2-h2 Fixed 30 Bundles to completion [main-old] 1 valid pair: +74 %; second pair invalid (mongod crash) INCONCLUSIVE (invalid) Load-suspect runs
M-05 Bundle batch vs d3976b4 One existence find per type + chunked unordered insert_many [main-old] 2 pairs: total time -47.5 %, resources/s about +90 %, p95/Bundle 31-36 s to 7.9-8.6 s, hfs RSS WORSENS 2.3 to 2.7 GB; every B beat every A SUPERSEDED by M-06 Only 2 pairs
M-06 Bundle batch vs origin/main (final) Same, rebased (6 commits), opt-in, no schema change [main] 4 pairs: resources/s +66.6 % (+58.8, +53.9, +74.3, +75.3 %), total time -39.3 %, p50 -64.1 %, p95 -68.8 %, p99 -45.9 %; hfs RSS +24.3 %; mongod +0.7 %; every B beat every A; 0 errors PR CANDIDATE Round trips were the limit; temporary bench-kit/ commit must be removed
M-07 Final-state equivalence Batched import vs main [main] IDENTICAL: 37,974 resources (26 types), history, 670,663 search rows, 39,336 contained rows (canonical hashes) ACCEPTED One run per arm
M-08 Three-arm indication with #2144 main vs ours vs #2144 [main] Ours +67.3 %, #2144 +76.3 %; #2144 vs ours +5.8 % (inside noise); RSS ours +22.3 %, #2144 -14.4 % INCONCLUSIVE 2 rounds, large variance; large-Bundle behaviour not exercised
M-09 Single-resource write round trips Fewer calls per create/update/delete [main-old] CRUD 2 pairs +3.0 % (noise, base spread 4.5 %); insert 2 pairs +50.9 % (base spread 72.1 %) INCONCLUSIVE Smaller than spread; risk of orphan rows and WriteConflict
M-10 Merged composite-group rows One search row per composite group (needs $reindex) [main] mongosh: 520-880 ms to 1-12 ms (about 100x fewer keys), identical ids in 21 forms; 10 % slice search rows -43 %; never via HFS OPTIONAL Schema change plus mandatory reindex; 0 ABAB
M-11 Covering index for pair check Read 0 instead of 1,024 documents [main] Wall time 520-880 to 526-753 ms (no change); 134.5 MB DISCARDED Driver read dominates
M-12 Bound composite driver / early exit Cap at about 10,000 rows or exit at 21 ids [main] Cap: no effect; early exit 60-680 ms but results NOT equivalent DISCARDED Changes order and subset
M-13 End-first date index Index on value_date_end (32 MB) [main] Only sparse gt improves (31 to 10 ms); lt, le, sa, eq, eb WORSEN up to 25-30 ms; 98/98 equivalent DISCARDED "gt scans all keys" not reproduced
M-14 Covering date-membership index 256-id membership check reads 0 documents [main] 2-6 ms to 1-2 ms per batch (39.9 MB) OPTIONAL (not implemented) Small gain
M-15 Hint type-scan index for large id sets Force idx_resources_type_scan at 2,000+ ids [main] (audit) Obs?date eb 233 to 27 ms (8.6x); hurts small sets (56 vs 3 ms); 0 pairs INCONCLUSIVE Unmeasured; default-on NO-GO
M-16 PUT rewrites only changed search rows Diff stored rows on update [main] (audit) About 320 index ops saved per update (expected); 366 URLs, 0 differences; 0 pairs INCONCLUSIVE Default-on and unmeasured
M-17 Exact-only reference search $in instead of $or + regex, opt-in [main] (audit) 3 ms vs 4-5 ms; 0 pairs OPTIONAL Cannot prove no versioned references
M-18 Skip idx_resources_missing_params Do not build a 168.7 MB index used only by :missing [main] (audit) Inserts 40-47 % faster, -168.7 MB (21 % of index bytes); 0 pairs OPTIONAL :missing would break; deployment choice
M-19 Drop idx_search_resource Prefix of idx_search_composite [main] (audit) search_index inserts about -11 %, -59.6 MB; 0 timed pairs INCONCLUSIVE Index drop at first boot needs an issue
M-20 Lower count-probe caps 100,000 to about 10,001 [main] (audit) Probe 10,001 6 ms vs 1,001 2 ms DISCARDED Ties saturated probes; may worsen driver choice
M-21 Deferred index + raise Bundle gate Two env settings [main] Never run INCONCLUSIVE Raising the gate causes eviction rollbacks
M-22 Bulk-import settings INDEX_BUILD=off, file_concurrency=4, batch_size=1000 [main] No new data; earlier study about 2x at a 2 % slice INCONCLUSIVE Never run here
M-23 Five Mongo tuning flags Various HFS_MONGODB_* flags [main] No measurement INCONCLUSIVE NO-GO until evidence
M-24 w=1&journal=false Relaxed write concern [main] Audit: w:majority 5.35 ms vs w:1 1.26 ms DISCARDED Not comparable with default durability
M-25 Wire compression, regex without "i" Micro-tweaks [main] None DISCARDED Driver lacks zstd/snappy; regex weighs little
M-26 Bundle handler fast path 4 fewer deep clones, 30 s conditional-reference cache [main] Never measured end to end; unit tests pass INCONCLUSIVE Cache not invalidated across nodes; may not be taken for ifNoneExist

Elasticsearch

Result Before -> After (index) Gain Verdict
Sweep limiter + backoff vs main (earlier version that still had the skip, same reduction), retry log lines per 90 s CRUD run (4 pairs) 100 -> 1 ▏ IMPROVES -99 % (17,752-34,415 to 187-306); no client errors either way PR CANDIDATE (skip dropped; limiter + backoff only)
Same, CRUD req/s 100 -> 111 +11.2 % median, CI -15.2 to +94.5, 3 of 4 pairs: not conclusive no claim
Same, insert req/s (3 pairs) 100 -> 101 +1.0 % (CI -1.7 to +1.5): neutral no claim
Correctness gap test fails Stale contained docs can survive a failed sync plus a skipped sweep Blocks the PR until fixed
Async sync mode for CRUD, even fixed 100 -> 46 (vs sync) WORSENS -54 %; up to 1,457 deferred writes DISCARDED
COALESCE_WRITES 100 -> 2 WORSENS -98 %, 10 s stalls DISCARDED (defect)
All 21 experiments for Elasticsearch
Experiment What it changes Baseline Result (pairs) Verdict Why
E-01 es-write-path, insert Skip sweep on create, no refresh on update, HEAD once [main-old] 2 pairs: creates/s +12.6 % (+14.8, +10.4 %); every B beat every A DISCARDED (whole patch) Same patch breaks CRUD (E-02)
E-02 es-write-path, CRUD Same, 300 VU CRUD [main-old] 2 pairs: req/s -50.8 % WORSENS (-46.4, -54.3 %); p95 2.2 s vs 0.8-1.0 s; retries 11.4k vs 3.8k DISCARDED 429 too_many_scroll_contexts; retries redo the whole event
E-03 es-async-batch, insert Async worker, batches of 500, one _bulk per tenant [main-old] 2 pairs: creates/s +416.9 % (+561.5, +277.0 %) DISCARDED (for CRUD) Gain is a lagging secondary index
E-04 es-async-batch, CRUD Same worker on CRUD [main-old] 2 pairs: req/s -93.9 % WORSENS; p95 23-27 s; 300 errors in one run DISCARDED One sequential worker, blocking bounded queue
E-05 Skip total hit count, trim _source track_total_hits follows wants_total(); smaller _source [main-old] Search +29.4 %, p95 48 to 40 ms, hfs CPU 2.25 to 1.32 cores, RSS 825 to 540 MB; 1 run per arm INCONCLUSIVE (OPTIONAL) n = 1 per arm; load1 5.95 at start
E-06 es-sync-fixes part 1, sync CRUD Targeted and bounded sweep, 429 back-off [integration] 3 pairs: req/s +88 % mean (+128, +42, +102 %); retry lines 23,268/31,120/14,517 to 0; every B beat every A SUPERSEDED by E-09 Base carried the unmerged es-write-path: the +88 % undid our own regression, withdrawn
E-07 es-sync-fixes part 2, async CRUD Deletes in the bulk, send_timeout [integration] 3 pairs: async req/s +177 % mean; B async mean 478 vs B sync mean 1,050; deferred writes up to 1,457 DISCARDED Stays about 54 % below sync; p95 target missed
E-08 es-sync-fixes, insert Same two commits [integration] Sync insert +1.4 % neutral; async insert -15 % WORSENS (1 pair) DISCARDED (async) No gain, slight cost
E-09 pr/es-contained-sweep-skip vs main (earlier version, superseded by pr/es-sweep-limiter-backoff) Exact sweep skip, concurrency limiter, 429 back-off [main] 4 pairs: retry lines -99 % (4/4); CRUD +11.2 % (CI -15.2 to +94.5, mean +25.4 %, 3 of 4 pairs, not every B above A); insert +1.0 % (3 pairs); 0 k6 errors SUPERSEDED (the skip was dropped; limiter + backoff kept: -95 % to -99.5 % retries in 5 pairs total) Main has the retry storm; load1 16-23; needs an issue (default-on change)
E-10 Sweep-skip gap test Real-cluster test: failed sync of the version that dropped contained[] [main] The skip had a real gap (stale contained docs stayed in ES); a GET-before-skip fix passed the test and kept the retry reduction, but arm D (skip off) showed the skip never fires in the benchmark, so the skip was dropped (decision 2026-10-20). The test remains as a regression test asserting the stale docs ARE removed DONE (skip dropped) The retry reduction comes from the limiter and backoff, not the skip
E-11 COALESCE_WRITES Group single writes into one _bulk [main-equivalent] Insert 50 VU: 247 to about 5 creates/s (-98 % WORSENS), reproduced 4 of 4 DISCARDED (defect) About 10 s stall per group, hfs and ES idle; cause unknown
E-12 TRANSLOG_ASYNC Async translog on new indices [main-equivalent] Insert 247 to 257.6 (single run, inside spread): no gain; audit: single write 10-14 to 0.7 ms DISCARDED Limit is the wait_for refresh (about 270 creates/s)
E-13 INDEX_SORT New indices sorted by last_updated [main-equivalent] Audit (ES only): broad shapes about 20-100x lower; not through HFS INCONCLUSIVE Triage stopped it
E-14 REQUEST_CACHE request_cache=true [main-equivalent] Audit: repeated queries 15-30x; not through HFS INCONCLUSIVE Benchmark uses random values
E-15 FORCE_MERGE_AFTER_REBUILD Merge to 1 segment after reindex [main-equivalent] Audit: broad searches 2.5-4x faster; not through HFS INCONCLUSIVE Not run
E-16 REINDEX_REFRESH=false Skip refresh during rebuild [main-equivalent] Audit: reindex 51-69 s to 24-32 s (2.1x); not through HFS INCONCLUSIVE Final refresh unverified
E-17 BULK_MAX_OPS=2000, concurrency above 1 Bigger, parallel _bulk [main-equivalent] Audit: +36 % and 2.6x; HFS-level inconclusive INCONCLUSIVE Not run
E-18 Refresh policy, DEFER_INDEXING=false, ingest knobs Env-only for +ES legs [main] No data; earlier: deferred indexing 2.6x slower in pg-es INCONCLUSIVE Never run
E-19 One _bulk per Bundle Batch per-entry writes [main] Isolated estimate about 600 s to 3 s for 6,000 docs SUPERSEDED Transaction Bundles already grouped by type
E-20 Rejected rewrites Flatten nested, must to filter, reduced mapping, terms [main] No gain, or under 20 % DISCARDED ES is not the search bottleneck (1-12 ms per shape)
E-21 i3es original plan integration3b vs main [main] Dropped; 367 common URLs identical SUPERSEDED by E-09 PR-sized branch used instead

SQLite

Two baselines exist and must not be mixed: vs main (origin/main, no covering indexes) and vs integration2 (already at schema v42).

Result Before -> After (index) Gain Verdict
vs main, search 30 VU req/s (4 pairs) 100 -> 3146 ████████████████████▶ IMPROVES +3046 % (x31); p50 -96.6 %, p95 -96.8 %, CPU/request -96.8 %, RSS peak -56.6 % PR CANDIDATE (package)
vs main, search 1 VU p95 100 -> 1 ▏ IMPROVES -98.8 %; no shape of 22 WORSENS (component-code-value-quantity p50 -94 %) PR CANDIDATE (package)
vs main, CRUD 300 VU 100 -> 108 +7.5 % (+-17): neutral; CPU/request -3.7 % (4/4) No regression, no gain claimed
vs integration2 (not main), search req/s 100 -> 192 ███████████████████ IMPROVES +92 % (median; every B above every A); CPU/request -41 % Reference only (same code, other baseline)
vs integration2, CRUD 100 -> 120 +20.5 % median, not conclusive (one pair -18.4 %); CPU/request -3.7 % No claim
First v43/v44 patch CRUD cost 100 -> 119 WORSENS +19 % bytes and CPU per request; one shape about 2x SUPERSEDED (replaced by reshape v46)
ANALYZE req/s 100 -> 57 WORSENS -43 % DISCARDED
All 22 experiments for SQLite
Experiment What it changes Baseline Result (pairs) Verdict Why
S-1 Covering search indexes (v42) resource_key added to quantity, token, composite indexes; startup self-repair [main-old] 30 VU req/s x11.8 (12.24 to 144.8), p95 about 13.8x lower, 1 VU about x18, RSS 4,461 to 2,662 MB; not ABAB (base 12.2 twice, covering once); migration 16.0 s, +78.6 MB (+1.2 %); insert -3.1 % (1 rep); 22 comparisons identical ACCEPTED (largest part of S-19) No table lookup per hit; HFS CPU saturated in both arms
S-2 ANALYZE / PRAGMA optimize Planner statistics at startup [main-old] req/s 12.17 to 6.99 (-43 %), patch default -31 %, no-temp_store arm -25 %; 1-VU p50 -70 %; 94 of 554 plans worse, dates 8-10x slower; optimize costs 8-11 s at startup; all B worse DISCARDED Plans change but heavy shapes do not get cheaper
S-3 Memory / page PRAGMAs temp_store, mmap_size, cache, page size [main-old] Audit: cache 2 MB 2.8x worse, 8 MB 2.5x worse than 64 MB; mmap/temp_store no gain; page 8-16 KB reads +4-9 % but WAL x1.7-2.9 DISCARDED Current config already best
S-4 Bounded first-page probe Count at most 2,001 rows, then rowid read or newest-512 window; switch HFS_SQLITE_SEARCH_PROBE [integration] 2 pairs: req/s +31.4 % (+63.6, +5.6 %), every B beat every A; 1-VU p50 -55 %; code-value-quantity 253 to 402 s WORSENS INCONCLUSIVE; kept in S-19 2 pairs, load1 6-14; equivalence check was the vacuous one; composites fixed later
S-5 Create in one transaction, standalone One IMMEDIATE transaction; optional write gate [main-old] q1: insert -11.6 % (p95 +74.3 %), CRUD +26.0 % but variant cut; q1b: +54 % / +28.3 %; harness INVALID DISCARDED Base swung 452-886 req/s; the predicted 2-4x never reproduced
S-6 TOKIO_WORKER_THREADS=64 More tokio threads [main-old] Never run INCONCLUSIVE SQLite pool fixed at 10
S-7 BULK_INDEX_REBUILD=true on SQLite Drop and rebuild indexes around bulk submit [main-old] Never measured (CI: rebuild 1,541 s of the total) INCONCLUSIVE No slot; focus on search
S-8 Composite code-first index (v43) + EXISTS pairing New index plus pairing rewrite [integration] Probe/page 43/42 to 5.5/4.2 ms; combo 49/50 to 5.2/4.5; HTTP code-value-quantity 68 to 15 ms; WAL 302 to 266 KB (-12 %); no ABAB alone SUPERSEDED by S-17 Extra index is the costly part
S-9 Token code-first extra index (v44) idx_search_token_code [integration] code= probe 8.9 to 0.07 ms; Observation?code 13.4 to 1.9 ms; WAL 302 to 332 KB (+10 %) SUPERSEDED by S-17 Reshape instead of add
S-10 Drop two prefix indexes (v45) Drop idx_resources_type, idx_history_resource [integration] No run alone; WAL about -5 % estimate; "neither" config 247 vs 302 KB (-18 %) ACCEPTED (in package) Primary keys serve the lookups
S-11 Recency window for date UNION ALL Newest-first strategy, wider retry [integration] Observation?date 17.7 to 9.7 ms, Encounter?date 5.5 to 3.7; ne/le/ge 51.6 to 2.1, 30.2 to 5.4, 19.5 to 1.5 ms; replays, not ABAB ACCEPTED (in package) Shape built 146k-292k temp keys for 21 rows
S-12 Multi-row INSERT into search_index One INSERT per resource [integration] Audit estimate -20-25 % CPU; no isolated run ACCEPTED (in package) Row order unchanged
S-13 Create/restore in one IMMEDIATE transaction Existence check under write lock [integration] strace 100 creates: 637 to 618 KB WAL (-3 %); no CPU change ACCEPTED (in package) Atomicity real, throughput gain not shown
S-14 explain-wins package vs integration S-8 to S-13 together [integration] Search 5 pairs +42.9 % mean, conservative +13.9 % (2 clean pairs); 1 VU x3.4 (+239 %); CRUD 3 pairs -13.7 % mean, p50 WORSENS 3/3, bytes +19 %, CPU +19 % SUPERSEDED by S-17 Two extra indexes; "fast mode" of B inflated the mean
S-15 component-code-value-quantity regression Defect in S-14: filter evaluated twice [integration] p50 at 30 VU 140.5 to 234.3 ms (WORSENS +72 %); 1 VU invisible FIXED by S-17 Double evaluation, not a bad plan
S-16 Per-index write-cost table Replay of 2,000 rows per index set [integration] WAL per create: old 302 KB; v43+v44 356 (+18 %); v43 only 266; v44 only 332; neither 247 (-18 %); v46 274 (-9 %) ACCEPTED (chose v46) v46 replaces an index instead of adding
S-17 fix2: token index code-first (v46) + rowid paging Rebuilds idx_search_token, drops v43/v44, filter runs once [integration] 4 pairs: search +89.3 % mean (+103.8, +97.2, +86.5, +69.8; median +91.9), every B beat every A; p50 -47.9 %, p95 -48.5 %, CPU/req -41 %; 1 VU x4.3; CRUD +20.5 % median (not every B above A), CPU/req -3.7 %; 22/22 equivalent, 0 BUSY ACCEPTED Fewer index entries per write; +19 % write cost gone
S-18 fix2 residual component-code-value-quantity Slowdown left after S-17 [integration] p50 134.6 to 154.7 ms (+15 %, 4/4 pairs worse); 1 VU -43 % INCONCLUSIVE Target +10 % missed; vs main it does not regress (S-21)
S-19 pr/sqlite-token-index-v46 vs main, search 11 commits on origin/main [main] 4 pairs: 30 VU +3,046 % (x31; +2,921.8 to +3,154.6 %), p50 -96.6 %, p95 -96.8 %, CPU/req -96.8 %, RSS -56.6 %; 1 VU +6,988 %, p95 -98.8 %; every B beat every A; 22 of 22 forms improve; 2,416 tests, 22/22 equivalent PR CANDIDATE Main has no covering indexes; package not independent; migrations need an issue
S-20 Same branch vs main, CRUD Write path of the package [main] 4 pairs: +7.5 % (-12.4, +6.8, +6.4, +29.1; neutral), p50 -7.8 % (4/4), CPU/req -3.7 % (4/4), bytes -3.1 % PR CANDIDATE No regression; no gain claimed
S-21 Same branch vs main, component-code-value-quantity Check of the regressed shape [main] p50 -94 %, p95 -95 %; none of 22 forms WORSENS ACCEPTED The +15 % exists only vs integration
S-22 EXPLAIN-audit ideas Wide composite row, FTS off hot path, no v1 history on create, skip unchanged reindex, drop more indexes [integration] Estimates only: e.g. create 0.95 to 0.48 ms; skip-reindex 2-3x OPTIONAL Changes semantics or schema

Discarded or inconclusive: do not repeat

IDs refer to the collapsed tables above.

What (IDs) Result Why it failed
ES COALESCE_WRITES (E-11) WORSENS -98 % on insert, 4 of 4 About 10 s stalls while hfs and ES are idle: a defect, do not enable
ES async sync mode (E-03, E-04, E-07, E-08) CRUD -93.9 %; fixed still -54 % vs sync, up to 1,457 deferred writes; insert +416.9 % Insert gain is only a lagging index; one sequential worker and bounded queue
ES es-write-path patch (E-01, E-02) Insert +12.6 %, CRUD WORSENS -50.8 % Sweeps kept on updates cause 429 retry storms
The "+88 % ES" (E-06, E-21) Superseded Its base carried our own regressing patch: the gain only undid it
ES flags and rewrites (E-05, E-12 to E-20) TRANSLOG_ASYNC no gain; others audit-only or never run Limit is the wait_for refresh (about 270 creates/s); ES is not the search bottleneck
Postgres write pipelining, pool 64, one SQL fewer, empty DB, synchronous_commit=off (PG-24 to PG-28) Per-request metrics IMPROVE, throughput does not move; insert -12.8 % CRUD is bound by WAL commit/fsync (about 20 of 32 backends in WALWrite): more committers only queue
IS NOT NULL date rewrite (PG-2, PG-3) Encounter WORSENS 3-5x The index alone gives the gain
plan_cache_mode auto / generic (PG-23) 15-54x slower date, category, quantity shapes Generic plans pathological; only whitelisted search_auto (PG-22) is safe
Date probe arm, ladder rung 4 (PG-12, PG-13) WORSENS +5.4 % DB CPU; 11.5x slower at 380 rows Fixed overhead and a plan flip at the threshold
Diff-based search update, ideas from EXPLAIN audit (PG-30, PG-31) -76 % WAL records but wrong paging Cold single-query numbers misled
Superseded Postgres runs (PG-4, PG-7, PG-14, PG-15, PG-17, PG-19) Same direction as the ladder Baseline carried unmerged work, 1-2 pairs, or a vacuumed clone
Postgres inconclusive (PG-6, PG-11, PG-16, PG-18, PG-32) One run per arm, or inside box noise; PG-11 diagnosed Noise about +-40 % on req/s
SQLite ANALYZE (S-2), PRAGMAs (S-3) WORSENS -43 %; dates 8-10x slower; 94 of 554 plans worse Statistics change plans, not cost of heavy shapes
SQLite create-in-one-transaction alone (S-5) INVALID runs Base swung 452-886 req/s; the predicted 2-4x never reproduced
SQLite v43/v44/explain-wins (S-8, S-9, S-14, S-15) CRUD +19 % cost; one shape +72 % Two extra indexes; double filter evaluation; replaced by reshape (S-17)
SQLite not run or open (S-4, S-6, S-7, S-18) See table Few pairs or never run
Mongo discarded (M-02, M-11, M-12, M-13, M-20, M-24, M-25) Rewrite deferred, no gain, wrong results, lt/le/eq WORSEN Reading the driver dominates; early exit not equivalent; relaxed durability not comparable
Mongo superseded or invalid (M-01, M-03, M-04, M-05, M-08) +114 % withdrawn mongod OOM, window-length dependent rate, only 2 pairs or 2 rounds
Mongo never measured (M-09, M-15, M-16, M-19, M-21, M-22, M-23, M-26) No valid ABAB on main Triage stopped them; default-on changes need an issue
The vacuous SQLite equivalence checker Malformed URLs returned the same total for every query Earlier "identical" claims were unverified until redone (22/22)

Distance to the public benchmark

Caveat: shared 8-core box, 30 % corpus slice, windows 90-120 s, k6 and HFS share CPU. Public numbers come from 8 CPU / 24 GB servers. Local figures are valid only as before/after ratios. SQLite is not in the public ranking; only Postgres has a measurable path to the top 2.

Operation Local vs public target (derived, not comparable) Starting point Limit
Postgres search, 30 VU about 1.20 of Aidbox (from the [integration] combined run, not the ladder; not comparable) the ladder gives index 247 over main host CPU
Postgres CRUD about 0.4-0.7 of WSO2: does not reach WAL commit/fsync WALWrite
SQLite search, 30 VU 0.124 of Aidbox, 0.103 of WSO2 0.004 of Aidbox on main; CI full corpus 2 req/s hfs CPU
SQLite CRUD, 300 VU 0.133 of WSO2, 0.116 of Aidbox neutral vs main single writer / WAL
Mongo import no target comparison; +66.6 % vs its own main 39.6k existence queries round trips
SQLite import, bulk-import no measurement with any patch CI SQLite import 2.4 % errors unknown

The public "Import" metric also counts GETs to the tgz server.

Open items and risks

Block a PR:

Owed or optional:

  • Full-corpus validation (1.63M resources); CI run on matched hardware (separate task) for every backend.
  • Observation?date 1-VU follow-up (EXISTS only for gt/ge): diagnosed, unmeasured, not blocking.
  • SQLite insert suite for the final package; component-code-value-quantity +15 % vs integration only; bare system token searches lose their seek (20-40 % slower on full-count queries).
  • Fingerprint TTL needs a quiet box (5 or more pairs) to prove req/s; stays opt-in.
  • max_parallel_workers_per_gather = 0 and search_auto: recheck on the full corpus. WAL-diet flags: summary never written.
  • Next hot Postgres shapes: Encounter?date (about 8.5 % of time), quantity and composites (about 30 %).
  • Mongo composite rows, explain-wins, write-trips, bundle fast path, remaining ES and Mongo tuning flags: no valid ABAB; issue first.
  • Document every touched HFS_* variable in the README tables.

Lessons that may save others time

  1. Measure against main, not a branch with other optimisations. The same EXISTS removal gave -45 % DB CPU stacked but -25 % alone; the "+88 % ES" only undid our own regression.
  2. Postgres CRUD here is WAL-commit bound: removing round trips (8 to 1), indexes or statements moves per-request metrics, not throughput; a bigger pool only queues.
  3. Equivalence checks must be non-vacuous: an early SQLite checker built malformed URLs and returned the same total for every query. Require distinct totals and first ids.
  4. A shared saturated box makes req/s unreliable (+-40 %). Use DB CPU, buffers, plan+exec, 1-VU latency and bytes written per request, paired by block, with foreign CPU recorded.
  5. Warm single-query EXPLAIN replays mislead: the probe arm, ordered reference index and cold audits were contradicted by later warm replays or ABAB runs. Sweep selectivity around any threshold.
  6. Where you measure decides the verdict: the fingerprint probe costs 0.15 ms on a vacuumed table and 2.4-6 ms on a never-vacuumed one.
  7. The index alone explained the date win; the IS NOT NULL rewrite added nothing and hurt Encounter. Generic plans are pathological (15-54x).
  8. Run-window rates are invalid: import rate fell from 577 to 274 resources/s when the window grew; use total time for a fixed number of Bundles.
  9. Memory is the hidden cost of batching (+24.3 % hfs RSS); also run guards: OOM-killed mongod invalidates runs, one lock per shared DB, disk under the ES watermark.
  10. ES write limit is the wait_for refresh (about 270 creates/s); async mode is not a CRUD configuration; coalescing stalled 10 s.
  11. Predictions failed repeatedly (create-txn 2-4x, audit -34 % WAL cancelled by new indexes): measure end to end before claiming.
  12. Pass explicit SHAs to build scripts and keep build slots low: a wrong-commit build and a memguard kill each cost a measurement.

PR index

Child issues and PRs are linked here (one issue per PR, each referencing this issue). The first performance PR is open: #2199 (Postgres EXISTS removal, child issue #2195); the others are still to come.

Activity

  1. eterceros
    self-assigned this
    on Oct 20, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions