regarding statistics retaining with 18 Upgrade

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

---------------------------------------------------------------------------

before upgrade:

postgres=# select * from pg_stat_user_tables;

-[ RECORD 1 ]-------+------------------------------

relid               | 16384

schemaname          | public

relname             | emp

n_tup_ins           | 1000000

n_tup_upd           | 0

n_tup_del           | 0

n_tup_hot_upd       | 0

n_tup_newpage_upd   | 0

n_live_tup          | 1000000

n_dead_tup          | 0

n_mod_since_analyze | 0

n_ins_since_vacuum  | 0

last_vacuum         | 2025-11-15 09:40:48.662853+00

last_autovacuum     | 2025-11-15 09:40:04.302062+00

last_analyze        | 2025-11-15 09:40:44.523538+00

last_autoanalyze    | 2025-11-15 09:40:04.455379+00

vacuum_count        | 1

autovacuum_count    | 1

analyze_count       | 1

autoanalyze_count   | 1

postgres=# select version();

-[ RECORD 1 ]
------------------------------------------------------------------------------------------------------

version | PostgreSQL 16.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1
20250617 (Red Hat 14.3.1-2), 64-bit

after upgrade:

[postgres@ip-172-31-35-206 ~]$ /usr/pgsql-18/bin/pg_upgrade -b /usr/pgsql-16/
bin/ -B /usr/pgsql-18/bin/ -d /var/lib/pgsql/16/data/ -D /var/lib/pgsql/18/data
/ -p 5432 -P 5433
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
+++++------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
    /usr/pgsql-18/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

[postgres@ip-172-31-35-206 ~]$ psql
psql (18.1)
Type "help" for help.

postgres=# select * from pg_stat_user_tables;
 relid | schemaname | relname | seq_scan | last_seq_scan | seq_tup_read |
idx_scan | last_idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del |
n_tup_hot_upd | n_tup_newpage_upd | n_live_tup | n_dead_tup |
n_mod_since_analyze | n_ins
_since_vacuum | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
| vacuum_count | autovacuum_count | analyze_count | autoanalyze_count |
total_vacuum_time | total_autovacuum_time | total_analyze_time |
total_autoanalyze_time
-------+------------+---------+----------+---------------+--------------+----------+---------------+---------------+-----------+-----------+-----------+---------------+-------------------+------------+------------+---------------------+------
--------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------+-------------------+-----------------------+--------------------+------------------------
 16384 | public     | emp     |        0 |               |            0 |      
   |               |               |         0 |         0 |         0 |      
      0 |                 0 |          0 |          0 |                   0 |
            0 |             |                 |              |                
 |            0 |                0 |             0 |                 0 |      
          0 |                     0 |                  0 |                    
 0
(1 row)

postgres=# \q

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,

Specifically, check out the pg_stats view. Try this on your new cluster:

select count(distinct tablename) from pg_stats where tablename !~
'pg_|sql_';

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

I wonder if it might be worth trying to clarify this a little bit
more... perhaps something like the attached?

Robert Treat
https://xzilla.net

On Sun, Nov 16, 2025 at 5:22 AM Robert Treat <rob@xzilla.net> wrote:

On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

I wonder if it might be worth trying to clarify this a little bit
more... perhaps something like the attached?

IMHO it makes sense to update the doc as you suggested to avoid any confusion.

--
Regards,
Dilip Kumar
Google

Thanks all for the proactive response and clarification.

people will be confused by seeing the release notes, it should be a little
bit clear or need to update.

i have one more question :

with every version of package installation , PG 17 version is getting by
default installed, is it a hard dependency or bug?

*[root@db1 ~]# dnf install -y postgresql16-server postgresql16-contrib
-y*Updating
Subscription Management repositories.
Unable to read consumer identity

This system is not registered with an entitlement server. You can use "rhc"
or "subscription-manager" to register.

Last metadata expiration check: 0:05:28 ago on Sun Nov 16 04:56:13 2025.
Dependencies resolved.
====================================================================================================================================================================================================================
Package Architecture
Version
Repository Size
====================================================================================================================================================================================================================
Installing:
postgresql16-contrib x86_64
16.11-1PGDG.rhel10
pgdg16 730 k
postgresql16-server x86_64
16.11-1PGDG.rhel10
pgdg16 6.8 M
Installing dependencies:
libicu x86_64
74.2-5.el10_0
rhel-10-baseos-rhui-rpms 10 M
libxslt x86_64
1.1.39-8.el10_0
rhel-10-appstream-rhui-rpms 190 k
postgresql16 x86_64
16.11-1PGDG.rhel10
pgdg16 1.8 M
postgresql16-libs x86_64
16.11-1PGDG.rhel10
pgdg16 339 k
postgresql17 x86_64
17.7-1PGDG.rhel10
pgdg17 1.9 M
postgresql17-libs x86_64
17.7-1PGDG.rhel10
pgdg17 346 k
Installing weak dependencies:
postgresql17-server x86_64
17.7-1PGDG.rhel10
pgdg17 7.0 M

