SQLite 3.40 to support browser API, including OPFS

[rhashimoto]

In case you didn't see it in the OPFS discussion, the next SQLite non-patch release will contain a browser implementation, and that will include an Origin Private File System VFS for persistent storage. That's great news for everyone!

Assuming this new API works well, what does that mean for wa-sqlite? If you want to use wa-sqlite solely for a persistent SQL database (and not, say, to write your own storage extensions), then the answer depends on whether you prefer the limitations of Asyncify to the limitations of SharedArrayBuffer and OPFS.

There are currently two implementation approaches for connecting SQLite's synchronous file system operations to a browser's asynchronous storage: Asyncify (used by wa-sqlite), and SharedArrayBuffer (used by the official SQLite OPFS VFS). Choosing Asyncify makes the WASM bigger and slower, while choosing SharedArrayBuffer adds requirements on where and how web assets are served and OPFS is not yet supported on Firefox (though the tracking bug activity looks very promising).

If you can live with SharedArrayBuffer's COOP/COEP restrictions and OPFS browser support, I would recommend the official SQLite implementation for its official support, complete API, legendary test coverage, and permissive license (actually it doesn't require a license at all). Otherwise, if COOP/COEP is burdensome, you can't wait for Firefox, or you have custom storage ideas (including for virtual tables), wa-sqlite will still be here.

I am curious to see how the official OPFS VFS performance will compare to the wa-sqlite IDBBatchAtomic VFS. In my comparisons of OPFS vs IndexedDB (both in wa-sqlite), the ability to use batch atomic writes made transactions significantly faster. That might be enough to make up for (or exceed) the Asyncify performance penalty, depending on the workload of course. I would love to see someone create a batch atomic IndexedDB VFS using SharedArrayBuffer - that might be the fastest of all.

[sgbeal]

Otherwise, if COOP/COEP is burdensome, you can't wait for Firefox, or you have custom storage ideas (including virtual tables), wa-sqlite will still be here.

(Speaking as the developer of the new sqlite subproject...)

COOP/COEP is a huge nuisance but we currently have no alternative for OPFS except using Asyncify. The sqlite project is made up of long-time C programmers, none of whom are big fans of huge JS build tools. We'd prefer to "keep it simple," and all of the current code, minus the glue code from Emscripten, is hand-written by us with no magic involved. We're not keen on using magic tools like Asyncify because, essentially, when they break there is no fixing them. We don't want to tie ourselves to that, and have made every effort to avoid dependencies where at all possible (noting that certain Emscripten dependencies are, at the lowest levels, unavoidable).

For those with more modest persistence needs, localStorage and sessionStorage can be used without COOP/COEP, but are limited to main-thread use.

I am curious to see how the official OPFS VFS performance will compare to the wa-sqlite IDBBatchAtomic VFS.

Our benchmarks were unfortunately not well-documented. Until just recently we benchmarked continually against the Emscripten WASMFS's OPFS support, and finally (with much tuning) got ours to a competitive point with that one, more or less neck and neck with no consistent winner in terms of overall performance. Their impl. also requires SAB/Atomics. We also compared, though less intensively, against WebSQL, again with no clear all-around winner - it really depends on the workload.

One of my mid-term goals is to create a benchmarking page very much like the one you've done for your various backends. Until then, we've used sqlite's age-old benchmarking app, speedtest1, for most benchmarking and are quite pleased with the overall performance.

[rhashimoto]

@sgbeal In your discussion of absurd-sql at the bottom of this page, you mention that you experimented with IndexedDB but found it slow. That's the opposite of my experience, based mainly on Chrome, that OPFS is slower than IndexedDB (some musings on this are here). What kind of comparisons did you make?

[sgbeal]

you mention that you experimented with IndexedDB but found it slow. ... What kind of comparisons did you make?

