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?
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
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 :-)
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.
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.
In Postgres, updates contain the entire row, including all column values. Since the Spock extension follows the "Last Write Wins" model by default, one row version will win, while the other is essentially discarded. This is assuming the update happened on each node _before_ the new value was synchronized over, or essentially simultaneously.
You can address this partially using a CRDT such as the Delta Apply functionality for certain columns:
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), 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."
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.
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.
Multi-master can be useful in cases where writes to the data are usually logically grouped by an attribute that correlates to the distribution of masters e.g. sales info by geography. The chances of write conflicts become much smaller (though not zero.
There's a lot of ways to approach the common problems found when running multi-master / active-active PostgreSQL. (A complete guide on this, specifically using PostgreSQL in general, was written by one of our solutions engineers, Shaun Thomas: https://www.pgedge.com/blog/living-on-the-edge)
Could you elaborate on what problems you experienced?
Agree; the part of the application requiring multi-master semantics is probably a small piece and can be handled outside the database where there is enough domain-specific knowledge that it can be made simpler and more obvious how conflicts for example are avoided or handled.
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?
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.
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.
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."
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
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.
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.
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.
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.
[+] [-] verelo|5 months ago|reply
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?
[+] [-] pgedge_postgres|5 months ago|reply
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
And the FAQ also delves into it some: https://www.pgedge.com/resources/faq
[+] [-] pgedge_postgres|5 months ago|reply
[+] [-] jwr|5 months ago|reply
They should! Read some of the excellent Jepsen analyses to see how scary things can be: https://jepsen.io/analyses
[+] [-] victor9000|5 months ago|reply
[+] [-] vyruss|5 months ago|reply
[+] [-] baq|5 months ago|reply
[+] [-] jwr|5 months ago|reply
See https://jepsen.io/consistency/models for a classification of consistency models.
[+] [-] OsrsNeedsf2P|5 months ago|reply
[+] [-] pgedge_postgres|5 months ago|reply
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
Sharing these alongside my other comment in case additional information is helpful :-)
[+] [-] bonesmoses|5 months ago|reply
You can address this partially using a CRDT such as the Delta Apply functionality for certain columns:
https://docs.pgedge.com/spock_ext/conflicts
That will only work with numeric-type (INT, BIGINT, NUMERIC, etc.) columns, but effectively merges data so updates work cumulatively.
[+] [-] pgedge_postgres|5 months ago|reply
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), 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."
[+] [-] pisikesipelgas|5 months ago|reply
[+] [-] baq|5 months ago|reply
[+] [-] sgarland|5 months ago|reply
Source: I have operated a large multi-master Postgres cluster.
[+] [-] phs318u|5 months ago|reply
[+] [-] pgedge_postgres|5 months ago|reply
Could you elaborate on what problems you experienced?
[+] [-] jasonthorsness|5 months ago|reply
[+] [-] bigwheels|5 months ago|reply
[+] [-] foreigner|5 months ago|reply
[+] [-] traceroute66|5 months ago|reply
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.
[+] [-] znpy|5 months ago|reply
[+] [-] snthpy|5 months ago|reply
[+] [-] tonyhart7|5 months ago|reply
[+] [-] pgedge_postgres|5 months ago|reply
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
One of our solutions engineers (Paul Rothrock) created a video on this topic in the last month: 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
[+] [-] throwawaygo|5 months ago|reply
[+] [-] vyruss|5 months ago|reply
[+] [-] imglorp|5 months ago|reply
https://github.com/bucardo/bucardo
[+] [-] vyruss|5 months ago|reply
[+] [-] pgedge_postgres|5 months ago|reply
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.
[+] [-] philipallstar|5 months ago|reply