14 ms·

Pgcat – Enhanced PostgreSQL logical replication

145 points by jinhualuo 7y ago

anarazel 7y ago
> Instead of worker processes and low-level row ingression, pgcat uses sql template to apply high-level sql commands, so it could make largest compatibility on target table type. It is written in golang and runs in separate process. Doing "pgcat uses sql template to apply high-level sql commands" unconditionally is somewhat sad. It has WAY higher overhead than the referenced "low-level row ingression". It makes sense to so in some cases (into a view, triggers, apply functions, ...), but always doesn't seem great.
- jinhualuo 7y ago
  Yes, the template generated sql would bring some overhead. But it's the cost for flexibility: The target table would not be limited to base table. It could be a view, a foreign table, partitioned table, or citus distributed table. And, it also support table name mapping.
  - anarazel 7y ago
    You can detect whether a target is a plain table or not. And use a different path if not. And independent of using SQL or not: Executing them separately out of process kills you via latency, even if you're on the same machine. The context switches are just too expensive. Which makes single threaded apply untenable for anything with a few more clients, or bulk load.
    - jinhualuo 7y ago
      I would try to do more test for performance, especially in product environment. Detect the target table is easy, but it seems tricky to redirect the flow to pg build-in logical replication (not necessary to re-implement it, especially it was written in C language, so redirection is the right way). In fact, you could use pg logical replication directly for some cases like big and real-time data flows. But currently, it has one big flaw: bi-directional replication or multi-master replication is impossible, where it would cause dead cycle loops, because it do not filter any origin currently, you could check function pgoutput_origin_filter. And, even if the filter is right, it still lacks of conflict solution, but pgcat could help there, and you don't need to use pgcat, because pgcat has trigger way to resolve conflict, you could check pgcat_setup_lww command.