We did only very basic testing with indexeddb, storing a whole sqlite databases inside an IDB record (as opposed to one db page per record, which may have been more sensible but we weren't yet far enough along at the time, in terms of sqlite3_vfs<==>JS communication, to do that). It was surprisingly performant for tiny/trivial dbs, but we're looking to be relevant for "all (reasonable) sizes" of databases, and that approach to misusing IDB was not going to give us that. More significantly, it was really easy to get it into an inconsistent state via access from two tabs, and that alone rules it out as something we want to target. We didn't manage to outright corrupt a db, but we could, via very simple hand-driven tests (as opposed to automated stress-testing) get it into states where tab X would write data very close in time to when tab Y wrote completely different data, and tab X's data would simply disappear. That by itself ruled out IDB as a target for us.

We might have better results with IDB if we'd go back and implement an sqlite3_vfs which wrote one db page per IDB entry, but IDB is not a target we're all that interested in pursuing. However, our targets are always subject to change based on user feedback, so very little can be outright ruled out at this point.

The OPFS API has been fast in our tests: the read/write operations have been in the handful-of-microseconds ranges on my mid-range i3-based NUC desktop computer. Of all time time spent marshaling stuff into and out of the sqlite3_vfs API, OPFS itself took up (the last time i measured it, a month or so ago) only a single-digit percentage of it. We don't have an automated way to calculate that handy or i'd run some numbers for you, but creating such tools is certainly on our TODO list. A cross-country move will keep me mostly preoccupied the next several months, though, so nice-to-haves like that are currently down at the bottom of the priority list. Some 30-odd to 40-odd percent of our OPFS time is just waiting on the thread communication boundaries, which really sucks. OPFS should not be async, period, but that's a battle i've long-since lost with the Chrome folks.

We've always tested with a recent dev-channel version of Chrome and have seen significant speed improvements on their end since we our first OPFS results in June or July (with WASMFS - we didn't have our own sqlite3_vfs until September). We've made no effort whatsoever to ensure any sort of compatibility with any versions older than the latest dev versions. Once we're "out the door" we'll switch gears to remain compatible with whatever versions users will find in the wild, insofar as feasible.

Speed tip: we're getting better OPFS performance with journal_mode=truncate than journal_mode=wal and journal_mode=delete. Though i don't have precise numbers, we experimented with those three one day spontaneously and truncate was the clear winner. The single biggest speed boosts we got with OPFS were by increasing its default max page cache size to 8-16mb by compiling with -DSQLITE_DEFAULT_CACHE_SIZE=-8192 or -16384 (negative numbers mean kilobytes, positive mean db page counts).

In parting, here's some output from the wasm build of speedtest1 (our benchmarking app) using the OPFS sqlite3_vfs for a workload which we consider to be "larger than web-sized":

speedtest1.wasm --big-transactions --singlethread --size 50 --vfs opfs /speedtest1.sqlite3
-- Speedtest1 for SQLite 3.40.0 2022-11-04 09:02:21 479ad980dfe509403e184e39a5aa
 100 - 25000 INSERTs into table with no index......................    0.048s
 110 - 25000 ordered INSERTS with one index/PK.....................    0.066s
 120 - 25000 unordered INSERTS with one index/PK...................    0.084s
 130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.039s
 140 - 10 SELECTS, LIKE, unindexed.................................    0.104s
 142 - 10 SELECTS w/ORDER BY, unindexed............................    0.146s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    0.083s
 150 - CREATE INDEX five times.....................................    0.132s
 160 - 5000 SELECTS, numeric BETWEEN, indexed......................    0.040s
 161 - 5000 SELECTS, numeric BETWEEN, PK...........................    0.040s
 170 - 5000 SELECTS, text BETWEEN, indexed.........................    0.076s
 180 - 25000 INSERTS with three indexes............................    0.134s
 190 - DELETE and REFILL one table.................................    0.129s
 200 - VACUUM......................................................    0.346s
 210 - ALTER TABLE ADD COLUMN, and query...........................    0.011s
 230 - 5000 UPDATES, numeric BETWEEN, indexed......................    0.078s
 240 - 25000 UPDATES of individual rows............................    0.094s
 250 - One big UPDATE of the whole 25000-row table.................    0.059s
 260 - Query added column after filling............................    0.004s
 270 - 5000 DELETEs, numeric BETWEEN, indexed......................    0.147s
 280 - 25000 DELETEs of individual rows............................    0.111s
 290 - Refill two 25000-row tables using REPLACE...................    0.352s
 300 - Refill a 25000-row table using (b&1)==(a&1).................    0.168s
 310 - 5000 four-ways joins........................................    0.128s
 320 - subquery in result set......................................    0.171s
 400 - 35000 REPLACE ops on an IPK.................................    0.088s
 410 - 35000 SELECTS on an IPK.....................................    0.029s
 500 - 35000 REPLACE on TEXT PK....................................    0.082s
 510 - 35000 SELECTS on a TEXT PK..................................    0.058s
 520 - 35000 SELECT DISTINCT.......................................    0.057s
 980 - PRAGMA integrity_check......................................    0.164s
 990 - ANALYZE.....................................................    0.036s
       TOTAL.......................................................    3.304s

