Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

On Fri, 2019-10-04 at 10:00 -0300, Marcelo Lacerda wrote:
> There are a few instances where the release notes seem to indicate
> that the administrator should use pg_dump to upgrade a database so
> that improvements on btree can be available.
>
> Here are they:
>
> 1.
>
> >In new btree indexes, the maximum index entry length is reduced by
> eight bytes, to improve handling of duplicate entries (Peter
> Geoghegan)
> > This means that a REINDEX operation on an index pg_upgrade'd from a
> previous release could potentially fail.
>
>
> 2.
> >Improve performance and space utilization of btree indexes with many
> duplicates (Peter Geoghegan, Heikki Linnakangas)
> >...
> >Indexes pg_upgrade'd from previous releases will not have these
> benefits.
>
> 3.
> >Allow multi-column btree indexes to be smaller (Peter Geoghegan,
> Heikki Linnakangas)
> >...
> >Indexes pg_upgrade'd from previous releases will not have these
> benefits.
>
>
> My questions are:
>
> 1. Is this a current limitation of pg_upgrade that will be dealt
> afterwards?
>
> 2. Are we going to see more of such cases were pg_upgrade leaves the
> database incompatible with newer features.
>
> 3. What's the recommendation for administrators with databases that
> are too large to be upgraded with pg_dump?

pg_upgrade doesn't touch the index data, so it cannot rewrite indexes
to take advantage of these improvements.

There is no incompatibility involved.

You can always REINDEX some indexes later.
Now that we have REINDEX CONCURRENTLY, it shouldn't hurt as much.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com