timescale.info#290 · A small timeline's catalogue text sorts its moments instead of walking
- timeline-app
- pull request
Closes #285. **Cause.** `oc_event_text` read moments by walking the moment tree through `oc_events`, one PL/pgSQL statement per node and per moment. On a fork each of those goes through `oc_events_lineage`, which tries a live and then a retired version at each level it reads through. After the source has been saved many times, more of what the fork reads is retired, so more lookups miss before they hit. The plain function also had JIT on, and compiling its plans took about 2 s on a connection's first rebuild. **Fix** (`server/schema.sql`): - For a timeline of at most 50,000 moments, `oc_event_text` sorts all the moments' IDs in one query (`ORDER BY time, id COLLATE "C"`, the moment tree's own order). For a fork it reads them from `oc_moments_at` across its levels. It then fetches the moments by ID 1,000 at a time and runs them through the same text extraction as before, so the text still stops once 1 MiB is full: long notes don't make the rebuild read every moment. - Larger timelines walk as before. At 200,000 moments, sorting every ID costs more than walking to 1 MiB, and #135 requires the cost to follow the text, not the timeline size. - JIT is turned off for `oc_event_text`, as it already was for the `_lineage` variants. **Results.** `bench/fork-text.mjs` (new) times the rebuild and checks the fork's text against a timeline created from the fork's document. Main's schema vs this branch, each in a fresh database: | Moments (source saves) | Source, before | Source, now | Fork, before | Fork, now | | --- | ---: | ---: | ---: | ---: | | 5,000 (100) | 0.29–0.35 s | 0.15–0.18 s | 1.95 s | 0.25 s | | 20,000 (200) | 1.13 s | 0.72 s | 5.71 s | 0.60 s | | 50,000 (200) | 1.10 s | 1.18 s | 7.58 s | 0.78 s | | 200,000 (20, walked) | 0.72 s | 0.72 s | 3.2 s | 3.2 s | The 5,000 and 200,000 rows come from interleaved runs on the same data. The first rebuild on a connection dropped from 2.2 s to 0.3 s at 5,000 moments, and from 3.0 s to 1.7 s at 200,000. The texts are byte-identical (same md5) before and after, on sources and forks. **Tests:** - `test/catalogue-text-postgres.mjs` now builds every text both ways: sorted, and walked (by raising `event_count` inside a rolled-back transaction). Both must equal the reference, so both paths stay covered. - #284's deleted-during-rebuild check still passes. The rebuild now pauses at its first read of `oc_moments`. - These Postgres suites pass against a local PostgreSQL 16 with pgmp, with no "Broken timeline index" logged: catalogue-text, fork-read-through (482 revisions, 4 levels deep), index-cleanup, index-versions, incremental, shared-history, search, deletion, fork-preparing, summaries-equivalence, and `next-postgres` (`npm run test:platform:postgres`). - `npm test` passes 305 of 306. The failure is the README check tripping on a `node_modules` symlink in my worktree, which isn't in the PR. - `npm run build` passes. **Also:** `bench/fork-reads.mjs` now copies with `duplicate`. Since #262 an owner can't fork their own timeline, so that benchmark stopped at its first copy. Docs: `docs/architecture.md` (catalogue text), `docs/benchmarks.md` (new section), `bench/README.md`.
Sources
Open #290 · A small timeline's catalogue text sorts its moments instead of walking in the timeline