Our generic (and rather arbitrary) baseline workload is half that size:

speedtest1.wasm --big-transactions --singlethread --size 25 --vfs opfs /speedtest1.sqlite3
-- Speedtest1 for SQLite 3.40.0 2022-11-04 09:02:21 479ad980dfe509403e184e39a5aa
 100 - 12500 INSERTs into table with no index......................    0.024s
 110 - 12500 ordered INSERTS with one index/PK.....................    0.033s
 120 - 12500 unordered INSERTS with one index/PK...................    0.039s
 130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.020s
 140 - 10 SELECTS, LIKE, unindexed.................................    0.051s
 142 - 10 SELECTS w/ORDER BY, unindexed............................    0.066s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    0.042s
 150 - CREATE INDEX five times.....................................    0.063s
 160 - 2500 SELECTS, numeric BETWEEN, indexed......................    0.017s
 161 - 2500 SELECTS, numeric BETWEEN, PK...........................    0.018s
 170 - 2500 SELECTS, text BETWEEN, indexed.........................    0.036s
 180 - 12500 INSERTS with three indexes............................    0.062s
 190 - DELETE and REFILL one table.................................    0.064s
 200 - VACUUM......................................................    0.183s
 210 - ALTER TABLE ADD COLUMN, and query...........................    0.011s
 230 - 2500 UPDATES, numeric BETWEEN, indexed......................    0.037s
 240 - 12500 UPDATES of individual rows............................    0.042s
 250 - One big UPDATE of the whole 12500-row table.................    0.032s
 260 - Query added column after filling............................    0.003s
 270 - 2500 DELETEs, numeric BETWEEN, indexed......................    0.071s
 280 - 12500 DELETEs of individual rows............................    0.058s
 290 - Refill two 12500-row tables using REPLACE...................    0.180s
 300 - Refill a 12500-row table using (b&1)==(a&1).................    0.092s
 310 - 2500 four-ways joins........................................    0.060s
 320 - subquery in result set......................................    0.066s
 400 - 17500 REPLACE ops on an IPK.................................    0.045s
 410 - 17500 SELECTS on an IPK.....................................    0.015s
 500 - 17500 REPLACE on TEXT PK....................................    0.044s
 510 - 17500 SELECTS on a TEXT PK..................................    0.029s
 520 - 17500 SELECT DISTINCT.......................................    0.027s
 980 - PRAGMA integrity_check......................................    0.080s
 990 - ANALYZE.....................................................    0.021s
       TOTAL.......................................................    1.631s

Those are running in Chrome v109 on a 3.6ghz i3-based intel NUC computer.

[rhashimoto]

More significantly, it was really easy to get [IndexedDB] into an inconsistent state via access from two tabs, and that alone rules it out as something we want to target. We didn't manage to outright corrupt a db, but we could, via very simple hand-driven tests (as opposed to automated stress-testing) get it into states where tab X would write data very close in time to when tab Y wrote completely different data, and tab X's data would simply disappear.

I have not seen this, and no one has (yet) reported corruption with wa-sqlite IndexedDB classes (and people are using it with multiple tabs). Assuming your code is correct, this would indicate your browser did not properly implement the IndexedDB spec. How were you handling locking? If you were relying on IndexedDB's locks then I can understand how that could happen because I have made my own mistakes with them. I implement SQLite locking (i.e. xLock and xUnlock) with the WebLocks API.

For example, absurd-sql depends on IndexedDB locks for SQLite locking, but its implementation has at least one problem in that it doesn't match SQLite's locking model which might explain why it also has corruption issues with multiple tabs.

I'm not saying that you should be using IndexedDB over OPFS - IndexedDB can be a pain. I just think your reason to rule it out completely seems like a bug, either in your code or your browser, which could be fixed.

[sgbeal]

Assuming your code is correct,

A big assumption :). It was literally our first-ever, and only-ever, IDB code. We weren't terribly excited about using it, partly because...

this would indicate your browser did not properly implement the IndexedDB spec.

it has a poor reputation for cross-browser incompatibilities. We didn't do more than very cursory toying with it. Spending time working around browser-specific issues, especially for browsers i won't use for reasons of principle (but whose users are always the first to cry "it doesn't work on my iDevice!"), is not a way i intend to spend my coding time nowadays.

