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?
We had thought that the physical slot would only retain the WAL it needed for its own restart_lsn, not the segments needed by only logical slots as well. Any explanation would be much appreciated!
Thanks,
Jeremy