Show HN: Open source, logical multi-master PostgreSQL replication

I tried to escape this world as quickly as possible, realizing how horrible it was, but the largest issue I ran into was around IO. Creating an environment that was highly tolerant to fault while having little to no replication delay meant checking in on the master database frequently. Keeping in mind this was around 2010 I found that the IO load on these databases was substantially larger than any database that i had ever worked on before. Things like available file handlers and other related performance problems came up more frequently than I’ve ever experienced before and frankly more frequently than I’ve ever experienced since.

If I was to summarize it, I would just say the performance characteristics were not something I was used to experiencing and often they would surprise me when they occurred, which meant having a good quality of a while for running this application was very challenging.

Cool project!

How do you generate the timestamps for last writer wins? What happens if there is a tie?

Just my 2c: if I see a distributed database, the first question I ask is how it handles distributed transactions. Perhaps this topic should be higher on your FAQ, currently it is the 21st question.

Hi, Thanks for the reply. This is what i figured too. So there is essentially no way to achieve this without service downtime when using application which is not written to handle those kind of situations (eg. 3rd party things).

That's just the point, it always sounds like a great idea to people not experienced in database operations.

The problem with the setup is you will have a data corruption issue at some point. It's not an "if" it's a "when". If you don't have a plan to deal with it, then you're hosed.

This is why the parent is turning around the burden of proof. If you can't definitely say why you absolutely need this, and no other solution will do, then avoid it.

I replied above with some problems I experienced, but this question is slightly different, so I'll add more here.

IME - both at a place using active-active, and at places that suggested using it - the core issue is developer competency. People in general like to think of themselves as above average in most areas of life (e.g. "I'm an above-average driver"). I'm certainly not excluded from this, but over the last several years, I like to think I've become self-aware enough to understand my own limitations, and to know what I am and am not an expert in.

So, you'll get devs who read some blog posts, and then when the CTO announces that they're going multi-region, they rush forward with the excitement of people not yet hardened by the horrors of distributed systems. They're probably running a distributed monolith, because obviously the original monolith had to be decomposed into micro services for trendy reasons, but since that wasn't done well, they now have a dependency chain, each with its own sub-dependencies.

There is also a general lack of understanding of computing fundamentals in the industry. By fundamentals, I mean knowledge of concepts like latency (and the relative latency of CPU cache levels, RAM, disk, network, etc.), IOPS, etc. People love to believe that these lower-order elements have been abstracted away, but abstractions leak, and then you're stuck. There are also more practical skills that I wrongly assumed were universal, like the ability to profile one's code, read logs, and read technical documentation for the tools you're using.

Finally, there is an overwhelming desire to over-complicate, and to build anew instead of using existing and proven technology. Why run HAProxy when you can build your own little health checker for fun in NodeJS (this actually happened to me)? Sure, we could redesign our schema to have better normalization, and stop using UUIDv4 PKs so our pages aren't scattered all around the B+tree, or we could just rent bigger servers, and add another caching layer.

That's not multi-master in the typical sense, it's sharding, and done correctly, you shouldn't have any write conflicts because each shard should be strongly consistent within itself.

Typically a strongly consistent (CP) system works by having a single elected master where writes are only ack'd when they're written to the majority of the cluster. The downside of this system is you need majority of the cluster working and up-to-date and the performance impact of doing this.

A multi-master system is generally ( AP ) allows writes to any master node, but has some consensus algorithm where it picks and chooses winners based on conflicting writes. It should be faster and more available at the cost of potentially lost data.

There are some systems that claim to beat CAP but they typically have caveats and assurances that are required. After-all, if you ack a write, and then that node blows up, how will it ever sync?

To clarify, I was working with 2nd Quadrant BDR (now Enterprise DB Postgres Distributed), running on some extremely large EC2 instances, in a global mesh - IIRC, five regions. Also in fairness, EDB told us that we were the largest mesh they had seen, and so we frequently ran into fun edge cases.

Each node had N replicas running vanilla Postgres attached, which were on EC2s with node-local NVMe drives for higher performance. This was absolutely necessary for the application. There were also a smattering of Aurora Postgres instances attached, which the data folk used for analytics.

In no particular order:

* DDL is a nightmare. BDR by default will replicate DDL statements across the mesh, but the locking characteristics combined with the latency between `ap-southeast-2` and `us-east-1` (for example) meant that we couldn't use it; thus, we had to execute it separately on each node. Also, since the attached Aurora instances were blissfully unaware of anything but themselves, for any table-level operations (e.g. adding a column), we had to execute it on those first, lest we start building up WAL at an uncomfortable pace due to replication errors.

* You know how it's common to run without FK constraints, because "scalability," etc.? Imagine the fun of having devs manage referential integrity combined with eventual consistency across a global mesh.

* Things like maximum network throughput start to become concerns. Tbf, this is more due to modern development's tendency to use JSON everywhere, and to have heavily denormalized tables, but it's magnified by the need to have those changes replicated globally.

* Hiring is _hard_. I can already hear people saying, "well, you were running on bare EC2s," and sure, that requires Linux administration knowledge as a baseline - I promise you, that's a benefit. To effectively manage a multi-master RDBMS cluster, you need to know how to expertly administrate and troubleshoot the RDBMS itself, and to fully understand the implications and effects of some of those settings, you need to have a good handle on Linux. You're also almost certainly going to be doing some kernel parameter tuning. Plus, in the modern tech world, infra is declared in IaC, so you need to understand Terraform, etc. You're probably going to be writing various scripts, so you need to know shell and Python.

There were probably more, but those are the main ones that come to mind.

You're actually 100% correct! CockroachDB is only 57.25% compatible with standard PostgreSQL (according to https://pgscorecard.com, which details the way it comes up with these numbers) whereas we are 100% compatible (and 100% open-source, whereas they are source-available).

It technically is source-available, as of Nov 2024, anyway: https://news.itsfoss.com/cockcroachdb-no-open-source/

So yes, license (and compatibility - see https://pgscorecard.com) are two major differences between pgEdge and CockroachDB.

pgEdge version updates also come in very close alignment with upstream PostgreSQL intentionally to make sure security patches/bugfixes and the latest features get to users ASAP.

You can't even run pgbench unaltered on CockroachDB, as simple table structures and indexes are fundamentally different there. It is in no way a compatible product, and never has been.