Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work"
HA is one of those things where MySQL wins hands down, sadly. Sadly in the sense that PostgreSQL HA still looks like a couple of hacks held together with duct tape, at least when compared to MySQL solutions.
The Percona MySQL distribution has multi-master HA (Percona Xtradb Cluster) which is great for HA, and vanilla MySQL 8.x has group replication which iirc also is multi-master. MariaDB instead has multi-master based on Galera Cluster (although i'm not very knowledgeable about MariaDB).
In PostgreSQL world there are many HA solutions, none of which is both multi-master and open source (sadly).
Only people who haven't run MySQL multi-master HA at scale praise it. In reality, it's a world of pain, where one single seemingly innocent query can take down the entire cluster.
Speaking from operation experience with MariaDB Galera and Percona Xtradb Cluster (which is also Galera).
Only people who haven't run MySQL multi-master HA at scale praise it.
This statement (and the following phrases) is so vague that the only sensible thing that I can reply is that you're probably using it wrong, and that you should go back to the fine manual.
We run (several) Percona XtraDB Cluster (PXC) and even though there is some occasional crash (usually a node that goes into OOM) it's very stable. We never had, so far, a whole cluster go down. In the worst case a single node was still up, and was operative while we recovered the other nodes.
Of course stability doesn't come from for free: we spent some time using PMM (Percona Monitoring&Management) to find bottlenecks, and we keep PMM around for developers to troubleshoot performance issues on their own.
At the same time we just don't grant risky permissions to developers. One of the riskiest grants when dealing with PXC is ALTER TABLE, which requires the appropriate tooling (pt-online-schema change from the percona toolkit) and also requires following a procedure. Need an ALTER? Open a ticket. Ez.
What can I say?
If PXC doesn't work well for you, you're probably using it wrong.
In practice, you should typically only send write traffic to 1 galera node, and only fall back to "multi-master" for a split-second when promoting another galera node as the new writer node, either due to scheduled maintenance or unexpected downtime on the old writer node. This setup works great when being fronted by a fleet of stateless proxysql nodes. The whole DBA team can sleep well, without having to worry about whether the automated failover logic would actually work or not.
Also note that galera is just a set of API (https://github.com/codership/wsrep-API), which can be implemented against other databases to bring the same replication capability.
In practice, you should typically only send write traffic to 1 galera node
Yes, we did that too, it's the only way to reduce the deadlock and certification failure with Galera. However, it won't prevent a single `ALTER TABLE` to lock up the entire cluster. You can use `pt-online-schema-change` to workaround that, but it's not perfect.
We've switched to simple Asynchronous Replication with Openark Orchestrator for the majority of our clusters and the DBA team is much happier now.
Comments
HA is one of those things where MySQL wins hands down, sadly. Sadly in the sense that PostgreSQL HA still looks like a couple of hacks held together with duct tape, at least when compared to MySQL solutions.
The Percona MySQL distribution has multi-master HA (Percona Xtradb Cluster) which is great for HA, and vanilla MySQL 8.x has group replication which iirc also is multi-master. MariaDB instead has multi-master based on Galera Cluster (although i'm not very knowledgeable about MariaDB).
In PostgreSQL world there are many HA solutions, none of which is both multi-master and open source (sadly).
Only people who haven't run MySQL multi-master HA at scale praise it. In reality, it's a world of pain, where one single seemingly innocent query can take down the entire cluster.
Speaking from operation experience with MariaDB Galera and Percona Xtradb Cluster (which is also Galera).
This statement (and the following phrases) is so vague that the only sensible thing that I can reply is that you're probably using it wrong, and that you should go back to the fine manual.
We run (several) Percona XtraDB Cluster (PXC) and even though there is some occasional crash (usually a node that goes into OOM) it's very stable. We never had, so far, a whole cluster go down. In the worst case a single node was still up, and was operative while we recovered the other nodes.
Of course stability doesn't come from for free: we spent some time using PMM (Percona Monitoring&Management) to find bottlenecks, and we keep PMM around for developers to troubleshoot performance issues on their own.
At the same time we just don't grant risky permissions to developers. One of the riskiest grants when dealing with PXC is ALTER TABLE, which requires the appropriate tooling (pt-online-schema change from the percona toolkit) and also requires following a procedure. Need an ALTER? Open a ticket. Ez.
What can I say?
If PXC doesn't work well for you, you're probably using it wrong.
In practice, you should typically only send write traffic to 1 galera node, and only fall back to "multi-master" for a split-second when promoting another galera node as the new writer node, either due to scheduled maintenance or unexpected downtime on the old writer node. This setup works great when being fronted by a fleet of stateless proxysql nodes. The whole DBA team can sleep well, without having to worry about whether the automated failover logic would actually work or not.
Also note that galera is just a set of API (https://github.com/codership/wsrep-API), which can be implemented against other databases to bring the same replication capability.
Yes, we did that too, it's the only way to reduce the deadlock and certification failure with Galera. However, it won't prevent a single `ALTER TABLE` to lock up the entire cluster. You can use `pt-online-schema-change` to workaround that, but it's not perfect.
We've switched to simple Asynchronous Replication with Openark Orchestrator for the majority of our clusters and the DBA team is much happier now.