SQLite persistence: a first visit sets up its own tables and each collection's in separate write transactions, one collection after another
Maintainer antworten meist innerhalb von 1 Tag
Dieses Issue hat noch niemand übernommen.
Bewertung
- Schwierigkeit
- 4/5
- Geschätzter Aufwand
- 3-5 Tage
- Anfängerfreundlichkeit
- 46/100
- Issue-Typ
- Bug
- Klarheit
- Klar beschrieben
- Aktivitätsstatus
- Aktiv
- Tech-Stack
- sqlite, typescript
- Bereich
- database, performance
Rechercherichtung
The proposal identifies SQLiteCorePersistenceAdapter, ensureInitialized, setUpDatabase, ensureCollectionReadyInternal, and PersistedCollectionRuntime.ensureStarted; begin by locating those entry points and checking how transactions and shared adapters are handled. The issue describes an OPFS measurement with five eager collections, so reproduce that startup path and compare transaction counts and request timing. Done means database setup and eligible announced collections use fewer write transactions while each collection keeps its schema version and claimed or prefixed collections retain their existing setup path.
Vom Indexierungsmodell aus dem Issue-Text verfasst.
Beschreibung
- I've validated the bug against the latest version of DB packages
Problem
On a first visit, SQLiteCorePersistenceAdapter sets up its own tables and each collection's tables with many separate write transactions, one collection after another. On OPFS each write transaction pays to start (the first write, 15-35 ms) and to commit (15-30 ms), so the cost is in the transactions, not the statements.
setUpDatabaseruns about 20CREATE TABLE/CREATE INDEX/ALTER TABLEstatements throughthis.driver.exec, each its own implicit transaction: about 190 ms on a first visit.ensureCollectionReadyInternalregisters a new collection in one transaction (INSERT INTO collection_registry) and then creates its tables, indexes and triggers in a second (createCollectionTables; onmaineach of those nine statements is a commit of its own). That is about 90 ms a collection.- Collections share one
SharedPersistenceScheduler, and each collection's startup task (metadata, then its sync's first request) waits for every earlier collection's setup. With five collections before a page's data, the fifth collection's first request waits about 0.4 s behind the first's.
Measurement
Chrome, @tanstack/browser-db-sqlite-persistence on OPFS, an empty browser profile, five eager collections starting together. We timed each worker message from postMessage to its answer:
- From the first collection's first Electric request to the fifth's: 360-500 ms. All of it was spent in the worker; the main thread waited 8 ms between messages.
- Per collection:
INSERT INTO collection_registry(first write) 17-35 ms, itsCOMMIT11-20 ms,CREATE TABLE(first write of the second transaction) 9-14 ms, itsCOMMIT16-30 ms. The other statements took 0.1-10 ms each.
Proposed change
We run this as a pnpm patch on 1.0.0-rc.1.5:
-
ensureInitializedrunssetUpDatabase(driver)insiderunInTransaction. Every statement uses the transaction's driver. A duplicate-columnALTERstill fails alone and is caught as before. -
An eager collection tells its adapter it is starting (
adapter.expectCollection?.(storageCollectionId)inPersistedCollectionRuntime.ensureStarted, afterclaimCacheGeneration). Announcements are kept per driver, so they are shared across the adapterscreateBrowserWASQLitePersistencemakes for each schema version. -
The first of those collections to reach
ensureCollectionReadyInternalwithout a registration does the following in one transaction:- it registers and creates the tables for itself;
- it does the same for every other announced collection that has no registration yet, each through its own adapter, so each keeps its own
schemaVersion.
Collections with a cache-generation claim or id prefix are left out and set up alone, as now. When the others reach
ensureCollectionReady, each finds its registration withsetup_complete = 1and only caches its table mapping.
The change as a diff of the ESM build. It is applied on top of an earlier local patch, which already gives createCollectionTables a driver argument so that it can run inside a transaction:
diff
--- a/dist/esm/sqlite-core-adapter.js
+++ b/dist/esm/sqlite-core-adapter.js
@@ -80,6 +80,7 @@
}
var sharedPersistenceSchedulers = /* @__PURE__ */ new WeakMap();
var observedDriverSchedulingKeys = /* @__PURE__ */ new WeakMap();
+var collectionsStarting = /* @__PURE__ */ new WeakMap();
function getSharedPersistenceScheduler(key) {
let scheduler = sharedPersistenceSchedulers.get(key);
if (!scheduler) {
@@ -1866,8 +1867,43 @@
this.collectionTableLoads.delete(collectionId);
}
}
- async loadCollectionRegistration(collectionId) {
- return (await this.driver.query(`SELECT table_name, tombstone_table_name, schema_version,
+ // An eager collection says it is starting, so the first of those started together to reach SQLite registers them all in one transaction.
+ expectCollection(collectionId) {
+ if (hasCacheGenerationIdPrefix(collectionId) || this.collectionTableCache.has(collectionId)) return;
+ let starting = collectionsStarting.get(this.schedulingIdentitySource);
+ if (!starting) collectionsStarting.set(this.schedulingIdentitySource, starting = /* @__PURE__ */ new Map());
+ starting.set(collectionId, this);
+ }
+ takeCollectionsStartingTogether(collectionId, cacheGenerationClaimId) {
+ const starting = collectionsStarting.get(this.schedulingIdentitySource);
+ if (!starting) return [];
+ starting.delete(collectionId);
+ if (cacheGenerationClaimId !== void 0 || hasCacheGenerationIdPrefix(collectionId)) return [];
+ const together = [...starting];
+ starting.clear();
+ return together;
+ }
+ async registerCollection(collectionId, driver) {
+ const tableName = createPersistedTableName(collectionId, `c`);
+ const tombstoneTableName = createPersistedTableName(collectionId, `t`);
+ await driver.run(`INSERT INTO collection_registry (
+ collection_id,
+ table_name,
+ tombstone_table_name,
+ schema_version,
+ updated_at
+ )
+ VALUES (?, ?, ?, ?, CAST(strftime('%s', 'now') AS INTEGER))
+ ON CONFLICT DO NOTHING`, [
+ collectionId,
+ tableName,
+ tombstoneTableName,
+ this.schemaVersion
+ ]);
+ await this.createCollectionTables(collectionId, tableName, tombstoneTableName, driver);
+ }
+ async loadCollectionRegistration(collectionId, driver = this.driver) {
+ return (await driver.query(`SELECT table_name, tombstone_table_name, schema_version,
(SELECT COUNT(*) FROM sqlite_master
WHERE (type = 'table'
AND name IN (table_name, tombstone_table_name))
@@ -1891,24 +1927,12 @@
const managedAtAdmission = cacheGenerationClaimId !== void 0 || hasCacheGenerationIdPrefix(collectionId) && (await this.driver.query(`SELECT physical_id FROM cache_generation WHERE physical_id = ?`, [collectionId]))[0] !== void 0;
let registration = await this.loadCollectionRegistration(collectionId);
if (!registration) {
- const tableName = createPersistedTableName(collectionId, `c`);
- const tombstoneTableName = createPersistedTableName(collectionId, `t`);
+ const startingTogether = this.takeCollectionsStartingTogether(collectionId, cacheGenerationClaimId);
+ // One transaction for this collection and every other started with it: on OPFS each write transaction costs ~40-60 ms to start and commit, and collections set up one after another.
await this.runInTransaction(async (transactionDriver) => {
await this.assertCacheGenerationReadClaim(transactionDriver, collectionId, cacheGenerationClaimId);
- await transactionDriver.run(`INSERT INTO collection_registry (
- collection_id,
- table_name,
- tombstone_table_name,
- schema_version,
- updated_at
- )
- VALUES (?, ?, ?, ?, CAST(strftime('%s', 'now') AS INTEGER))
- ON CONFLICT DO NOTHING`, [
- collectionId,
- tableName,
- tombstoneTableName,
- this.schemaVersion
- ]);
+ await this.registerCollection(collectionId, transactionDriver);
+ for (const [otherId, otherAdapter] of startingTogether) if (!await otherAdapter.loadCollectionRegistration(otherId, transactionDriver)) await otherAdapter.registerCollection(otherId, transactionDriver);
});
registration = await this.loadCollectionRegistration(collectionId);
if (!registration) throw new InvalidPersistedCollectionConfigError(`Unable to register persistence tables for collection "${collectionId}"`);
@@ -2074,7 +2098,8 @@
}
async ensureInitialized() {
if (this.initialized) return;
- if (!await this.databaseSetupComplete()) await this.setUpDatabase();
+ // One transaction, one commit: each statement on its own committed to OPFS, ~190 ms on a first visit.
+ if (!await this.databaseSetupComplete()) await this.runInTransaction((transactionDriver) => this.setUpDatabase(transactionDriver));
this.initialized = true;
}
async databaseSetupComplete() {
@@ -2105,39 +2130,39 @@
AND sql LIKE '%source_progress_incompatible%')
AS complete`))[0]?.complete === 1;
}
- async setUpDatabase() {
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS cache_generation (
+ async setUpDatabase(driver) {
+ await driver.exec(`CREATE TABLE IF NOT EXISTS cache_generation (
logical_id TEXT NOT NULL,
generation INTEGER NOT NULL,
physical_id TEXT NOT NULL UNIQUE,
retired INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (logical_id, generation)
)`);
- await this.driver.exec(`CREATE UNIQUE INDEX IF NOT EXISTS cache_generation_head
+ await driver.exec(`CREATE UNIQUE INDEX IF NOT EXISTS cache_generation_head
ON cache_generation (logical_id) WHERE retired = 0`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS cache_generation_sequence (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS cache_generation_sequence (
logical_id TEXT PRIMARY KEY,
next_generation INTEGER NOT NULL
)`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS cache_generation_claim (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS cache_generation_claim (
claim_id TEXT PRIMARY KEY,
logical_id TEXT NOT NULL,
physical_id TEXT NOT NULL,
expires_at_ms INTEGER NOT NULL
)`);
- const claimColumns = await this.driver.query(`PRAGMA table_info(cache_generation_claim)`);
- if (!claimColumns.some(({ name }) => name === `logical_id`)) await this.driver.exec(`ALTER TABLE cache_generation_claim
+ const claimColumns = await driver.query(`PRAGMA table_info(cache_generation_claim)`);
+ if (!claimColumns.some(({ name }) => name === `logical_id`)) await driver.exec(`ALTER TABLE cache_generation_claim
ADD COLUMN logical_id TEXT NOT NULL DEFAULT ''`);
- if (!claimColumns.some(({ name }) => name === `expires_at_ms`)) await this.driver.exec(`ALTER TABLE cache_generation_claim
+ if (!claimColumns.some(({ name }) => name === `expires_at_ms`)) await driver.exec(`ALTER TABLE cache_generation_claim
ADD COLUMN expires_at_ms INTEGER NOT NULL DEFAULT 0`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS collection_registry (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS collection_registry (
collection_id TEXT PRIMARY KEY,
table_name TEXT NOT NULL UNIQUE,
tombstone_table_name TEXT NOT NULL UNIQUE,
schema_version INTEGER NOT NULL,
updated_at INTEGER NOT NULL
)`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS persisted_index_registry (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS persisted_index_registry (
collection_id TEXT NOT NULL,
signature TEXT NOT NULL,
index_name TEXT NOT NULL,
@@ -2149,7 +2174,7 @@
last_used_at INTEGER NOT NULL,
PRIMARY KEY (collection_id, signature)
)`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS applied_tx (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS applied_tx (
collection_id TEXT NOT NULL,
term INTEGER NOT NULL,
seq INTEGER NOT NULL,
@@ -2160,26 +2185,26 @@
applied_at INTEGER NOT NULL,
PRIMARY KEY (collection_id, term, seq)
)`);
- await this.driver.exec(`CREATE INDEX IF NOT EXISTS idx_applied_tx_collection_tx_id
+ await driver.exec(`CREATE INDEX IF NOT EXISTS idx_applied_tx_collection_tx_id
ON applied_tx (collection_id, tx_id)`);
try {
- await this.driver.exec(`ALTER TABLE applied_tx ADD COLUMN replay_json TEXT`);
+ await driver.exec(`ALTER TABLE applied_tx ADD COLUMN replay_json TEXT`);
} catch (error) {
if (!isDuplicateColumnAddError(error, `replay_json`)) throw error;
}
try {
- await this.driver.exec(`ALTER TABLE applied_tx ADD COLUMN replay_requires_full_reload INTEGER NOT NULL DEFAULT 0`);
+ await driver.exec(`ALTER TABLE applied_tx ADD COLUMN replay_requires_full_reload INTEGER NOT NULL DEFAULT 0`);
} catch (error) {
if (!isDuplicateColumnAddError(error, `replay_requires_full_reload`)) throw error;
}
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS collection_version (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS collection_version (
collection_id TEXT PRIMARY KEY,
latest_row_version INTEGER NOT NULL,
key_set_evidence_available INTEGER NOT NULL DEFAULT 0,
key_set_evidence_incompatible INTEGER NOT NULL DEFAULT 0,
source_progress_incompatible INTEGER NOT NULL DEFAULT 0
)`);
- const collectionVersionColumns = await this.driver.query(`PRAGMA table_info(collection_version)`);
+ const collectionVersionColumns = await driver.query(`PRAGMA table_info(collection_version)`);
for (const columnName of [
`key_set_evidence_available`,
`key_set_evidence_incompatible`,
@@ -2187,28 +2212,28 @@
]) {
if (collectionVersionColumns.some(({ name }) => name === columnName)) continue;
try {
- await this.driver.exec(`ALTER TABLE collection_version ADD COLUMN ${columnName} INTEGER NOT NULL DEFAULT 0`);
+ await driver.exec(`ALTER TABLE collection_version ADD COLUMN ${columnName} INTEGER NOT NULL DEFAULT 0`);
} catch (error) {
if (!isDuplicateColumnAddError(error, columnName)) throw error;
}
}
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS collection_expected_keys (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS collection_expected_keys (
collection_id TEXT NOT NULL,
key TEXT NOT NULL,
PRIMARY KEY (collection_id, key)
)`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS collection_metadata (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS collection_metadata (
collection_id TEXT NOT NULL,
key TEXT NOT NULL,
value TEXT NOT NULL,
updated_at INTEGER NOT NULL,
PRIMARY KEY (collection_id, key)
)`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS leader_term (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS leader_term (
collection_id TEXT PRIMARY KEY,
latest_term INTEGER NOT NULL
)`);
- await this.driver.exec(`CREATE TABLE IF NOT EXISTS collection_reset_epoch (
+ await driver.exec(`CREATE TABLE IF NOT EXISTS collection_reset_epoch (
collection_id TEXT PRIMARY KEY,
reset_epoch INTEGER NOT NULL,
updated_at INTEGER NOT NULL
--- a/dist/esm/persisted.js
+++ b/dist/esm/persisted.js
@@ -847,6 +847,7 @@
resolveStartupMetadata();
return;
}
+ if (this.syncMode !== `on-demand`) this.persistence.adapter.expectCollection?.(this.storageCollectionId);
let metadataExpired = false;
const loadStartupMetadata = async (adapter) => {
if (lifecycleGeneration !== this.lifecycleGeneration) {
Effect
On the same machine, the same data, an empty profile each run, interleaved, five runs each, medians:
- The library's own tables took 65 ms, down from 190 ms.
- The five collections' setup is one transaction of 130 ms, down from about 450 ms over five transactions in a row.
- All five first requests go out within about 40 ms of each other.
- Time until a list page showed its rows: 3.11 s down to 2.73 s and 3.40 s down to 2.52 s at a desktop viewport; 2.85 s down to 2.76 s and 3.41 s down to 2.59 s at a phone viewport. The page with more collections before its rows gains more.
Versions
@tanstack/db1.0.0-rc.1.5@tanstack/db-sqlite-persistence-core1.0.0-rc.1.5@tanstack/browser-db-sqlite-persistence1.0.0-rc.1.5
- Vorherrschende Sprache
- TypeScript
- Sterne
- 4k
- Forks
- 286
- Ø Merge
- 1 T. 8 Std.
- Gemergte PRs (30 T.)
- 105
Entwicklungsumgebung
- Kein Dockerfile und keine Docker-Compose-Datei
- Hat eine Pull-Request-Vorlage
- Beitragsleitfaden lesen
Erste Schritte
- Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
- Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
- Forken Sie das Repository und arbeiten Sie in einem Branch.
- Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.
Mehr aus TanStack/db
-
extractSimpleComparisons returns value: null when the right side of a comparison isn't a literalOffen
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 82/100
Maintainer antworten meist innerhalb von 1 Tag
-
`avg`, `min`, `max` and `sum` treat `null` values as `0`Evtl. vergeben @oyobbeb hat das vor 11 Tagen übernommen. Offen
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 76/100
Maintainer antworten meist innerhalb von 1 Tag
-
`Collection.currentStateAsChanges` JSDoc examples don’t match `CurrentStateAsChangesOptions`Evtl. vergeben Ein verknüpfter Pull Request ist offen oder bereits gemergt. Offen
Schwierigkeit 1/5 1-3 Stunden Anfängerfreundlichkeit 88/100
Maintainer antworten meist innerhalb von 1 Tag
-
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 78/100
Maintainer antworten meist innerhalb von 1 Tag
-
Two @tanstack/db tests time out under CPU load because a cold module import counts against the 5 s default timeoutEvtl. vergeben @jp-knj hat das vor 23 Tagen übernommen. Offen
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 82/100
Maintainer antworten meist innerhalb von 1 Tag
Ähnliche Issues
-
inference/go type/feature
Schwierigkeit 1/5 1-3 Stunden Anfängerfreundlichkeit 72/100
anomalyco/opencode#65148 · 1 Kommentar ·
Maintainer antworten meist innerhalb von 1 Tag
-
truapi-release
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 72/100
paritytech/product-sdk#529 ·
Maintainer antworten meist innerhalb von 1 Tag
-
Add: Mansudae TVOffenapproved check:passed streams:add
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 64/100
Maintainer antworten meist innerhalb von 1 Tag
-
[Perf] Canvas layer composition is quadratic: splitRenderLayers re-copies the node array per nodeOffen
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 78/100
Maintainer antworten meist innerhalb von 1 Tag
-
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 77/100
devlikeapro/waha#2369 ·
Maintainer antworten meist innerhalb von 5 Tagen