Hacker News Reader

Comment by binwiederhier | Hacker News Reader

Your comment implies that running a database cluster is easy, or easier than what OP describes. And while I do not endorse his suggestion of using SQLite in a multi node setup, I can tell you from personal experience that running a multi-node RDMS is hard as hell.

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.

View on HN

> Your comment implies that running a database cluster is easy

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.

Tangentially related: my personal open source project (ntfy.sh) is starting to outgrow a one node SQLite-backed setup, and I am now faced with the decision we are talking about here (something I would not have imagined, I might add): do I use some SQLite-replication thing, or do I rewrite it all to work with a RDMS and deal with managing that? It's quite the annoying choice, so I really wish there was a great SQLite backed solution, hehe.

If by "outgrow" you mean you want to improve concurrent read/write performance, availability, and throughput via replication, then I would -- personally, for peace of mind and ease -- look at just buying a managed RDBMS from someone with predictable pricing and using that. Buy PlanetScale, Aurora, Neon, CockroachDB Cloud, etc and let them handle it. PlanetScale and Aurora in particular have plans that let you fix the I/O costs and only pay for compute and storage, which is pretty attractive. They also use standard MySQL/Postgres, so you can migrate in/out to other providers later. It does mean you must spend time on the migration though, and spend time understanding the implications of your new storage choice (latency, failure modes, etc.)

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 on regular cadence -- thus turning the design from having a primary/standby to simply having two interchangeable systems that swap roles. 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.

I do not know what other architectural constraints you might have. These are just suggestions. Good luck!

I know it's heresy, but could you defer the decision by sharding? E.g. customers with an email that hashes to a positive number goes to instance A; others to instance B. That might buy you some time.

Can you throw more RAM and CPU at the problem instead?

I have and it works really well. This is less about SQLite not being able to handle it -- because it does -- and more about reliability and availability of the overall system. I don't want one server being down to affect the entire system.

https://github.com/maxpert/marmot is SQLite replication thats eventually consistent and doesnt require a master server.

Fascinating, thanks. I'll check it out.

Congrats, that's a great position to be in.

> some SQLite-replication thing

This feels like an uncharitable interpretation of the maturity of projects like LiteFS/rqlite/dqlite.

The vagueness of this comment and its perceived derogatory nature is merely a result of my ignorance around these technologies. It was not meant the way you perceived it.

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.

There is also Litestream.

And: the SQLite homepage runs on - guess it. Maybe they have some doc/blog describing how they manage their traffic.

There is one edge case where comparison makes sense: read only workloads. If you have a large, mostly static database (e.g. a car parts website) it can make sense to just sync a Sqlite file across all your servers.

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.

Underrated development strategy. If your max envisioned workload won't exceed what a single server can reasonably handle, this is orders of magnitude easier&cheaper&performant than anything else.

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.

You can get Dual AMD EPYC™ 9634 Processor 84-core 2.25GHz 384MB Cache with 3TB RAM and 200TB of SSD storage for less than a car. So 336 threads in 2u box it's really crazy.

> I can tell you from personal experience that running a multi-node RDMS is hard as hell

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.

> we have a very small cluster comprised of a main PostgreSQL instance and four replicas

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.

PG fully consistent automatic failovers are also pretty hard, at least they were 3-4 years ago when we did such a setup (initially it seemed easy, but tests showed its limits, and getting it reliable was pretty hard). Now I heard that there are Kubernetes based solution which are said to automate it pretty well, but have not tried those yet.

Imagine how hard it would be to manage SQLite clusters ;)

I did handwave it a bit, yes :)

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 :/

Reply on news.ycombinator.com