How we sped up Notion in the browser with WASM SQLite

I don't know about your specific example, but doing an operation where you rewrite most of a multi terabyte database online is almost certainly not best accomplished with SELECT/UPDATE. Even if you need multiple passes, that's N terabytes times M passes times two. That's... not a lot of reads and writes. Dumping the database to files on blob storage, rewriting them, then reading them into your destination is almost certainly the fastest and cheapest way to go about that.

And that's not an iops avoidance thing, that's a "this isn't what your database is built to do with the configuration your running it in" sort of thing.

Not necessarily. It's just that a lot of SPAs don't contain a lot of business logic because it's too tedious to do without a client side database; which means you end up offloading a lot of business logic to server side databases behind some kind of API.

Adding local persistence in the form of an actual database, allows you to do more of these things client side. Which means you end up with more business logic that you can cleanly separate from your data rendering logic and other cruft needed for e.g. form filling, data validation, and what not. You still need that of course but it makes web applications more similar to full desktop applications in the sense that the server might be a lot lighter or even be optional (other than serving the code and other assets).

I've actually been toying with building a Google Reader style application that stores its data in the browser recently. I use a few minimal server scripts to work around cors issues for fetching feeds and html previews. But aside from that, there is no need for a server. I can save local state (stored in indexdb) to a file and download it and then restore it from the same file as a backup strategy.

I've been adding search capabilities with tf/idf ranking, phrase matching. I'm using OpenAI to help summarize and tag content. And I'm currently adding a light weight vector search implementation. This is all running in the browser (except for openAI).

My goal with this is experimenting with RAG against news content. So, I've been piecing together things I need for this and raising the ambition level as I've progressed in the last few weeks. Most of this is probably not optimal and a big motivation for me is to just wrap my head around all the bits and pieces I need. But there's no good reason why most of those things could not be optimized with e.g. some wasm code that uses web-gpu for doing math and less memory intensive ways of storing stuff.

BTW, I'm using kotlin-js and kotlin-multiplatform which makes it easy to forget that I'm dealing with Javascript and very limited browser APIs under the hood. UI is still tedious to do but I have a growing amount of code that is pure business logic, algorithms, or other stuff you'd normally run on a server and implement in a language like Kotlin. Which is why it's nice to be using that in the browser.

This is both silly and somewhat off-topic, but on the subject of doing database work on the client side ... has anyone experimented with using non-rendered DOM as 'table' and CSS selectors as 'query language'?

I'm not talking about 'using the DOM to store data' in the traditional sense. The idea is rather (roughly) to store each database 'table' as a child of a hidden DocumentFragment, with 'rows' as its children, etc. Then we'd query this data using CSS selectors (or XPath).

For example, instead of

  SELECT * FROM Employees WHERE gender = 'male' AND age > 30

you'd have something like:

  customersTbl.querySelectorAll('row:has(cell-gender[value="male"]):has(cell-age[value > "30"])')

And instead of

  SELECT * FROM Employees WHERE gender = 'male' OR age > 30

you'd have something like:

  employeesTbl.querySelectorAll('row:has(cell-gender[value="male"], cell-age[value > "30"])')

You can even have (truly) structured cells! Instead of

  SELECT * FROM Employees WHERE name->>'first_name' = 'John'

you can have:

  employeesTbl.querySelectorAll('row:has(cell-name:has(cell-first_name[value="John"]))')

And for querying array-like structures, instead of:

  SELECT * FROM employees WHERE skills::jsonb ? 'JavaScript';

you can have:

  employeesTbl.querySelectorAll('row:has(cell-skills > cell-skill[value="JavaScript"])')

I can certainly imagine the performance to be horrible, but having never tried out the idea (and having too meager a mental model of DOM performance to reason from) I can also imagine it being surprisingly decent for smaller datasets. Who knows? :-)

Edit: silly me indeed to think CSS attribute selectors already can do numeric comparisons! See here: https://github.com/w3c/csswg-drafts/issues/354

Presumably GP meant Mozilla agitated for a Document/KV-store, yet for some inexlicable reason, in their own browser, they chose to implement IndexedDB with SQLite.

They could have used an actual KV store behind it, like chromium did (levelDB IIRC).

> I hope everyone at least once in their career gets to experience just how god damn fast hardware (especially networking speeds between your own servers) is.

THIS. I realized cloud disks were much slower than I thought when I ran the same tests in RDS – with a local NVMe cache – against my decade-old Dell R620, with its disks also being NVMe, but via Ceph over Mellanox Infiniband. My server matched or beat the many-generations-newer RDS instance on almost every query.

You can’t get around latency. Even at 1 msec, that’s a maximum of 1000 ops a single thread can do per second, modulo the various buffering and chunking strategies every layer does.