7 ms·

PostgreSQL 16 Bi-Directional Logical Replication

127 points by alexzeitler 3y ago

perlgeek 3y ago
The description of preconditions seems a bit incomplete. To me it seems you need to avoid certain constructs, like UNIQUE constraints. Otherwise you might have a local insert plus a replicated one, both the same value in the unique column, and different nodes reject different inserts.
- smilliken 3y ago
  Agreed. Also exclusion constraints (a generalization of unique constraints). Also foreign key constraints, because one database might delete a record simultaneously with another database adding a reference to it.
  - hinkley 3y ago
    Deleting from a database is a sucker's bet. If you add to a record and mark it dead there are fewer semantic problems to deal with. Partial indexes give you approximately the same speedup as deletes.
    - arthurcolle 3y ago
      Why is this comment greyed out? Seems like a reasonable comment
      - hobs 3y ago
        I didn't vote it down but there's huge tradeoffs here: 1. Row bloat. 2. Bad plan estimation due to bloat. 3. Needing filtered indexes for anything that's a row based unique constraint. 4. All history in one table means changing schemas is a PITA, and you're backfilling stuff that's not real. 5. There's more but that's just off the top.
        hinkley 3y ago
        1. Most of the problem with row 'bloat' is complexity of indexes, row deletion increases the number of required indexes, and lack of those indexes is a footgun 2. Got a citation for that? Pretty sure #3 makes that not true, but I'm open to be educated. 3. Yes, and? You're going to have those indexes anyway. That's the point. 4. You don't have to have all rows in a single table just because you took delete out of any user-facing query logic. 5. I think you need some more examples.
        arthurcolle 3y ago
        I agree with all of your comments - this is why I asked initial question because literally all his initial comments seems perfectly valid. I appreciate the discourse, and I work on this stuff every day so I was just confused
        hobs 3y ago
        And the number of rows in the table, effectively bloating it for all time. Deleting rows should generally be via the same type of query patterns you use to find them, or that's pretty weird. I think #4 is something that most people dont implement - ime they just tombstone in their own table, and then all of the other problems are pretty rampant. Having separate indexes for deletes might be a thing, but taking a history of every change to a thing in a relational way is still a big pain because of the schema cost, merging changes in an audit table when its really a slowly changing dimension is really weird to most query patterns. To #2 - it depends on your query engine, but if every query does have filtered indexes there's still a cost to having two tables in one table (ignoring #4)
        hinkley 3y ago
        > And the number of rows in the table, effectively bloating it for all time. Particularly in the case of growth oriented companies, if your user base is growing exponentially, then half of your records are less than a year old anyway. This bloat is not the problem you make it. And as I said elsewhere, a row that is tombstoned can be deleted offline at some cadence that doesn’t break your other workflows. For instance when nearly all of its siblings are also dead.
      - hinkley 3y ago
        HN is not immune to forgetting things we used to have consensus on. There's always someone who is prepared to downvote, literally or figuratively, something that will eventually be the solution to some problem they created in production. When I get downvoted for ideas and not demeanor, 2/3rds of the time it's just job security.
        refulgentis 3y ago
        Neutral party here: not the case, on your end, the mind-reading distracts from hearing the voice of Chesterton talking about his fence. c.f. sibling comment written 10 minutes before yours, several trivial things. More concretely from a downvoter: 1. I want _nothing_ to do with user data. Nothing. Toxic nuclear waste. The idea of keeping the waste on hand needs strong justification. 2. The idea of "just leave the rows but mark is_deleted" has a fundamental distate to a profession where O(n) is a constant worry. 3. The breezy way it dismisses this as an obvious solution that ~eveyone agrees suggets a small breadth of experience. That's not bad! But then we're at Chesterton's fence. In summary, you can get downvoted for ruling out someone else's concrete lived experience, not just because your dangerous ideas threaten their paycheck (I don't even have a job!)
        hinkley 3y ago
        The lived experience you talk about is that this is a difficult problem without a solution, not a different solution that I dismissed. What I dismissed is the problem. How do we solve X? You don't solve X, you solve Y. The XY problem, not Chesterton's Fence. Not sure I follow you entirely on user data. There's user data that absolutely requires an audit trail, like subscriptions or orders. There's user data that might start out as null and eventually transition to an inane value, like avatar or self-description. You don't need complex merge rules for that data. It possibly only happens when their account is being hacked and that's not the data you need in that particular situation, yeah?
        vlovich123 3y ago
        Couldn’t you just mark a deletion timestamp and then lazily delete later as part of a GC operation?
    - cpursley 3y ago
      Another option is an audit log that tracks all changes to a record.
      - hinkley 3y ago
        It's possible to do row deletions and an audit log table, but do you see a lot of people who do both? I haven't. It's easier to keep a FK to the mutable table.
        epcoa 3y ago
        > but do you see a lot of people who do both? Yes, pretty sure it’s very common. That doesn’t make it the greatest design, common doesn’t mean good.
        hinkley 3y ago
        I have too much scar tissue around accidental table scans caused by the fact that foreign key constraints don't implicitly create an index. If you think logarithmic insertion time is bad for your p95 times, try quadratic times on delete operations gone horribly wrong.
    - azlev 3y ago
      I wouldn't word that way. Delete records is how SQL works. Also it's wiser to move old information to a separate database instead of paying maintenance costs of old or dead records. There are a lot of gotchas in keep old data, like GDPR.
      - hinkley 3y ago
        Only if all records age out at the same rate, otherwise you’re making Swiss cheese of your tables. And/or you’re losing history data. If your rows do age out at the same time, then I’d be tempted to ask you why you’re storing logs in a relational database, because that’s essentially what you have at that point.
- rvdginste 3y ago
  Would I be wrong in thinking that this "bi-directional logical replication" is only usable in a very limited number of use cases? As you mention, unique constraints pose a problem, and a lot of tables would have unique constraints to protect business keys? The article references another article on this, the refers to the PostgreSQL documentation: - conflicts [1] - restrictions [2] You need to be very aware of the limitations to decide whether this is usable in a specific context. I don't think this is really intended to be used for "bi-directional" logical replication. [1] https://www.postgresql.org/docs/current/logical-replication-conflicts.html https://www.postgresql.org/docs/current/logical-replication-... [2] https://www.postgresql.org/docs/current/logical-replication-restrictions.html https://www.postgresql.org/docs/current/logical-replication-...
  - philjohn 3y ago
    This feels like something for the specific use case of heavy writes that are treating the DB like an append-only log, and then running analytics queries over the top. Something like a distributed metrics collection system.
- dazzla76 3y ago
  Unique constraints can still be used as long as you accommodate the multiple sources. Something as simple of increment by 2 and the servers alternative odd and even as the starting point. I used to do this with 3 way DB2 LUW replication back when i was a DBA. It all needs a bit of thought but is fairly simple when it comes down to it.
egamirorrim 3y ago
I don't know that this article tackled the real elephant in the room for bi-di replication. What happens when the same insert happens on both primaries at once, who wins?
- singron 3y ago
  They will each apply the insert locally. Then they will attempt to replicate that insert to each other. Each will attempt to apply the replicated conflicting insert, which will cause an error and halt replication for both nodes. If you update the same data on both nodes, this is a recipe for almost certain disaster. Postgres is not a distributed database and this doesn't make it one.
  - cogman10 3y ago
    Yeah, read the docs here [1] and really does not seem like this would be a good system to work with. To make something fault tolerant you have to have a side subscription running watching for server errors so you can resolve these conflicts when they arise. pglogical gave you the option to determine the winner of a conflicting write (which I think is preferable). The inbuilt postgres stuff seems like it'd be rife with potential errors. [1] https://www.postgresql.org/docs/16/logical-replication-conflicts.html https://www.postgresql.org/docs/16/logical-replication-confl...
    - brianwawok 3y ago
      I assume you just go very basic. All updates to table X go to database Y. Don’t have all the clients slamming different databases with updates to the same stuff. Not ideal but would still help with some scale stuff.
      - philjohn 3y ago
        Or an analytics system - spray events at whatever server, because each row is a unique record separate from the others, you get no conflicts. Benefit - if your current system with a single primary for writes is suffering and your data and use-case fits this pattern, you can increase your write throughput without scaling up.
MarvinYork 3y ago
Could this handle more than two databases with this technique or would it generate the mentioned loop when a third database sends the data to multiple databases?
- ttfkam 3y ago
  Yes, the implementation distinguishes between a replication client and a user/app client specifically to prevent multiple query invocations. Network latency and CAP tradeoffs still apply of course, but you can absolutely scale well beyond two instances with this.
westurner 3y ago
/? postgres replication https://hn.algolia.com/?dateRange=all&page=0&prefix=true&query=postgresql%20replication&sort=byDate&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que... "Pgactive: Active-Active Replication Extension for PostgreSQL on Amazon RDS" (2023) https://news.ycombinator.com/item?id=37838223 https://news.ycombinator.com/item?id=37838223 /? "pgactive" site:github.com https://www.google.com/search?q=%22pgactive%22+site%3Agithub.com https://www.google.com/search?q=%22pgactive%22+site%3Agithub... cloudnative-pg/cloudnative-pg: https://github.com/cloudnative-pg/cloudnative-pg https://github.com/cloudnative-pg/cloudnative-pg : > primary/standby cloudnative-pg > Logical replication #13: https://github.com/cloudnative-pg/cloudnative-pg/issues/13 https://github.com/cloudnative-pg/cloudnative-pg/issues/13 /? logical replication: https://www.google.com/search?q=logical+replication https://www.google.com/search?q=logical+replication pgadmin docs > Publication Dialog; logical replication: https://www.pgadmin.org/docs/pgadmin4/development/publication_dialog.html https://www.pgadmin.org/docs/pgadmin4/development/publicatio... https://github.com/dalibo/pg_activity#faq https://github.com/dalibo/pg_activity#faq ; pip install `pg_activity[psychopg]` : > FAQ: I can't see my queries only TPS is shown (How) Do any ~pg_top tools delineate logical replication activity? pgcenter > PostgreSQL statistics [virtual tables] (and also /proc) https://github.com/lesovsky/pgcenter#postgresql-statistics https://github.com/lesovsky/pgcenter#postgresql-statistics ... "Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps" (2023) from the creators of CRDTs https://news.ycombinator.com/item?id=37590257 https://news.ycombinator.com/item?id=37590257 electric-sql/electric: https://github.com/electric-sql/electric https://github.com/electric-sql/electric : > ElectricSQL is a local-first software platform that makes it easy to develop high-quality, modern apps with instant reactivity, realtime multi-user collaboration and conflict-free offline support. > Local-first is a new development paradigm where your app code talks directly to an embedded local database and data syncs in the background via active-active database replication. Because the app code talks directly to a local database, apps feel instant. Because data syncs in the background via active-active replication it naturally supports multi-user collaboration and conflict-free offline "SQLedge: Replicate Postgres to SQLite on the Edge" (2023) https://news.ycombinator.com/item?id=37067980 https://news.ycombinator.com/item?id=37067980 ; gh-ost, dolt, sqldiff /?hnlog ctrl-f Consistenc, Consensus : - "A few notes on message passing" https://news.ycombinator.com/item?id=26535969 https://news.ycombinator.com/item?id=26535969 : > The C in CAP theorem is for Consistency [3][4]. [...] > [3] https://en.wikipedia.org/wiki/Consistency_model https://en.wikipedia.org/wiki/Consistency_model > [4] https://en.wikipedia.org/wiki/CAP_theorem https://en.wikipedia.org/wiki/CAP_theorem
kroolik 3y ago
I find the explanation of logical and physical replication to be weird. Seems like only logical replication transfers changes. I have always thought physical replication is replicating using WAL logs, which is a stream of changes. The logical replication would execute the queries on the replicating nodes, leaving WAL management to the replica. This is also the reason why physical replication requires same pg versions (there may be diffs to the physical format of data in WAL), whilst logical replication uses domain language, which is independent from physical layout.
tristenharr 3y ago
This is exciting, I’m hopeful that we get distributed SQLite via something like Turso that can hook into this to stream subsets of data into SQLite edge-caches that can be pushed through the edge onto devices via something like embedded replicas. I know experimental work has been done here with pg_turso (I recently wrote my first few lines of zig to add support for some additional data-types for it) and this bodes well with the recent announcement of Postgres coming to fly.io, I dream of a day when I can make Postgres and SQLite play nicely together.

127 points by alexzeitler 3y ago

The description of preconditions seems a bit incomplete. To me it seems you need to avoid certain constructs, like UNIQUE constraints. Otherwise you might have a local insert plus a replicated one, both the same value in the unique column, and different nodes reject different inserts.

smilliken 3y ago
Agreed. Also exclusion constraints (a generalization of unique constraints). Also foreign key constraints, because one database might delete a record simultaneously with another database adding a reference to it.
- hinkley 3y ago
  Deleting from a database is a sucker's bet. If you add to a record and mark it dead there are fewer semantic problems to deal with. Partial indexes give you approximately the same speedup as deletes.
  - arthurcolle 3y ago
    Why is this comment greyed out? Seems like a reasonable comment
    - hobs 3y ago
      I didn't vote it down but there's huge tradeoffs here: 1. Row bloat. 2. Bad plan estimation due to bloat. 3. Needing filtered indexes for anything that's a row based unique constraint. 4. All history in one table means changing schemas is a PITA, and you're backfilling stuff that's not real. 5. There's more but that's just off the top.
      - hinkley 3y ago
        1. Most of the problem with row 'bloat' is complexity of indexes, row deletion increases the number of required indexes, and lack of those indexes is a footgun 2. Got a citation for that? Pretty sure #3 makes that not true, but I'm open to be educated. 3. Yes, and? You're going to have those indexes anyway. That's the point. 4. You don't have to have all rows in a single table just because you took delete out of any user-facing query logic. 5. I think you need some more examples.
        arthurcolle 3y ago
        I agree with all of your comments - this is why I asked initial question because literally all his initial comments seems perfectly valid. I appreciate the discourse, and I work on this stuff every day so I was just confused
        hobs 3y ago
        And the number of rows in the table, effectively bloating it for all time. Deleting rows should generally be via the same type of query patterns you use to find them, or that's pretty weird. I think #4 is something that most people dont implement - ime they just tombstone in their own table, and then all of the other problems are pretty rampant. Having separate indexes for deletes might be a thing, but taking a history of every change to a thing in a relational way is still a big pain because of the schema cost, merging changes in an audit table when its really a slowly changing dimension is really weird to most query patterns. To #2 - it depends on your query engine, but if every query does have filtered indexes there's still a cost to having two tables in one table (ignoring #4)
        hinkley 3y ago
        > And the number of rows in the table, effectively bloating it for all time. Particularly in the case of growth oriented companies, if your user base is growing exponentially, then half of your records are less than a year old anyway. This bloat is not the problem you make it. And as I said elsewhere, a row that is tombstoned can be deleted offline at some cadence that doesn’t break your other workflows. For instance when nearly all of its siblings are also dead.
    - hinkley 3y ago
      HN is not immune to forgetting things we used to have consensus on. There's always someone who is prepared to downvote, literally or figuratively, something that will eventually be the solution to some problem they created in production. When I get downvoted for ideas and not demeanor, 2/3rds of the time it's just job security.
      - refulgentis 3y ago
        Neutral party here: not the case, on your end, the mind-reading distracts from hearing the voice of Chesterton talking about his fence. c.f. sibling comment written 10 minutes before yours, several trivial things. More concretely from a downvoter: 1. I want _nothing_ to do with user data. Nothing. Toxic nuclear waste. The idea of keeping the waste on hand needs strong justification. 2. The idea of "just leave the rows but mark is_deleted" has a fundamental distate to a profession where O(n) is a constant worry. 3. The breezy way it dismisses this as an obvious solution that ~eveyone agrees suggets a small breadth of experience. That's not bad! But then we're at Chesterton's fence. In summary, you can get downvoted for ruling out someone else's concrete lived experience, not just because your dangerous ideas threaten their paycheck (I don't even have a job!)
        hinkley 3y ago
        The lived experience you talk about is that this is a difficult problem without a solution, not a different solution that I dismissed. What I dismissed is the problem. How do we solve X? You don't solve X, you solve Y. The XY problem, not Chesterton's Fence. Not sure I follow you entirely on user data. There's user data that absolutely requires an audit trail, like subscriptions or orders. There's user data that might start out as null and eventually transition to an inane value, like avatar or self-description. You don't need complex merge rules for that data. It possibly only happens when their account is being hacked and that's not the data you need in that particular situation, yeah?
        vlovich123 3y ago
        Couldn’t you just mark a deletion timestamp and then lazily delete later as part of a GC operation?
  - cpursley 3y ago
    Another option is an audit log that tracks all changes to a record.
    - hinkley 3y ago
      It's possible to do row deletions and an audit log table, but do you see a lot of people who do both? I haven't. It's easier to keep a FK to the mutable table.
      - epcoa 3y ago
        > but do you see a lot of people who do both? Yes, pretty sure it’s very common. That doesn’t make it the greatest design, common doesn’t mean good.
        hinkley 3y ago
        I have too much scar tissue around accidental table scans caused by the fact that foreign key constraints don't implicitly create an index. If you think logarithmic insertion time is bad for your p95 times, try quadratic times on delete operations gone horribly wrong.
  - azlev 3y ago
    I wouldn't word that way. Delete records is how SQL works. Also it's wiser to move old information to a separate database instead of paying maintenance costs of old or dead records. There are a lot of gotchas in keep old data, like GDPR.
    - hinkley 3y ago
      Only if all records age out at the same rate, otherwise you’re making Swiss cheese of your tables. And/or you’re losing history data. If your rows do age out at the same time, then I’d be tempted to ask you why you’re storing logs in a relational database, because that’s essentially what you have at that point.
rvdginste 3y ago
Would I be wrong in thinking that this "bi-directional logical replication" is only usable in a very limited number of use cases? As you mention, unique constraints pose a problem, and a lot of tables would have unique constraints to protect business keys? The article references another article on this, the refers to the PostgreSQL documentation: - conflicts [1] - restrictions [2] You need to be very aware of the limitations to decide whether this is usable in a specific context. I don't think this is really intended to be used for "bi-directional" logical replication. [1] https://www.postgresql.org/docs/current/logical-replication-conflicts.html https://www.postgresql.org/docs/current/logical-replication-... [2] https://www.postgresql.org/docs/current/logical-replication-restrictions.html https://www.postgresql.org/docs/current/logical-replication-...
- philjohn 3y ago
  This feels like something for the specific use case of heavy writes that are treating the DB like an append-only log, and then running analytics queries over the top. Something like a distributed metrics collection system.
dazzla76 3y ago
Unique constraints can still be used as long as you accommodate the multiple sources. Something as simple of increment by 2 and the servers alternative odd and even as the starting point. I used to do this with 3 way DB2 LUW replication back when i was a DBA. It all needs a bit of thought but is fairly simple when it comes down to it.

egamirorrim 3y ago

I don't know that this article tackled the real elephant in the room for bi-di replication. What happens when the same insert happens on both primaries at once, who wins?

singron 3y ago
They will each apply the insert locally. Then they will attempt to replicate that insert to each other. Each will attempt to apply the replicated conflicting insert, which will cause an error and halt replication for both nodes. If you update the same data on both nodes, this is a recipe for almost certain disaster. Postgres is not a distributed database and this doesn't make it one.
- cogman10 3y ago
  Yeah, read the docs here [1] and really does not seem like this would be a good system to work with. To make something fault tolerant you have to have a side subscription running watching for server errors so you can resolve these conflicts when they arise. pglogical gave you the option to determine the winner of a conflicting write (which I think is preferable). The inbuilt postgres stuff seems like it'd be rife with potential errors. [1] https://www.postgresql.org/docs/16/logical-replication-conflicts.html https://www.postgresql.org/docs/16/logical-replication-confl...
  - brianwawok 3y ago
    I assume you just go very basic. All updates to table X go to database Y. Don’t have all the clients slamming different databases with updates to the same stuff. Not ideal but would still help with some scale stuff.
    - philjohn 3y ago
      Or an analytics system - spray events at whatever server, because each row is a unique record separate from the others, you get no conflicts. Benefit - if your current system with a single primary for writes is suffering and your data and use-case fits this pattern, you can increase your write throughput without scaling up.

MarvinYork 3y ago

Could this handle more than two databases with this technique or would it generate the mentioned loop when a third database sends the data to multiple databases?

ttfkam 3y ago
Yes, the implementation distinguishes between a replication client and a user/app client specifically to prevent multiple query invocations. Network latency and CAP tradeoffs still apply of course, but you can absolutely scale well beyond two instances with this.

westurner 3y ago

/? postgres replication https://hn.algolia.com/?dateRange=all&page=0&prefix=true&query=postgresql%20replication&sort=byDate&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que... "Pgactive: Active-Active Replication Extension for PostgreSQL on Amazon RDS" (2023) https://news.ycombinator.com/item?id=37838223 https://news.ycombinator.com/item?id=37838223 /? "pgactive" site:github.com https://www.google.com/search?q=%22pgactive%22+site%3Agithub.com https://www.google.com/search?q=%22pgactive%22+site%3Agithub... cloudnative-pg/cloudnative-pg: https://github.com/cloudnative-pg/cloudnative-pg https://github.com/cloudnative-pg/cloudnative-pg : > primary/standby cloudnative-pg > Logical replication #13: https://github.com/cloudnative-pg/cloudnative-pg/issues/13 https://github.com/cloudnative-pg/cloudnative-pg/issues/13 /? logical replication: https://www.google.com/search?q=logical+replication https://www.google.com/search?q=logical+replication pgadmin docs > Publication Dialog; logical replication: https://www.pgadmin.org/docs/pgadmin4/development/publication_dialog.html https://www.pgadmin.org/docs/pgadmin4/development/publicatio... https://github.com/dalibo/pg_activity#faq https://github.com/dalibo/pg_activity#faq ; pip install `pg_activity[psychopg]` : > FAQ: I can't see my queries only TPS is shown (How) Do any ~pg_top tools delineate logical replication activity? pgcenter > PostgreSQL statistics [virtual tables] (and also /proc) https://github.com/lesovsky/pgcenter#postgresql-statistics https://github.com/lesovsky/pgcenter#postgresql-statistics ... "Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps" (2023) from the creators of CRDTs https://news.ycombinator.com/item?id=37590257 https://news.ycombinator.com/item?id=37590257 electric-sql/electric: https://github.com/electric-sql/electric https://github.com/electric-sql/electric : > ElectricSQL is a local-first software platform that makes it easy to develop high-quality, modern apps with instant reactivity, realtime multi-user collaboration and conflict-free offline support. > Local-first is a new development paradigm where your app code talks directly to an embedded local database and data syncs in the background via active-active database replication. Because the app code talks directly to a local database, apps feel instant. Because data syncs in the background via active-active replication it naturally supports multi-user collaboration and conflict-free offline "SQLedge: Replicate Postgres to SQLite on the Edge" (2023) https://news.ycombinator.com/item?id=37067980 https://news.ycombinator.com/item?id=37067980 ; gh-ost, dolt, sqldiff /?hnlog ctrl-f Consistenc, Consensus : - "A few notes on message passing" https://news.ycombinator.com/item?id=26535969 https://news.ycombinator.com/item?id=26535969 : > The C in CAP theorem is for Consistency [3][4]. [...] > [3] https://en.wikipedia.org/wiki/Consistency_model https://en.wikipedia.org/wiki/Consistency_model > [4] https://en.wikipedia.org/wiki/CAP_theorem https://en.wikipedia.org/wiki/CAP_theorem

kroolik 3y ago

I find the explanation of logical and physical replication to be weird. Seems like only logical replication transfers changes. I have always thought physical replication is replicating using WAL logs, which is a stream of changes. The logical replication would execute the queries on the replicating nodes, leaving WAL management to the replica. This is also the reason why physical replication requires same pg versions (there may be diffs to the physical format of data in WAL), whilst logical replication uses domain language, which is independent from physical layout.

tristenharr 3y ago

This is exciting, I’m hopeful that we get distributed SQLite via something like Turso that can hook into this to stream subsets of data into SQLite edge-caches that can be pushed through the edge onto devices via something like embedded replicas. I know experimental work has been done here with pg_turso (I recently wrote my first few lines of zig to add support for some additional data-types for it) and this bodes well with the recent announcement of Postgres coming to fly.io, I dream of a day when I can make Postgres and SQLite play nicely together.