On Thu, Mar 21, 2024 at 7:48 AM Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> 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
