Re: Legacy GiST invalid tuples

On 2018-Jul-04, Tom Lane wrote:

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > Requiring a scan of all indexes during pg_upgrade might increase the
> > upgrade time prohibitively for some sites, so I don't think that's a
> > good solution.
>
> Perhaps VACUUM could be taught to clean up invalid entries? That
> wouldn't help Andrey's unstated goal of being able to re-use the bits
> for some other purpose in v12, but it might be practical to re-use
> them sometime sooner than v17.

We tried to clean up HEAP_MOVED_IN / HEAP_MOVED_OFF a long time ago, but
gave up :-) I recall Andres posted a write-up about adding columns to
pg_class to indicate "what version did last vacuum this whole table", as
a marker for features that are no longer needed. Maybe that idea can be
reused for this purpose. I'm thinking pg_upgrade can use its --check
phase to look for indexes marked as older than X (possibly containing
invalid tuples), so it instructs the user to run vacuum on it prior to
the upgrade. I think the soonest this can work is to add the column in
pg12 so that it can be used to upgrade to pg13.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services