You obviously don’t need a cluster if you’re considering SQLite as an alternative.
Comparing a full database cluster to SQLite doesn’t make sense.
If you just want peace of mind and better disaster handling, and your current uptime, concurrent workload split, and performance is fine -- maybe your hosting provider eats it for an hour or two and you don't want to be hamstrung -- I would suggest just using a tool like Litestream, replicating the DB to S3, and setting up a hot standby server in some alternative region that you can fail over to and that actively synchronizes the working set. Always keep them up to date with deployment automation. Set up some alerting, and if a failure happens, terminate the main instance with prejudice, let the replica catch up by replaying any latest changes, and reorient your load balancer to point to it (Cloudflare tunnels are a good low-tech solution you could use to do that.) This might imply a small downtime window for the hot failover. For bonus points, you can automate this whole task, and actively perform it regularly, swapping between servers A/B on regular cadence -- and then you have good confidence in hot-failover disaster recovery. e.g. just do this whole dance every week on Monday at 1am UTC, and you can have confidence it works and will stay working.
This feels like an uncharitable interpretation of the maturity of projects like LiteFS/rqlite/dqlite.
That said, I looked at 2/3 a while ago:
- rqlite looks great and mature, though it has a HTTP interface, so I'll need to rewrite my database layer anyway to use it. At that point might as well to Postgres
- dqlite seemed like a failed Canonical experiment to me last time I checked, but looking again I think I may have had the wrong impression.
And: the SQLite homepage runs on - guess it. Maybe they have some doc/blog describing how they manage their traffic.
On the other hand, read replicas are also the easy case for database clusters. And just making your one database server bigger also scales a lot, there is nothing stopping you from adding a terabyte of RAM.
And just making your one database server
bigger also scales a lot, there is nothing
stopping you from adding a terabyte of RAM.
Additionally, I think a large percentage of the HN crowd underestimates what a single large-ish single server can handle these days. Probably because we are all used to only seeing tiny virtualized slices these days.
192+ threads, 2TB of RAM, terabytes of crazy fast PCIe storage. This was multiple racks of servers not too long ago, now in ~2U and it costs less than a car.
Relatively easy to get started with a 'production-grade' cluster.
And they typically don't require any external load balancers, connection poolers, etc.
I'd like to think once NewSQL DBs mature, we'll see a shift away from using/abusing SQLite for these kinds of use cases.
There exist varying levels of complexity. Here at my company, we have a very small cluster comprised of a main PostgreSQL instance and four replicas. It just works and requires very low maintenance effort.
But I imagine that things can get really messy if you're dealing with dozens or hundreds of instances, for sure, especially if you need sharding.
As you said, it depends on what you want:
- If you are okay with losing data in the event of a failover, then a simple setup is fine. - If you want lossless failovers (i.e. not a single transaction is lost), then that's hard. - If you want entirely automatic failovers, that's also very hard. - ...
My experience stems from MySQL, and not PostgreSQL, so this may or may not apply to pg. I wrote about my experience in this blog post: https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
What concerns me more than anything with a process like LiteFS is the 'unknown unknowns' perspective of what can go wrong - whereas I think MySQL and Postgres have generally more understood failure modes.
I also think that a lot of people in middle sized projects where they're not big enough to have a dedicated database team, etc will end up using some kind of managed database solution - something like Amazon Aurora, or RDS, or even someone like DigitalOcean's managed database product? Those generally take care of sensible configuration, backup, and failover at least.
edit: FWIW, I've done both ways - self-managed MySQL & Postgres clusters and managed services. You're right - getting MySQL in particular set up to be crash-safe etc requires careful documentation reading, and it's very frustrating that default installs of MySQL don't come already configured like this. In my eyes, safe-but-slow is a much better default than fast-but-risky :/
In $oldjob I designed and managed a system that was eventually running many hundreds of MySQL 3-node groups. MySQL in particular has a lot of nobs that you need to understand to set up a cluster that doesn't lose data during a fail over (semi sync, sync bin log, trx commit flush, ....). And then of course: neither MySQL nor PostgreSQL have a built-in mechanism to handle fail overs at all. It's another software stack around that, or even something custom built.
Again, not saying I agree with the author. I'm just saying you're portraying it easier than it actually is.