Re: pgsql: Track last_inactive_time in pg_replication_slots.

On Tue, Mar 26, 2024 at 03:44:29PM +0530, Amit Kapila wrote:
> On Tue, Mar 26, 2024 at 2:11 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>> Well, I think a GUC is good to have regardless of the slot parameter,
>> because the GUC can be used as an instance-wide protection against going
>> out of disk space because of broken replication. However, now that I
>> think about it, I'm not really sure about invalidating a slot based on
>> time rather on disk space, for which we already have a parameter; what's
>> your rationale for that? The passage of time is not a very good
>> measure, really, because the amount of WAL being protected has wildly
>> varying production rate across time.
>
> The inactive slot not only blocks WAL from being removed but prevents
> the vacuum from proceeding. Also, there is a risk of transaction Id
> wraparound. See email [1] for more context.

FWIW I'd really prefer to have something like max_slot_xid_age for this. A
time-based parameter would likely help with most cases, but transaction ID
usage will vary widely from server to server, so it'd be nice to have
something to protect against wraparound more directly. I don't object to a
time-based setting as well, but that won't always work as well for this
particular use-case, especially if we are relying on users to set a
slot-level parameter.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com