Transaction Summary
====================================================================================================================================================================================================================
Install 9 Packages

Total download size: 30 M
Installed size: 120 M
Downloading Packages:
(1/9): postgresql17-libs-17.7-1PGDG.rhel10.x86_64.rpm

11 MB/s | 346 kB 00:00
(2/9): postgresql17-17.7-1PGDG.rhel10.x86_64.rpm

42 MB/s | 1.9 MB 00:00
(3/9): postgresql16-16.11-1PGDG.rhel10.x86_64.rpm

56 MB/s | 1.8 MB 00:00
(4/9): postgresql16-contrib-16.11-1PGDG.rhel10.x86_64.rpm

25 MB/s | 730 kB 00:00
(5/9): postgresql16-libs-16.11-1PGDG.rhel10.x86_64.rpm

20 MB/s | 339 kB 00:00
(6/9): libxslt-1.1.39-8.el10_0.x86_64.rpm

6.6 MB/s | 190 kB 00:00
(7/9): libicu-74.2-5.el10_0.x86_64.rpm

104 MB/s | 10 MB 00:00
(8/9): postgresql17-server-17.7-1PGDG.rhel10.x86_64.rpm

28 MB/s | 7.0 MB 00:00
(9/9): postgresql16-server-16.11-1PGDG.rhel10.x86_64.rpm

33 MB/s | 6.8 MB 00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total

92 MB/s | 30 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing :

1/1
Installing : postgresql17-libs-17.7-1PGDG.rhel10.x86_64

1/9
Running scriptlet: postgresql17-libs-17.7-1PGDG.rhel10.x86_64

1/9
Installing : libicu-74.2-5.el10_0.x86_64

2/9
Installing : postgresql16-libs-16.11-1PGDG.rhel10.x86_64

3/9
Running scriptlet: postgresql16-libs-16.11-1PGDG.rhel10.x86_64

3/9
Installing : postgresql16-16.11-1PGDG.rhel10.x86_64

4/9
Running scriptlet: postgresql16-16.11-1PGDG.rhel10.x86_64

4/9
Installing : postgresql17-17.7-1PGDG.rhel10.x86_64

5/9
Running scriptlet: postgresql17-17.7-1PGDG.rhel10.x86_64

5/9
Running scriptlet: postgresql17-server-17.7-1PGDG.rhel10.x86_64

6/9
Installing : postgresql17-server-17.7-1PGDG.rhel10.x86_64

6/9
Running scriptlet: postgresql17-server-17.7-1PGDG.rhel10.x86_64

6/9
Running scriptlet: postgresql16-server-16.11-1PGDG.rhel10.x86_64

7/9
Installing : postgresql16-server-16.11-1PGDG.rhel10.x86_64

7/9
Running scriptlet: postgresql16-server-16.11-1PGDG.rhel10.x86_64

7/9
Installing : libxslt-1.1.39-8.el10_0.x86_64

8/9
Installing : postgresql16-contrib-16.11-1PGDG.rhel10.x86_64

9/9
Running scriptlet: postgresql16-contrib-16.11-1PGDG.rhel10.x86_64

9/9
Installed products updated.

Installed:
libicu-74.2-5.el10_0.x86_64
libxslt-1.1.39-8.el10_0.x86_64
postgresql16-16.11-1PGDG.rhel10.x86_64
postgresql16-contrib-16.11-1PGDG.rhel10.x86_64
postgresql16-libs-16.11-1PGDG.rhel10.x86_64
postgresql16-server-16.11-1PGDG.rhel10.x86_64
postgresql17-17.7-1PGDG.rhel10.x86_64
postgresql17-libs-17.7-1PGDG.rhel10.x86_64
postgresql17-server-17.7-1PGDG.rhel10.x86_64

Complete!

