How OpenChronology was built

#37 · Compute PostgreSQL duration summaries set-based over a start index

2026-10-09T14:39:44Z CE

• timeline-app
• pull request

Follow-up to #35 / #36 (step 3: PostgreSQL whole-timeline views). ## Results (`npm run bench`, PostgreSQL means) | Viewport | 1k | 10k | 100k | |---|--:|--:|--:| | Whole timeline, before | 7.4 ms | 23 ms | 101 ms | | **Whole timeline, now** | 10 ms | **14 ms** | **27 ms** | | 1% of the timeline, before | 3.0 ms | 7.5 ms | 12 ms | | 1% of the timeline, now | 2.8 ms | 7.1 ms | 11 ms | At 100k, `oc_duration_overview` alone went from 79 ms to 14 ms. Reads and saves are unchanged (saves 3.9–5.5 ms, flat). At 1k, the whole-timeline view costs a little more (10 ms against 7.4 ms): each cluster now takes two small index queries, and with few durations the old walk was cheaper. ## Change - **New `oc_duration_overview`.** It mirrors the SQLite query from #36: each cluster's anchor is the first collapsed start at or after the previous anchor plus the threshold, found by an index range scan. Count, latest end and the single band are aggregated over the starts before the next anchor. The tree walk, which long durations forced through nearly every node, is gone. - **Index without `mpq` B-tree keys.** The schema deliberately keeps `mpq` values out of B-tree keys, so `oc_duration_nodes` (and `oc_view_duration_nodes`) gain a stored generated column, `first_approx float8 = first_time::float8`, indexed on `(timeline_id, first_approx)`. - pgmp's conversion never reverses the order of two values, and saturates at ±Infinity and 0. So a range of approximations always contains every start in the exact range, and exact `mpq` comparisons recheck each row. Ties in the approximation, including all starts beyond float8's range, are ordered by the exact value. - Because the column is generated, full rebuilds and incremental saves maintain it with no application changes. - The separation views' generated function picks up the new definition automatically. **Migration:** `npm run migrate` adds the column, which rewrites `oc_duration_nodes` once, and builds the index. ## Equivalence tests `test/summaries-equivalence-postgres.mjs` (already in CI) compares the new function with the original traversal from before #35, copied verbatim, and requires identical rows: - 320 random windows over 8 timelines (about 1,850 groups), with trees reshaped by incremental saves. - New: starts beyond float8's range (±10⁴⁰⁰, and 10⁻⁴⁰⁰ that rounds to 0), whose approximations saturate and tie. - New: a deterministic cluster of 40 short durations around 10⁴⁰⁰, all with the same `Infinity` approximation, split into several groups. The test catches two deliberate bugs: - removing the exact recheck, so the group trusts the approximate range alone; - making the group boundary inclusive (`<=`). All other PostgreSQL suites (including `sql-oracle`, `summaries-postgres`, `views-postgres`, `incremental-postgres` and `files`), 180 JS unit tests and the typecheck pass.

Open #37 · Compute PostgreSQL duration summaries set-based over a start index in the timeline