[HN Gopher] Overcoming pitfalls of Postgres logical decoding
___________________________________________________________________
Overcoming pitfalls of Postgres logical decoding
Author : saisrirampur
Score : 33 points
Date : 2024-06-14 16:05 UTC (1 days ago)
(HTM) web link (blog.peerdb.io)
(TXT) w3m dump (blog.peerdb.io)
| xnickb wrote:
| > If you are replicating changes from only a few tables, ensure
| that you create a PUBLICATION that includes just those tables.
| Postgres efficiently persists changes for only those tables in
| the replication slot. This helps reduce the size of the
| replication slot and improves logical decoding performance.
|
| where is this coming from?
| nijave wrote:
| I didn't think the replication slot "stored" changes. I thought
| it just had a WAL position to hold on to and filtering was done
| by WAL sender (so it helps reduce outgoing traffic but not
| retained WAL)
|
| But maybe I'm wrong here...
| xnickb wrote:
| You are not.
| matharmin wrote:
| This is a great list to start with. Postgres logical replication
| is great for capturing changes, but the documentation is really
| lacking, and gives the impression that it was really just
| designed for Postgres -> Postgres replication, with a database
| administrator present to handle any edge cases.
|
| Some others I've run into as part of developing a consumer for
| PowerSync:
|
| 1. LSN is strictly increasing for transaction commits, but not
| for row operations inside transactions. Specifically, when you
| have multiple concurrent transactions, they'll have overlapping
| LSN ranges for operations inside them.
|
| 2. When you restart replication, it always restarts at the
| beginning of a transaction. Combined with the above, there's not
| much of a point in trying to track progress within transactions.
|
| 3. Some hosting providers such as AWS RDS flushes the WAL every 5
| minutes, causing an increase of 64MB each time. Make sure to
| acknowledge progress on "Primary keepalive messages", otherwise
| the WAL for an idle database will grow at over 750MB/hour.
| (Unlike what the article mentions, you don't need heartbeat
| messages as long as you acknowledge the primary keepalive
| messages.)
|
| 4. If you have a table without an unique index, you can replicate
| using REPLICA IDENTITY FULL or REPLICA IDENTITY NOTHING. But if
| you have duplicate rows in the source table, there will be no way
| to distinguish them with logical replication, and you can end up
| with inconsistent state.
|
| DDL changes are also tricky. As the post mentions, you can detect
| columns added or removed using just the logical replication
| protocol. But there are other cases not covered:
|
| 1. You only get notifications of new tables once data has been
| inserted.
|
| 2. You get no notifications of dropped tables.
|
| 3. If a column is added with a default value, that default value
| is present on new rows, but you don't get any updates on existing
| rows.
|
| Catering for initial snapshots is also not trivial:
|
| 1. Create a new logical replication slot using the logical
| replication protocol, with the "EXPORT_SNAPSHOT" option to create
| a "snapshot" of the state.
|
| 2. On a separate connection, start a repeatable read transaction,
| with `SET TRANSACTION SNAPSHOT '<snapshot>'` using the above
| snapshot.
|
| 3. Do the initial snapshot using this transaction.
|
| 4. Once the snapshot is complete, continue using logical
| replication.
|
| Care must be taken that all data types match between the initial
| snapshot and logical replication. Also check things like columns
| matching up between the two (e.g. generated columns included in
| the initial snapshot, but not logical replication).
|
| There are also a couple of cases where replication slots will
| break. I've seen a couple of cases triggered by the source
| database running out of disk space, despite the rest of the
| database having recovered in the meantime. Some examples:
|
| 1. I've seen a delay of hours without any messages being sent on
| the replication protocol, likely due to a large transaction in
| the WAL not committed when running out of disk space.
|
| 2. `PgError.58P02: could not create file
| \"pg_replslot/<slot_name>/state.tmp\": File exists`
|
| 2. `replication slot "..." is active for PID ...`, with some
| system process holding on to the replication slot.
|
| 3. `can no longer get changes from replication slot
| "<slot_name>". ... This slot has never previously reserved WAL,
| or it has been invalidated`.
|
| Each one of those can be handled, typically by restarting
| postgres and/or deleting the replication slot. But there are many
| different edge cases that are difficult to know about and test
| for, before suddenly running into it in a production system. So
| generally it's very difficult to create a system that "just
| works", and can automatically recover without manual intervention
| in cases like those.
| nijave wrote:
| >DDL changes are also tricky
|
| You can use triggers to write DDL to a table which is then
| replicated (and optionally use triggers or app logic on the
| consumer side to process them)
|
| >Snapshots
|
| I feel like these are way under documented. You can combine
| with tools like pg_dump and set "section" or schema only dump.
| Pgcopydb also supports snapshots or even combined with plain
| COPY
|
| I recently learned SUBSCRIPTIONS with data copy just automate
| snapshots, COPY, and appear to have some optimization to speed
| up initial sync with multiple replication slots (I originally
| thought there was something special at the protocol level)
___________________________________________________________________
(page generated 2024-06-15 23:02 UTC)