Re:Why you should probably be using SQLite
by binwiederhier on Friday October 27, 2023 @11:47AM
(#38040064)
> 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.
Re:Why you should probably be using SQLite
by fodkodrasz on Saturday October 28, 2023 @02:57AM
(#38047774)
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.
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.