timescale.info#201 · Large timelines are written with the overview index loaded in bulk (#199)
- timeline-app
- pull request
Closes #199. `och-convert write` (server `.och` exports, and the desktop's staging of large documents) filled the overview index one row at a time. Each insert walked and rebalanced the tree through about 100 SQL statements, roughly 0.35 ms per distinct time. Conversions ran out of the 20 s CPU limit at about 45,000 moments. ## Bulk loading in sqlite-rational [athanclark/sqlite-rational#1](https://github.com/athanclark/sqlite-rational/pull/1) adds two modes to `rational_index`, vendored here (upstream commit `8f0c8ef`, recorded in `vendor/versions.json`): - `mode='append'` stores a row in an empty index without linking it into the tree; - `INSERT INTO points(mode) VALUES('build')` sorts the rows and writes a balanced tree bottom-up, each node once. Until the build, queries and other changes are refused. Duplicate keys and weight overflow fail the build. Ordinary inserts, updates and deletes are unchanged, and so is the file format: a loading index exists only inside the save's transaction. ## save() `save()` appends the buckets in ranges of 5,000 times, then builds once. - **Why ranges:** one append statement for 200,000 times used 18,800 of the 20,000-tick instruction budget (#193); a range uses a fraction of it. The build uses 9,800. - **Who's affected:** every caller of `save()` writes a new file, so the index is always empty there; desktop edits still go through the ordinary per-time path in `workspace.rs`. ## Measured (`och-convert write`, release build, moments only) | Moments | Before | After | | --- | --- | --- | | 10,000 | 4.3 s | 0.9 s | | 30,000 | 13.3 s | ~2 s | | 200,000 | killed at the CPU limit | 9.0 s | All read back whole. At 200,000 the process peaks at about 400 MB, against the 512 MiB address-space limit. Most of that is the parsed JSON document, unchanged by this PR, so moments with large metadata could still reach the memory or 32 MiB file-size limits before the CPU one. ## Testing - sqlite-rational's suite, with three new tests, passes. - `cargo test` for native-store (24) and `npm test` (294) pass.