SQLite persistence: a first visit sets up its own tables and each collection's in separate write transactions, one collection after another

Đang mở
#2,453 0 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

Chưa có ai nhận issue này.

Đánh giá

Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức phù hợp với người mới
46/100
Loại issue
Lỗi
Độ rõ ràng
Đặc tả rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
sqlite, typescript
Lĩnh vực
database, performance

Hướng nghiên cứu

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.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

  • 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.

  1. setUpDatabase runs about 20 CREATE TABLE / CREATE INDEX / ALTER TABLE statements through this.driver.exec, each its own implicit transaction: about 190 ms on a first visit.
  2. ensureCollectionReadyInternal registers a new collection in one transaction (INSERT INTO collection_registry) and then creates its tables, indexes and triggers in a second (createCollectionTables; on main each of those nine statements is a commit of its own). That is about 90 ms a collection.
  3. 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, its COMMIT 11-20 ms, CREATE TABLE (first write of the second transaction) 9-14 ms, its COMMIT 16-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:

  1. ensureInitialized runs setUpDatabase(driver) inside runInTransaction. Every statement uses the transaction's driver. A duplicate-column ALTER still fails alone and is caught as before.

  2. An eager collection tells its adapter it is starting (adapter.expectCollection?.(storageCollectionId) in PersistedCollectionRuntime.ensureStarted, after claimCacheGeneration). Announcements are kept per driver, so they are shared across the adapters createBrowserWASQLitePersistence makes for each schema version.

  3. The first of those collections to reach ensureCollectionReadyInternal without 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 with setup_complete = 1 and 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/db 1.0.0-rc.1.5
  • @tanstack/db-sqlite-persistence-core 1.0.0-rc.1.5
  • @tanstack/browser-db-sqlite-persistence 1.0.0-rc.1.5
Ngôn ngữ chính
TypeScript
Star
4k
Fork
286
Merge trung bình
1 ngày 8 giờ
Pull request đã merge (30 ngày)
105

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của TanStack/db

Tất cả issue của TanStack/db

Issue tương tự

Thêm issue về TypeScript

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.