4 ms·

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 m

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.

tcgv 3y ago
> 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.
- binwiederhier 3y ago
  > 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-replication-and-automated-failover/ https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
  - fodkodrasz 3y ago
    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.
minimaul 3y ago
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 :/
beoberha 3y ago
Imagine how hard it would be to manage SQLite clusters ;)
Aurornis 3y ago
> 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.
- binwiederhier 3y ago
  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.
  - yawaramin 3y ago
    Congrats, that's a great position to be in.
  - robertlagrant 3y ago
    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.
  - simonw 3y ago
    Can you throw more RAM and CPU at the problem instead?
    - binwiederhier 3y ago
      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.
      - 3y ago
deleted 3y ago
[deleted]

tcgv 3y ago

> 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.

binwiederhier 3y ago
> 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-replication-and-automated-failover/ https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
- fodkodrasz 3y ago
  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.

minimaul 3y ago

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

beoberha 3y ago

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

Aurornis 3y ago

> 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.

binwiederhier 3y ago
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.
- yawaramin 3y ago
  Congrats, that's a great position to be in.
- robertlagrant 3y ago
  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.
- simonw 3y ago
  Can you throw more RAM and CPU at the problem instead?
  - binwiederhier 3y ago
    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.
    - 3y ago

deleted 3y ago

[deleted]