#135 Scale saves and whole-timeline work toward very large timelines (1 billion moments)
–
- github
- github/openchronology/timeline-app
- github/openchronology/timeline-app#135
- issue
- state:closed
## Goal Support very large timelines (eventually around **1 billion moments**) on a self-hosted server, starting with the save path that log ingestion depends on. ## The immediate problem: large batches near the statement timeout Measured during #129 / #134 on an 8-core VM with PostgreSQL 17, using the benchmark timelines: | Moments | Saving 5,000 new moments | Of which, one statement updating the moment tree | |---|--:|--:| | 200,000 | 7–9 s | ~5.8 s | | 1,000,000 | 21–25 s | ~16 s | The server runs every statement with a 30 s `statement_timeout` (`server/platform.mjs`). The log importer (`och-import-logs`) and the syslog receiver save up to 5,000 entries at a time (`BATCH_LIMIT` in `server/timeline-writer.mjs`, and `batchLimit` in `server/import-logs.mjs`). So somewhere past a million moments, a full batch will hit the timeout and the save will fail. Saved history (#134) is only ~0.6 s of the 1M save. The slow statement is the moment tree's `path` query (`MOMENT_TREE.path` in `server/avl.mjs`). It's a recursive CTE that walks from the root once per key, then fetches every visited node and its children. Things to investigate: - **Casting:** `p.key::mpq` is parsed from text on every step of every walk (5,000 keys × tree depth); keys could be cast once. - **Shared paths:** appended log moments mostly land at the same right edge, so their paths share nearly all their nodes. Walking sorted keys together, or a range-based descent, would visit each node once. - **Deduplication:** `UNION` dedupes `(key, id)` pairs on every iteration. - **Fetching:** the final fetch reads whole node rows, including `moment_ids`. - **Batch size:** adaptive batch sizing in the writers (shrink on slow saves, grow on fast ones), as a safety net whatever the query cost. - **Measurement:** a benchmark of save time vs. timeline size for 10 / 1,000 / 5,000-entry batches up to several million moments, so regressions show. The interval trees for durations and relationship arcs use the same pattern (`INTERVAL_TREE.path`) and likely have the same cost. ## Toward 1 billion moments: other whole-timeline work Each of these grows with the timeline and needs to become incremental, paged, streamed, or done inside PostgreSQL: - **Catalogue search text** (`oc_event_text`, rebuilt after saves) already hit the 30 s timeout at 200,000 moments during the #134 measurements. It needs a bound (it's capped at 1 MiB of text but still walks the timeline in chunks) or an incremental form. - **Index rebuilds:** non-incremental saves (large patches, plugin changes, whole-document saves, syncs, merges, forks) rebuild the indexes in Node from a whole document (`indexedNodes`, `writeNodes` in `server/store.mjs`). At 1B moments every operation must be incremental, or built in the database. - **Exports and saved revisions:** `.ochx` export and reading a saved revision (`historyDocument`) build whole documents in memory (21 s and ~1.3 GB at 1M). These need to stream. - **Forks** copy rows (stage 2 of #134's plan: share the parent's rows instead). - **Pull requests and syncs** merge three whole documents in memory and store whole documents in `oc_proposals` (stage 3: do it in SQL, per entity). - **Migration:** `startHistory` copies every timeline in one statement per table (45 s at 1M). At 1B it needs paging or a background job. - **Storage layout:** `oc_moments`, `oc_nodes`, `oc_entity_search` and the history tables hold every timeline together. At this scale, partitioning by timeline (or by timeline and time) and per-table autovacuum settings matter, as does the size of the search index. - **Retention** deletes in batches of 200,000 per save (`BATCH` in `server/retention.mjs`), which also goes through the tree path query. ## Suggested order 1. Make the moment tree's path query cost proportional to the distinct nodes touched, not keys × depth. Add adaptive batch sizing in the writers, and a save-vs-size benchmark. 2. Bound or incrementalize the catalogue text refresh. 3. Stre……
Related
- #129 Why is there a limit of 200k moments on a timeline in platform mode?
- #134 Keep saved history as rows instead of whole documents
- #136 Optional saved history per timeline
- #138 Share saved history between timelines and their forks
- #139 Saves right after a large write get bad plans from stale statistics
- #140 Copy a fork's index in the database instead of rebuilding it in Node
- #143 Keep saves fast right after a large timeline is written
- #144 Copy a fork's index in the database instead of rebuilding it
- #145 Share index rows between a timeline and its forks instead of copying them
- #154 Make saves follow the nodes they touch, and timeline creation linear
- #155 Build the catalogue text in order and stop once it is full
- #156 Stream exported timelines and saved revisions
- #157 Plugins validate their own metadata; the server compiles scripts only in a sandbox
- #158 Apply large patches in place, a slice at a time
- #159 Expire in small saves, finding what to delete through the trees
- #160 Export large server timelines from the server
- #161 Vacuum and analyse the large tables after 1-2% of them change
- #290 A small timeline's catalogue text sorts its moments instead of walking
- Vacuum and analyse the large tables after 1-2% of them change