Re: xid wraparound danger due to INDEX_CLEANUP false - Mailing list pgsql-hackers

On Wed, Jun 24, 2020 at 4:02 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > Apparently, what we're really doing here is that even when
> > INDEX_CLEANUP is OFF, we're still going to keep all the dead tuples.
> > That seems sad, but if it's what we have to do then it at least needs
> > comments explaining it.
>
> +1. Though I think that AMs should technically have the right to
> consider it advisory.

I'm not really convinced. I agree that from a theoretical point of
view an index can have arbitrary needs and is the arbiter of its own
needs, but when I pull the emergency break, I want the vehicle to
stop, not think about stopping. There's a fine argument for the idea
that depressing the regular brake pedal entitles the vehicle to
exercise some discretion, and on modern cars it does (think ABS, if
nothing else). But pulling the emergency break is a statement that I
wish to override any contrary judgement about whether stopping is a
good idea. I think this option is rightly viewed as an emergency
break, and giving AMs the right to decide that we'll instead pull off
at the next exit doesn't sit well with me. At the end of the day, the
human being should be in charge, not the program.

(Great, now Skynet will be gunning for me...)

> 1. How often is that likely to happen in The Real World™?
>
> 2. If we fail to do cleanup and leak already-deleted pages, how bad is
> that? ( Both in general, and in the worst case.)
>
> I'll hazard a guess for 1: I think that it might not come up that
> often. Page deletion is often something that we hardly ever need. And,
> unlike some DB systems, we only do it when pages are fully empty
> (which, as it turns out, isn't necessarily better than our simple
> approach [1]). I tend to think it's unlikely to happen in cases where
> INDEX_CLEANUP is used, because those are cases that also must not have
> that much index churn to begin with.

I don't think I believe this. All you need is one small range-deletion, right?

> Then there's question 2. The intuition behind the approach from
> Sawada-san's patch was that allowing wraparound here feels wrong -- it
> should be in the AM's hands. However, it's not like I can point to
> some ironclad guarantee about not leaking deleted pages that existed
> before the INDEX_CLEANUP feature. We know that the FSM is not crash
> safe, and that's that. Is this really all that different? Maybe it is,
> but it seems like a quantitative difference to me.

I don't think I believe this, either. In the real-world example to
which you linked, the user ran REINDEX afterward to recover from index
bloat, and we could advise other people who use this option that it
may leak space that a subsequent VACUUM may fail to recover, and
therefore they too should consider REINDEX. Bloat sucks and I hate it,
but in the vehicle analogy from up above, it's the equivalent of
getting lost while driving someplace. It is inconvenient and may cause
you many problems, but you will not be dead. Running out of XIDs is a
brick wall. Either the car stops or you hit the wall. Ideally you can
manage to both not get lost and also not hit a brick wall, but in an
emergency situation where you have to choose either to get lost or to
hit a brick wall, there's only one right answer. As bad as bloat is,
and it's really bad, there are users who manage to run incredibly
bloated databases for long periods of time just because the stuff that
gets slow is either stuff that they're not doing at all, or only doing
in batch jobs where it's OK if it runs super-slow and where it may
even be possible to disable the batch job altogether, at least for a
while. The set of users who can survive running out of XIDs is limited
to those who can get by with just read-only queries, and that's
practically nobody. I have yet to encounter a customer who didn't
consider running out of XIDs to be an emergency.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company