4 ms·

In my experience, HA stories of Postgres are always buggy and disappointing for homegrown systems. None of these tools actually gives you a solid HA like propri

In my experience, HA stories of Postgres are always buggy and disappointing for homegrown systems. None of these tools actually gives you a solid HA like proprietary databases do. Managed database services can reduce or eliminate some of the problems, but you can't put critical parts of your system on the cloud.

teaearlgraycold 3y ago
> you can't put critical parts of your system on the cloud This seems highly dependent on how you define “critical”. I think most people’s definition allows for everything to be in the cloud.
jgimenez 3y ago
What would you recommend instead?
- feverzsj 3y ago
  If you have enough budget, go for proprietary databases. StackOverflow runs on a single Sql Sever instance with a hot standby. The tooling is just much better and easy to use. The save on maintenance solely is worthy. The free alternative would be Mysql/Mariadb + Galera Cluster. Not as solid as proprietary ones, but far easier to use and less buggy than Postgres + tons of tools.
  - dikei 3y ago
    >> The free alternative would be Mysql/Mariadb + Galera Cluster. Not as solid as proprietary ones, but far easier to use and less buggy than Postgres + tons of tools. Until someone accidentally run an expensive DDL on your Galera Cluster: now your cluster is down for hours without anyway to cancel that query except nuking the entire database and restore from backup.
sargun 3y ago
Having run Oracle RAC, MySQL circular replication, Postgres replication since 9.X, Amazon RDS, and others in production, I can say that Oracle RAC was the worst experience. We had to provide our own active / active storage backend, and fabric. If there were any hiccups, the entire system fell over. The horizontal scalability was nice, but if you caused I/O saturation on the backend, you'd end up knocking over the entire cluster due to shared components. Several times, the entire DB just "broke" (this was a couple scenarios, one where the DB was using 100% of CPU, one where the DB was frozen, and not allowing connections, and things like hung queries that couldn't be stopped), and it required restarting the whole cluster, for which there was minimal tooling. Perhaps SQL server is better, but that comes with an entirely new ecosystem, and other problems. My biggest thing is being able to crack open the DB, and look at the source code when it breaks. In this, the likes of Datastax Cassandra and Cockroachdb are great, but I wouldn't call them "proprietary" by any means.
- pi-e-sigma 3y ago
  To this day only Oracle RAC provides true active-active cluster. All the other things are not real clusters, just replications.
  - sargun 3y ago
    err, CockroachDB would like a word... Although, I'm not sure if its just data serving that's active-active, or query execution is distributed. Also, Cassandra is active-active as well. I'm sure there are others, but I'm less familiar with them (Yugabyte, Couchbase, etc..)
    - pi-e-sigma 3y ago
      Active-active is when you don't have any replicas, you have just one data store and multiple 'engines' accessing it. So there is no replication, because there is nothing to replicate. So CockroachDB is definitively not active-active, despite their claims to the otherwise; what they call active-active is two-way synchronous replication, which is worse than asynchronous replication because of the latency and worse than Oracle RAC active-active because it's less fail-tolerant. In Oracle RAC your cluster is up and running even if just one engine is still alive, in CockroachDB approach when you lose majority of nodes the cluster goes down. Obviously Oracle RAC active-active has it's own downsides, too, but it's another topic. EDIT to expand: Think about a SQL transaction. In Oracle RAC it gets picked up by any of the nodes and is executed by this node. All the other nodes see updated data immediately. In CockroachDB the same transaction would need to be executed by each of the nodes separately plus the overhead of communication between the nodes.
magden 3y ago
There are good and solid HA options in the Postgres ecosystem that are fully open. You can use Patroni for standard Postgres or YugabyteDB that is fault-tolerant by design. Specifics are here: https://www.yugabyte.com/postgresql/postgresql-high-availability/ https://www.yugabyte.com/postgresql/postgresql-high-availabi...

teaearlgraycold 3y ago

> you can't put critical parts of your system on the cloud This seems highly dependent on how you define “critical”. I think most people’s definition allows for everything to be in the cloud.

jgimenez 3y ago

What would you recommend instead?

feverzsj 3y ago
If you have enough budget, go for proprietary databases. StackOverflow runs on a single Sql Sever instance with a hot standby. The tooling is just much better and easy to use. The save on maintenance solely is worthy. The free alternative would be Mysql/Mariadb + Galera Cluster. Not as solid as proprietary ones, but far easier to use and less buggy than Postgres + tons of tools.
- dikei 3y ago
  >> The free alternative would be Mysql/Mariadb + Galera Cluster. Not as solid as proprietary ones, but far easier to use and less buggy than Postgres + tons of tools. Until someone accidentally run an expensive DDL on your Galera Cluster: now your cluster is down for hours without anyway to cancel that query except nuking the entire database and restore from backup.

sargun 3y ago

Having run Oracle RAC, MySQL circular replication, Postgres replication since 9.X, Amazon RDS, and others in production, I can say that Oracle RAC was the worst experience. We had to provide our own active / active storage backend, and fabric. If there were any hiccups, the entire system fell over. The horizontal scalability was nice, but if you caused I/O saturation on the backend, you'd end up knocking over the entire cluster due to shared components. Several times, the entire DB just "broke" (this was a couple scenarios, one where the DB was using 100% of CPU, one where the DB was frozen, and not allowing connections, and things like hung queries that couldn't be stopped), and it required restarting the whole cluster, for which there was minimal tooling. Perhaps SQL server is better, but that comes with an entirely new ecosystem, and other problems. My biggest thing is being able to crack open the DB, and look at the source code when it breaks. In this, the likes of Datastax Cassandra and Cockroachdb are great, but I wouldn't call them "proprietary" by any means.

pi-e-sigma 3y ago
To this day only Oracle RAC provides true active-active cluster. All the other things are not real clusters, just replications.
- sargun 3y ago
  err, CockroachDB would like a word... Although, I'm not sure if its just data serving that's active-active, or query execution is distributed. Also, Cassandra is active-active as well. I'm sure there are others, but I'm less familiar with them (Yugabyte, Couchbase, etc..)
  - pi-e-sigma 3y ago
    Active-active is when you don't have any replicas, you have just one data store and multiple 'engines' accessing it. So there is no replication, because there is nothing to replicate. So CockroachDB is definitively not active-active, despite their claims to the otherwise; what they call active-active is two-way synchronous replication, which is worse than asynchronous replication because of the latency and worse than Oracle RAC active-active because it's less fail-tolerant. In Oracle RAC your cluster is up and running even if just one engine is still alive, in CockroachDB approach when you lose majority of nodes the cluster goes down. Obviously Oracle RAC active-active has it's own downsides, too, but it's another topic. EDIT to expand: Think about a SQL transaction. In Oracle RAC it gets picked up by any of the nodes and is executed by this node. All the other nodes see updated data immediately. In CockroachDB the same transaction would need to be executed by each of the nodes separately plus the overhead of communication between the nodes.

magden 3y ago

There are good and solid HA options in the Postgres ecosystem that are fully open. You can use Patroni for standard Postgres or YugabyteDB that is fault-tolerant by design. Specifics are here: https://www.yugabyte.com/postgresql/postgresql-high-availability/ https://www.yugabyte.com/postgresql/postgresql-high-availabi...