10 ms·

How we sped up Notion in the browser with WASM SQLite

253 points by jFriedensreich 2y ago

gnabgib 2y ago
Article title: How we sped up Notion in the browser with WASM SQLite Discussion (19 points, yesterday) https://news.ycombinator.com/item?id=40931957 https://news.ycombinator.com/item?id=40931957
DANmode 2y ago
[flagged]
nafey 2y ago
I believe it's a missed opportunity not to bundle sqlite in major browsers. I understand that making SQLite part of the browser spec introduces all sorts of complications [1] down the line. But nevertheless, it would have proved to be quite a nifty tool. [1] https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
- compressedgas 2y ago
  They already did that. It was called WebSQL. It was removed I think in favor of IndexedDB.
  - slightwinder 2y ago
    [..] Web SQL Database was a prior API developed by Apple.[12] But Firefox refused to add support for it and argued against it becoming a standard because it would codify the quirks of SQLite.[13][14] It was thus deprecated in favor of IndexedDB.[..] Mozilla again.. The real joke is, Firefox is now the only(?) browser using sqlite out-of-the-box for internal databases.
    - r0ks0n 2y ago
      nope, chromium too. look in your cookies file
      - slightwinder 2y ago
        I did, but seems I missed the actual subfolder where the databases are saved. But this makes it even worse. They all have sqlite installed, but offer not API for making use of it. Really missed chance.
      - LAC-Tech 2y ago
        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).
- simonw 2y ago
  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.
jFriedensreich 2y ago
from my own experience what notion did is pretty much required to use wasm sqlite with persistence in production. on the one hand being used at notions scale really points to wasm sqlite being ready for primetime, on the other hand there are still a LOT of ugly parts that need to be worked around: - 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
AlexErrant 2y ago
> OPFS doesn’t come with graceful handling of concurrency out of the box. Developers should be aware of this and design around it. 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 Love the shoutout to Roy Hashimoto. He's been writing VFSs for SQLite-on-the-browser and perf testing them. He's recently wrote "IDBMirrorVFS", which "is a new example VFS that keeps all SQLite files in memory while persisting to IndexedDB". It has remarkable performance, of course. https://github.com/rhashimoto/wa-sqlite/discussions/189 https://github.com/rhashimoto/wa-sqlite/discussions/189 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/examples/README.md#opfspermutedvfs https://github.com/rhashimoto/wa-sqlite/blob/master/src/exam...
- jauntywundrkind 2y ago
  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.
  - jitl 2y ago
    The profusion of underpowered interfaces is frustrating but I don't see how browsers could "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!
slightwinder 2y ago
They have an actual sql-datebase on the client as cache, but still no offline-mode? Damn it. This must be their most demanded feature for years.
- funemployd 2y ago
  Why couldn't they just make it, like... a program that runs?
bearjaws 2y ago
Overall I think using SQLite locally to offload database work is incredibly powerful. Given most laptops have an SSD now a days, you can scan an entire 30-100MiB SQLite db in miliseconds. 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/ https://cluttr.pages.dev/ (working on deployment to https://cluttr.ai https://cluttr.ai later today)
- bigbones 2y ago
  > 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
  - bearjaws 2y ago
    RDS with 256,000 IOPS (the max) on a single primary instance of MariaDB. 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.
- NortySpock 2y ago
  If you think those read speeds are great, try DuckDB (which has many SIMD improvements) if you want to blow your socks off.
  - 2y ago
nikeee 2y ago
Sadly, SharedWorkers still are not supported on Chrome+Android. Hope that changes soon because it would be a game-changer for many applications to have this API broadly available.
stpn 2y ago
Pretty neat! If notion folks are in this thread, what do you do about lack of SharedWorker in Android Chrome?
- jitl 2y ago
  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.
RcouF1uZ4gsC 2y ago
Honestly, we should just have native SQLite supported by the browser. 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.
- biftek 2y ago
  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
- mickael-kerjean 2y ago
  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
- infogulch 2y ago
  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.
