Re: pgsql: Track last_inactive_time in pg_replication_slots.

On Wed, Mar 27, 2024 at 11:06 AM Nathan Bossart
<nathandbossart(at)gmail(dot)com> wrote:
> IMHO the use-case where this doesn't work so well is when you have many,
> many servers to administer (e.g., a cloud provider). In those cases,
> picking a default timeout to try to prevent wraparound is going to be much
> less accurate, as any reasonable value you pick is still going to be
> insufficient in some cases. I think the XID-based parameter would be
> better here; if the server is at imminent risk of an outage due to
> wraparound, invalidating the slots is probably a reasonable course of
> action.

Well, I'm certainly willing to believe that your experience with
administering servers in the cloud is superior to mine. I don't really
understand why it makes a difference, though. Whether you have one
server or many, I agree that it is reasonable to invalidate slots when
XID wraparound looms. But also, whether you have one server or many,
by the time wraparound looms, you will typically have crippling bloat
as well. If preventing that bloat isn't important or there are other
defenses against it, then I see the value of the XID-based cutoff as a
backstop. And I will admit that in an on-prem installation, I've
occasionally seen situations where lots of XIDs got burned without
really causing a lot of bloat -- say, because there are heavily
updated staging tables which are periodically truncated, and very
little modification to long-lived data.

I'm not really strongly against having an XID-based threshold if smart
people such as yourself want it. I just think for a lot of users it's
going to be fiddly to get right.

--
Robert Haas
EDB: http://www.enterprisedb.com