pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about PostgreSQL upgrade from version 12 to version 15
3+ messages / 3 participants
[nested] [flat]
* Question about PostgreSQL upgrade from version 12 to version 15
@ 2024-03-21 09:48 Joseph Kennedy <joseph.kennedy.486@gmail.com>
2024-03-21 11:47 ` Re: Question about PostgreSQL upgrade from version 12 to version 15 Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 3+ messages in thread
From: Joseph Kennedy @ 2024-03-21 09:48 UTC (permalink / raw)
To: pgsql-general
Hello everyone,
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?
Thank you in advance for your assistance!
Best regards,
Joseph=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about PostgreSQL upgrade from version 12 to version 15
2024-03-21 09:48 Question about PostgreSQL upgrade from version 12 to version 15 Joseph Kennedy <joseph.kennedy.486@gmail.com>
@ 2024-03-21 11:47 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-03-21 15:17 ` Re: Question about PostgreSQL upgrade from version 12 to version 15 Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 3+ messages in thread
From: Alvaro Herrera @ 2024-03-21 11:47 UTC (permalink / raw)
To: Joseph Kennedy <joseph.kennedy.486@gmail.com>; +Cc: pgsql-general
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-ou...
--
Á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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about PostgreSQL upgrade from version 12 to version 15
2024-03-21 09:48 Question about PostgreSQL upgrade from version 12 to version 15 Joseph Kennedy <joseph.kennedy.486@gmail.com>
2024-03-21 11:47 ` Re: Question about PostgreSQL upgrade from version 12 to version 15 Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2024-03-21 15:17 ` Robert Treat <rob@xzilla.net>
0 siblings, 0 replies; 3+ messages in thread
From: Robert Treat @ 2024-03-21 15:17 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Joseph Kennedy <joseph.kennedy.486@gmail.com>; pgsql-general
On Thu, Mar 21, 2024 at 7:48 AM Alvaro Herrera <alvherre@alvh.no-ip.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-ou...
>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-21 15:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-21 09:48 Question about PostgreSQL upgrade from version 12 to version 15 Joseph Kennedy <joseph.kennedy.486@gmail.com>
2024-03-21 11:47 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-03-21 15:17 ` Robert Treat <rob@xzilla.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox