5 ms·

Sounds interesting. So how soon one knows if his write has been accepted or rejected? Is it immediate or eventual?

ForHackernews 1y ago
It's eventual consistency: Latest-write wins after the dust settles. As I understand it, this is a wrapper on top of Postgres' native logical replication features. Writes are committed locally and then published via a replication slot to subscriber nodes. You have ACID guarantees locally, but not across the entire distributed system. https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication....
- gritzko 1y ago
  So the outcomes are essentially random? It all feels like they expect developers to sift through the conflict log to resolve things manually or something. If a transaction did not go through on some of the nodes, what are the others doing then? What if they can not roll it back safely? Such a rabbit hole.
  - deleted 1y ago
    [deleted]
  - zknill 1y ago
    Typically applications will have some kind of logical separation of the data. Given this is targeted at replication of postgres nodes, perhaps the nodes are deployed across different regions of the globe. By using active-active replication, all the participating nodes are capable of accepting writes, which simplifies the deployment and querying of postgres (you can read and write to your region-local postgres node). Now that doesn't mean that all the reads and writes will be on conflicting data. Take the regional example, perhaps the majority of the writes affecting one region's data are made _in that region_. In this case, the region local postgres would be performing all the conflict resolution locally, and sharing the updates with the other nodes. The reason this simplifies things, is that you can treat all your postgres connections as-if they are just a single postgres. Writes are fast, because they are accepted in the local region, and reads are replicated without you having to have a dedicated read-replica. Ofc you're still going to have to design around the conflict resolution (i.e. writes for the same data issued against different instances), and the possibility of stale reads as the data is replicated cross-node. But for some applications, this design might be a significant benefit, even with the extra things you need to do.
    - gritzko 1y ago
      I think I understand the use case. Like, we have in fact several regional Postgreses, but we want them to be one physical database for the sake of simplicity. Probably this should be in the motivational part of the README.
  - ForHackernews 1y ago
    In our case, we're designing around INSERT-only tables with a composite primary key that includes the site id, so (in theory) there will never be any conflicts that need resolution.
    - zozbot234 1y ago
      > with a composite primary key that includes the site id It doesn't look like you'd need multi master replication in that case? You could simply partition tables by site and rely on logical replication.
      - ForHackernews 1y ago
        I think that's absolutely true in the happy scenario when the internet is up. There's a requirement that during outages each site continue operating independently and might* need to make writes to data "outside" its normal partition. By having active-active replication the hope is that the whole thing recovers "automatically" (famous last words) to a consistent state once the network comes back.
        teraflop 1y ago
        But if you drop the assumption that each site only writes rows prefixed with its site ID, then you're right back to the original situation where writes can be silently overwritten. Do you consider that acceptable, or don't you?
        ForHackernews 1y ago
        Not silently overwritten: the collision is visible to the application layer once connectivity is restored and you can prompt humans to reconcile it if need be.
        LudwigNagasena 1y ago
        Sounds like a recipe for a split brain that requires manual recovery and reconciliation.
  - shermantanktop 1y ago
    There’s no free lunch. The rabbit hole is only worth going down if the benefits are worth the operational pain. I view this as a building block, not a checkbox feature that magically just works all the time. For someone who has these requirements out of the gate, another datastore might be better. But if someone is already deeply tied to Postgres and perhaps doing their own half assed version of this, this option could be great.
    - ForHackernews 1y ago
      What are good off-the-shelf distributed databases? We looked at MongoDB but it wasn't worth giving up SQL. To reiterate the no free lunch point, no one has figured out how to outsmart the CAP theorem yet, so all you can do is design around it.
      - perfmode 1y ago
        Spanner
      - jwr 1y ago
        FoundationFB and anything based on that.
      - mike_hearn 1y ago
        I work for them so take with a pinch of salt, but Oracle DB. It gives you a fully multi-master horizontally scalable database with ACID transactions (not sharding), full SQL, elastic scalability, built in queues, JavaScript stored procs, automatic REST API construction, many other features. Its pricing is competitive with a cloud hosted Postgres, believe it or not (the clouds are making a lot of money off customers who are wedded to Postgres). I work through some of the numbers for an extreme case here [1]. Behind the scenes, the way it works is by combining software tricks with special hardware. You rent a (part of a) database cluster. The cluster is running on high end hardware running customized kernels, with a private Infiniband RDMA-capable interconnect between the nodes separate from the front-side network that clients connect with. A lock manager coordinates ownership of data blocks, which can be read either from disk nodes or directly out of the RAM of other database nodes. So if one node reads a block then writes to it, the only thing written to disk immediately is the transaction log. If another node then needs to write to that block, it's transferred directly over the interconnect using RDMA to avoid waiting on the remote CPU, the disk is never touched. Dirty blocks are written back to disk asynchronously. The current transaction counter is also poked directly into remote nodes via RDMA. In the latest versions the storage nodes can also do some parts of query processing using predicate push-down, so the amount of data to be transferred over the interconnect is also lowered. The client drivers understand all the horizontal scalability stuff and can failover between nodes transparently, so the whole setup is HA. A node can die and the cluster will continue, including open transactions. If you need to accelerate performance further you can add read-through coherent cache nodes. These act as proxies and integrate with the block ownership system to do processing locally. Other than financial reasons (I own some stock), I've started making this argument here on HN because it's unintuitive but correct, which is just enjoyable. A lot of people in the startup world don't realize any of the above, thinking that horizontally scalable fully coherent SQL databases either don't exist or have severe caveats. E.g. one reply to you suggests FoundationDB which is great, but it's a KV store and not a SQL database. [1] https://news.ycombinator.com/item?id=44074506 https://news.ycombinator.com/item?id=44074506 (last paragraph)
  - whizzter 1y ago
    My guess is that you want to change your entire design philosophy a little bit with regards to table design, moving some entities to use a composite GUID+timestamp as PK's and replace most updates with inserts to avoid conflicts and instead resolve things at query-time (Basically a CRDT modelling philosophy contained within a relational schema). Ideal? Not entirely but it should still give most query benefits of regular SQL and allows one to to benefit from good indexes (the proper indexes of an SQL database will also help contain the costs of an updated datamodel). I think this is more interesting for someone building something social media like perhaps rather than anything involving accounting.
    - rjbwork 1y ago
      Are there any Datomic-like query layers on top of Postgres for approaches like this where you're recording immutable occurrences rather than updating mutable records?
      - whizzter 1y ago
        No, this would be more be about using different ways of mapping data to the database from an applications point of view. So for an developer it would increase the load a tad in terms of exploring data when debugging simple things. On the other hand, the increase in exploration costs should be more than offset by having most data changes logged to be able to track changes.
  - zozbot234 1y ago
    > So the outcomes are essentially random? In principle you could use CRDTs to end up with a "not quite random" outcome that simply takes the conflict into account - it doesn't really attempt to "resolve" it. That's quite good for some cases.
    - dboreham 1y ago
      This is a kind of CRDT. CRDT is just some papers defining reasonably clear terminology to cover the kind of eventually consistent replication that has been done for decades, including this kind (timestamp-based last-writer wins).
  - mnahkies 1y ago
    One way we dealt with this in the past was assigning an "affinity" to each tenant and basically routing their writes/reads to that host, excepting if that host was down. You would still get weird replication issues/conflicts when requests failed over in some conditions, but it worked fairly well the majority of the time. These days I'd stick to single primary/writer as much as possible though tbh.
okigan 1y ago
It took 20 years to acknowledge that pushing eventual consistency to application layer is not worth it for most applications. Seems the same is playing out out in Postgres with this extension, maybe will take it another 20 years
- tinkertamper 1y ago
  I'm curious about what you mean here. It sounds like you're saying that applications shouldn't concern themselves with consistency. Can you elaborate?
- rubiquity 1y ago
  The idea of active-active is too seductive compared to how hard learning distributed systems is.
  - okigan 1y ago
    It is so seductive that people don’t read the footnotes that explain that active-active does not do what they think it does.
    - m11a 1y ago
      I'd agree. There's so many footguns involved in multi-master setups, that most organisations should avoid this until they're big enough to hire distributed systems engineers to design a proper solution for the company. I personally don't love any of the Postgres multi-master solutions. You can scale surprisingly far on a single-master Postgres with read replicas.

It's eventual consistency: Latest-write wins after the dust settles. As I understand it, this is a wrapper on top of Postgres' native logical replication features. Writes are committed locally and then published via a replication slot to subscriber nodes. You have ACID guarantees locally, but not across the entire distributed system. https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication....

gritzko 1y ago
So the outcomes are essentially random? It all feels like they expect developers to sift through the conflict log to resolve things manually or something. If a transaction did not go through on some of the nodes, what are the others doing then? What if they can not roll it back safely? Such a rabbit hole.
- deleted 1y ago
  [deleted]