jakubmazanec 2y ago
Will the tables in the Notion mobile app still be so painfully slow? Every time I scroll few rows down (in a table with few than 100 rows with dozen columns), the app hangs for several seconds while trying to fetch/render (who knows what it's actually doing) few additional rows. When I edit a row and return back to the table view, scroll position is lost and I have to suffer through the fetching once again.
- nailer 2y ago
  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.
  - ilrwbwrkhv 2y ago
    I think their real innovation was the community and getting "cosy productivity" during Covid going. They are more of a social phenomenon than a technical one.
    - threetonesun 2y ago
      Notion is a great example of my hatred of the current "upgrade or die" delivery method of software. Notion 1.0, when it was mostly text with a unique way to view tabular data, was fast and enjoyable to use. Once it shifted to competing with Atlassian et al, it acquired all the bloat those products already have.
      - nxobject 2y ago
        Sadly, I don’t think basing a business (with the tech investor desire for growth! growth! growth!) off one product, is best for an app that’s best when predictable.
      - satvikpendem 2y ago
        Notion was never fast, and I've been using it on and off for the past 10 years or so.
mrtksn 2y ago
Seeing things like that excite me a lot, I can't wait for a future where all the code is written in whatever language you like and runs through WASM in the browser. The HTML and JS can go back doing that thing they are built for: Displaying the UI and handling the interactions.
- jitl 2y ago
  displaying ui and handling interactions ends up being most of the app though
  - jillesvangurp 2y ago
    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.
mortenjorck 2y ago
All this wonderful optimization, and yet there's still no UI to warn that (perhaps due to a websocket connection being lost?) a comment won't be saved. I lost two Notion comments this way yesterday!
born-jre 2y ago
after conquering all smartphones, browser, edge db and what not now every web page will have own instance of SQLite running in wasm, lol
solarkraft 2y ago
This is cool because I haven’t seen WASM SQlite in production. I wasn’t sure whether it’d end up being more performant than something built in like IndexedDB. Seeing that it’s practically feasible is definitely cool.
- DylanSp 2y ago
  Same - I've seen plenty of exploratory/experimental posts, but this is the most information I've seen about it being used in production.
NelsonMinar 2y ago
Why is IndexedDB so bad that you have to load a second database in WASM instead? I've used the API successfully but never measured performance.
- kevingadd 2y ago
  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.
  - NelsonMinar 2y ago
    yeah I gave up on the direct API, I used Dexie for my product. 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?
    - fastball 2y ago
      Yes, it's the worst of both. Dexie is at the mercy of the aforementioned issues with IDB (differing browser implementations, blocking of the main thread, hard to debug, can't actually guarantee persistence beyond "best effort"). The issues are much more prevalent on mobile browsers in our experience (iOS Safari in particular being the worst), to the point I think we'll need to move off it for our persistence layer in our own app and will unfortunately be forced to require our users to download wrapped apps (Capacitor and Electron) if they want offline persistence guarantees. But yes, the Dexie API is indeed a much better experience for working with IDB.
- dbalatero 2y ago
  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).
  - 2y ago
remram 2y ago
Notion takes 15s to load to an empty page. Then another 5 to dismiss the popup about new AI features and the like. 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.
- jamil7 2y ago
  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.
- alex_lav 2y ago
  Curious where you're moving? About to begin finding a tool similar to Notion (but not Notion).
  - ivanjermakov 2y ago
    I felt like a grandpa when using Notion, but haven't found a good alternative yet. As of now, I use google sheets for collaborative work and plain text/md files for notetaking.
  - vevoe 2y ago
    I also moved away from Notion a while ago for being crazy slow. For personal note taking I use Obsidian, big fan of it. 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.
    - arrowsmith 2y ago
      I second the Obsidian recommendation.
    - remram 2y ago
      Not sure. We're currently using GitLab wikis a lot, oddly enough. Obsidian is nice but start time on mobile is bad too.
