search

yaqwsx / jlcparts

Better parametric search for components available for JLC PCB assembly
https://yaqwsx.github.io/jlcparts/
MIT License
572 stars 52 forks source link

Consider moving to SQLite #37

Open yaqwsx opened 3 years ago

yaqwsx commented 3 years ago

Recently, we started to struggle with the performance of IndexedDb. This blogpost mentions possible usage of SQLite. Let's have a look and find out if it could be a suitable solution.

PS: Definitely a layer of caching will be necessary to avoid refetching the database.

leo60228 commented 3 years ago

Chrome plans to deprecate WebSQL at some point in the future: https://www.chromestatus.com/feature/5684870116278272

leo60228 commented 3 years ago

...wait, this repo uses IndexedDB. Oops.

yaqwsx commented 3 years ago

Huh? I am confused; this issue has nothing to do with WebSQL, nor the project uses it.

leo60228 commented 3 years ago

I specifically recalled that the project used WebSQL. I'm not sure why I thought that.

yaqwsx commented 2 years ago

We should probably test how large is zipped SQLite DB is. There's a huge chance that it will be actually smaller than the JSON files we send (it is around 100 MB). If that's the case the update feature could just download the whole DB and store it in a cache and we don't have to hassle with sql.js-httpfs that has poor support and instead, we could use directly sql.js in a web worker. In this way, performance could be increased and we could solve the Firefox issue and also, make the update feature, much, much faster.

phiresky commented 1 year ago

Just want to mention that SQLite has native wasm support now. They have a few "ideas" for how to do persistent storage https://sqlite.org/wasm/doc/trunk/persistence.md . You'd download the whole SQLite db (which should be pretty small when compressed as a whole), extract it to the origin private file system, then query it from there. I'm not sure how mature those are by now. If that doesn't work, absurd-sql or wa-sqlite are other options.

If your updates are frequent but somewhat small you could in theory ship them via log-shipping the WAL files (i can link tons of resources if you're interested), but potentially just downloading the whole thing new is enough. Also just for fun note you can use simply use an old file as the dictionary in ZSTD to get delta compression for the new file - you could store delta zstd updates for all files of the past month for frequent users and download the whole new file otherwise. Remember to always run vacuum when you change your sqlite file.

yaqwsx commented 1 year ago

@phiresky : Native WASM SQLite and OPFS sounds exciting and something we should probably use. Thank you for letting me know.