public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version 10+ messages / 5 participants [nested] [flat]
* [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-24 03:30 [email protected] 0 siblings, 1 reply; 10+ messages in thread From: [email protected] @ 2026-03-24 03:30 UTC (permalink / raw) To: [email protected] Hi hackers, I initially assumed that PG18's statistics transfer during pg_upgrade would only work when upgrading from PG18 to a future version. I hear this misconception from others fairly often too. In reality, it works when upgrading *to* PG18 from any older supported version (PG14, 15, 16, 17), because pg_upgrade uses the new cluster's pg_dump, which reads from standard catalog views (pg_class and pg_stats) that exist in all PostgreSQL versions. The pg_dump docs already note that "pg_dump can also dump from PostgreSQL servers older than its own version" — so no changes needed there. The confusion seems specific to the major upgrade context. The attached patch adds a brief clarification to pgupgrade.sgml to address this. I tested pg_dump --statistics-only from PG18 against PG16 and PG14 — both work as expected, stats are transferred and restored correctly. -- Nik Attachments: [text/x-patch] pg-stats-cross-version.patch (2.1K, ../../CAM527d_Qt9ZGt99vgTaidfObRiDH0R5F_uEfum9zHeZbc+LwCQ@mail.gmail.com/2-pg-stats-cross-version.patch) download | inline diff: From 339ce77cac927e463324276a6691962c1de852d6 Mon Sep 17 00:00:00 2001 From: Nikolay Samokhvalov <[email protected]> Date: Tue, 24 Mar 2026 03:26:24 +0000 Subject: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version. Clarify in pgupgrade.sgml that optimizer statistics transfer works regardless of the old cluster's major version. The new cluster's pg_dump reads statistics from standard catalog views (pg_class and pg_stats) in the old cluster, which exist in all supported PostgreSQL versions. The restore functions (pg_restore_relation_stats and pg_restore_attribute_stats) only need to exist on the target cluster. Tested with PG14->PG18 and PG16->PG18. --- doc/src/sgml/ref/pgupgrade.sgml | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index 38ca09b..d552907 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -833,10 +833,15 @@ psql --username=postgres --file=script.sql postgres <para> Unless the <option>--no-statistics</option> option is specified, <command>pg_upgrade</command> will transfer most optimizer statistics - from the old cluster to the new cluster. This does not transfer - all statistics, such as those created explicitly with - <xref linkend="sql-createstatistics"/>, custom statistics added by - an extension, or statistics collected by the cumulative statistics system. + from the old cluster to the new cluster. This works regardless of + the old cluster's major version, because the new cluster's + <command>pg_dump</command> reads statistics from standard catalog views + (<structname>pg_class</structname> and <structname>pg_stats</structname>) + that exist in all supported <productname>PostgreSQL</productname> + versions. However, some statistics are not transferred: those created + with <xref linkend="sql-createstatistics"/>, custom statistics added by + an extension, and statistics collected by the cumulative statistics + system. </para> <para> -- 2.43.0 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-24 06:59 Laurenz Albe <[email protected]> parent: [email protected] 0 siblings, 1 reply; 10+ messages in thread From: Laurenz Albe @ 2026-03-24 06:59 UTC (permalink / raw) To: [email protected]; [email protected] On Mon, 2026-03-23 at 22:30 -0500, [email protected] wrote: > I initially assumed that PG18's statistics transfer during pg_upgrade > would only work when upgrading from PG18 to a future version. I hear > this misconception from others fairly often too. > > In reality, it works when upgrading *to* PG18 from any older supported > version (PG14, 15, 16, 17), because pg_upgrade uses the new cluster's > pg_dump, which reads from standard catalog views (pg_class and > pg_stats) that exist in all PostgreSQL versions. The pg_dump docs > already note that "pg_dump can also dump from PostgreSQL servers older > than its own version" — so no changes needed there. The confusion > seems specific to the major upgrade context. > > The attached patch adds a brief clarification to pgupgrade.sgml to address this. > > I tested pg_dump --statistics-only from PG18 against PG16 and PG14 — > both work as expected, stats are transferred and restored correctly. I agree that it would be a good idea to explicitly mention that statistics are transferred when upgrading from older PostgreSQL versions. However, I think that your patch offers too much detail, like from which relations the statistics are extracted. Anybody who wants to know that level of detail probably has to read the source code anyway. How about adding a simple remark, like --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -837,6 +837,8 @@ psql --username=postgres --file=script.sql postgres all statistics, such as those created explicitly with <xref linkend="sql-createstatistics"/>, custom statistics added by an extension, or statistics collected by the cumulative statistics system. + Transferring optimizer statistics works regardless of the old cluster's + major version. </para> <para> Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-24 12:36 Nikolay Samokhvalov <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Nikolay Samokhvalov @ 2026-03-24 12:36 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <[email protected]> wrote: ... > I think that your patch offers too much detail, > like from which relations the statistics are extracted. Anybody > who wants to know that level of detail probably has to read the > source code anyway. > > How about adding a simple remark, like > > --- a/doc/src/sgml/ref/pgupgrade.sgml > +++ b/doc/src/sgml/ref/pgupgrade.sgml > @@ -837,6 +837,8 @@ psql --username=postgres --file=script.sql postgres > all statistics, such as those created explicitly with > <xref linkend="sql-createstatistics"/>, custom statistics added by > an extension, or statistics collected by the cumulative statistics > system. > + Transferring optimizer statistics works regardless of the old > cluster's > + major version. > </para> > > <para> That would work too -- my goal was just to resolve confusion. Nik > ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-24 15:04 Bruce Momjian <[email protected]> parent: Nikolay Samokhvalov <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Bruce Momjian @ 2026-03-24 15:04 UTC (permalink / raw) To: Nikolay Samokhvalov <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] On Tue, Mar 24, 2026 at 05:36:42AM -0700, Nikolay Samokhvalov wrote: > On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <[email protected]> wrote: > ... > > I think that your patch offers too much detail, > like from which relations the statistics are extracted. Anybody > who wants to know that level of detail probably has to read the > source code anyway. > > How about adding a simple remark, like > > --- a/doc/src/sgml/ref/pgupgrade.sgml > +++ b/doc/src/sgml/ref/pgupgrade.sgml > @@ -837,6 +837,8 @@ psql --username=postgres --file=script.sql postgres > all statistics, such as those created explicitly with > <xref linkend="sql-createstatistics"/>, custom statistics added by > an extension, or statistics collected by the cumulative statistics > system. > + Transferring optimizer statistics works regardless of the old > cluster's > + major version. > </para> > > <para> > > > That would work too -- my goal was just to resolve confusion. I was under the same impression --- that it only worked with upgrades _from_ PG 18 and later. However, I can't find any documentation on this limitation, so why did so many think this was true? I don't think adding anything to the docs is the right approach. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-24 16:53 Laurenz Albe <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Laurenz Albe @ 2026-03-24 16:53 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; Nikolay Samokhvalov <[email protected]>; +Cc: [email protected] On Tue, 2026-03-24 at 11:04 -0400, Bruce Momjian wrote: > On Tue, Mar 24, 2026 at 05:36:42AM -0700, Nikolay Samokhvalov wrote: > > On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <[email protected]> wrote: > > How about adding a simple remark, like > > > > --- a/doc/src/sgml/ref/pgupgrade.sgml > > +++ b/doc/src/sgml/ref/pgupgrade.sgml > > + Transferring optimizer statistics works regardless of the old cluster's > > + major version. > > > > That would work too -- my goal was just to resolve confusion. > > I was under the same impression --- that it only worked with upgrades > _from_ PG 18 and later. However, I can't find any documentation on this > limitation, so why did so many think this was true? I don't think > adding anything to the docs is the right approach. Bruce, you are confusing me. Your first sentence suggests that you (erroneously) thought that upgrading statistics only works when upgrading *from* v18 or better. But your last sentence suggests that you'd rather not add anything to the documentation that could dispel that misconception. Why? Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-24 18:11 Bruce Momjian <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Bruce Momjian @ 2026-03-24 18:11 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Nikolay Samokhvalov <[email protected]>; [email protected] On Tue, Mar 24, 2026 at 05:53:27PM +0100, Laurenz Albe wrote: > On Tue, 2026-03-24 at 11:04 -0400, Bruce Momjian wrote: > > On Tue, Mar 24, 2026 at 05:36:42AM -0700, Nikolay Samokhvalov wrote: > > > On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <[email protected]> wrote: > > > How about adding a simple remark, like > > I was under the same impression --- that it only worked with upgrades > > _from_ PG 18 and later. However, I can't find any documentation on this > > limitation, so why did so many think this was true? I don't think > > adding anything to the docs is the right approach. > > Bruce, you are confusing me. Your first sentence suggests that you > (erroneously) thought that upgrading statistics only works when upgrading Right. > *from* v18 or better. But your last sentence suggests that you'd rather > not add anything to the documentation that could dispel that misconception. So, we don't normally document cases where a limitation does not exist. I think the logical place to document this is in the PG 18 release notes. I am still confused why people, like myself, got this wrong. What is the source of the confusion? Just unclear release notes? -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-25 06:03 Laurenz Albe <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Laurenz Albe @ 2026-03-25 06:03 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Nikolay Samokhvalov <[email protected]>; [email protected] On Tue, 2026-03-24 at 14:11 -0400, Bruce Momjian wrote: > On Tue, Mar 24, 2026 at 05:53:27PM +0100, Laurenz Albe wrote: > > Bruce, you are confusing me. Your first sentence suggests that you > > (erroneously) thought that upgrading statistics only works when upgrading > > Right. > > > *from* v18 or better. But your last sentence suggests that you'd rather > > not add anything to the documentation that could dispel that misconception. > > So, we don't normally document cases where a limitation does not exist. > I think the logical place to document this is in the PG 18 release > notes. I am still confused why people, like myself, got this wrong. > What is the source of the confusion? Just unclear release notes? I understand now, thanks. I agree with you in principle, but I wouldn't see that as a hard rule that should stand in the way of clarity. Let me quote a precedent from https://www.postgresql.org/docs/current/upgrading.html Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. To me, that clarifies that there is no limitation to performing minor updates with a simple restart. I think that is useful information. Perhaps you'd feel better if we phrase it as a limitation: Transferring optimizer statistics only work when upgrading to PostgreSQL version v18 or later, but there is no such limitation to the version of the old cluster. I like my original suggestion better, though. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-25 16:21 Bruce Momjian <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Bruce Momjian @ 2026-03-25 16:21 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Nikolay Samokhvalov <[email protected]>; [email protected] On Wed, Mar 25, 2026 at 07:03:01AM +0100, Laurenz Albe wrote: > On Tue, 2026-03-24 at 14:11 -0400, Bruce Momjian wrote: > > On Tue, Mar 24, 2026 at 05:53:27PM +0100, Laurenz Albe wrote: > > > Bruce, you are confusing me. Your first sentence suggests that you > > > (erroneously) thought that upgrading statistics only works when upgrading > > > > Right. > > > > > *from* v18 or better. But your last sentence suggests that you'd rather > > > not add anything to the documentation that could dispel that misconception. > > > > So, we don't normally document cases where a limitation does not exist. > > I think the logical place to document this is in the PG 18 release > > notes. I am still confused why people, like myself, got this wrong. > > What is the source of the confusion? Just unclear release notes? > > I understand now, thanks. I agree with you in principle, but I wouldn't > see that as a hard rule that should stand in the way of clarity. > > Let me quote a precedent from https://www.postgresql.org/docs/current/upgrading.html > > Minor releases never change the internal storage format and are always > compatible with earlier and later minor releases of the same major > version number. For example, version 10.1 is compatible with version 10.0 > and version 10.6. > > To me, that clarifies that there is no limitation to performing minor > updates with a simple restart. I think that is useful information. Right. > Perhaps you'd feel better if we phrase it as a limitation: > > Transferring optimizer statistics only work when upgrading to PostgreSQL > version v18 or later, but there is no such limitation to the version of > the old cluster. > > I like my original suggestion better, though. Uh, we added the feature in PG 18, so why would we say it only works in PG 18 --- that is kind of obvious. I am still asking, why did people, like me, think it only worked for _old_ PG 18 servers. If we find where that was communicated, we can clarify it _there_. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-26 01:13 Nikolay Samokhvalov <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Nikolay Samokhvalov @ 2026-03-26 01:13 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] two cents: On Wed, Mar 25, 2026 at 9:21 AM Bruce Momjian <[email protected]> wrote: > > On Wed, Mar 25, 2026 at 07:03:01AM +0100, Laurenz Albe wrote: > > On Tue, 2026-03-24 at 14:11 -0400, Bruce Momjian wrote: > > > On Tue, Mar 24, 2026 at 05:53:27PM +0100, Laurenz Albe wrote: > > > > Bruce, you are confusing me. Your first sentence suggests that you > > > > (erroneously) thought that upgrading statistics only works when upgrading > > > > > > Right. > > > > > > > *from* v18 or better. But your last sentence suggests that you'd rather > > > > not add anything to the documentation that could dispel that misconception. > > > > > > So, we don't normally document cases where a limitation does not exist. I see it rather as an undocumented feature, not an undocumented "limitation that doesn't exist". > > > I think the logical place to document this is in the PG 18 release > > > notes. I am still confused why people, like myself, got this wrong. > > > What is the source of the confusion? Just unclear release notes? > > > > I understand now, thanks. I agree with you in principle, but I wouldn't > > see that as a hard rule that should stand in the way of clarity. > > > > Let me quote a precedent from https://www.postgresql.org/docs/current/upgrading.html > > > > Minor releases never change the internal storage format and are always > > compatible with earlier and later minor releases of the same major > > version number. For example, version 10.1 is compatible with version 10.0 > > and version 10.6. > > > > To me, that clarifies that there is no limitation to performing minor > > updates with a simple restart. I think that is useful information. > > Right. > > > Perhaps you'd feel better if we phrase it as a limitation: > > > > Transferring optimizer statistics only work when upgrading to PostgreSQL > > version v18 or later, but there is no such limitation to the version of > > the old cluster. > > > > I like my original suggestion better, though. > > Uh, we added the feature in PG 18, so why would we say it only works in > PG 18 --- that is kind of obvious. I am still asking, why did people, > like me, think it only worked for _old_ PG 18 servers. If we find where > that was communicated, we can clarify it _there_. If you don't know anything about implementation, it is natural to expect that something new will work only when both old and new clusters support it. That's why I started with words that I see everyone (includfing both of us) were originally confused. I bumped into it in some blog posts and LinkedIn discussions after which decided to clarify [1] since I couldn't find clarification anywhere on surface – only in source code, diving into implementation, and the depth of discussions [2] [3] [4]. It would be great to get it covered in the 2nd (after source code) source of truth-- the docs. [1] https://postgres.ai/blog/20260324-pg18-stats-upgrade-across-versions [2] https://www.postgresql.org/message-id/flat/aC5F1LX6-uL0wRV1%40nathan#f71f3dcd5fd88ee970f0b1ea0a77929... [3] https://www.postgresql.org/message-id/flat/1322262.1741643606%40sss.pgh.pa.us#d666e5e99bbd955a4f2583... [4] https://www.postgresql.org/message-id/flat/Z_BcWVMvlUIJ_iuZ%40nathan#1f6ae665591cb5e5371552761aa9061... ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version @ 2026-03-26 02:33 David G. Johnston <[email protected]> parent: Nikolay Samokhvalov <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: David G. Johnston @ 2026-03-26 02:33 UTC (permalink / raw) To: Nikolay Samokhvalov <[email protected]>; +Cc: Bruce Momjian <[email protected]>; Laurenz Albe <[email protected]>; [email protected] <[email protected]> On Wednesday, March 25, 2026, Nikolay Samokhvalov <[email protected]> wrote: > > If you don't know anything about implementation, it is natural to > expect that something new will work only when both old and new > clusters support it. > > But we don’t call out “new features” in the documentation. Just “features”. And so when the pg_upgrade docs says “pg_upgrade supports upgrades from 9.2.X and later” then unless there is a note to the contrary (which there is not here) that applies to everything pg_upgrade does. Put differently, read the docs as if you were unaware in which version that capability was introduced. This is the style/assumption the documentation is written in/using. David J. ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-03-26 02:33 UTC | newest] Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-24 03:30 [PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version [email protected] 2026-03-24 06:59 ` Laurenz Albe <[email protected]> 2026-03-24 12:36 ` Nikolay Samokhvalov <[email protected]> 2026-03-24 15:04 ` Bruce Momjian <[email protected]> 2026-03-24 16:53 ` Laurenz Albe <[email protected]> 2026-03-24 18:11 ` Bruce Momjian <[email protected]> 2026-03-25 06:03 ` Laurenz Albe <[email protected]> 2026-03-25 16:21 ` Bruce Momjian <[email protected]> 2026-03-26 01:13 ` Nikolay Samokhvalov <[email protected]> 2026-03-26 02:33 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox