OT but what pains did you experience with matrix / what would you have done differently? this'd make a good blog post/HN article at this moment imo.

The issue with Synapse is the same as I currently have with Lemmy - lack of HA. One particular machine goes down and the system is 100% unavailable. And most of my machines are hardware I own, located at homes (mine, parents, etc) in different countries, so they're slightly more prone to random power or uplink outages than AWS' us-east-1. Hobbyist-grade geo-distributed cluster, hah :)

I've solved this for my email (two mailhosts on different continents, Dovecot dsync replication for mailboxes) and my storage (Minio works, and I'm slowly experimenting with Tahoe-LAFS), but I haven't solved it for Matrix.

Dendrite supports distributed deployments and reportedly works with CockroachDB (albeit not officially supported). Not sure what Conduit supports (last time I've checked it was all about embedded databases like sqlite, rocksdb, sled and persy), but it has pluggable database backend architecture so when I'll finally decide I want to fix my stuff, I'd certainly check it out as well.

We’re also working on account portability (MSC4014) that would eventually support active/active at the matrix layer, but it’s not ready tet.

PostgreSQL replication is hard for me. I know how to set up "classic" replication, but automatic failover and recovery that can solve split-brain issues is, sadly, not something I've figured out. And if it's not the machine, but the network that goes down (which happens few times a year on consumer-grade connections), it's a problem that happens in practice. I have learned how to repair nodes by hand, but I don't want to do this.

So far, I've managed to avoid thinking much of it by using distributed databases (CockroachDB and Consul, in particular) that already have all the magic built-in, so I just have to be careful with the settings.

Maybe I just need to learn how to deal with PostgreSQL...

(if you have a third-party commercial PG extension with clustering, like EnterpriseDB or CitusDB, ask them, they'll tell you what they want you doing.)

there is no inherent clustering support in postgres. You can't scatter tables across servers/etc. It's not supported. Always one master server - if you need more storage you can use SAS/fibrechannel/etc of course but there is one postgres instance that's in charge.

All of the postgres replication idioms rely on that model: the WAL is reliable up to the point it's been received/verified. If you've faithfully played out the WAL throughout your existence, everyone's storage instance is at the exact same state and the only thing that matters is who's got the most recent timestamp from the host.

This also includes bringup strategies: if you have an atomically checksummed snapshot of the datasets (all storage, and pg_wal, at the same instant) then you can also spawn new nodes. ZFS can do that. And with an incremental copy-on-write you can replicate the base state, then replicate only the changes (which is much faster) and then fail over quickly. That's popular for other applications that don't have sophisticated ACID stuff.

Anyway short version is, I'd think about a simple blue-green system (or round-robin system) where you can set an environment flag, and blue frontend nodes can simply choose to direct traffic to green backend's pgbouncer or similar when they see that. Then you flip web traffic over. Then you shut down the blue pg instance, and stuff fails over gracefully. The "healthy", "progressing", and "migrated" states, if that makes sense. And there is always one healthy node, even if you need to bring it up from backups. Yes, you have to look whether the other side is still healthy but that is now a k8s/pod problem and not an app one.

If you can live with an instance in the US and one in Europe or similar, a simple blue/green failover is probably easiest.