5 ms·

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

verelo 1y ago
Interesting, i always see attempts to make these types of database tools as super interesting but then I think about all the undocumented edge cases that can come up and they scare me off. Many many years ago I worked on a monitoring tool that itself needed to be highly available, and we needed a solution like this. Ever since that time I've done everything in my power to avoid it. What are the real world cases you built this for? And how can someone like me who has been bruised by past experiences get comfortable with it?
- victor9000 1y ago
  What failure cases did you encounter?
- pgedge_postgres 1y ago
  Getting some examples of real-world cases to share and will comment back with them ASAP; in the meantime, would you mind sharing what undocumented edge cases you came across and what solutions you explored to handle them? It would help with sharing super relevant use cases :-)
  - verelo 1y ago
    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.
- pgedge_postgres 1y ago
  Just a guess, but some of the undocumented edge cases you saw might be explored in this blog from one of our software engineers, Shaun Thomas. It's all about conflict resolution & avoidance in PostgreSQL, in general: https://www.pgedge.com/blog/living-on-the-edge https://www.pgedge.com/blog/living-on-the-edge If understanding how conflicts are handled in pgEdge is helpful, here's a link to the docs on the subject: https://docs.pgedge.com/spock_ext/conflicts https://docs.pgedge.com/spock_ext/conflicts And the FAQ also delves into it some: https://www.pgedge.com/resources/faq https://www.pgedge.com/resources/faq
- baq 1y ago
  Typical use case would be a anyone who has global presence, but serves users in particular geos (think AWS): you want a global user database but it’s soooo convenient to be able to join with regional data in a single query.
- jwr 1y ago
  > edge cases that can come up and they scare me off They should! Read some of the excellent Jepsen analyses to see how scary things can be: https://jepsen.io/analyses https://jepsen.io/analyses
- vyruss 1y ago
  Local write latency in a geo-distributed database is also important for some use cases.
sgarland 1y ago
You do not want multi-master. If you think you do, think again. Source: I have operated a large multi-master Postgres cluster.
- bigwheels 1y ago
  I imagined this position would depend almost entirely on the requirements of the project. Are you able to elaborate on why it's a universal "NO" for you?
  - gtowey 1y ago
    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.
    - bigwheels 1y ago
      Believe it or not, Mrs. Bigwheels is pretty experienced in the database department. I've seen multi-master HA architecture work out great for 10M+ DAU games, and many/most other cases where I wouldn't recommend it- as in it wouldn't even enter my brain, because the tradeoffs are harsh. IME it comes down to considering CAP against the business goals, and taking into account how much it will annoy the development team(s). If you follow "the rules" WRT to writes, it may fit the bill. Especially these days with beauties like RDS. But then again, Aurora is pretty awesome, and did not exist/mature until only ~5 years ago or so. Definitely more of a wart than a pancea or silver bullet. Even still, I wouldn't dismiss outright, always keen to compare alternatives. Overall it sounds like we're in the same camp, heh.
      - porridgeraisin 1y ago
        What would you say are the primary tradeoffs?
tonyhart7 1y ago
how do they resolve write conflict????
- pgedge_postgres 1y ago
  The official FAQ has a good amount of info on how conflict resolution is handled (https://www.pgedge.com/resources/faq https://www.pgedge.com/resources/faq)! Relevant excerpt: "pgEdge offers eventual consistency between nodes using a configurable policy (e.g. last-writer-wins) for conflict resolution, along with conflict-free delta apply columns (i.e. CRDTs) for running sum fields. This allows for independent, concurrent and eventually consistent updates across multiple nodes." Some specific documentation on the subject: https://docs.pgedge.com/spock_ext/conflicts https://docs.pgedge.com/spock_ext/conflicts One of our solutions engineers (Paul Rothrock) created a video on this topic in the last month: https://www.youtube.com/watch?v=prkMkG0SOJE https://www.youtube.com/watch?v=prkMkG0SOJE And if you're interested in more information about conflict management in PostgreSQL clusters in general, this article ("Living on the Edge: Conflict Management and You") from Shaun Thomas is probably useful to check out: https://www.pgedge.com/blog/living-on-the-edge https://www.pgedge.com/blog/living-on-the-edge
OsrsNeedsf2P 1y ago
If both nodes approve an update on the same primary key, what happens? I don't see this crucial detail described in the README
- pgedge_postgres 1y ago
  Thanks for pointing out the lack of info on conflict resolution in the README! It's been reported and we'll look at getting that updated ASAP. In the meantime, you can find a lot of information in the official FAQ on how conflict resolution is handled (https://www.pgedge.com/resources/faq https://www.pgedge.com/resources/faq), but at-a-glance, "pgEdge offers eventual consistency between nodes using a configurable policy (e.g. last-writer-wins) for conflict resolution, along with conflict-free delta apply columns (i.e. CRDTs) for running sum fields. This allows for independent, concurrent and eventually consistent updates across multiple nodes."
  - n_u 1y ago
    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.
    - bonesmoses 1y ago
      There's an option in the Postgres configuration named "track_commit_timestamp" that does this automatically. It's required to be enabled when using LWW as the conflict resolution model. If there's a tie, the node with the highest node number wins.
- pgedge_postgres 1y ago
  As a note, there's also specific documentation regarding this: https://docs.pgedge.com/spock_ext/conflicts https://docs.pgedge.com/spock_ext/conflicts And, one of our solutions engineers (Paul Rothrock) has a video released a month ago on this topic as well: https://www.youtube.com/watch?v=prkMkG0SOJE https://www.youtube.com/watch?v=prkMkG0SOJE Sharing these alongside my other comment in case additional information is helpful :-)
imglorp 1y ago
Third party, multi master postgres is such an old idea, it was done in Perl... https://github.com/bucardo/bucardo https://github.com/bucardo/bucardo
- pgedge_postgres 1y ago
  We're not claiming to be a new idea, by any means :-) Unfortunately, Bucardo is no longer being updated. Our goal is simply to support continued innovation of distributed PostgreSQL along with similar tools for enabling high availability / scalability in PG deployments.
- vyruss 1y ago
  True, but Bucardo is trigger-based and does not use WAL-based logical replication, and is unmaintained. There is also a world of difference in performance between them.
- philipallstar 1y ago
  I don't see why this matters. Ideas are easy; execution and adoption are hard. Clearly Bucado didn't take off well enough that this is a solved problem.
throwawaygo 1y ago
pgactive?
- vyruss 1y ago
  pgactive has limitations with not supporting DDL, sequence management, column and row filtering, conflict and exception handling, incompatibility with native logical replication, etc. The license is also different (Apache 2.0 for pgactive vs PostgreSQL for Spock). Most importantly, it's not "supported anywhere" by AWS, just on RDS.
foreigner 1y ago
What are the pros and cons of this compared to CockroachDB?
- snthpy 1y ago
  Not the OP nor knowledgeable in this area but I would suspect / hope postgres compatibility as a start. The last time I looked into whether I could use cockroachdb as a backend for my Airflow cluster, it wasn't possible due to compatibility issues.
  - pgedge_postgres 1y ago
    You're actually 100% correct! CockroachDB is only 57.25% compatible with standard PostgreSQL (according to https://pgscorecard.com 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).
- znpy 1y ago
  License. CockroachDB moved to a license that I can’t even remember if it’s source-available anymore.
  - pgedge_postgres 1y ago
    It technically is source-available, as of Nov 2024, anyway: https://news.itsfoss.com/cockcroachdb-no-open-source/ https://news.itsfoss.com/cockcroachdb-no-open-source/ So yes, license (and compatibility - see https://pgscorecard.com 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.
- traceroute66 1y ago
  > compared to CockroachDB CockroachDB != PostgreSQL. I take great issue with the way CockroachDB marketing seeks to imply compatability, when infact what they are promising is wire protocol compatability (i.e. you can fire up your copy of psql on the CLI and it will connect). Last time I looked, a great number of primitive, obvious, fundamental, low-hanging fruit were completely absent from CockroachDB, e.g. (IIRC) stored procedures are nowhere to be seen in CockroachDB.
pisikesipelgas 1y ago
Hi, How do You guys resolve the application database DDL issue when multimaster is in use? One node gets updated, DDL is will be replicated (?) to second node, which is used by not-jet-updated application which is not compatible with updated database structure. This problem has bugged me for a while. And second and similar issue with most replication setups is let's take for postgis for example. Again in one node this extension gets updated. Now what? Data will be replicated to node which is not jet updated and cause whole system to be not functional.
- baq 1y ago
  It’s an engineering problem: you have to design the system so that it remains functional in this exact scenario - it follows that the system isn’t just code and build artifacts, but also its deployment processes.
  - pisikesipelgas 1y ago
    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).
    - baq 1y ago
      Again an engineering problem. You can deploy with zero downtime and people have been doing this for decades. It takes infrastructure like load balancers, ability to run versions in parallel and runtime support for feature flags, but it’s absolutely doable and ultimately just another day in the office for anyone with global operations. A lot of 3rd party tools actually support these workflows for this exact reason.
      - qaq 1y ago
        You gonna pause writes for cutover so while not downtime, specifically for postgres load balancers, ability to run versions in parallel not gonna help you there.
jwr 1y ago
Bear in mind this no longer provides the same consistency model as PostgreSQL does. It's not a straightforward extension of the nice serializable world. That might not be what you expect given the name, this does not provide a strict serializable consistency model. See https://jepsen.io/consistency/models https://jepsen.io/consistency/models for a classification of consistency models.

Interesting, i always see attempts to make these types of database tools as super interesting but then I think about all the undocumented edge cases that can come up and they scare me off. Many many years ago I worked on a monitoring tool that itself needed to be highly available, and we needed a solution like this. Ever since that time I've done everything in my power to avoid it. What are the real world cases you built this for? And how can someone like me who has been bruised by past experiences get comfortable with it?

victor9000 1y ago
What failure cases did you encounter?
pgedge_postgres 1y ago
Getting some examples of real-world cases to share and will comment back with them ASAP; in the meantime, would you mind sharing what undocumented edge cases you came across and what solutions you explored to handle them? It would help with sharing super relevant use cases :-)
- verelo 1y ago
  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.
pgedge_postgres 1y ago
Just a guess, but some of the undocumented edge cases you saw might be explored in this blog from one of our software engineers, Shaun Thomas. It's all about conflict resolution & avoidance in PostgreSQL, in general: https://www.pgedge.com/blog/living-on-the-edge https://www.pgedge.com/blog/living-on-the-edge If understanding how conflicts are handled in pgEdge is helpful, here's a link to the docs on the subject: https://docs.pgedge.com/spock_ext/conflicts https://docs.pgedge.com/spock_ext/conflicts And the FAQ also delves into it some: https://www.pgedge.com/resources/faq https://www.pgedge.com/resources/faq
baq 1y ago
Typical use case would be a anyone who has global presence, but serves users in particular geos (think AWS): you want a global user database but it’s soooo convenient to be able to join with regional data in a single query.
jwr 1y ago
> edge cases that can come up and they scare me off They should! Read some of the excellent Jepsen analyses to see how scary things can be: https://jepsen.io/analyses https://jepsen.io/analyses
vyruss 1y ago
Local write latency in a geo-distributed database is also important for some use cases.

sgarland 1y ago

You do not want multi-master. If you think you do, think again. Source: I have operated a large multi-master Postgres cluster.

bigwheels 1y ago
I imagined this position would depend almost entirely on the requirements of the project. Are you able to elaborate on why it's a universal "NO" for you?
- gtowey 1y ago
  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.
  - bigwheels 1y ago
    Believe it or not, Mrs. Bigwheels is pretty experienced in the database department. I've seen multi-master HA architecture work out great for 10M+ DAU games, and many/most other cases where I wouldn't recommend it- as in it wouldn't even enter my brain, because the tradeoffs are harsh. IME it comes down to considering CAP against the business goals, and taking into account how much it will annoy the development team(s). If you follow "the rules" WRT to writes, it may fit the bill. Especially these days with beauties like RDS. But then again, Aurora is pretty awesome, and did not exist/mature until only ~5 years ago or so. Definitely more of a wart than a pancea or silver bullet. Even still, I wouldn't dismiss outright, always keen to compare alternatives. Overall it sounds like we're in the same camp, heh.
    - porridgeraisin 1y ago
      What would you say are the primary tradeoffs?

tonyhart7 1y ago

how do they resolve write conflict????

pgedge_postgres 1y ago
The official FAQ has a good amount of info on how conflict resolution is handled (https://www.pgedge.com/resources/faq https://www.pgedge.com/resources/faq)! Relevant excerpt: "pgEdge offers eventual consistency between nodes using a configurable policy (e.g. last-writer-wins) for conflict resolution, along with conflict-free delta apply columns (i.e. CRDTs) for running sum fields. This allows for independent, concurrent and eventually consistent updates across multiple nodes." Some specific documentation on the subject: https://docs.pgedge.com/spock_ext/conflicts https://docs.pgedge.com/spock_ext/conflicts One of our solutions engineers (Paul Rothrock) created a video on this topic in the last month: https://www.youtube.com/watch?v=prkMkG0SOJE https://www.youtube.com/watch?v=prkMkG0SOJE And if you're interested in more information about conflict management in PostgreSQL clusters in general, this article ("Living on the Edge: Conflict Management and You") from Shaun Thomas is probably useful to check out: https://www.pgedge.com/blog/living-on-the-edge https://www.pgedge.com/blog/living-on-the-edge

OsrsNeedsf2P 1y ago

If both nodes approve an update on the same primary key, what happens? I don't see this crucial detail described in the README

pgedge_postgres 1y ago
Thanks for pointing out the lack of info on conflict resolution in the README! It's been reported and we'll look at getting that updated ASAP. In the meantime, you can find a lot of information in the official FAQ on how conflict resolution is handled (https://www.pgedge.com/resources/faq https://www.pgedge.com/resources/faq), but at-a-glance, "pgEdge offers eventual consistency between nodes using a configurable policy (e.g. last-writer-wins) for conflict resolution, along with conflict-free delta apply columns (i.e. CRDTs) for running sum fields. This allows for independent, concurrent and eventually consistent updates across multiple nodes."
- n_u 1y ago
  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.
  - bonesmoses 1y ago
    There's an option in the Postgres configuration named "track_commit_timestamp" that does this automatically. It's required to be enabled when using LWW as the conflict resolution model. If there's a tie, the node with the highest node number wins.
pgedge_postgres 1y ago
As a note, there's also specific documentation regarding this: https://docs.pgedge.com/spock_ext/conflicts https://docs.pgedge.com/spock_ext/conflicts And, one of our solutions engineers (Paul Rothrock) has a video released a month ago on this topic as well: https://www.youtube.com/watch?v=prkMkG0SOJE https://www.youtube.com/watch?v=prkMkG0SOJE Sharing these alongside my other comment in case additional information is helpful :-)

imglorp 1y ago

Third party, multi master postgres is such an old idea, it was done in Perl... https://github.com/bucardo/bucardo https://github.com/bucardo/bucardo

pgedge_postgres 1y ago
We're not claiming to be a new idea, by any means :-) Unfortunately, Bucardo is no longer being updated. Our goal is simply to support continued innovation of distributed PostgreSQL along with similar tools for enabling high availability / scalability in PG deployments.
vyruss 1y ago
True, but Bucardo is trigger-based and does not use WAL-based logical replication, and is unmaintained. There is also a world of difference in performance between them.
philipallstar 1y ago
I don't see why this matters. Ideas are easy; execution and adoption are hard. Clearly Bucado didn't take off well enough that this is a solved problem.

throwawaygo 1y ago

pgactive?

vyruss 1y ago
pgactive has limitations with not supporting DDL, sequence management, column and row filtering, conflict and exception handling, incompatibility with native logical replication, etc. The license is also different (Apache 2.0 for pgactive vs PostgreSQL for Spock). Most importantly, it's not "supported anywhere" by AWS, just on RDS.

foreigner 1y ago

What are the pros and cons of this compared to CockroachDB?

snthpy 1y ago
Not the OP nor knowledgeable in this area but I would suspect / hope postgres compatibility as a start. The last time I looked into whether I could use cockroachdb as a backend for my Airflow cluster, it wasn't possible due to compatibility issues.
- pgedge_postgres 1y ago
  You're actually 100% correct! CockroachDB is only 57.25% compatible with standard PostgreSQL (according to https://pgscorecard.com 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).
znpy 1y ago
License. CockroachDB moved to a license that I can’t even remember if it’s source-available anymore.
- pgedge_postgres 1y ago
  It technically is source-available, as of Nov 2024, anyway: https://news.itsfoss.com/cockcroachdb-no-open-source/ https://news.itsfoss.com/cockcroachdb-no-open-source/ So yes, license (and compatibility - see https://pgscorecard.com 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.
traceroute66 1y ago
> compared to CockroachDB CockroachDB != PostgreSQL. I take great issue with the way CockroachDB marketing seeks to imply compatability, when infact what they are promising is wire protocol compatability (i.e. you can fire up your copy of psql on the CLI and it will connect). Last time I looked, a great number of primitive, obvious, fundamental, low-hanging fruit were completely absent from CockroachDB, e.g. (IIRC) stored procedures are nowhere to be seen in CockroachDB.

pisikesipelgas 1y ago

Hi, How do You guys resolve the application database DDL issue when multimaster is in use? One node gets updated, DDL is will be replicated (?) to second node, which is used by not-jet-updated application which is not compatible with updated database structure. This problem has bugged me for a while. And second and similar issue with most replication setups is let's take for postgis for example. Again in one node this extension gets updated. Now what? Data will be replicated to node which is not jet updated and cause whole system to be not functional.

baq 1y ago
It’s an engineering problem: you have to design the system so that it remains functional in this exact scenario - it follows that the system isn’t just code and build artifacts, but also its deployment processes.
- pisikesipelgas 1y ago
  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).
  - baq 1y ago
    Again an engineering problem. You can deploy with zero downtime and people have been doing this for decades. It takes infrastructure like load balancers, ability to run versions in parallel and runtime support for feature flags, but it’s absolutely doable and ultimately just another day in the office for anyone with global operations. A lot of 3rd party tools actually support these workflows for this exact reason.
    - qaq 1y ago
      You gonna pause writes for cutover so while not downtime, specifically for postgres load balancers, ability to run versions in parallel not gonna help you there.

jwr 1y ago

Bear in mind this no longer provides the same consistency model as PostgreSQL does. It's not a straightforward extension of the nice serializable world. That might not be what you expect given the name, this does not provide a strict serializable consistency model. See https://jepsen.io/consistency/models https://jepsen.io/consistency/models for a classification of consistency models.