- zknill 1y ago
  Typically applications will have some kind of logical separation of the data. Given this is targeted at replication of postgres nodes, perhaps the nodes are deployed across different regions of the globe. By using active-active replication, all the participating nodes are capable of accepting writes, which simplifies the deployment and querying of postgres (you can read and write to your region-local postgres node). Now that doesn't mean that all the reads and writes will be on conflicting data. Take the regional example, perhaps the majority of the writes affecting one region's data are made _in that region_. In this case, the region local postgres would be performing all the conflict resolution locally, and sharing the updates with the other nodes. The reason this simplifies things, is that you can treat all your postgres connections as-if they are just a single postgres. Writes are fast, because they are accepted in the local region, and reads are replicated without you having to have a dedicated read-replica. Ofc you're still going to have to design around the conflict resolution (i.e. writes for the same data issued against different instances), and the possibility of stale reads as the data is replicated cross-node. But for some applications, this design might be a significant benefit, even with the extra things you need to do.
  - gritzko 1y ago
    I think I understand the use case. Like, we have in fact several regional Postgreses, but we want them to be one physical database for the sake of simplicity. Probably this should be in the motivational part of the README.
- ForHackernews 1y ago
  In our case, we're designing around INSERT-only tables with a composite primary key that includes the site id, so (in theory) there will never be any conflicts that need resolution.
  - zozbot234 1y ago
    > with a composite primary key that includes the site id It doesn't look like you'd need multi master replication in that case? You could simply partition tables by site and rely on logical replication.
    - ForHackernews 1y ago
      I think that's absolutely true in the happy scenario when the internet is up. There's a requirement that during outages each site continue operating independently and might* need to make writes to data "outside" its normal partition. By having active-active replication the hope is that the whole thing recovers "automatically" (famous last words) to a consistent state once the network comes back.
      - teraflop 1y ago
        But if you drop the assumption that each site only writes rows prefixed with its site ID, then you're right back to the original situation where writes can be silently overwritten. Do you consider that acceptable, or don't you?
        ForHackernews 1y ago
        Not silently overwritten: the collision is visible to the application layer once connectivity is restored and you can prompt humans to reconcile it if need be.
      - LudwigNagasena 1y ago
        Sounds like a recipe for a split brain that requires manual recovery and reconciliation.