ggregoire 2y ago
> Using SQLite improved page navigation times by 20 percent in all modern browsers. 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.
- jitl 2y ago
  (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 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.
deleted 2y ago
[deleted]
bedatadriven 2y ago
This honestly sounds like a nightmare: multimegabyte wasm downloads, data corruption in production and byzantine hacks to coordinate writes between tabs. I am very grateful that we chose IndexedDB instead for our application!
jillesvangurp 2y ago
We've been using wasm sqlite with kotlin-js and the kotlin sqldelight framework. This is admittedly a bit of an exotic stack to be running in a browser. But it actually works surprisingly well. Unfortunately the failure modes with opfs are kind of ugly and need some attention. A key issue is controlling what happens when you have multiple tabs open interacting with the same DB. 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.
- randyl 2y ago
  Have you experienced the corruption issues mentioned in the original article?
aragonite 2y ago
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 https://github.com/w3c/csswg-drafts/issues/354
- captn3m0 2y ago
  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-databases-on-github-pages/#bonus-dom-as-a-database https://phiresky.github.io/blog/2021/hosting-sqlite-database...
  - aragonite 2y ago
    Thank you!
cageface 2y ago
OPFS is only available in Web Workers. I don't think this is correct: https://developer.mozilla.org/en-US/docs/Web/API/File_System_API/Origin_private_file_system#manipulating_the_opfs_from_the_main_thread https://developer.mozilla.org/en-US/docs/Web/API/File_System...
- carlo-notion 2y ago
  Great catch. It's specifically SQLite-via-OPFS that needs to be within a Web Worker. we fixed this in the post!
taf2 2y ago
I don’t see why they need to have a worker and a shared worker .. couldn’t they run the sqlite in the sharedworker directly?
- carlo-notion 2y ago
  SharedWorkers can't use OPFS, unfortunately.
- fastball 2y ago
  It's for multi-tab support.

Article title: How we sped up Notion in the browser with WASM SQLite Discussion (19 points, yesterday) https://news.ycombinator.com/item?id=40931957 https://news.ycombinator.com/item?id=40931957

DANmode 2y ago

[flagged]

nafey 2y ago

I believe it's a missed opportunity not to bundle sqlite in major browsers. I understand that making SQLite part of the browser spec introduces all sorts of complications [1] down the line. But nevertheless, it would have proved to be quite a nifty tool. [1] https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

compressedgas 2y ago
They already did that. It was called WebSQL. It was removed I think in favor of IndexedDB.
- slightwinder 2y ago
  [..] Web SQL Database was a prior API developed by Apple.[12] But Firefox refused to add support for it and argued against it becoming a standard because it would codify the quirks of SQLite.[13][14] It was thus deprecated in favor of IndexedDB.[..] Mozilla again.. The real joke is, Firefox is now the only(?) browser using sqlite out-of-the-box for internal databases.
  - r0ks0n 2y ago
    nope, chromium too. look in your cookies file
    - slightwinder 2y ago
      I did, but seems I missed the actual subfolder where the databases are saved. But this makes it even worse. They all have sqlite installed, but offer not API for making use of it. Really missed chance.
    - LAC-Tech 2y ago
      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).
simonw 2y ago
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.

jFriedensreich 2y ago

from my own experience what notion did is pretty much required to use wasm sqlite with persistence in production. on the one hand being used at notions scale really points to wasm sqlite being ready for primetime, on the other hand there are still a LOT of ugly parts that need to be worked around: - 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

AlexErrant 2y ago

> OPFS doesn’t come with graceful handling of concurrency out of the box. Developers should be aware of this and design around it. 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 Love the shoutout to Roy Hashimoto. He's been writing VFSs for SQLite-on-the-browser and perf testing them. He's recently wrote "IDBMirrorVFS", which "is a new example VFS that keeps all SQLite files in memory while persisting to IndexedDB". It has remarkable performance, of course. https://github.com/rhashimoto/wa-sqlite/discussions/189 https://github.com/rhashimoto/wa-sqlite/discussions/189 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/examples/README.md#opfspermutedvfs https://github.com/rhashimoto/wa-sqlite/blob/master/src/exam...

jauntywundrkind 2y ago
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.
- jitl 2y ago
  The profusion of underpowered interfaces is frustrating but I don't see how browsers could "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!

slightwinder 2y ago

They have an actual sql-datebase on the client as cache, but still no offline-mode? Damn it. This must be their most demanded feature for years.

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

bearjaws 2y ago

Overall I think using SQLite locally to offload database work is incredibly powerful. Given most laptops have an SSD now a days, you can scan an entire 30-100MiB SQLite db in miliseconds. 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/ https://cluttr.pages.dev/ (working on deployment to https://cluttr.ai https://cluttr.ai later today)

bigbones 2y ago
> 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
- bearjaws 2y ago
  RDS with 256,000 IOPS (the max) on a single primary instance of MariaDB. 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.
NortySpock 2y ago
If you think those read speeds are great, try DuckDB (which has many SIMD improvements) if you want to blow your socks off.
- 2y ago

nikeee 2y ago

Sadly, SharedWorkers still are not supported on Chrome+Android. Hope that changes soon because it would be a game-changer for many applications to have this API broadly available.

stpn 2y ago

Pretty neat! If notion folks are in this thread, what do you do about lack of SharedWorker in Android Chrome?

jitl 2y ago
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.

RcouF1uZ4gsC 2y ago

Honestly, we should just have native SQLite supported by the browser. 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.

biftek 2y ago
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
mickael-kerjean 2y ago
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
infogulch 2y ago
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.

jakubmazanec 2y ago