*[root@db1 ~]# rpm -qa|grep
postgrespostgresql17-libs-17.7-1PGDG.rhel10.x86_64postgresql16-libs-16.11-1PGDG.rhel10.x86_64postgresql16-16.11-1PGDG.rhel10.x86_64postgresql17-17.7-1PGDG.rhel10.x86_64postgresql17-server-17.7-1PGDG.rhel10.x86_64postgresql16-server-16.11-1PGDG.rhel10.x86_64postgresql16-contrib-16.11-1PGDG.rhel10.x86_64*

On Sun, Nov 16, 2025 at 12:43 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Sun, Nov 16, 2025 at 01:03:30PM +0800, Rambabu V wrote:

Thanks all for the proactive response and clarification.

people will be confused by seeing the release notes, it should be a little bit
clear or need to update.

Yes, the release notes are clear, but the non-release docs are not. I
will apply the suggested patch now to PG 18 and master.

i have one more question : 

with every version of package installation , PG 17 version is getting by
default installed, is it a hard dependency or bug?

I suggest you send a new email with an appropriate subject line rather
than trying to add a question to this thread.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

On Sat, Nov 15, 2025 at 06:52:15PM -0500, Robert Treat wrote:

On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

I wonder if it might be worth trying to clarify this a little bit
more... perhaps something like the attached?

Patch applied back to PG 18, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

On Tue, Nov 18, 2025 at 8:56 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:52:15PM -0500, Robert Treat wrote:

On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

I wonder if it might be worth trying to clarify this a little bit
more... perhaps something like the attached?

Patch applied back to PG 18, thanks.

Thanks for working on this!

   <para>
-   If <option>--statistics</option> is specified,
+   When <option>--statistics</option> is specified,
    <command>pg_dumpall</command> will include most optimizer statistics in the
-   resulting dump file.  However, some statistics may not be included, such as
-   those created explicitly with <xref linkend="sql-createstatistics"/> or
-   custom statistics added by an extension.  Therefore, it may be useful to
+   resulting dump file. This does not include 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.  Therefore, it may still be useful to
    run <command>ANALYZE</command> on each database after restoring from a dump
    file to ensure optimal performance.  You can also run <command>vacuumdb -a
    -z</command> to analyze all databases.

Since pgupgrade.sgml contains similar text, I think it should be
updated as well.
For example, something like this:

-------------------
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index 356baa91299..38ca09b423c 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -833,10 +833,10 @@ 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.  However, some statistics may
-     not be transferred, such as those created explicitly with <xref
-     linkend="sql-createstatistics"/> or custom statistics added by an
-     extension.
+     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.
     </para>
-------------------

Regards,

--
Fujii Masao

On Tue, Nov 18, 2025 at 12:41:28PM +0900, Fujii Masao wrote:

On Tue, Nov 18, 2025 at 8:56 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:52:15PM -0500, Robert Treat wrote:

On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

I wonder if it might be worth trying to clarify this a little bit
more... perhaps something like the attached?

Patch applied back to PG 18, thanks.

Thanks for working on this!

<para>
-   If <option>--statistics</option> is specified,
+   When <option>--statistics</option> is specified,
<command>pg_dumpall</command> will include most optimizer statistics in the
-   resulting dump file.  However, some statistics may not be included, such as
-   those created explicitly with <xref linkend="sql-createstatistics"/> or
-   custom statistics added by an extension.  Therefore, it may be useful to
+   resulting dump file. This does not include 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.  Therefore, it may still be useful to
run <command>ANALYZE</command> on each database after restoring from a dump
file to ensure optimal performance.  You can also run <command>vacuumdb -a
-z</command> to analyze all databases.

Since pgupgrade.sgml contains similar text, I think it should be
updated as well.
For example, something like this:

Yeah, you are probably right. I didn't see that. You should apply it
since it is your patch, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

On Wed, Nov 19, 2025 at 1:00 AM Bruce Momjian <bruce@momjian.us> wrote:

Yeah, you are probably right. I didn't see that. You should apply it
since it is your patch, thanks.

Okay, so barring any objection, I will commit the attached patch.

Regards,

--
Fujii Masao

On Wed, Nov 19, 2025 at 10:51 PM Fujii Masao <masao.fujii@gmail.com> wrote:

On Wed, Nov 19, 2025 at 1:00 AM Bruce Momjian <bruce@momjian.us> wrote:

Yeah, you are probably right. I didn't see that. You should apply it
since it is your patch, thanks.

Okay, so barring any objection, I will commit the attached patch.

I've pushed the patch. Thanks!

Regards,

--
Fujii Masao