- shermantanktop 1y ago
  There’s no free lunch. The rabbit hole is only worth going down if the benefits are worth the operational pain. I view this as a building block, not a checkbox feature that magically just works all the time. For someone who has these requirements out of the gate, another datastore might be better. But if someone is already deeply tied to Postgres and perhaps doing their own half assed version of this, this option could be great.
  - ForHackernews 1y ago
    What are good off-the-shelf distributed databases? We looked at MongoDB but it wasn't worth giving up SQL. To reiterate the no free lunch point, no one has figured out how to outsmart the CAP theorem yet, so all you can do is design around it.
    - perfmode 1y ago
      Spanner
    - jwr 1y ago
      FoundationFB and anything based on that.
    - mike_hearn 1y ago
      I work for them so take with a pinch of salt, but Oracle DB. It gives you a fully multi-master horizontally scalable database with ACID transactions (not sharding), full SQL, elastic scalability, built in queues, JavaScript stored procs, automatic REST API construction, many other features. Its pricing is competitive with a cloud hosted Postgres, believe it or not (the clouds are making a lot of money off customers who are wedded to Postgres). I work through some of the numbers for an extreme case here [1]. Behind the scenes, the way it works is by combining software tricks with special hardware. You rent a (part of a) database cluster. The cluster is running on high end hardware running customized kernels, with a private Infiniband RDMA-capable interconnect between the nodes separate from the front-side network that clients connect with. A lock manager coordinates ownership of data blocks, which can be read either from disk nodes or directly out of the RAM of other database nodes. So if one node reads a block then writes to it, the only thing written to disk immediately is the transaction log. If another node then needs to write to that block, it's transferred directly over the interconnect using RDMA to avoid waiting on the remote CPU, the disk is never touched. Dirty blocks are written back to disk asynchronously. The current transaction counter is also poked directly into remote nodes via RDMA. In the latest versions the storage nodes can also do some parts of query processing using predicate push-down, so the amount of data to be transferred over the interconnect is also lowered. The client drivers understand all the horizontal scalability stuff and can failover between nodes transparently, so the whole setup is HA. A node can die and the cluster will continue, including open transactions. If you need to accelerate performance further you can add read-through coherent cache nodes. These act as proxies and integrate with the block ownership system to do processing locally. Other than financial reasons (I own some stock), I've started making this argument here on HN because it's unintuitive but correct, which is just enjoyable. A lot of people in the startup world don't realize any of the above, thinking that horizontally scalable fully coherent SQL databases either don't exist or have severe caveats. E.g. one reply to you suggests FoundationDB which is great, but it's a KV store and not a SQL database. [1] https://news.ycombinator.com/item?id=44074506 https://news.ycombinator.com/item?id=44074506 (last paragraph)
- whizzter 1y ago
  My guess is that you want to change your entire design philosophy a little bit with regards to table design, moving some entities to use a composite GUID+timestamp as PK's and replace most updates with inserts to avoid conflicts and instead resolve things at query-time (Basically a CRDT modelling philosophy contained within a relational schema). Ideal? Not entirely but it should still give most query benefits of regular SQL and allows one to to benefit from good indexes (the proper indexes of an SQL database will also help contain the costs of an updated datamodel). I think this is more interesting for someone building something social media like perhaps rather than anything involving accounting.
  - rjbwork 1y ago
    Are there any Datomic-like query layers on top of Postgres for approaches like this where you're recording immutable occurrences rather than updating mutable records?
    - whizzter 1y ago
      No, this would be more be about using different ways of mapping data to the database from an applications point of view. So for an developer it would increase the load a tad in terms of exploring data when debugging simple things. On the other hand, the increase in exploration costs should be more than offset by having most data changes logged to be able to track changes.
- zozbot234 1y ago
  > So the outcomes are essentially random? In principle you could use CRDTs to end up with a "not quite random" outcome that simply takes the conflict into account - it doesn't really attempt to "resolve" it. That's quite good for some cases.
  - dboreham 1y ago
    This is a kind of CRDT. CRDT is just some papers defining reasonably clear terminology to cover the kind of eventually consistent replication that has been done for decades, including this kind (timestamp-based last-writer wins).
- mnahkies 1y ago
  One way we dealt with this in the past was assigning an "affinity" to each tenant and basically routing their writes/reads to that host, excepting if that host was down. You would still get weird replication issues/conflicts when requests failed over in some conditions, but it worked fairly well the majority of the time. These days I'd stick to single primary/writer as much as possible though tbh.

okigan 1y ago

It took 20 years to acknowledge that pushing eventual consistency to application layer is not worth it for most applications. Seems the same is playing out out in Postgres with this extension, maybe will take it another 20 years

tinkertamper 1y ago
I'm curious about what you mean here. It sounds like you're saying that applications shouldn't concern themselves with consistency. Can you elaborate?
rubiquity 1y ago
The idea of active-active is too seductive compared to how hard learning distributed systems is.
- okigan 1y ago
  It is so seductive that people don’t read the footnotes that explain that active-active does not do what they think it does.
  - m11a 1y ago
    I'd agree. There's so many footguns involved in multi-master setups, that most organisations should avoid this until they're big enough to hire distributed systems engineers to design a proper solution for the company. I personally don't love any of the Postgres multi-master solutions. You can scale surprisingly far on a single-master Postgres with read replicas.