Comment by bigiain | Hacker News Reader

Comment by bigiain | Hacker News Reader

Parent Full thread

Back in the day (and I'm talking about when memcached was still written in Perl here, so late 90s or so) "bursty-write workloads" were a really good fit for us - using SQL queries as keys and storing the responses as values. For every read you first do a (super fast) memcached query using the SQL you're about to run as the key. If it exists, you just send the value back without running the query, if it doesn't exist you run the query and store the response as the value before returning it.

It depends a lot on how big your dataset is, and the distribution of reads across your data. But if you have a "fat head long tail" type distribution it can work really well.

(Of course cache invalidation is always a hard problem, which we worked around by trying to ensure all our code that did database writes knew how to delete relevant memcached entries, force expiring the whole cache at our lowest traffic times, and having a big red panic button that would delete the entire cache if needed.)

> For every read you first do a (super fast) memcached query using the SQL you're about to run as the key. If it exists, you just send the value back without running the query, if it doesn't exist you run the query and store the response as the value before returning it.

Wouldn't it have been far simpler to just move the db behind a service that provides a cacheable request-response interface and just adopt the simplest of caching strategies? In the case of HTTP, you can even get local cache for free. No need to throw specialized memory cache services around.

Like I said, I was doing that with memcached about 25-30 years back. Nobody had yet built "a service that provides a cacheable request-response interface" to move the db behind.

Just last week I helped one of our devs through "caching" API responses for a "read heavy" use case by saving the responses to S3 and having the web app there instead of hitting the API endpoint and database.

There are a bunch of ways to skin this cat in 2024.

(One brilliant idea I've got bookmarked but haven't (yet) had an opportunity toi use, is a modified SQLite that uses S3 storage and range requests and is compiles to WASM - so a web app can run SQL queries locally and all the usual SQLite stuff "just works" from the warm/javascript's point of view. It _does_ need to be a very read-heavy use case though, because updating even a single row needs to upload the entire SQLite db file into S3.)

> so a web app can run SQL queries locally and all the usual SQLite stuff "just works" from the warm/javascript's point of view

Duckdb-wasm can do this.

Does it do the S3 storage and http range requests to seek to the needed byte offsets thing?

I think one day I'm gonna have a thing where I need a few GB or tens of GB of database to be available in a web or mobile app, but where that db rarely changes, and I want to do this without having to support/secure a backend platform or database.

Parquet.

i've been playing around with this idea on and off for a while. this was the original demo/implementation afaik: https://phiresky.github.io/blog/2021/hosting-sqlite-database....

this has all sorts of nice properties if it suits your needs, especially if you have lots of small read-only databases that just receive updates occasionally.

there's a little demo i did here: https://billywhizz.io/squeel-demo/

Reply on news.ycombinator.com