Re: Regarding huge logical slot lag between source and target

Hi Everyone

There are typically two main drivers that cause logical replication to fall
behind with no ERROR.

1. Updates are using REPLICA IDENTITY FULL This causes sequential scans to
apply the update. On a large table the logical replication worker on the
subscriber will choke. No setting will fix that—the only solution is to
change the updates to use a unique index or primary key.

2. The volume of updates across several tables is spread across multiple
sessions on the publisher, but is funneled down to a single worker on the
subscriber. This cannot be fixed with setting changes or by turning on
parallelization on the subscriber. Parallel apply is by transaction

To identify the root cause we need to peek at what logical replication is
pushing to the subscriber.

-- Copies the slot in question into a temporary slot that lives only as
long as the session that created it,
-- using the test_decoding plugin so we can read the output
SELECT * FROM pg_copy_logical_replication_slot('slot_fall_behind',
'copy_slot', true, 'test_decoding');

-- Peek at the changes
SELECT * FROM pg_logical_slot_peek_changes('copy_slot', NULL, 50);

If you see `UPDATE` rows that include all columns from the table (old and
new values), replica identity is set to FULL.
On PG 16 you cannot usefully change replica identity only on the
subscriber.
The publisher must have a suitable unique key and you must alter the
replica identity there:

ALTER TABLE <table> REPLICA IDENTITY USING INDEX <my_index>;

If the volume of updates is simply too high, you will see a large number of
individual DELETE/UPDATE/INSERT statements in separate transactions. This
prevents parallelization and the subscriber just chokes on the volume.
In that case, split the high-volume tables into separate
publications/subscriptions.