[Onyx Audit] Investigate SQLite for Web

@fabioh8010

Parent issue.

Coming from here.

SQLite is available in all the major browsers through a WASM build and the OPFS. Let's see if this provides any performance gains when used inside of Onyx for the web platform.

Looks like we did some internal research in the past so the main goal here is to double check the findings again, measure the performance difference and if we have good significant gains, create a proposal for it. Things to have in mind:

Will it be possible to use it in all web platforms? What's the compatibility coverage?
How to seamless migrate data from IndexedDB to SQLite
What further improvements/simplifications this can bring to Onyx internals? For example, right now we have some platform differences about how data is prepared is sent to the providers in Onyx.

Issue Owner

Current Issue Owner: @fabioh8010

I will work on this soon.

@fabioh8010

@fabioh8010 Still overdue 6 days?! Let's take care of this!

@fabioh8010

This issue has not been updated in over 14 days. @fabioh8010 eroding to Weekly issue.

@roryabraham

Implementation being discussed as part of @roryabraham proposal -> https://expensify.slack.com/archives/C05LX9D6E07/p1770841028162209

I did figure out how to greatly reduce the overhead with SQLite for web, so I feel much more confident that we can use it if we want to. However, my benchmarks in that proposal were sadly very inflated, because the baseline was awaiting non-blocking disk I/O, which is not representative of how Onyx is used in production.

The trouble is that I have no way of accurately measuring the main-thread CPU time of the persistence layer, particularly of IndexedDB calls themselves, because it doesn't give us a way to add performance timings only for the CPU time and not for the non-blocking disk I/O time.

Unfortunately I have no major breakthrough in the investigation.

I tried to check whether this slowdown comes from workers or SQLLite.

For this I modified getBackend method in platforms/index.ts to return idb instead of SQLLite.

These are the results:

main branch (baseline)

IndexedDB

SQLLite

numbers show ManaulOpenReport for the same set of reports on the same account. As we can see, IndexedDB with workers is quite similar to baseline. SQLLite behaves poorly.

Additional test. I've added JSON.parse|stringify overhead to IndexedDB provider. This is to mimic SQLLite JSON overhead. The results are:

IndexedDB + JSON overhead

SQLite vs IndexedDB

Onyx is a pure KV store (no queries, joins, indexes). SQLite's SQL engine is dead weight here. Plus SQLite has no binary JS interchange — every value crosses the boundary as JSON text — and pays a ~500ms init cost vs IDB's ~46ms. Adding equivalent JSON work to IDB collapses SQLite's lead, confirming the gap is interchange, not engine speed. SQLite becomes the right call only the day Onyx grows into a queryable client database.

Ruled out as the regression source

Worker persistence latency, postMessage structured-clone, BroadcastChannel echo, WriteBuffer.merge CPU, rIC overhead, merge-induced read penalty, and OnyxCache invariants (verified: re-opens hit cache, zero storage reads).

What worked: tombstone batching

Storage.removeItem was bypassing the WriteBuffer — every delete became a separate worker round-trip, and single-row SQLite DELETE on OPFS pays ~150ms fsync (up to ~800ms tail). Worker FIFO queue then starved everything behind it.

Fix (Onyx de20c96f): added 'remove' entry type, coalesced into a batched multiRemove flush, served as null from buffer on reads.

Op	rtMax before → after
`multiSet`	507 → 55ms (9×)
`multiMerge`	587 → 184ms
removes	809 → 214ms

Opening a report is now close to main.

To be checked

Microtask getItem→multiGet coalescing — 20 concurrent reads serialize through the worker today; batching collapses ~140ms wait to ~15ms. Path to go faster than main.
Read priority lane in worker — eliminate residual ~200ms tail when reads queue behind big batched flushes.
OPFS warm-cache snapshot on boot,
JSON_PATCH provider-side merges to skip merge-precondition reads.

Memory leak investigation.

Potential cause: The branch's value-bearing cross-tab sync had no origin filter: every write the storage worker performed was broadcast on the onyx-sync BroadcastChannel, and the originating tab's own main thread received it. Each echo arrived as a fresh structured-clone of the payload and was pushed through cache.set and OnyxUtils.keyChanged to every subscriber, so the cache reference flipped to the clone while in-flight closures, useOnyx refs, and prior collection snapshots still pinned the original. Net effect: every cached value lived at ~2× retention in main heap.

For a short session performed on exfy-perf@callstack.com the numbers were:
Pre-fix branch held 19.15M nodes / 1,289 MB vs. main's 7.97M / 778 MB — a +511 MB / +11.2M-node regression. The blow-up was concentrated in payload-shaped types: strings 3.60×, arrays 3.73×, objects 2.32× of main (closures/code stayed flat at ~1.0×), exactly what doubled value graphs look like.

Fix and result: A per-tab originId is minted on the main thread, forwarded to the worker via the init message, stamped on every broadcast, and dropped on receive in InstanceSync when it matches the local id. Post-fix branch is 8.19M / 957 MB — string/array/object ratios collapsed to 0.82×/0.92×/1.08× of main, recovering −332 MB (~65% of the regression). The remaining +180 MB is almost entirely native (+150 MB) — SQLite-WASM linear memory, OPFS handles, and the worker bundle's compiled-code cache — architectural cost of SQLite-in-worker, not duplication.

Changes are pushed to the branch for review.

Here are some tests performed for simillar (short) session for exfy-perf@callstack.com:

Name		post-fix			baseline			pre-fix
	count	avg	P90	count	avg	P90	count	avg	P90
