Re: Question about PostgreSQL upgrade from version 12 to version 15

On Thu, Mar 21, 2024 at 7:48 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> On 2024-Mar-21, Joseph Kennedy wrote:
> > I'm planning to upgrade my PostgreSQL database from version 12 to
> > version 15 using pg_upgrade. After completing the upgrade process, I'm
> > curious to know whether it's necessary to reindex the database.
> >
> > Could anyone please clarify whether reindexing is required after
> > completing the upgrade process from PostgreSQL version 12 to version
> > 15 using pg_upgrade?
>
> A reindex(*) is necessary for indexes on textual columns(**), and only
> if you're also changing the underlying OS version(***) such that the
> collation behavior changes. If you're keeping the database on the same
> OS version, there's no need to reindex anything.
>
> (*) More than reindex actually: you may need to refresh materialized
> views and consider carefully any partition bounds you may have, if you
> have any partition keys that include textual columns. Even worse: if
> you have FDWs on a Postgres server that queries a table from another
> Postgres server with different collation libraries, it could bit you
> there too.
>
> (**) textual column in this case means anything that is affected by
> collation changes; typically that's things like varchar, text, citext,
> etc, for which a collation other than "C" is explicit or implied. You
> don't need to do anything for indexes on numbers, dates, geometries,
> etc, nor for textual columns where the index is defined with the C
> collation.
>
> (***) the underlying C library changes collation rules rather frequently
> (especially glibc), because the developers of said libraries don't
> consider that this has any important, permanent impact (but it does
> impact indexes for Postgres). Most such changes are innocuous, but from
> time to time they make changes that wreak havoc. If you're using ICU
> collations with your Postgres 12 databases, you may also be affected if
> you upgrade from one ICU version to another.
>
>
> Joe Conway gave a very good presentation on this topic recently:
> https://www.postgresql.eu/events/fosdem2024/schedule/session/5127-collation-challenges-sorting-it-out/
>

As a bonus, if you do decide to reindex, you'll also benefit from the
index deduplication work that was introduced in v13, which should help
reduce disk space and make queries a little faster.

Robert Treat
https://xzilla.net