sqlite-persistence: collection table setup runs again on every page load #2204
Send to my agent- I've validated the bug against the latest version of DB packages
Describe the bug#
Every page load, each persisted collection runs these 9 statements in ensureCollectionReadyInternal, even if they all ran on the last load and nothing is missing. On the browser OPFS adapter each one is a round trip to the worker, and collections start one at a time, so it adds up.
Trace of one collection on a reload (start ms, duration ms):
253.0 2.0 SELECT table_name, tombstone_table_name, schema_version FROM collection_registry …
255.0 0.8 CREATE TABLE IF NOT EXISTS "c_qvzbpr_8" …
255.8 0.7 CREATE INDEX IF NOT EXISTS "c_qvzbpr_8_row_version_idx" …
256.5 0.7 CREATE TABLE IF NOT EXISTS "t_qvzbpr_8" …
257.3 0.7 CREATE INDEX IF NOT EXISTS "t_qvzbpr_8_row_version_idx" …
258.0 1.6 INSERT INTO collection_version … ON CONFLICT(collection_id) DO NOTHING
259.6 1.2 INSERT INTO collection_reset_epoch … ON CONFLICT(collection_id) DO NOTHING
260.9 0.7 CREATE TRIGGER IF NOT EXISTS "c_qvzbpr_8_key_evidence_insert" …
261.6 3.0 CREATE TRIGGER IF NOT EXISTS "c_qvzbpr_8_key_evidence_delete" …
264.6 1.1 CREATE TRIGGER IF NOT EXISTS "c_qvzbpr_8_key_evidence_update" …
265.7 1.0 BEGIN IMMEDIATE
To reproduce#
@tanstack/browser-db-sqlite-persistence0.2.36,@tanstack/db-sqlite-persistence-core0.4.11- OPFS database, 8 persisted collections (
syncMode: "eager",schemaVersion: 1,BrowserCollectionCoordinator), fill them once, reload - Log the database's
executecalls
Expected behavior#
Skip the 9 when nothing is missing. I tried one check in their place, after the schema version check and only when managedAtAdmission is false:
SELECT
(SELECT COUNT(*) FROM sqlite_master
WHERE (type = 'table' AND name IN (?, ?))
OR (type = 'index' AND name IN (?, ?))
OR (type = 'trigger' AND name IN (?, ?, ?))) = 7
AND EXISTS (SELECT 1 FROM collection_version WHERE collection_id = ?)
AND EXISTS (SELECT 1 FROM collection_reset_epoch WHERE collection_id = ?) AS ready
All 9 statements are IF NOT EXISTS or ON CONFLICT DO NOTHING, and this checks the same things they do, so if it returns 1 they would all have been no-ops. If it returns 0, the normal setup runs.
Numbers#
It saves 8 round trips per collection per page load (9 statements become 1). With 8 collections that took 39–51 ms off the time until all of them had read their rows from disk, about 5–6 ms per collection.
Headless Chromium, 8 collections, 3 runs of 15 reloads, before and after alternating. Medians in ms from creating the collections until their rows were read:
| Run | First 3 collections read | All 8 read |
|---|---|---|
| 1 | 134 → 113 (−21) | 233 → 182 (−51) |
| 2 | 126 → 112 (−14) | 226 → 184 (−42) |
| 3 | 130 → 115 (−15) | 228 → 189 (−39) |
Desktop#
Headless Chromium 141 on Linux.
Additional context#
Happy to send a PR with an oracle test if you want this.
KyleAMathews(GitHub profile, opens in a new tab) closed this
Sign in to join this conversation.
🎉 The fix for this issue has been released!
Fixing PRs: https://github.com/TanStack/db/pull/2219
Backfilled notification for published release candidates.