Compile to JavaScript?

[KyleAMathews]

I'm curious if anyone has tried compiling SQLite to JavaScript instead of wasm? It seems much of the cost of calls is traversing the js/wasm bridge so if SQLite was just js, it might be faster.

[tantaman]

I'm sure it isn't as simple as setting the WASM flag to 0 but: emscripten-core/emscripten#18013

[abhay-agarwal]

I'm curious if anyone has tried compiling SQLite to JavaScript instead of wasm? It seems much of the cost of calls is traversing the js/wasm bridge so if SQLite was just js, it might be faster.

An alternative is to rewrite the VFS in wasm so that someone could ostensibly stay inside of wasm for direct database calls, if they were willing to write their own side in wasm. The persistence aspect would still cross the boundary but since persistence is asynchronous the more noticeable aspect of speed is crossing the boundary during userspace calls to the library.

[sgbeal]

but since persistence is asynchronous the more noticeable aspect of speed is crossing the boundary during userspace calls to the library.

FWIW, within the sqlite project, the single biggest performance hit for a JS-side VFS is not entering and leaving WASM but is in the coordination of making the async storage behave synchronously for the sqlite3_vfs API. We could more than double the performance, very conservatively speaking, if the OPFS storage API was fully synchronous. (We know this for reasons too verbose to go into on this mobile device's keyboard.)

[samwillis]

There's a bunch of interesting problems with trying to make all these local databases faster, and there will be no way to beat a main thread JS native database. But I agree with @abhay-agarwal, the entire point of all of this to remove the network from the interaction loop, saving a few tens of ms is blown out of the water by just the fact we have the data locally.

I'm obviously all in on the unified frontend+backend database idea, but I'm still super excited to see what gets built in the way of JS native databases for when you truly do only have a 16ms frame budget.

[AntonOfTheWoods]

As an academic exercise it would be cool if someone could dig out a version of chrome that still has usable websql and bench a few things to see how that compares. Hardly very scientific given it's obviously not able to compare apples to apples but interesting nonetheless. My understanding was that it was basically SQLite integrated without all the hassles being mentioned here. Is that more or less the case?

[sgbeal]

As an academic exercise it would be cool if someone could dig out a version of chrome that still has usable websql and bench a few things to see how that compares.

We did some of that during the initial development of the sqlite/wasm project in mid/late 2022, but i don't have any numbers readily available. My vague recollection was that wasm was a tick higher than 2x websql on average, but that was very workload-dependent, and it slowed down for reasons opaque to us in later browser updates. The comparison is really apples and oranges, though, because websql's usage is (IMHO) so constrained (e.g. no control over transactions and a limited subset of SQL functions). We had to create a highly constrained set of test inputs to get tests which would run on both websql and plain sqlite.

[tantaman]

I'll record a talk at some point (there's a lot of background to get through) but the current set of databases are really designed for last generation's problems. SQLite included. They follow a request-response cycle rather than being streaming.

If you think of an app, most of its queries are static and it is the data that is changing. Today's DBs (SQLite, Postgres, MySQL) are built with the assumption that data is relatively static and queries are what changes. This is backwards and leaves a ton of potential on the table. I.e., apps need to be able to subscribe to queries rather than run queries.

Some people are catching on. E.g., https://materialize.com/, https://deephaven.io/, https://readyset.io/, https://www.feldera.com/
but very few in the embedded space have caught on.

With the inversion of the design of the DB you can get 1000x speedups and literally use your DB as if it were memory. No more need for some object model atop your DB. You can code directly to relations.

https://martin.kleppmann.com/2015/03/04/turning-the-database-inside-out.html is a good intro to these sorts of ideas.

[tantaman]

Benchmarks are sort of useless without context but this is getting exciting

200x speedup for the reactive case. Hope to have something public in another few months.

[samwillis]

I completely agree with @sgbeal, we are all holding are breath and hoping the browser gods will give us fully sync OPFS apis in workers 🙏

On @KyleAMathews nudge I'm reposting my comment from our discord below.