I implement SQLite locking (i.e. xLock and xUnlock) with the WebLocks API.

We didn't, IIRC, do any explicit locking, but i don't think that experiment ever even got checked in, so i can't go verify that.

WebLocks are still an experimental tech, which takes it off of our radar completely. We're far more interested in using techs which are currently widely deployed and cross-browser. OPFS has been the one exception to that guiding principle.

I just think your reason to rule it out completely seems like a bug, either in your code or your browser, which could be fixed.

That's a fair assessment, but we were never excited about IDB in the first place. Our whole wasm effort was literally triggered by one single catalyst: OPFS. Any other browser-side persistence we manage to get is just bonus points. We're not actively looking to extend our persistence catalog, nor to spend any development effort on IDB support. The idea of storing an sqlite db in an IDB which itself is very likely backed by sqlite kinda makes me queasy.

[rhashimoto]

All certainly your call.

FWIW WebLocks is widely supported now, basically everywhere except legacy browsers. And Chromium implements IndexedDB with LevelDB so chances are more than 2 out of every 3 browsers aren't using SQLite there.

[birtles]

I've spent the last few days making some performance comparisons to weigh up the benefit of converting an existing application from IndexedDB to WASM SQLite. The application is a dictionary tool that downloads all the data on startup (100,000s of writes) and then needs to do lookups on words, often prefix lookups. In particular, I'd really like to improve the initial insert performance.

I have the comparison here: https://wasm-sqlite-test.netlify.app/ (source code)

In summary, I found that both insert and query performance improved with the official SQLite WASM build using the OPFS SyncAccessHandle VFS, but the insert performance improvement was much better when using wa-sqlite with IDBBatchAtomicVFS.

On Chrome the following results are pretty typical for me (and Firefox and Safari are similar):

I'd prefer to stick with the official SQLite WASM build but wa-sqlite with IDBBatchAtomicVFS is often 2~3 times faster on inserts and also has the advantage of working on more browsers and in more contexts.

That said, I'm completely new to SQLite so it's quite possible I'm just holding it wrong!

[birtles]

That's interesting. In my case, I'm just stuffing the JSON for each record into SQLite as a blob (since ultimately I want it back in that format) so the SQLite file necessarily ends up being bigger. I did a quick measurement on a subset of the data, however, and the difference wasn't as big as I feared:

Format	Uncompressed size	Brotli compressed size
JSONL	5.2M	850k
SQLite (indices removed)	7.6M (+46%)	1.2M (+41%)

[AntonOfTheWoods]

Not sure what the added benefit is of SQLite if you are starting and ending in JS, and aren't even going to search on it. What are you gaining over native IDB (dexie or similar)?

[birtles]

