Meta VFS for adding storage options

[rhashimoto]

UPDATE: Code links in this comment are stale. Check the thread for the latest info.

I have an experimental sql.js branch with changes that enable defining a SQLite VFS in Javascript. It's a platform for creating a VFS, so I've been calling it a meta-VFS. The idea is to enable more storage options in the browser, beyond the current in-memory filesystem, as has been suggested a number of times, e.g. #302.

The branch doesn't define any VFS itself, but in a separate repo I have examples of creating an in-memory VFS and an IndexedDB VFS (using it as a block device) with only Javascript. Neither is a huge amount of code, less than 350 lines for IndexedDB and no function over a page, so I think not difficult to understand.

None of this is production-ready code (mainly because of lack of testing), and the branch is not currently suitable for a pull request. But I wanted to start a discussion on whether adding something like this makes sense, and if it does how to incorporate it.

I know that the maintainers have limited time and adding multiple storage options to the code base isn't going to help with that. So my idea is to provide only the capability for storage extensions, i.e. just the meta-VFS, to allow developers to create the extensions themselves outside the scope of the project.

There are some drawbacks to this idea, among them:

• Some browser storage options have only asynchronous APIs (e.g. IndexedDB), which require using Asyncify and its signficant space and time costs. It is likely that both Asyncify and non-Asyncify builds would be needed, so at least one more build than provided now.
• SQLite is sort of a worst case for Asyncify. The .wasm file is nearly twice the size and the execution cost is said to be ~5x (plus any cost for slower storage). This may not be acceptable for many applications, and so may not be worth the effort to provide it. The future might bring native coroutines/continuations to WebAssembly, which we could just wait for.
• The current sql.js application API is synchronous. An asynchronous application API is needed to use asynchronous storage. This could be a substantial effort all by itself (and one I would rather leave to someone else).
• Splitting responsibility for a platform and its extensions can lead to support issues, like finger pointing when problems occur.

Any thoughts, especially from maintainers?

