How we sped up Notion in the browser with WASM SQLite

If you think those read speeds are great, try DuckDB (which has many SIMD improvements) if you want to blow your socks off.

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

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

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

This is kind of an unfair comparison. Essentially nobody needs a million iops for their database. Even an extremely busy database doesn't need to scan all of the data it holds (or at least, if it does, you're using it very wrong—that's why we have indexes).

A fast disk is possible on a laptop because it's a tiny hop to RAM. And it's desirable because you probably have nowhere near 2TB of RAM handy, so you need it to be fast.

In the cloud you can get 2TB of ram for $11k/mo (4x r6g.16xlarge). Not that you need anything like that to run your database. Most of that data is never being queried.

It's also the case that a laptop workload is very different than a server workload. If I run a steam game, I want it open fast. My laptop isn't crunching numbers on all the bytes at that moment. When I run a table scan on a Postgres table, processing needs to happen on every single tuple. A million iops isn't useful if your CPU immediately becomes the bottleneck. A Thinkpad would simply never match the response times of a server with a tenth of the iops under load (if the workload required scanning huge amounts of data).

So yes, the iops are more expensive, but that's really not a metric that anyone in the target market is hurting over.

It's now live at https://cluttr.ai

I'm going to write a few blog articles about some of the challenges I've had, and where I'd like to see the tooling of local first to mature.

A central Postgres (RDS) and a local SQLite are pretty for different use cases. You can't compare it.

On a very slow SSD SQLite can query a 20 gigabyte database file in milliseconds.

I wanted to try SQLite in the browser for a side project, but the startup time was unacceptable to me. It takes a solid second or two to crunch through the WASM binary, init SQLite, and open a database. Gets worse on first boot when you need to create all the tables.

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.

It sucks that even the newest make-wadm-sqlite-fast FileAccessHandles are still an intermediated virtual file system hosted by the browser. I don't get why we have three different file system APIs on the web and none that just use files.

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.

I was about to say, does Notion actually feel any faster to anyone? I stopped using it years ago but the last place I contracted at was invested in it heavily and the thing was crazy slow.

Curious where you're moving? About to begin finding a tool similar to Notion (but not Notion).

Stay away from Clickup, it's just as slow if not worse. Also same AI notices.

That's because the big optimization would be "don't cram this entire application into the browser", but we've all collectively decided desktop software is impossible, so.

One team works insanely hard and saves 2 seconds on app load. Another team takes the opportunity to add 3 seconds worth of crap that no one wants. Welcome to modern software development.

Developing a fast system requires an entire different mindset, unless it is critical it never happens. Most people think it is w/e we don’t need to optimize and when you have people complaining it is already too late

They’re a very un-user focused company. They used the | cursor for any horizontally scrolling content for years, making a kanban board with non default states is nightmarishly complex, and there’s a billion odd UI slownesses and niggles.

But they’re recently added AI.

Why couldn't they just make it, like... a program that runs?

displaying ui and handling interactions ends up being most of the app though

Computers can already do this! :D No browser required!

They already did that. It was called WebSQL. It was removed I think in favor of IndexedDB.

I'm so glad they didn't. We would all be stuck on some 14 year old baseline version of SQLite at this point.

SQLite as WASM is a much better solution IMO.

Great catch. It's specifically SQLite-via-OPFS that needs to be within a Web Worker. we fixed this in the post!

Not this, but the sqlite.js-http-vfs wasm implementation[0] (not the current official one) has the browser DOM available as a virtual table. Sample query from the post:

    select count(*) as number_of_demos from dom where selector match '.content div.sqlite-httpvfs-demo';

[0]: https://phiresky.github.io/blog/2021/hosting-sqlite-database...

Have you experienced the corruption issues mentioned in the original article?

Unfortunately WebSQL was ahead of its time, I have no doubt it would have caught on more today with the resurgent popularity of SQLite.

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

100% agree, the websql debacle is the reason I stopped using Firefox entirely. Dropping the ball when faced with some issues isn't ok when you're in a position to fix the specs

I was also disappointed, but this decision seems reasonable to me. SQLite is still evolving, if you pick a specific version then you're stuck on that version or risk breaking people that depend on specifics of that version. Also a vulnerability anywhere in SQLite would expose billions of people to exploits. This is not the same as applications using sqlite internally, the web is a much hotter environment: most apps may expect potentially hostile input in SQL params, but with WebSQL everything is potentially hostile: the queries, the database files, even the schema may conspire under confusing concurrency conditions to corrupt the state of the sqlite engine to break out and exploit the user. Whatever you think you have to agree that this is a LOT of new surface area.

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

Same - I've seen plenty of exploratory/experimental posts, but this is the most information I've seen about it being used in production.

(I work at Notion, but didn't build the WASM sqlite thingy)

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.

It has a weird, verbose API that's more like a simple document store than a SQL database, so it's naturally going to be outperformed by SQLite, especially if you're performing complex operations. It's also heavily async which means operations have to spin the event loop.

It's buggy depending on browser implementation, Firefox will corrupt DBs, it's a herculean task to debug things with users, it blocks the main thread for reads or writes (maybe wasm stuff does too).

SharedWorkers can't use OPFS, unfortunately.

It's for multi-tab support.

This is an optional cache so Notion continues to work the way it did before in browsers that don't support WASM SQLite.

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.