Skip to content
Source
Sign inSign up
TanStack/db

sqlite-persistence: collection table setup runs again on every page load #2204

Send to my agent
Closedkristianhjelle(GitHub profile, opens in a new tab) opened this issue 1 comment
  • 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

  1. @tanstack/browser-db-sqlite-persistence 0.2.36, @tanstack/db-sqlite-persistence-core 0.4.11
  2. OPFS database, 8 persisted collections (syncMode: "eager", schemaVersion: 1, BrowserCollectionCoordinator), fill them once, reload
  3. Log the database's execute calls

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:

RunFirst 3 collections readAll 8 read
1134 → 113 (−21)233 → 182 (−51)
2126 → 112 (−14)226 → 184 (−42)
3130 → 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.

Sign in to join this conversation.