--

https://discord.com/channels/933657521581858818/1212676471588520006/1222822993034477668

Hey, yep, I've had exactly the same thoughts. A "JS native" sql database would be super interesting as all types could be maintained in their native format in memory. No marshalling or serialising needed.
Back before WASM, Emscripten would compile to something called esm.js, that was pure JS (sort of), however it was still allocating a large array buffer to simulate the memory system C would expect. You still had to convert all JS types to C types across a boundary.
To solve that a C to JS source-to-source compiler would need to infer what JS types it could use to represent C types for each specific use, then trace these through the program and use the appropriate JS methods on them. Then there is going to be slight difference in how those types behave. I think it's a very difficult problem to solve in a general way.
That being said, an application specific source-to-source compiler, one tailored to a specific code base with knowledge of how it works could be possible. It could be provided with a definition of how each struct type should be translated.

However, I think the main performance bottle necks in both SQLite and PGlite are not in the marshalling/serialising. The keys ones to look at are:

• Caching prepared statements so that the SQL has to only be parsed once - very close to this one in PGlite, already possible in SQLite.
• Removing the need for Asyncify (this gave us a 2-3x speed boost)
• Better synchronous persistence options (one day the browsers will realise that offering a bunch of fully sync APIs in web workers is the solution, currently OPFS is partially sync, but opening a file is still async 😢 )
• Caching rows that have been deserialised on the JS side, and invalidating that cache when they are changed. Would make repeated queries significantly faster, we could just pass over its ID and no other data.
• Some sort of incremental or streaming queries as the results change, no need for re-querying.

[sgbeal]

... we are all holding are breath and hoping the browser gods will give us fully sync OPFS apis in workers

Please don't hold it for too long :/. FWIW, i literally begged the Chrome storage team (in meetings, as opposed to emails) for this during the development of sqlite's initial OPFS VFS, but they wouldn't budge. My argument has always been that async APIs exist to account for high latency, but local storage, at the browser-class-application level, simply does not have any appreciable latency, ergo an async API for OPFS is not only completely unnecessary but is a huge kick in the genitals for folks trying to integrate OPFS with synchronous APIs. i had this discussion with them several times, but they were insistent that the Async All The Things approach was necessary. They did eventually make all of the SyncAccessHandle ops synchronous except (curiously) for opening them, despite my repeated moaning that so long as one method is async, we cannot integrate that API directly with 100% synchronous APIs.

Sigh. #FirstWorldProblems and all that.

However, I think the main performance bottle necks in both SQLite and PGlite are not in the marshalling/serialising. The keys ones to look at are:

In the sqlite project's impl the biggest bottleneck, by far, is at the threading-related call boundaries, namely Atomics.notify() and Atomics.wait(). That's where that impl spends most of its time twiddling its proverbial thumbs. We wouldn't need this if OPFS gave us a fully synchronous API.

PS: we know approximately what the Atomics-related performance hit is because our second OPFS VFS, based entirely on Roy's work, pre-allocates a fixed pool of SharedAccessHandles before installing the VFS, and gives sqlite access only to those handles. That eliminates the need for the Atomics APIs and improves performance for most workloads by an average of roughly 3x. Both VFSes do the same data marshalling. Each has distinct trade-offs and features, though, so one does not entirely obviate the other. If we had a 100% synchronous OPFS API, we could very likely obviate both with a third VFS and get the best of both worlds.

Sigh. #FirstWorldProblems and all that.

Anyway: to bring this all back to the OP's point: WASM in browsers is, in my experience with the sqlite project, stupendously fast, and neither crossing the JS/WASM boundary nor marshaling data between the two has proven to be any appreciable bottleneck in our project (despite our initial concerns to the contrary).

[abhay-agarwal]

Hi @samwillis! excited about this pglite project you have going on.

But when are any of these db in the browser projects going to give me my Rust hooks 😭. If nothing else, the devex of interacting with the sqlite c api and doing all the caching of prepared statements would be so much nicer if I could just have it in 🦀.