Findings (hand-written; the tables below are generated)

Instance: r6id.metal (Intel Xeon Platinum 8375C, 2 sockets and 128 vCPU, 1 TiB RAM, 4×1.9 TB instance NVMe as XFS on md RAID-0), AL2023 kernel 6.18, us-east-2a. PostgreSQL 16.15 built from source (-O2, no cassert). pg_mentat 1.9.0 was built --release with pgrx 0.17. shared_buffers = 857 GiB (85% of RAM) on 2 MiB huge pages. The postmaster ran under numactl --interleave=all. Full settings are in env.txt, and the reasoning is in benchmarks/scale/ec2/tune.sh.

The working set is smaller than shared_buffers at every scale. pg_database_size was 0.8 GB at s (1M datoms), 8.0 GB at m (10M), 80 GB at l (98M) and 244 GB at xl (303M). xl uses 28% of s_b. On disk that is about 0.81 to 0.86 KB per datom (see sizes.txt).

Where each backend scales, and where it falls over

  1. PostgreSQL (pg_mentat) is the only deployment that reached xl (303M datoms). Bulk load ran at 131 to 206K datoms/s with 16 to 32 parallel loaders (xl: 1472 s). ANALYZE took 0.7 to 1.5 s and VACUUM 0.4 to 8.4 s. The concurrency curve (read mix q1+q2+pull, quiet machine, median of 3, ops/s at 1 / 8 / 32 / 64 / 128 clients):

    scale 1 8 32 64 128 p50 @1 → @128
    s (1M) 1,316 10,236 38,581 64,669 69,167 0.77 → 1.6 ms
    m (10M) 625 4,894 18,871 31,911 35,769 1.6 → 3.5 ms
    l (98M) 101 810 3,195 5,730 5,844 13.6 → 30.8 ms
    xl (303M) 33 265 1,061 1,922 1,932 44 → 96 ms

    Scaling is linear up to 64 clients (one per physical core), then flat. The throughput level falls with scale because of q1 (see below). pull stays flat at 0.7 to 1.3 ms p50 from s to xl, because it uses the EAVT pkey. Two query classes do not scale:

    • Point lookup by a unique attribute degrades linearly with the attribute’s size. q1 p50 was 0.41 ms at s, 1.6 ms at m, 13.6 ms at l and 49 ms at xl. plans/l-q1.json: the current_text table has only (store_id,e,a,v) and (store_id,a,e) INCLUDE (v,tx), so [?e :user/email "x"] is a Parallel Index Only Scan of every :user/email value with Filter: v = 'x'. There is no AVET index on the current-state tables. q2, input_bindings and as_of inherit the same cost. Fix (crates/pg): add (store_id, a, v) INCLUDE (e) on current_*, at least for :db/unique/:db/index attributes.
    • Full-attribute scans (q3 aggregate, q4 predicate) are O(n) and slow. q3 took 126 ms at s, 1.5 s at m and 16 s at l. At xl it exceeded the 20 s probe (ceiling). plans/*-q3.json: (count ?i) compiles to COUNT(DISTINCT e) with a full sort. At xl the first attempt died on pg_mentat’s own mentat.temp_file_limit (1 GB default). q4 at xl returns 3.2M rows, and the single jsonb result exceeds PostgreSQL’s 256 MB jsonb limit (ERROR: total size of jsonb object elements exceeds the maximum): a hard ceiling of the edn_q → jsonb API, not a tuning issue.
    • pg_mentat errors once a result passes mentat.max_result_rows (default 100000). From m up, q4 and since need it lifted. The suite sets it to 0 in the bench DBs.
    • Writes: single-datom edn_t with synchronous_commit=on ran at 1.3K tx/s at s, about 500 tx/s at m and l, and 1.0K/s at xl, with p99 of 1 to 11 ms, while 8 readers ran. All writes succeeded; there were no serialization failures.

  2. Embedded (the mentat crate on SQLite) has the best single-thread latency for entity-shaped reads, but does not scale with threads or history.

    • Point lookup was 0.09 ms at s, 0.66 ms at m and 7.9 ms at l. Pull was 0.056 to 0.074 ms from s to l, 10 to 20× faster than PG, because the call is in-process and there is an AVET index. Input bindings (100 emails) took 0.45 ms at s and 23 ms at l.
    • The joins and scans are much slower than PG. At m, q2 took 640 ms, q3 1.3 s and q4 320 ms. At l, q2 took 14.7 s and q3 16 s. as_of was a ceiling (over 20 s) at every scale from s up. It uses a correlated NOT EXISTS over timelined_transactions, which has no (e,a,v) index.
    • Concurrency falls over. Threads with one Store each (concurrency_sweep) gave 63 ops/s at 1 client and 10 to 14 ops/s at 8 to 64 clients, with p99 of 2 to 16 s. At 128 clients it hit the ceiling. At m, 8 clients already hit the ceiling. Readers during write_mixed had a p99 of 2.7 s at s and 32 s at m. The metadata mutex in Conn::q_once is per Store, and each thread has its own Store, so that mutex is not the cause. The suspected cause is SQLite’s global allocator mutex: the bundled build keeps memstatus on, and the gdb dump of the 128-thread Store::open shows every thread in sqlite3_free → pthread_mutex_lock. A profile of the query-phase collapse was not taken, so profile it with perf before fixing.
    • Store::open is O(history). It took 0.7 s at s, 6.8 s at m and about 20 s at l, because read_partition_map scans the parts view (a GROUP BY over all of timelined_transactions). 128 concurrent opens on the m store did not finish. All 128 threads sat in sqlite3_free → pthread_mutex_lock, RSS grew to 80 GB, and the kernel OOM-killed the process after 25 min (logs/embedded-m-open128-gdb.txt.gz, logs/embedded-m-oom.txt). The runner now opens stores in batches.
    • Bulk load: 42 to 55K datoms/s at m and l with direct entids (l: 39 min), against 11K/s at s when refs were (lookup-ref …). Lookup-ref resolution is the embedded bulk-load bottleneck (see gen.py). Any transaction of 5461 or more datoms panics (off-by-one assert in db.rs insert_non_fts_searches, 6*n < 32766).
    • An interrupted query (sqlite3_interrupt) panics in the projector (projectors/simple.rs unwraps the row iterator), which poisons the Store’s mutex.

  3. The SQLite loadable extension and the DuckDB extension pay a full Store::open on every call. Every scenario costs the same: 0.57 to 0.72 s per call at s and 9.5 to 11 s at m, whether it is q1, pull or q3. Bulk load through edn_t is therefore O(n²). It ran at 5.9K datoms/s (sqlite-ext) and 5.5K/s (DuckDB) at s. At m, sqlite-ext reached only 3.2M of 9.85M datoms in the 900 s cap (ceiling). Its m reads were measured on a copy of the embedded-built store. More processes help only up to the core count: sqlite-ext at s did 1.7 ops/s at 1 client and 78 ops/s at 128. The fix belongs in crates/: cache the Store per db_path per connection, or at least cache the partition map. DuckDB m was not run, within the instance budget. Its per-call cost is the same Store::open (DuckDB at s is within 5% of sqlite-ext on every scenario).

Sustained load, cold vs warm

  • sustained, 20 min (cut from the planned 30 min to stay inside the 6 h budget). PG at xl (32 readers of q1+q2+pull, plus 1 writer) and embedded at m (8 readers of the same mix, plus 1 writer) ran at the same time on the 128-vCPU box.
    • PG xl was stable. Comparing the first and last sixth of the 10 s windows, reads went from 1036 to 987 ops/s, read p50 from 44.4 to 44.9 ms and p99 from 49.6 to 50.1 ms. Writes went from 566 to 755 tx/s, with p99 of 2.6 then 2.0 ms. 1.25M reads and 0.80M writes finished with 0 errors. pg_stat_bgwriter shows 0 buffers_clean. Backend relation reads grew from 4.9M to 9.7M blocks, because first touches of the 244 GB DB came from the OS cache into s_b. temp_bytes did not grow. Postgres RSS stayed flat, since s_b is in huge pages and does not count as RSS. There was no latency drift and no throughput collapse.
    • Embedded m readers starved. 8 reader threads did 932 queries in total in 20 min: 0.8 ops/s, p50 3.9 ms, p99 35 s. The single writer meanwhile ran at 480 tx/s (p99 2.9 ms). Writer throughput was flat over the run. Reader p50 improved (11.7 s → 5.8 s) but p99 stayed around 33 to 35 s. Process RSS grew from 27 MB to 1.36 GB (8 Stores' page caches), then stayed there. The same collapse shows in write_mixed. Under a steady writer, embedded mentat’s WAL-mode readers get almost no work done against q2’s multi-second scans.
    • The windows are in logs/*-sustained-windows-*.csv. The samplers (RSS, iostat, pg_stat_io/bgwriter/database) are in logs/sampler-*.log and logs/iostat-*.log.
  • cold_vs_warm restarted PG and dropped the OS page cache. A cold first call cost 18 to 48 ms for q1 at s to l (424 ms at xl) and 11 to 14 ms for pull. After that, warm latency matched the steady state. shared_buffers starts empty after a restart. Embedded cold means a new process: the open time above plus a first query 2 to 10× slower than warm.

Correctness

Every scenario verifies its answer against the generator’s truth (checks.txt: point lookups, per-state counts, as_of pre-update states, the since count, input bindings, pull contents). No backend returned a wrong answer. 440 checks passed. The 7 FAIL lines are the annotated harness false positive described below. Some checks were SKIPped where the backend could not answer within limits: embedded/sqlite-ext/duckdb as_of (timeout), PG q4 at xl (jsonb size limit). Two harness false positives are annotated in checks.txt: a stale runner binary on the box, and a re-check without --post-write after write_mixed had mutated the data. Both are fixed in the suite.

Methodology caveats

  • All PG concurrency sweeps (s to xl) were re-run at the end with nothing else on the box, and the tables show those runs. The earlier sweeps overlapped other backends' phases and are kept in logs/pg-sweeps-contended.csv.
  • The single-client scenario phases of different backends sometimes overlapped: PG l while embedded l loaded, and embedded m while PG xl loaded. Each phase is at most one busy core plus IO on a 128-vCPU box with 7 TB of NVMe, but it is not a quiet machine. Use compare.py against a rerun before citing a single-client number to better than about 10%.
  • Embedded, sqlite-ext and duckdb load through tempids with @@ entid substitution. PG loads caller-chosen entids. The datoms are the same.
  • The mentat_git value in env.txt (815164a1+) is the suite commit when the run began. The engine is v1.9.0 (tag, 8c6c8816). Later suite commits changed only benchmarks/.