Oh we search it. The search happens using a separate index table (since it's a multi-keyed) built from the JSON and in some cases we will use full-text search too. SQLite gives much better query performance over IDB and FTS tokenization and is also better than the manual tokenization I've been doing in IDB.

[rhashimoto]

In summary, I found that both insert and query performance improved with the official SQLite WASM build using the OPFS SyncAccessHandle VFS, but the insert performance improvement was much better when using wa-sqlite with IDBBatchAtomicVFS.

Cool, real numbers are always interesting.

When I search for "durability" in your project, I don't get any application code hits so I assume you aren't using relaxed durability. If your application can tolerate it, and it sounds like perhaps it can, using the option durability: "relaxed" for your raw IndexedDB and IDBBatchAtomic implementations might significantly reduce your per-transaction cost.

Of course, if that does make a difference then all your implementations could benefit by reducing the number of your transactions, and then you might see OPFS overtake IDB for INSERT as well (OPFS inherently has higher write transaction overhead). I see that you're already batching inserts in groups of 2000, but if I'm understanding your initial post you have 100,000+ INSERTs and that would still be quite a few transactions.

@AntonOfTheWoods has done much more experimentation with direct VFS import than anyone I know of (including me), and I agree that should be both (1) faster and (2) trickier for loading a database than INSERTs. Figuring out why that is so temperamental with IDBBatchAtomicVFS is on my TODO list but I'm not sure when I'll get to it.

[birtles]

Wow, thank you so much for the incredibly valuable advice.

When I search for "durability" in your project, I don't get any application code hits so I assume you aren't using relaxed durability. If your application can tolerate it, and it sounds like perhaps it can, using the option durability: "relaxed" for your raw IndexedDB and IDBBatchAtomic implementations might significantly reduce your per-transaction cost.

I didn't know about that flag. Most of my users are on Firefox which I believe defaults to durability: "relaxed" so I don't think it will make a difference there, but I'll be sure to use it for Chrome users. I've added it to my performance comparison already.

Of course, if that does make a difference then all your implementations could benefit by reducing the number of your transactions, and then you might see OPFS overtake IDB for INSERT as well (OPFS inherently has higher write transaction overhead). I see that you're already batching inserts in groups of 2000, but if I'm understanding your initial post you have 100,000+ INSERTs and that would still be quite a few transactions.

That's a very good point. Initially the 2,000 record batch size was in order to provide more granular progress reporting but maybe it's time I revisited that. The explanation about why OPFS has higher write transaction also makes a lot of sense. Thank you.

I've also realized that, unlike for IDB, Firefox doesn't support OPFS in private browsing yet and a number of my users tend to rely on that (e.g. enabling Firefox's always-on private browsing) so I might only be able to use OPFS for new projects for the foreseeable future. (There is a bug that disabled OPFS in private browsing but none to enable it yet, as best I can find.)

@AntonOfTheWoods has done much more experimentation with direct VFS import than anyone I know of (including me), and I agree that should be both (1) faster and (2) trickier for loading a database than INSERTs. Figuring out why that is so temperamental with IDBBatchAtomicVFS is on my TODO list but I'm not sure when I'll get to it.

Thank you for those links. That's fascinating work. I think I might not pursue that approach after all because I'm not sure how to make it resumable. For users on intermittent connections, downloading a > 100Mb SQLite database in one hit could be problematic.

[AntonOfTheWoods]

For users on intermittent connections, downloading a > 100Mb SQLite database in one hit could be problematic.

For many of mine, simply impossible. So I chop the 200MB+ file into 10MB blocks and then put them together on the other side. I don't currently have storage for the blocks so you can resume downloading in a separate session, but I did when I was using JSON. With IDB you can't have any await between your block writes, so need to think about that and have everything in memory. Otherwise, it works fine.

That said, for those users, insert time is probably going to be an order of magnitude less important than download time anyway... But there is nothing stopping you from first downloading all the blocks, then importing in one go.

[birtles]

Ah, interesting. For the current JSON setup, I download the data in chunks, insert the chunk data as a transaction and update the current chunk number in the database at the same time. As a result, you can get partial results while the data is still downloading. I'm not sure that's particularly useful, however.

As I understand it, for downloading a SQLite database file in blocks, you'd need to download all the blocks first and re-assemble them before importing the file.

I think in my particular case where users can choose which tables they want to download (but where I ultimately want them to exist in the same database file for simplicity) the binary download might not fit.

[AntonOfTheWoods]

I have a base .db file I regenerate once a day in python, which I copy and fill with user-specific stuff when a user installs on a new device. After getting the final .db, I then split and send in chunks (or rather I send back a list of links to the chunks). You could definitely do something similar, having tables or not. It might not be the best choice for you though (and might not be for me either, but hey! :-))

[birtles]

That's very clever and might be worth trying in future. Thank you!

[rhashimoto]

I chop the 200MB+ file into 10MB blocks and then put them together on the other side.

If your server supports it, you could instead use HTTP range requests on the entire file. You might want it to also support on-the-fly compression, though.

As I understand it, for downloading a SQLite database file in blocks, you'd need to download all the blocks first and re-assemble them before importing the file.

You probably wouldn't want to start using the database until it was completely in the file system, but you could still overlap downloading and writing to storage.

If you wanted to import into IDBBatchAtomicVFS, since you're already familiar with the IndexedDB API, perhaps the easiest (and less tricky) approach for you would be to open the underlying IndexedDB database directly and write pages using raw IndexedDB. That should be fairly straightforward and would sidestep issues with calling the VFS. The basic idea for how pages are stored is described here, and I think if you look at some actual IndexedDB objects in browser dev tools it should fill in most of the details.

[AntonOfTheWoods]

You probably wouldn't want to start using the database until it was completely in the file system, but you could still overlap downloading and writing to storage.

With the IDB VFS I definitely got corruption when I did this (there definitely being await between writes). No problem at all with the sync VFS though.