On Tue, 19 Nov 2019 at 05:37, Jeremy Finzel <finzelj@gmail.com> wrote:
We had a scenario today that was new to us. We had a logical replication slot that was severely far behind. Before dropping this logical slot, we made a physical point-in-time-recovery snapshot of the system with this logical slot.
This logical slot was causing severe catalog bloat. We proceeded to drop the logical slot which was over 12000 WAL segments behind. The physical slot was only a few 100 segments behind and still in place.
But now proceeding to VAC FULL the catalog tables did not recover any bloat beyond the now-dropped logical slot. Eventually to our surprise, we found that dropping the physical slot allowed us to recover the bloat.
We saw in forensics after the fact that xmin of the physical slot equaled the catalog_xmin of the logical slot. Is there some dependency here where physical slots made of a system retain all transactions of logical slots it contains as well? If so, could someone help us understand this, and is there documentation around this? Is this by design?
I expect that you created the replica in a manner that preserved the logical replication slot on it. You also had hot_standby_feedback enabled.
PostgreSQL standbys send the global xmin and (in Pg10+) catalog_xmin to the upstream when hot_standby_feedback is enabled. If there's a slot holding the catalog_xmin on the replica down, that'll be passed on via hot_standby_feedback to the upstream. On Pg 9.6 or older, or if the replica isn't using a physical replication slot, the catalog_xmin is treated as a regular xmin since there's nowhere in PGPROC or PGXACT to track the separate catalog_xmin. If the standby uses a physical slot, then on pg10+ the catalog_xmin sent by the replica is stored as the catalog_xmin on the physical slot instead.
Either way, if you have hot_standby_feedback enabled on a standby, that feedback includes the requirements of any replication slots on the standby.
