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

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/

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Por suerte hoy explotó el califont porque si no me habría muerto
de aburrido" (Papelucho)