(As an aside the above mentioned branch uses Emscripten modularization to build ES6 modules, so perhaps of interest for #284 and #438.)

[phiresky]

Can you say how you think using the SQLite VFS Api compares to using the Emscripten filesystem api?

I ask because I just built a VFS that fetches chunks of the database using HTTP range requests using just the emscripten filesystem api and it works fine with only a few changes to sql.js: 837d25d

The emscripten FS api is very similar to the sqlite one, it's just one level lower in providing a POSIX api for sqlite instead of the sqlite-specific api.

demo: https://phiresky.github.io/youtube-sponsorship-stats/?uploader=Adam+Ragusea

It seems like Emscripten also provides an IndexedDB file storage backend that could maybe be used in a similar fashion? https://emscripten.org/docs/api_reference/Filesystem-API.html#filesystem-api-idbfs

[rhashimoto]

@phiresky I haven't written an Emscripten FS, so this is based on my understanding of it.

As you note, the SQLite VFS interface is higher level than the Emscripten FS interface, so I think it's a little easier to understand and implement. A more fundamental difference, however, is there is no way I know of to use an Emscripten FS to bridge synchronous calls to an asynchronous API, whereas SQLite VFS can use Asyncify.

I assume that to satisfy this synchronous requirement you use synchronous XMLHttpRequest, is that right? Sync XHR can only be used in a Worker. That's not a horrible restriction as SQLite should probably only be deployed for production in a Worker anyway, but it is an extra step for a developer who is just experimenting.

It's great that you can use sync XHR; that's fine for HTTP requests. One might think that we could use sync XHR as a bridge to any asynchronous API by installing a Service Worker. That would be wonderful, but unfortunately at least Chrome and Safari don't support this.

Emscripten does provide IDBFS, but because of the synchronous restriction, it is actually MEMFS with manual two-way mirroring to IndexedDB. Or viewed another way, it's IndexedDB with everything cached in memory. So it won't support a filesystem larger than available memory, even if no files are actively being used. In addition, I believe that the current implementation of IDBFS stores the entire file as an object, so sync will slow down as the files grow even if the changes are small (this behavior could be changed with a reimplementation).

I don't believe one can implement a blocking lock with an Emscripten FS (other than with sync XHR to an external server). That means that if you want to support concurrent access to an IDBFS database, e.g. a user has two tabs open on your page, you have to arbitrate above the filesystem level. You can hack together a locking scheme at the library level, but how do you know whether you need a shared read lock or an exclusive write lock? It's a lot easier and more elegant to handle locks exactly when SQLite tells you it needs them and why.

My toy IndexedDB VFS will support a database up to the browser storage limit, which could be larger than available memory. It doesn't support locking either but that's a design choice - the framework itself doesn't exclude it. I think adding support for concurrent access from same-origin contexts is quite doable.

Many applications can live within the constraints of Emscripten FS - that is not in dispute - but extending via the SQLite VFS opens potentially useful possibilities outside those constraints. If you want to mount over a WebSocket or encrypt with WebCrypto, for example, I don't know how you do that with Emscripten FS. I also think that the SQLite VFS is conceptually simpler, and so is more accessible (e.g. easier to read existing implementations) because it is higher level and only includes what SQLite uses.

[phiresky]

Thanks for the detailed response. By the way, did you try using Atomics.wait() with a separate worker for communication instead of asyncify?

[rhashimoto]

By the way, did you try using Atomics.wait() with a separate worker for communication instead of asyncify?

I have not tried Atomics at all. I generally try to avoid features not supported across the evergreen browsers, but because you mentioned it I see that the holdout Safari has it in preview behind a flag so hopefully that will be coming soon.

It seems like there might be a nice development path in initially creating a VFS with Asyncify, debugging and testing in a single context, then moving the VFS class to a separate worker and replacing it with a proxy that no longer needs Asyncify (or never moving it if performance isn't an issue).

[phiresky]

I just tried it and using Atomics for async stuff (without asyncify) works!

In my case I'm trying to write a virtual table to access the DOM right now, which has to be async since my SQLite is running in a Worker but the DOM is only accessible in the main thread.

Here's some (shitty) code:

sqlite.worker.ts

// sends a request to the main thread via postMessage,
// then synchronously waits for the result via a SharedArrayBuffer
function doAsyncRequestToMainThread(request: MainThreadRequest) {
  // todo: dynamically adjust this for response size
  const sab = new SharedArrayBuffer(1024 * 1024);
  // first element is for atomic synchronisation, second element is the length of the response
  const metaArray = new Int32Array(sab, 0, 2);
  metaArray[0] = 1;
  // send message to main thread
  self.postMessage({
    action: "eval",
    notify: sab,
    request,
  });
  Atomics.wait(metaArray, 0, 1); // wait until first element is not =1
  const dataLength = metaArray[1];
  console.log("got response of length", dataLength);
  // needs to be copied because textdecoder and encoder is not supported on sharedarraybuffers (for now)
  const dataArray = new Uint8Array(sab, 2 * 4, dataLength).slice();
  const res = new TextDecoder().decode(dataArray);
  return JSON.parse(res);
}

main.ts

worker.addEventListener("message", async (e) => {
if (e.data && e.data.action === "eval") {
    const metaArray = new Int32Array(e.data.notify, 0, 2);
    const dataArray = new Uint8Array(e.data.notify, 2 * 4);
    const response = JSON.stringify(await handleRequest(e.data.request));
    const text = new TextEncoder().encode(response);
    dataArray.set(text, 0); // need to copy here because:
    // sadly TextEncoder.encodeInto: Argument 2 can't be a SharedArrayBuffer or an ArrayBufferView backed by a SharedArrayBuffer [AllowShared]
    // otherwise this would be better:
    /*const encodeRes = new TextEncoder().encodeInto(response, data);
    if (encodeRes.read !== response.length) {
    console.log(encodeRes, response.length)
    throw Error(`not enough space for response: ${response.length} > ${data.byteLength}`);
    }*/
    metaArray[1] = text.length;
    Atomics.notify(metaArray, 0);
}
});

There's some restrictions:

1. SharedArrayBuffer only works on https and with some weird headers set
2. kinda hard to implement robustly
3. needs two threads, e.g. main thread + worker thread

Now I can do this:

So useful 🤣

[lovasoa]

Hello !
First, thank you all for the great work ! I wasn't up to date on SharedArrayBuffers and Atomics, and your last message blew my mind. I didn't think there was a way to synchronously wait for an asynchronous event. This opens up so many possibilities.

@rhashimoto I am willing to merge a pull request with your VFS interface once it's fully developed, tested, and documented. And I do agree that the interface and its various implementations for different storage backends should be maintained separately.

Of course, having a synchronous IndexedDB API available in web workers would make everything infinitely easier. MDN states that

[IndexedDB's] synchronous API was intended for use only with Web Workers but was removed from the spec because it was unclear whether it was needed. However, the synchronous API may be reintroduced if there is enough demand from web developers.

I am not sure to who this interest should be formulated, but I think we should at least make ourselves known from the standard committee in charge of that.

And I do agree that using Emscripten's IndexedDB backend doesn't make much sense. Its behavior can easily be replicated by library users without any change in sql.js itself.

[rhashimoto]

Here are some timings for various VFS combinations. For each combination I did some simple operations:

• Create a database
• Create a table
• Insert 1300 rows
• Sum over one of the columns
• Close the database
• Open the database again
• Sum over one of the columns again
• Close the database again

I used three VFS implementations. "unix" is the default in-memory filesystem that SQL.js already uses. "mem" is a synchronous in-memory VFS I wrote, and "async mem" is an asynchronous in-memory VFS I wrote.

I tested with two builds, one regular build that only permits synchronous VFS, and one with Asyncify that allows either synchronous or asynchronous VFS.

I noticed that a lot of the filesystem traffic is for the journal file, so I also tested with PRAGMA journal_mode settings DELETE (default) and MEMORY.

Here are average times over 100 iterations:

VFS	Build	Journal Mode	Time (ms)
unix	std	DELETE	359
		MEMORY	260
	Asyncify	DELETE	537
		MEMORY	408
mem	std	DELETE	232
		MEMORY	205
	Asyncify	DELETE	394
		MEMORY	347
async mem	Asyncify	DELETE	2884
		MEMORY	1281

I see these interesting things in the results:

• The Asyncify build is slower than the standard build with any VFS (roughly 1.6x for a synchronous VFS), but the asynchronous VFS (which will only run with Asyncify) was vastly slower than anything else.
• Everyone should probably use PRAGMA journal_mode = MEMORY (or OFF if your usage allows it) with any in-memory database.
• My synchronous in-memory VFS turned out to be faster than the standard VFS. I didn't expect that; I thought it would be superfluous except as an example. I'm not sure whether the extra cycles are on the SQLite side or the Emscripten side or both.

[rhashimoto]

I created a demo page with several SQLite VFS combinations of filesystems (default, memory, IndexedDB) and builds (standard, Asyncify). It seems to work on Chrome, Firefox, and Safari (haven't tried Edge). Note that it restores your SQL from the previous session and you can execute a portion of your SQL by highlighting it first. This is all just to get some idea of what can be done, what things might cost, and what is the best way to implement them.

The interesting VFS is obviously IndexedDB - the others are mainly for comparison with it. IDB is used like a block device and is limited by the origin storage limits imposed by the browser, instead of by memory.

Unlike the other VFS implementations, when using IDB after you reload the page the database is still there. If multiple tabs are open, completed transactions in one tab are visible across all tabs. I implemented a crude locking scheme that should make this safe (more on this below).

IDB does feel quite a bit slower. Of course, it's always going to be slow compared with RAM; it takes a performance hit from using Asyncify and has IDB on top of that. I didn't implement any caching other than for checking file size, so all reads and writes use an IDB transaction. Looking at the access pattern, caching the first block of a database file might be a win because SQLite keeps checking a change counter there. I'm not sure how much benefit a general LRU-type cache would be.

A lot of the slowness (most, I think) comes from the default journal_mode setting, which journals to the same VFS as the database itself. The access pattern for a journal file is quite bad when storage is slow and you don't have a cache (adding a 1-block cache for journal files is probably a big win). Using PRAGMA journal_mode = MEMORY (or OFF) isn't as clear-cut a decision as it is with an in-memory database because your IDB database can be left corrupted (instead of completely gone) in case of a crash. Maybe what you really want is to journal into memory but buffer all database writes until the end of a SQLite transaction, then write everything in a single IDB transaction (so you can lose the journal but the database is unharmed). There must be people who know a lot more about this than I do.

Locking is a rabbit hole all by itself. The Web Locks API is nearly perfect for VFS...except that it isn't supported on Safari. Service Worker is supported across the evergreen browsers, but using it for locking across windows and workers would take some work. In the demo I used a lock file in IDB as an exclusive lock (i.e. multiple readers not allowed). I think it works and the implementation is small and all in one place (unlike a Service Worker approach), but this is not a high-quality solution (ignoring the lack of shared locks, which could be addressed) - it polls to test availability (inviting starvation) and it can leave a lock file in place on an abnormal termination (orphaned lock). So if you leave a transaction open and close the tab, you may have to use browser Dev Tools to clear IndexedDB for the page origin (just the "locks" database would be sufficient) for it to work again.

[Taytay]

Nice work on these benchmarks! I'll have to play with them - I've been wanting to compare journal modes and the like to have a set of recommended best practices for sql.js users.

One thing I'm immediately curious about is the overhead percentage for various aspects of your test. For example, I would expect typical usage to involve creating/opening a DB, performing a LOT of transactions, and then closing the DB when the session is done. In your benchmarks, I would expect there to be a lot of overhead in The opening and closing of the DB...that's likely intentional, but it's the first thing I want to tweak and test for my own expected usage.

For our (planned) usage of sql.js, we are okay with a local, in memory database losing data, but ideally, before we talk to our server, we will need a consistent flushed-to-disk version of the DB so that a crash after that would restore us to a consistent state. This would probably allow us to use atypical database semantics that give us the speed of PRAGMA=off, but occasionally take a hit when flushing the DB to disk before a server API call.
Of course, if flushing everything to disk takes too long, we would need to write data safely and incrementally, and we'd be back to desiring a journal file of some sort that writes only what changed to disk in a consistent manner...so we are back to wanting a real "file system" that something like indexedDB affords. Boy, I wish there was a synchronous browser API to emulate the file system more performantly. :)

[rhashimoto]

@Taytay There isn't very much overhead for opening and closing a database, at least in terms of i/o. That seems unintuitive because we're trained to consider opening and closing files as expensive operations, but a database moves a lot of those costs (e.g. flushing) inside transactions which leaves much less to do outside. Here's a log of what happens on open:

xOpen idb
xDeviceCharacteristics 5414000
xDeviceCharacteristics 5414000
xSectorSize idb
xDeviceCharacteristics 5414000
xSectorSize idb
xRead idb 0 100
xFileControl 5414000 15
xFileControl 5414000 30

Everything there except xOpen and xRead just returns a constant, so this basically reads two IndexedDB objects (xOpen reads a small metadata object). For comparison, here's a SELECT * FROM tbl, where the table is completely empty:

xFileSize idb
xRead idb 0 8192
xDeviceCharacteristics 5414000
xRead idb 24 16
xFileSize idb
xRead idb 8192 8192
xDeviceCharacteristics 5414000

That takes three reads. One of them could have been read from cache but my VFS doesn't have a cache.

Here's a single row insertion REPLACE INTO tbl VALUES ('foo', 6):

xRead idb 24 16
xFileSize idb
xRead idb 16384 8192
xFileControl 5414000 20
xOpen idb-journal
xDeviceCharacteristics 5414000
xWrite idb-journal 0 8192
xWrite idb-journal 8192 4
xWrite idb-journal 8196 8192
xWrite idb-journal 16388 4
xWrite idb-journal 16392 4
xWrite idb-journal 16396 8192
xWrite idb-journal 24588 4
xWrite idb-journal 24592 4
xWrite idb-journal 24596 8192
xWrite idb-journal 32788 4
xDeviceCharacteristics 5414000
xWrite idb 0 8192
xWrite idb 8192 8192
xWrite idb 16384 8192
xFileControl 5414000 21
xSync idb
xDelete idb-journal
xFileControl 5414000 22
xDeviceCharacteristics 5414000

The numbers on the xRead and xWrite lines are file position and number of bytes. You can see that the operations on the database file are block-aligned and block-sized, except on the first block. In contrast, operations on the journal file are serial but are not block-aligned, so note that without a cache a write generally also requires reading first.

In my benchmark, I didn't wrap my insertions (or anything else) in a BEGIN...COMMIT block so inserting each row is a transaction. My guess is that's what dominates the timings. I mainly put the reopening in to make sure that SQLite couldn't use its own internal cache and the data really was making a successful round-trip.

[Taytay]

This is fascinating @rhashimoto.
I love the foundation you have laid to allow you/us to so easily experiment with different implementations.

I'm now curious about even more!
1: If we're trying for a fully safe file-system aware implementation, I wonder how performant would WAL mode be? https://sqlite.org/wal.html
2: What are the expected semantics of SQLite for flushing changes to disk via the VFS? I read through some of the docs (https://sqlite.org/pragma.html#pragma_synchronous), but not in much dept. Assuming that we can rely upon calls to xSync as a form of flushing to disk, wouldn't that imply that we could perform the VFS operations in memory and then commit them to IDB only upon calls to xSync? If so, that seems like a massive win. ( Otherwise, the overhead of the file writes to the journal is extremely chatty, as you already pointed out. In your example, there were 10 writes to the journal file for a single small transaction. If each of those round trips to IndexedDB, we're gonna have a bad time. ) This is just based on loose assumptions about how xSync works, although admitedly we're pretty far over my head already. But that assumption is not supported by the list of calls in your output. I only see one call to xSync for the idb file...What journal mode were you using for that test?

This page is a good explanation of the assumptions that SQLite is making regarding the filesystem when using the journal. I think this could help us implement the VFS in a more performant way. (It also indicates why the Journal might be slower than the WAL): https://www.sqlite.org/atomiccommit.html

I think there's gonna be a good combo here that gives us the safety we want, with the file-system semantics that are IndexedDB friendly. 🤞 anyway...

[rhashimoto]

@Taytay Regarding WAL mode, that's a question for someone who knows more about database implementation and its trade-offs than I do. I probably only know enough to be horribly wrong.

Regarding the journal mode for those logs, it was DELETE, i.e. the default. I've been wondering myself why there isn't an xSync for the journal file, so if that's what you're puzzling over I'm puzzled, too. I do think you are correct that one should be able to cache data until an xSync.

I don't want to get too far down into the details of implementing any specific VFS in this thread. There assuredly will be many opportunities to optimize performance and tune for specific usages, like the ones you've mentioned. I'm highly gratified that this has sparked your and others' interest, and that was one reason I started the discussion, to find out if anyone else wanted this. I'm hoping to pivot to the other motivating reason, which is to figure out how to provide the platform for anyone to write their own VFS using only Javascript and without involving Emscripten.

I don't think I'm prepared to integrate Meta VFS into sql.js all by myself. The biggest issue is that using an asynchronous VFS requires an asynchronous API that sql.js currently does not have. We could start with only synchronous VFS support which still can be useful with the existing API (e.g. the Atomics experiment by @phiresky) but the best browser support (at least for now) and easiest development are unlocked with asynchronous. I'm not really interested in defining or implementing this new API; for the demo, I wrote my own simple API directly on the exported SQLite C API. So I'm hoping that perhaps some others can help out.

@lovasoa Would you accept a PR that only had the minimal Meta VFS code, with low-level tests written using the exported C API (i.e. via ccall/cwrap) and not the Database API? If not, is a branch a suitable way to proceed? I'm hoping that there is an integration path that progresses in smaller steps instead of everything at once.

There are some other things that make it frustrating to work in the sql.js tree, so much so that I moved my development to a repo I made from scratch. The restriction to ES5 Javascript is pretty painful, harder to read and maintain, and I don't know how anyone will write an asynchronous API (Promise is ES6, async/await even later). I played with adjusting the Emscripten flags to transpile to ES5 in release builds, which worked, but then updating the ESLint rules broke on the worker code which Emscripten doesn't process and that's when I gave up working directly in the tree. If someone good at tooling could take this on that would make things better all around. #438 and #284 would also make life a little nicer for those of us who like buildless development.

[phiresky]

One more thing I found while experimenting is pragma cache_size = 0: SQLite by default has its own internal page cache of 2MB, so if you want to really see what pages SQLite is reading you need to disable that. And it might make sense to disable it in any case since we might already be already holding stuff in our own RAM cache - or it might make sense to increase to be able to use it better when we have a "slow" storage backend like IndexedDB.

I just published the code and an article about my http-vfs and and my DOM virtual table by the way: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ It shows an exact page read log for each query (based on the dbstat virtual table) which could be useful.

I think those many "weird" writes to the journal file are explained by this from the file format spec:
https://www.sqlite.org/fileformat.html

It's probably writing the old page content, the page ID, and the checksum in three separate writes. So in your indexed DB thing you could align your rows with those three items.

you really want is to journal into memory but buffer all database writes until the end of a SQLite transaction, then write everything in a single IDB transaction

I mean that kinda sounds like WAL mode but with extra steps. The WAL file is literally just a set of pages that are overlaid over the main db file during READs.

Can't you always have an IDB transaction open that is committed and a new one created when xSync is called?