timescale.info#30 · Benchmark latency by timeline size across PostgreSQL, SQLite and in-memory
- timeline-app
- pull request
Fixes #27. ## What's included - **Seeds** (`bench/generate.mjs`, `bench/seed.mjs`): deterministic timelines with clustered moments, tags, durations and relationships, written as `.ochx`, `.och` (via the new `och-seed` binary) and PostgreSQL (`BENCH_DATABASE_URL`, dedicated `bench` user). - **Measurements**: tinybench for the in-memory `TimelineIndex` and the PostgreSQL store (`bench/run.mjs`); criterion, in-process, for the native SQLite store (`native-store/benches/backends.rs`). Seven operations: whole/1% viewport, page of 100, read one, create/update/delete. The cleanup between samples is excluded. - **Report** (`bench/report.mjs`): self-contained HTML with one log-log chart per operation. Backends are coloured lines on a shared y axis, with ±SD whiskers, direct labels, hover/focus tooltips (median, p99, samples), a full table and dark mode. JSON results are written too. - `npm run bench:seed`, `npm run bench`, `npm run bench:report`. - **Benchmarks workflow** (manual `workflow_dispatch`, sizes/time inputs, defaults 1k/10k/50k): PostgreSQL 16 + native pgmp, uploads `bench/results/` as an artifact. - `docs/benchmarks.md` covers the method and findings, linked from the README. ## Findings (local run, means) | | 1k | 10k | 100k | |---|--:|--:|--:| | PostgreSQL save (create/update/delete) | 180 ms | 1.6 s | **18.5 s** | | SQLite save | 12 ms | 97 ms | 1.2 s | | In-memory save | 0.1 ms | 0.1 ms | 0.2 ms | | Whole-timeline viewport: memory / SQLite / PostgreSQL | 3.5 / 16 / 61 ms | 14 / 105 / 95 ms | 283 / 922 / 331 ms | Reads (a page of 100 moments, or one moment) stay flat on every backend. Saves on both persistent backends are **O(n)**: - PostgreSQL rebuilds the index, snapshot, search and arcs on every save. - SQLite stages a full copy of the file before applying the patch. At 100k moments, server-side editing is not interactive. This is probably worth a follow-up issue. Viewport queries also grow roughly linearly, even for a 1% window. ## Notes - Criterion's SD is across batched samples and tinybench's is per call, so whisker widths aren't directly comparable between SQLite and the others (documented). - SQLite timings exclude Tauri IPC. - Lockfiles: only `tinybench` was added to npm/pnpm/yarn; `criterion` (dev-only, default features off) to Cargo.lock. ## Testing typecheck, `npm test` (179 pass), `cargo test` and `cargo fmt` pass. The full suite was run locally at 1k/10k/100k against a disposable PostgreSQL 16 + pgmp container. The report was checked in light/dark and at phone width.