5 ms·

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-100Mi

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.
- codetrotter 2y ago
  Do these SIMD improvements still apply if you’re compiling DuckDB to WASM and run it in a browser?
  - nilslice 2y ago
    Yes, if the browser enables SIMD! https://caniuse.com/wasm-simd https://caniuse.com/wasm-simd
- bearjaws 2y ago
  This solutions scales to tens of thousands of images in my testing (probably ~200k or more before searching is too slow). Why use a nuclear bomb when a stick of dynamite is good enough :)
- drozycki 2y ago
  Isn’t one OLTP and the other OLAP? I don’t understand why DuckDB is often suggested as a drop-in replacement.
  - NortySpock 2y ago
    Yes, DuckDB is OLAP, SQLite is OLTP. I should have called that out. 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.
bastawhiz 2y ago
> 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.
- stackskipton 2y ago
  I'd also point out about AWS cost, which is pretty high, getting IOPS is easy. Getting Redundant IOPS is hard part.
  - Spivak 2y ago
    Huh? Not really. AWS is a marvel of engineering in that it can be everyone's redundant IOPS but being your IOPS isn't such a big deal. Most folks are single region and rent-a-datacenter colo operations can get you two racks with separate power/uplink no issue. 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.
bearjaws 2y ago
It's now live at https://cluttr.ai 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.
WuxiFingerHold 2y ago
A central Postgres (RDS) and a local SQLite are pretty for different use cases. You can't compare it.
CyberDildonics 2y ago
On a very slow SSD SQLite can query a 20 gigabyte database file in milliseconds.
resonious 2y ago
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.
- lenkite 2y ago
  SQLite is not SQL standards compliant. It doesn't even support data types from core SQL-92. One needs more than INTEGER, REAL, TEXT, BLOB for applications. Adopting it as WebSQL would have been a terrible mistake.
  - AntonCTO 2y ago
    Is IndexedDB SQL standards compliant? Inventing IndexedDB is a terrible mistake.
    - lenkite 2y ago
      Fully agreed. Lets not compound a mistake with another mistake.
      - resonious 2y ago
        Fair enough. But if I could pick just one mistake, I'd pick SQLite.

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.

codetrotter 2y ago
Do these SIMD improvements still apply if you’re compiling DuckDB to WASM and run it in a browser?
- nilslice 2y ago
  Yes, if the browser enables SIMD! https://caniuse.com/wasm-simd https://caniuse.com/wasm-simd
bearjaws 2y ago
This solutions scales to tens of thousands of images in my testing (probably ~200k or more before searching is too slow). Why use a nuclear bomb when a stick of dynamite is good enough :)
drozycki 2y ago
Isn’t one OLTP and the other OLAP? I don’t understand why DuckDB is often suggested as a drop-in replacement.
- NortySpock 2y ago
  Yes, DuckDB is OLAP, SQLite is OLTP. I should have called that out. 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.

bastawhiz 2y ago

> 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.

stackskipton 2y ago
I'd also point out about AWS cost, which is pretty high, getting IOPS is easy. Getting Redundant IOPS is hard part.
- Spivak 2y ago
  Huh? Not really. AWS is a marvel of engineering in that it can be everyone's redundant IOPS but being your IOPS isn't such a big deal. Most folks are single region and rent-a-datacenter colo operations can get you two racks with separate power/uplink no issue. 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.

bearjaws 2y ago

It's now live at https://cluttr.ai 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.

WuxiFingerHold 2y ago

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

CyberDildonics 2y ago

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

resonious 2y ago

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.

lenkite 2y ago
SQLite is not SQL standards compliant. It doesn't even support data types from core SQL-92. One needs more than INTEGER, REAL, TEXT, BLOB for applications. Adopting it as WebSQL would have been a terrible mistake.
- AntonCTO 2y ago
  Is IndexedDB SQL standards compliant? Inventing IndexedDB is a terrible mistake.
  - lenkite 2y ago
    Fully agreed. Lets not compound a mistake with another mistake.
    - resonious 2y ago
      Fair enough. But if I could pick just one mistake, I'd pick SQLite.