ManualAppStartup	1	930	930	1	1303	1303	1	1854	1854
ManualOpenReport	31	223	309	16	233	404	13	320	743
ManualNavigateToInboxTab	6	531	724	3	549	654	4	1380	1886
ManualNavigateToReports	5	743	971	7	205	720	4	625	1023
BootsplashVisible	1	873	873	1	1258	1258	1	1800	1800
BootsplashVisibleDeepLink	1	189	189	1	28	28	1	818	818
BootsplashVisibleHider	1	297	297	1	266	266	1	305	305
BootsplashVisibleLocale	1	271	271	1	109	109	1	872	872
BootsplashVisibleNavigation	1	367	367	1	954	954	1	350	350
BootsplashVisibleOnyx	1	192	192	1	29	29	1	1129	1129
BootsplashVisibleOnyxMigrations	1	179	179	1	22	22	1	796	796
BootsplashVisiblePublicRoomCheck	1	192	192	1	29	29	1	1129	1129

One step ahead, multiple steps backward.

While performing more performance tests I've noticed a pattern for ManualAppStartup

Each consecutive refresh increased app startup time.
This pattern is not visible for baseline:

No clue, yet. First thing was that there may be some memory leak, but memory tab does not show any difference.

Onyx Web Storage — Refresh-Growth Investigation Summary

Root cause

On the feature branch, each consecutive page refresh got slower (from ~2.5 s climbing to 10+ s over a session) because the storage Web Worker (wild guess, but the only clue we have) is torn down on every refresh while the next page immediately calls new Worker(...). The worker uses the opfs-sahpool SQLite VFS, which holds FileSystemSyncAccessHandle locks against OPFS files; worker.terminate() (or implicit page-unload teardown) doesn't release those handles synchronously

Taken steps

Instrumented every phase of init (new Worker → top-level → firstMessage → installOpfsSAHPoolVfs → openDb → schema → prepareStatements → initResponseReceived) and confirmed that ~95 % of the regression lives in the gap between new Worker(...) and the worker's top-level executing.
Added a synchronous disposeSync() to the SQLite provider that finalizes prepared statements, closes the DB, and calls poolUtil.pauseVfs() to release SAH handles synchronously; wired a dispose worker message and a main-thread page-lifecycle listener (pagehide / beforeunload / unload / visibilitychange→hidden, since pagehide didn't fire reliably in dev).
Attempted a SharedWorker migration (idempotent init, per-port originId tracking, single global queue) to eliminate worker spawn from the refresh path entirely.

Results

Neither approach actually fixed the underlying leak. The dispose path runs end-to-end as designed (dispose received, disposeSync completes in ~5 ms before the worker dies), but startup time still grew monotonically across consecutive refreshes — 40 → 85 → 215 ms over just three cycles, the same shape of curve as before, only with a flatter slope; over many refreshes it would still climb toward seconds.

The SharedWorker attempt failed outright because FileSystemSyncAccessHandle — required by installOpfsSAHPoolVfs — is only exposed in DedicatedWorkerGlobalScope; in SharedWorker context the sahpool threw Error: Missing required OPFS APIs, the worker silently fell back to in-memory SQLite, and the user appeared logged out (empty DB). The branch was reverted to the dedicated-Worker-with-dispose state; nothing was shipped.

Next steps

read in-depth documentation of workers to understand how these works

these are proposed by Claude

try navigator.locks next: have each worker hold a named lock for the lifetime of its sahpool, and have the next page's main thread await navigator.locks.request(lockName, ...) before constructing its worker, so the previous worker's SAH release is observable before the new one tries to install.

If that doesn't flatten the curve either, the alternatives get more invasive:

switch from sahpool to the async OPFS VFS (no SAH locks, ~2× per-op cost), or move SQLite behind a coordinator (Service Worker or BroadcastChannel-based lease) so the worker outlives the page. SharedWorker remains blocked until Chrome exposes FileSystemSyncAccessHandle in shared-worker scope.

Refresh ManualAppStartup duration creep was caused by Chrome Dev Tools. This is some browser problem with workers or WASM.

I've measured startup time without DevTools open and these are results:

AppStartup on Home:

Baseline: 1056 ms
Branch: 1073 ms (+1,6%)

AppStartup on Inbox:

baseline: 1071
branch: 1053 (-1,6%)

AppStartup on Spend:

baseline: 1125
branch: 1144 (-1,7%)

As we can see the results are comparable.

This are tests for navigation between Inbox nad Spend pages:

Nav to inbox:

Baseline: 522
Branch: 658 (+26%)

Nav to Spend:

Baseline: 171 ms
Branch: 171 ms (0%)

Navigation to Inbox is much slower than baseline.

Test results for ManualOpenReport:

Baseline: 253
Branch 274 (+8,2%)

@roryabraham

This is another test for ManualOpenReport. For these two accounts 100 reports were open and our branch performs similar to baseline (or is slightly worse). Previous tests were promising but it was probably test flakiness.

After a lot of time spent on this I don't think that pursuing SQL Lite via WASM is worth the effort. A lot of time was spent to achieve same results as already working IndexedDB and we still need to resolve a lot of bugs and errors.

cc: @roryabraham @fabioh8010

@sosek108

Hi @sosek108 , thanks for all the effort here digging into the solution!

I agree that it's best to pause these efforts for now as the benchmarks aren't showing us enough gains that could justify the migration to SQLite. We now however have good knowledge about the SQLite implementation for web and we can revisit this again in case we have opportunities that could bring us big gains with SQLite.

Navigation Menu

Repository navigation

[Onyx Audit] Investigate SQLite for Web #80245