Date: Tue, 22 Apr 2025 16:29:21 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Christoph Berg <myon@debian.org>
Cc: Corey Huinker <corey.huinker@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Update guidance for running vacuumdb after pg_upgrade.
Message-ID: <aAgKMavWTlgjWkar@nathan>
References: <E1tueZC-0039rk-2P@gemulon.postgresql.org>
 <aAfxfKC82B9NvJDj@msg.df7cb.de>
 <aAgCBgIh3BxkvceM@nathan>
 <aAgEIXZ9zlqThJCZ@msg.df7cb.de>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="JWVqibUa/J+F7jYr"
Content-Disposition: inline
In-Reply-To: <aAgEIXZ9zlqThJCZ@msg.df7cb.de>
Archived-At: <https://www.postgresql.org/message-id/aAgKMavWTlgjWkar%40nathan>
Precedence: bulk


--JWVqibUa/J+F7jYr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Apr 22, 2025 at 11:03:29PM +0200, Christoph Berg wrote:
> Re: Nathan Bossart
>> In any case, IMO it's unfortunate
>> that we might end up recommending roughly the same post-upgrade steps as
>> before even though the optimizer statistics are carried over.
> 
> Maybe the docs (and the pg_upgrade scripts) should recommend the old
> procedure by default until this gap is closed? People could then still
> opt to use the new procedure in specific cases.

I think we'd still want to modify the --analyze-in-stages recommendation
(from what is currently recommended for supported versions).  If we don't,
you'll wipe out the optimizer stats you brought over from the old version.

Here is a rough draft of what I am thinking.

-- 
nathan

--JWVqibUa/J+F7jYr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="post_upgrade_guidance.patch"

diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index df13365b287..648c6e2967c 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -833,17 +833,19 @@ psql --username=postgres --file=script.sql postgres
 
     <para>
      Because not all statistics are not transferred by
-     <command>pg_upgrade</command>, you will be instructed to run a command to
+     <command>pg_upgrade</command>, you will be instructed to run commands to
      regenerate that information at the end of the upgrade.  You might need to
      set connection parameters to match your new cluster.
     </para>
 
     <para>
-     Using <command>vacuumdb --all --analyze-only --missing-stats-only</command>
-     can efficiently generate such statistics.  Alternatively,
+     First, use
      <command>vacuumdb --all --analyze-in-stages --missing-stats-only</command>
-     can be used to generate minimal statistics quickly.  For either command,
-     the use of <option>--jobs</option> can speed it up.
+     to quickly generate minimal optimizer statistics for relations without
+     any.  Then, use <command>vacuumdb --all --analyze-only</command> to ensure
+     all relations have updated cumulative statistics for triggering vacuum and
+     analyze.  For both commands, the use of <option>--jobs</option> can speed
+     it up.
      If <varname>vacuum_cost_delay</varname> is set to a non-zero
      value, this can be overridden to speed up statistics generation
      using <envar>PGOPTIONS</envar>, e.g., <literal>PGOPTIONS='-c
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 18c2d652bb6..f1b90c5957e 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -814,9 +814,12 @@ output_completion_banner(char *deletion_script_file_name)
 	}
 
 	pg_log(PG_REPORT,
-		   "Some optimizer statistics may not have been transferred by pg_upgrade.\n"
+		   "Some statistics are not transferred by pg_upgrade.\n"
 		   "Once you start the new server, consider running:\n"
-		   "    %s/vacuumdb %s--all --analyze-in-stages --missing-stats-only", new_cluster.bindir, user_specification.data);
+		   "    %s/vacuumdb %s--all --analyze-in-stages --missing-stats-only\n"
+		   "    %s/vacuumdb %s--all --analyze-only",
+		   new_cluster.bindir, user_specification.data,
+		   new_cluster.bindir, user_specification.data);
 
 	if (deletion_script_file_name)
 		pg_log(PG_REPORT,

--JWVqibUa/J+F7jYr--