4 ms·
You need to consider some of what sqlite is good at. Querying local sqlite is very fast. Much lower latency than querying postgres. It's also very reliable – a
by mrkurt 4y ago
You need to consider some of what sqlite is good at. Querying local sqlite is very fast. Much lower latency than querying postgres. It's also very reliable – a local file will always work better than a network service.
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.
- losfair 4y agoHi mrkurt! 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/1dd1a80d2ff7263b07aac9cf990f027da3b7467a/.github/workflows/ci.yml#L224 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.
- mrkurt 4y agomvSQLite checks a lot of my boxes. The FoundationDB requirement makes it suboptimal for tiny little node/rails/laravel apps, though. If we were in the business of hosting FoundationDB it'd be perfect.
- CoolCold 4y agoI've got impression this approach is heavily focused towards tiny DBs with small amount of concurrent writes and single digit teams behind the project. 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.