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)