akdor1154 7y ago
Looks really nice! Out of interest, at what stage would the dbas here consider running a newish third party project such as this on a production workload?
- edoceo 7y ago
  I use pg logical in production today on a small system (I'm only a couple 100 gig). This tool looks really promising. However, for my core data I look at 6mo of replica realworld load in test before putting anywhere close to live. Also, for some bits we also run some near shit-hack custom pg extensions in production so...I'm not risk adverse.
- CuriouslyC 7y ago
  Personally I would test this in a greenfield project first, unless this solved a serious problem I was facing.
  - gigatexal 7y ago
    Yep. Former DBA here. Don’t trust something new with price ion data without getting really familiar with it and learning its corner cases and how to recover and learning to trust it with your data
ahachete 7y ago
I'm not a big fan of conflict resolution, and LWW is a pretty bad choice in most cases. Conflict resolution means data loss (it violates the D of ACID, as an user expects that a transaction that was committed would endure, but it is deleted or overwritten by another one), and most likely C. The effects of this are hard to predict and can hit pretty hard your business logic. This is not the kind of expectation you get from Postgres. This would fail Jepsen at second 1. So I'd advise not to use it. The only use case I see for bi-directional replication is where you ensure that all the master nodes have disjoint sets of data, and the logical replication just aggregates them, without a possibility for conflicts. If you really want multi-master you need a system that works on top of a consensus protocol. And there are definitely tools out there for this. But not LWW.
- bonesmoses 7y ago
  LWW / LUW systems are a fine first approximation, but generally do not fulfill the strictest guarantees and desirable outcomes for a database without accounting for a lot of edge cases. A good conflict management system will have that only as a default. Better ones will also provide mechanisms for overriding LWW in specific scenarios on a per table or even row basis depending on the in/out values from both nodes. The trick is building these conflict management rules so they're independently deterministic, or you'll end up with node divergence. Normally we just do what you said, and strongly recommend interacting with sticky sessions, geographically disparate segments, etc., to prevent the problem before it happens. It's far easier to avoid a conflict than to correct it after the fact. While a consensus model "fixes" this, it drastically cuts your throughput to a function of the latency of the most remote node in the quorum, essentially defeating the purpose of having a local Master node. Ideally you would reserve this for specific transactions that are sensitive in that they require absolute consistency regardless of incurred latency (See PACELC). And even this requires a distributed deadlock detector unless more than just the transactions are managed through the consensus layer. BDR for example provides both of these models for the above reasons. Multi-Master is not an easy problem to solve, and requires a multi-faceted approach to even come close to something usable in a generalized scenario.
  - 7y ago
_-___________-_ 7y ago
What use cases are there for a replication that doesn't necessarily produce an accurate replica?

145 points by jinhualuo 7y ago

> Instead of worker processes and low-level row ingression, pgcat uses sql template to apply high-level sql commands, so it could make largest compatibility on target table type. It is written in golang and runs in separate process. Doing "pgcat uses sql template to apply high-level sql commands" unconditionally is somewhat sad. It has WAY higher overhead than the referenced "low-level row ingression". It makes sense to so in some cases (into a view, triggers, apply functions, ...), but always doesn't seem great.

jinhualuo 7y ago
Yes, the template generated sql would bring some overhead. But it's the cost for flexibility: The target table would not be limited to base table. It could be a view, a foreign table, partitioned table, or citus distributed table. And, it also support table name mapping.
- anarazel 7y ago
  You can detect whether a target is a plain table or not. And use a different path if not. And independent of using SQL or not: Executing them separately out of process kills you via latency, even if you're on the same machine. The context switches are just too expensive. Which makes single threaded apply untenable for anything with a few more clients, or bulk load.
  - jinhualuo 7y ago
    I would try to do more test for performance, especially in product environment. Detect the target table is easy, but it seems tricky to redirect the flow to pg build-in logical replication (not necessary to re-implement it, especially it was written in C language, so redirection is the right way). In fact, you could use pg logical replication directly for some cases like big and real-time data flows. But currently, it has one big flaw: bi-directional replication or multi-master replication is impossible, where it would cause dead cycle loops, because it do not filter any origin currently, you could check function pgoutput_origin_filter. And, even if the filter is right, it still lacks of conflict solution, but pgcat could help there, and you don't need to use pgcat, because pgcat has trigger way to resolve conflict, you could check pgcat_setup_lww command.

akdor1154 7y ago

Looks really nice! Out of interest, at what stage would the dbas here consider running a newish third party project such as this on a production workload?

edoceo 7y ago
I use pg logical in production today on a small system (I'm only a couple 100 gig). This tool looks really promising. However, for my core data I look at 6mo of replica realworld load in test before putting anywhere close to live. Also, for some bits we also run some near shit-hack custom pg extensions in production so...I'm not risk adverse.
CuriouslyC 7y ago
Personally I would test this in a greenfield project first, unless this solved a serious problem I was facing.
- gigatexal 7y ago
  Yep. Former DBA here. Don’t trust something new with price ion data without getting really familiar with it and learning its corner cases and how to recover and learning to trust it with your data

ahachete 7y ago

I'm not a big fan of conflict resolution, and LWW is a pretty bad choice in most cases. Conflict resolution means data loss (it violates the D of ACID, as an user expects that a transaction that was committed would endure, but it is deleted or overwritten by another one), and most likely C. The effects of this are hard to predict and can hit pretty hard your business logic. This is not the kind of expectation you get from Postgres. This would fail Jepsen at second 1. So I'd advise not to use it. The only use case I see for bi-directional replication is where you ensure that all the master nodes have disjoint sets of data, and the logical replication just aggregates them, without a possibility for conflicts. If you really want multi-master you need a system that works on top of a consensus protocol. And there are definitely tools out there for this. But not LWW.

bonesmoses 7y ago
LWW / LUW systems are a fine first approximation, but generally do not fulfill the strictest guarantees and desirable outcomes for a database without accounting for a lot of edge cases. A good conflict management system will have that only as a default. Better ones will also provide mechanisms for overriding LWW in specific scenarios on a per table or even row basis depending on the in/out values from both nodes. The trick is building these conflict management rules so they're independently deterministic, or you'll end up with node divergence. Normally we just do what you said, and strongly recommend interacting with sticky sessions, geographically disparate segments, etc., to prevent the problem before it happens. It's far easier to avoid a conflict than to correct it after the fact. While a consensus model "fixes" this, it drastically cuts your throughput to a function of the latency of the most remote node in the quorum, essentially defeating the purpose of having a local Master node. Ideally you would reserve this for specific transactions that are sensitive in that they require absolute consistency regardless of incurred latency (See PACELC). And even this requires a distributed deadlock detector unless more than just the transactions are managed through the consensus layer. BDR for example provides both of these models for the above reasons. Multi-Master is not an easy problem to solve, and requires a multi-faceted approach to even come close to something usable in a generalized scenario.
- 7y ago

_-___________-_ 7y ago

What use cases are there for a replication that doesn't necessarily produce an accurate replica?