Will the tables in the Notion mobile app still be so painfully slow? Every time I scroll few rows down (in a table with few than 100 rows with dozen columns), the app hangs for several seconds while trying to fetch/render (who knows what it's actually doing) few additional rows. When I edit a row and return back to the table view, scroll position is lost and I have to suffer through the fetching once again.

nailer 2y ago
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.
- ilrwbwrkhv 2y ago
  I think their real innovation was the community and getting "cosy productivity" during Covid going. They are more of a social phenomenon than a technical one.
  - threetonesun 2y ago
    Notion is a great example of my hatred of the current "upgrade or die" delivery method of software. Notion 1.0, when it was mostly text with a unique way to view tabular data, was fast and enjoyable to use. Once it shifted to competing with Atlassian et al, it acquired all the bloat those products already have.
    - nxobject 2y ago
      Sadly, I don’t think basing a business (with the tech investor desire for growth! growth! growth!) off one product, is best for an app that’s best when predictable.
    - satvikpendem 2y ago
      Notion was never fast, and I've been using it on and off for the past 10 years or so.

mrtksn 2y ago

Seeing things like that excite me a lot, I can't wait for a future where all the code is written in whatever language you like and runs through WASM in the browser. The HTML and JS can go back doing that thing they are built for: Displaying the UI and handling the interactions.

jitl 2y ago
displaying ui and handling interactions ends up being most of the app though
- jillesvangurp 2y ago
  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.

mortenjorck 2y ago

All this wonderful optimization, and yet there's still no UI to warn that (perhaps due to a websocket connection being lost?) a comment won't be saved. I lost two Notion comments this way yesterday!

born-jre 2y ago

after conquering all smartphones, browser, edge db and what not now every web page will have own instance of SQLite running in wasm, lol

solarkraft 2y ago

This is cool because I haven’t seen WASM SQlite in production. I wasn’t sure whether it’d end up being more performant than something built in like IndexedDB. Seeing that it’s practically feasible is definitely cool.

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

NelsonMinar 2y ago

Why is IndexedDB so bad that you have to load a second database in WASM instead? I've used the API successfully but never measured performance.

kevingadd 2y ago
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.
- NelsonMinar 2y ago
  yeah I gave up on the direct API, I used Dexie for my product. 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?
  - fastball 2y ago
    Yes, it's the worst of both. Dexie is at the mercy of the aforementioned issues with IDB (differing browser implementations, blocking of the main thread, hard to debug, can't actually guarantee persistence beyond "best effort"). The issues are much more prevalent on mobile browsers in our experience (iOS Safari in particular being the worst), to the point I think we'll need to move off it for our persistence layer in our own app and will unfortunately be forced to require our users to download wrapped apps (Capacitor and Electron) if they want offline persistence guarantees. But yes, the Dexie API is indeed a much better experience for working with IDB.
dbalatero 2y ago
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).
- 2y ago

remram 2y ago

Notion takes 15s to load to an empty page. Then another 5 to dismiss the popup about new AI features and the like. 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.

jamil7 2y ago
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.
alex_lav 2y ago
Curious where you're moving? About to begin finding a tool similar to Notion (but not Notion).
- ivanjermakov 2y ago
  I felt like a grandpa when using Notion, but haven't found a good alternative yet. As of now, I use google sheets for collaborative work and plain text/md files for notetaking.
- vevoe 2y ago
  I also moved away from Notion a while ago for being crazy slow. For personal note taking I use Obsidian, big fan of it. 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.
  - arrowsmith 2y ago
    I second the Obsidian recommendation.
  - remram 2y ago
    Not sure. We're currently using GitLab wikis a lot, oddly enough. Obsidian is nice but start time on mobile is bad too.

ggregoire 2y ago

> Using SQLite improved page navigation times by 20 percent in all modern browsers. 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.

jitl 2y ago
(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 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.

deleted 2y ago

[deleted]

bedatadriven 2y ago

This honestly sounds like a nightmare: multimegabyte wasm downloads, data corruption in production and byzantine hacks to coordinate writes between tabs. I am very grateful that we chose IndexedDB instead for our application!

jillesvangurp 2y ago

We've been using wasm sqlite with kotlin-js and the kotlin sqldelight framework. This is admittedly a bit of an exotic stack to be running in a browser. But it actually works surprisingly well. Unfortunately the failure modes with opfs are kind of ugly and need some attention. A key issue is controlling what happens when you have multiple tabs open interacting with the same DB. 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.

randyl 2y ago
Have you experienced the corruption issues mentioned in the original article?

aragonite 2y ago

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 https://github.com/w3c/csswg-drafts/issues/354

captn3m0 2y ago
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-databases-on-github-pages/#bonus-dom-as-a-database https://phiresky.github.io/blog/2021/hosting-sqlite-database...
- aragonite 2y ago
  Thank you!

cageface 2y ago

OPFS is only available in Web Workers. I don't think this is correct: https://developer.mozilla.org/en-US/docs/Web/API/File_System_API/Origin_private_file_system#manipulating_the_opfs_from_the_main_thread https://developer.mozilla.org/en-US/docs/Web/API/File_System...

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

taf2 2y ago

I don’t see why they need to have a worker and a shared worker .. couldn’t they run the sqlite in the sharedworker directly?

carlo-notion 2y ago
SharedWorkers can't use OPFS, unfortunately.
fastball 2y ago
It's for multi-tab support.