Meanwhile in AWS you would pay $27k a month to have the same IOPS as a Lenovo Thinkpad X1.

I just got done with a side project using WASM SQLite as well, it's incredibly powerful, even supports full text search.

My project "cluttr" is a local first site that focuses on cleaning up your screen shots folder and making it searchable via OCR. All in browser.

It also supports using Ollama to run a vision model against your images to provide more context.

Really helpful if you take a lot of screenshots for clients or PMs and need to find them later. The best part is 99% of the hosting cost is offloaded to the client, so I am not really worried about a large server bill.

https://cluttr.pages.dev/

(working on deployment to https://cluttr.ai later today)

But if you are doing aggregate or skip-scan analysis and if they're talking about read speeds and in-memory processing, well, SQLite leaves some performance on the table by being single-threaded, as far as I can tell.

There was a web.dev post or something saying "Web SQL is finally here! Just use SQLite with WASM!" Like, sure, that does seem to work, but it requires a huge WASM blob and a huge heap of JS glue. Indexeddb--as horrible as it is--starts up and is usable almost instantly. The sad part is that Indexeddb's querying is so bad you're forced to build your own database on top of it anyway...

I get where the web standards folks were coming from when they didn't like WebSQL just being SQLite in every browser, but frankly that would be so much better than what we have now even 5 years after back-peddling.

Curious about how this number was arrived at and what products were involved

Of course its not realistic to run a primary DB on a laptop, but the IOPS are extremely expensive in AWS was the point.

Offloading expensive queries to a browser is a viable solution. If I had decided to make this some sort of SaaS offering I am certain running full text search at scale would cost thousands of dollars per month long term, with the data becoming increasingly irrelevant over time, and I would still be forced to host it.

I've had to scrub a multi terabyte database of PII before moving to a staging environment, it hurts. With modern data architectures, you may write the same data 4-5 times in its life cycle, staging data, data warehouses, marketing, PowerBI, Looker, etc...

Especially reporting solutions, where they may aggregate massive amounts of data and write it to temp tables.

It will require IOPS, and you will pay handsomely for it.

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.

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. Sweet lord loading up three bare-metal dbs with 1TB ram each and bonded 10G nics where the app servers didn't even have to hit a router to talk to them. We initially thought the replication lag being pinned at 0 was a mistake.

Don't take this as any sort of condemnation of cloud offerings, being able to spin up comparable infra in my underwear and not having to think about purchasing and dealing with hardware vendors are truly a gift.

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.

There's a multiple readers and writers proposal [0]. It's been "position: positive" by Firefox [1], implemented in Chrome [2], and ignored by Webkit [3] (of course).

    0: https://github.com/whatwg/fs/blob/main/proposals/MultipleReadersWriters.md
    1: https://github.com/mozilla/standards-positions/issues/861
    2: https://chromestatus.com/feature/5172892632875008
    3: https://github.com/WebKit/standards-positions/issues/238

Also, looks like Roy takes advantage of the aforementioned proposal when he wrote `OPFSPermutedVFS`, which takes 2nd pace behind `IDBMirrorVFS` w/r/t perf. https://github.com/rhashimoto/wa-sqlite/blob/master/src/exam...

Meanwhile WASI has their own server-side file system APIs but neither WASI nor the browser side seem to have any effort to get on the same page. It'll be 2035 before component-model ends up being usable on the web, at this rate, before modules are modular.

"just use files"

What does this mean? Providing the POSIX filesystem C language APIs with file descriptor numbers, etc? I guess the browser makers could go Cosmopolitan-C style and emulate the Linux filesystem interface on all platforms, but I think you'd find there's still a weird intermediary layer in between your code and the underlying OS filesystem. Even on Linux the browser would have a virtual filesystem to provide sandboxing & quality-of-service governance. On other systems you'd need a virtual filesystem to paper over those differences between operating systems. Windows exists!

I'm glad they are making their app faster, in the meantime I (browser user) have cancelled my team's subscription and will be using something else.

the customers are the ones who did.

Suppose there was a desktop app, but a competitor has a web version. It's much easier, faster to use the web version, because you dont need to download the desktop app, install it (which might require admin perms). Then your files are local, so if you have a second computer, there needs to be some way to share those files between.

For my dev team I recently moved us to eraser.io. Still in the early stages with it but it's working well enough for us atm.

But they’re recently added AI.

And by fast I mostly mean, when you did a / command, the menu came up quickly. Now it's stuffed with so many options as to be useless.

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.

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

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

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

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

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

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

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

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

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

One interesting thing is that opfs maximum disk usage is kind of browser specific but tends to be a percentage of the available diskspace measured in GB rather than some lowish number like 5MB as is typical for e.g. browser local storage. This makes it suitable for locally caching and syncing large amounts of remote database content.

We've had a few challenges with opfs and particularly limited support for this on Safari it kind of works but with some caveats. Chrome/Firefox are fine. Our web app is packaged up as a PWA for use on mobile.

Another issue is that opfs creates issues with loading resources from external websites. E.g. we have HTML previews that may include images on external domains that the browser will render fine without opfs enabled but will refuse to render when using opfs, unless you set crossOrigin=anonymous on the img tag.

Relative to indexed-db, which is supported by most browsers, you gain a more sane API to access data and more flexible querying and support for things like joins. I've had some exposure to indexed-db and IMHO it's a case study in bad API design gone horribly wrong. Querying is very limited and the APIs are poorly documented and have weird failure modes. Sqlite is an absolute pleasure to use in comparison and probably faster and way more capable.

Couldn't you cache the data in LocalStorage and get similar speed improvements?

I mean, how to run SQLite in the browser is a great topic and surely has a lot of very good use cases. But attributing the performance improvements to it sounds a bit misleading, when you had no client caching strategy before and you do now. Obviously loading data from a local cache is faster than loading data from a remote server.

Comparing WASM SQLite vs. other client caching solutions (LS, IndexDB, etc) could be interesting. In terms of perf, complexity, compatibility, storage capacity, query language, etc.

We've implemented this same cache using LocalStorage, IndexedDB, and SQLite. The Android & iOS apps used SQLite for this since 2020 (I built the Android version IIRC), and the desktop app used SQLite running on a native thread since 2021.

We migrated away from LocalStorage for two reasons:

1. LocalStorage is limited to 10mb file size. We also use LocalStorage for a bunch of less-durable state like "is this toggle open?" or "which view in this database was open last", and as our customer workspaces grew, we faced mounting errors as the record cache competed with the rest of the app for that space. We'd have a bunch of slowdowns under contention as we tried to delete keys in LocalStorage synchronously, which manifested as major UI lag. No good!

- LocalStorage loves to lose writes if you're writing from multiple tabs. It's just not a reliable or trustworthy API. For a cache that doesn't matter as much, but we'd still end up with cache misses for power users for pages that should really be totally locally loadable.

I implemented the IndexedDB version in 2019, to replace the earlier LocalStorage option. We used the IDB record cache in the desktop app until we switched over to native SQLite there in 2021 (https://www.notion.so/blog/faster-page-load-navigation) but we never shipped it for browser users for a few reasons:

- Performance and reliability problems with IDB in browsers is hard to debug; in the native app we can trust the version of Chromium we ship and remediate issues using Electron APIs, where as in the browser wild we're at the mercy of the user-agent

- Our testing in the browser showed limited performance improvements across all device categories: faster devices & scenarios were even faster with IndexedDB, but slower devices & scenarios could be even slower.

The reason I'd attribute for the performance challenge is that IndexedDB pays a high tax per row written and row read. It can be fine in terms of total throughput for a cache if you have large, coarse-grained cache rows, like caching all of a document as a single object, and you update the cache infrequently.

Notion's data model is tree/graph of very fine-grained records; each paragraph is its own database row. Our cache on IndexedDB would perform great for smaller workspace sizes and for a single tab, but with multiple tabs and medium-to-large workspaces, we'd hit contention in IndexedDB and get major slowdowns.

We should improve our cache architecture to have another layer of cache that does whole-pages, but need to weigh the improvement/complexity there versus other performance opportunities.

- tooling, support and debugging for opfs

- only really works with a single worker using sync api, but this is not available from service workers

- the official js releases are really not idiomatic js and require in depth understanding to pick out the right parts and then build a usable layer on top, afaik there is no existing npm package that just provides what is required

SQLite as WASM is a much better solution IMO.

Mozilla again.. The real joke is, Firefox is now the only(?) browser using sqlite out-of-the-box for internal databases.

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

I think it's funny you say a problem is that it's async while another commentor says a problem is it blocks the UI. Maybe it's the worst of both?

As an alternative we have apps for iOS, Android, Mac, Windows. In those apps we already run SQLite in a native thread talking to the normal filesystem for years.

SQLite has become the defacto local single file database standard.

It has a very permissive license.

It has support contracts out to the 2050s (service lifetime of Airbus A350 airframe).

SQLite will probably have a longer lifetime than whatever browser standard we have now.

IndexedDB got to ride the NoSQL hype of the 2010's

I much prefer this approach where the site brings their own sandboxed SQLite WASM package.