search

jlongster / absurd-sql

sqlite3 in ur indexeddb (hopefully a better backend soon)
MIT License
4.15k stars 101 forks source link

WAL Mode #19

Open antimatter15 opened 3 years ago

antimatter15 commented 3 years ago

Awesome work! I was playing around with the SQLite VFS mechanism myself back in February— But I hadn't thought of your clever Atomics.wait approach.

In the blog post, you mention:

There’s also write-ahead logging, or “WAL mode”. This can be more efficient if working with a real disk. However, to achieve its performance it requires things like shared memory using mmap and things like that. I don’t think we can reliably map those semantics onto IndexedDB, and I don’t even see the point. WAL potentially adds more overhead if we’re just writing them all to IDB anyway. The structure of the writes isn’t as important to us.

I haven't tested this, but I suspect that for the SQLite-on-IDB case, WAL actually makes a fair amount of sense. For one, you can avoid the extra writes to a rollback journal (and i don't think JOURNAL_MODE=MEMORY is sufficent— as when you run BEGIN TRANSACTION and COMMIT as part of different statements— closing the tab in between them may corrupt the SQLite database).

And also I think it's better aligned with how the underlying storage engines for IndexedDB work. Firefox uses SQLite in WAL mode, so any in-place edit of any IDB block is going to actually just append data to a WAL anyway. Likewise Chrome's IDB is implemented on top of LevelDB which is a Log-Structured Merge database that similarly never writes over anything and just appends changes to the end.

The other alternative is the more obscure VFS options like SQLITE_FCNTL_COMMIT_ATOMIC_WRITE— which allows you to tell SQLite that your underlying storage medium is capable of doing atomic commits and then in certain circumstances SQLite won't bother maintaining a journal altogether. Unfortunately it's more of an optimization rather than something you can rely on entirely— as I've found that in some situations it'll still try to write to a regular rollback journal.

jlongster commented 2 years ago

I am so sorry for the delay here! Work got super busy right after I launched this because I had put off some stuff for this, but I'll be going through the issues soon. Thanks so much!