71–80 of 330 posts
Question for people using SQLite in prod: how do you cope if your app is running on a platform like Heroku or Cloud Run, rather than a proper server or VM? Have you found a solution for the fact that those environments, and disk, is ephemeral?
My manager currently runs a number of personal sites with a SQLite backend and they all seem very performant so I have been honestly considering giving it a second look.
Earlier quoted context omitted.
With 4 Gb you might as well just load the data into RAM.
But then you have to implement all the SELECT and DML logic yourself. SQL makes this a breeze with JOIN, ON UPDATE CASCADE, etc. And being SQL, it is very easy to maintain, even by the PFY that replaces you.
Since this is a no-update and no live-insert scenario we're talking about, it's fairly easy to produce code that is an order of magnitude faster than a DBMS, since they're not only primarily optimized for efficiently reading off disk (an in-memory hash table beats a B-tree every day of the week), they've got really unfortunate CPU cache characteristics, and additionally need to acquire read locks.
I’ve been using (locally) a Redis container for a very early prototype because it seems to be simple enough to use.
I know you can query json strings in salute but that’s not quite the same thing. For one redis offers some geo features.
I also built a Google Go library wrapping the sql amalgamation file and then cross compiled it for Android and iOS but with some more SQLite extension (GIS), which the stock Android/iOS SQLite did not have. This was some time in 2017 I guess.
I am a big fan of SQLite. You can integrate it in all kinds of stuff and adapt it to your needs. Compiling it is also straightforward.
Earlier quoted context omitted.
But then you have to implement all the SELECT and DML logic yourself. SQL makes this a breeze with JOIN, ON UPDATE CASCADE, etc. And being SQL, it is very easy to maintain, even by the PFY that replaces you.
SQLite (also H2 and some other embedded SQL databases) can be used entirely in-memory, one can also drop an SQLite file on a RAM-hosted filesystem (tmpfs/ramdrive). You really can put everything into RAM (and still enjoy SQL) if you have enough, don't mind long cold-load and potential data loss.
Side question: what’s something as simple as SQLite, but more of an unstructured key-value store? I’ve been using (locally) a Redis container for a very early prototype because it seems to be simple enough to use. I know you can query json strings in salute but that’s not quite the same thing. For one redis offers some geo features.
Even though SQLite can handle 99% of peoples use cases, WAL2 + BEGIN TRANSACTION will greatly close that last 1% gap.
b. Expensify has created a client/server database based on SQLite called https://bedrockdb.com and years ago it was scaling to 4M+ qps https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...