I've seen a lot of Sqlite hype here in the past weeks and months.
Please excuse my ignorance, but what do all these Sqlite-but-make-it-X solutions offer over a simple, more established solution like (nobody ever got fired for choosing) Postgres?
Please excuse my ignorance, but what do all these Sqlite-but-make-it-X solutions offer over a simple, more established solution like (nobody ever got fired for choosing) Postgres?
I'm pretty into Litestream/LiteFS. Here's what I'm after:
1. Operational simplicity. Fly.io devs run small app servers in a bunch of places. They're usually read heavy. Running network database servers gets very complicated, very fast. You need a DB node in each place your app server lives.
2. Graceful failure. When you scatter app servers around the world, internet weather causes problems for an app. I want my DB to be ok when that happens. Reads should continue to work, if they can. And writes should fail in a way that makes it obvious what's happening.
3. Good for caching. Most fullstack apps use Postgres and then layer in Redis/Memcached for caching. This is yet another moving part. sqlite has amazing performance for cache workloads.
These all make an embedded DB interesting. If Postgres fails, your app server needs to know that Postgres failed and then also fail. Same if you add Redis. If your embedded DB fails, it's obvious to the app server that something is awry.
Another thing I'm after is drop in usage without touching code. We run a lot of peoples' apps and have limited ability to have them add libraries or write new code. Almost all of their frameworks speak sqlite, though.
Adding clustering to sqlite is not perfect. There are still networks to deal with and things will still break. All it's doing is shifting complexity and giving us different levers to use to keep things reliable.
Litestream/LiteFS are amazing projects. The FUSE-based approach is interesting (I'm implementing something similar in mvSQLite, thanks for the idea!)
> Graceful failure
mvSQLite is designed to continue to operate under degraded network (there is a fault-injection test specifically for checking this property: https://github.com/losfair/mvsqlite/blob/1dd1a80d2ff7263b07a...). Network errors and service unavailability are handled with idempotent retries and not exposed to the application.
> Good for caching
mvSQLite caches pages read and written, and does differential cache invalidation (only remotely modified pages are invalidated in the local page cache). The local cache is just a regular KV store with invalidation strategies, and can be moved onto the disk. So it essentially becomes a consistent local database snapshot.
For something larger, finding out writes to DB fail is not a big deal - apps have logs after all.
Another thing captured my attention is DB sizing/fault tolerance
1.keeping average sized DB of 100-300 GB locally, without much buffer pool/vfs cache with higher chances will slowdown the reads and joins. Something bigger like couple of TB should be even harder to do.
2. Syncing multiple concurrent writes can be even longer or on par - guessing here.
3. Both Redis and Postgres (and MySQL) can be (should be) kept behind LB. If node fails, clustering + LB will handle it. LBs of course can be present per location and provide local reads and forwarding writes to remote.
Regarding LB, probably simplest would be have different tcp ports for readwrite/readonly types of load. Reads can be split among multiple replicas be needed.
I have not used it for anything big, but from my experience small/average load (80k rps to Redis with 3Gbit of bandwidth) LB was not an issue. 500k rps may change the situation of course, not a silver bullet.
I can't speak for all, but keep in mind that SQLite does not require a server or sends requests over a network to get to the data. This means SQLite is a far simpler and cheaper deployment, which is totally acceptable if you don't have high reliability and horizontal scalability in mind. Sometimes it even outperforms production RDBMS.
Bolting on distributed access to SQLite adds horizontal scalability for (in the very least) cases where eventual consistency is more than enough to meet your requirements.