How OpenChronology was built

#143 · Keep saves fast right after a large timeline is written

2026-10-14T02:36:11Z CE

• timeline-app
• pull request

Fixes #139. ## Problem After a timeline is written in one go (a fork, a duplicate, a create or an import), PostgreSQL's statistics still count its rows as almost none. Measured at 200,000 moments, a 10-moment save to a fresh fork took **5.29 s** instead of about 0.2 s, and the catalogue text refresh after a larger save could exceed the 30 s statement limit. Fencing the lookups (the issue's first option) wasn't enough on its own. The query plans showed a second failure: even a lookup made one key at a time picked whichever index starts with `timeline_id`, because with the timeline counted as one row, every such index looks equally cheap. For example, each duration lookup read the end-moment index and filtered 20,000 rows. So both options are needed. ## What changes **Statistics are refreshed after large writes, before the request returns** (`store.analyze`, called from `store.transaction`). - `replace()` flags the transaction when it writes 20,000 moments and durations or more. - After commit, the store analyzes the six index tables. - This happens outside the writing transaction, which would otherwise hold `ANALYZE`'s lock until commit and serialize concurrent large writes. - Writes in quick succession share one more run. - `idle()` waits for it. - The 30k-row sample costs well under a second, next to the roughly 100 s a 200k fork takes. **Lookups by key stay one per key, whatever the statistics say.** Each is a lateral subquery ending in `OFFSET 0`, which the planner can't flatten into a join: - the moment-tree and interval-tree path queries in `server/avl.mjs`, both the recursive step and the nodes and children read at the end; - every key lookup in `server/incremental.mjs`; - the moment lookup in `oc_event_text`, which builds catalogue text. The next node ID is read as `ORDER BY id DESC LIMIT 1` instead of `max(id)`. ## Measured (200,000 benchmark moments, `node bench/fork-save.mjs`) | Saving 10 moments | Before | After | |---|--:|--:| | Right after forking | 5.29 s | 0.16 s | | With current statistics | 0.28 s | 0.18–0.54 s (noise between runs) | **Catalogue text.** The refresh after a 5,000-moment save to a new fork previously timed out at 30 s; it now completes in about 5 s. **Not this issue.** A 5,000-moment save costs about 25 s either way, with or without current statistics: that's the tree path work tracked in #135. Writing a fork's 1 MB catalogue text is also slow, because of the GIN index rather than statistics. ## Tests - New `test/statistics-postgres.mjs`, added to CI: - a 1,000-moment create doesn't trigger `ANALYZE`; - a 25,000-moment create, and a fork of it, have all six index tables analyzed before they return; - lookups by ID on the fork then use their own indexes (`oc_duration_nodes_duration_id`, `oc_duration_nodes_pkey`, `oc_moments_pkey`). That's the failure seen in the plans. - Removing the refresh makes the test fail. - New `bench/fork-save.mjs`, described in `docs/benchmarks.md`. It is the measurement above. Locally, all of these pass: - unit tests (257) and typecheck; - every PostgreSQL suite, including the platform's. Among them are the oracles that compare incremental saves with full rebuilds, and the summary, duration and relationship view tests, which exercise the changed tree queries.

Open #143 · Keep saves fast right after a large timeline is written in the timeline