pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Update guidance for running vacuumdb after pg_upgrade.
6+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2025-04-30 d5f1b6a75b Further adjust guidance for running vacuumdb after pg_upgrade.
* pgsql: Update guidance for running vacuumdb after pg_upgrade.
@ 2025-03-18 21:33 Nathan Bossart <nathan@postgresql.org>
2025-04-22 19:43 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2025-03-18 21:33 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Update guidance for running vacuumdb after pg_upgrade.
Now that pg_upgrade can carry over most optimizer statistics, we
should recommend using vacuumdb's new --missing-stats-only option
to only analyze relations that are missing statistics.
Reviewed-by: John Naylor <johncnaylorls@gmail.com>
Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/c9d502eb68094d817fe94c2e954f6fdbb62f4d48
Modified Files
--------------
doc/src/sgml/ref/pgupgrade.sgml | 9 +++++----
src/bin/pg_upgrade/check.c | 2 +-
2 files changed, 6 insertions(+), 5 deletions(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Update guidance for running vacuumdb after pg_upgrade.
2025-03-18 21:33 pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathan@postgresql.org>
@ 2025-04-22 19:43 ` Christoph Berg <myon@debian.org>
2025-04-22 20:54 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Berg @ 2025-04-22 19:43 UTC (permalink / raw)
To: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Re: Nathan Bossart
> Update guidance for running vacuumdb after pg_upgrade.
>
> Now that pg_upgrade can carry over most optimizer statistics, we
> should recommend using vacuumdb's new --missing-stats-only option
> to only analyze relations that are missing statistics.
I've been looking at vacuumdb --missing-stats-only because Debian's
pg_upgradecluster is using that now.
I am wondering if this is really good advice in the pg_upgrade
documentation. Sure it's nice that optimizer statistics are carried
over by pg_upgrade, but the pg_stat_user_tables statistics are not
carried over, and afaict these are the numbers that determine when the
next autovacuum or autoanalyze run is going to happen. By removing the
"please run vacuumdb on all tables" step from the pg_upgrade docs, we
are effectively telling everyone that they should be starting with
these numbers all 0, postponing the next run to some indeterminate
point. Running `vacuumdb --missing-stats-only` does not fix that
because it's skipping the tables. Is that the message we want to send?
(If I am misinterpreting the situation the docs should still explain
why this is ok.)
Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Update guidance for running vacuumdb after pg_upgrade.
2025-03-18 21:33 pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathan@postgresql.org>
2025-04-22 19:43 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
@ 2025-04-22 20:54 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-22 21:03 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2025-04-22 20:54 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
On Tue, Apr 22, 2025 at 09:43:56PM +0200, Christoph Berg wrote:
> Re: Nathan Bossart
>> Update guidance for running vacuumdb after pg_upgrade.
>>
>> Now that pg_upgrade can carry over most optimizer statistics, we
>> should recommend using vacuumdb's new --missing-stats-only option
>> to only analyze relations that are missing statistics.
>
> I've been looking at vacuumdb --missing-stats-only because Debian's
> pg_upgradecluster is using that now.
>
> I am wondering if this is really good advice in the pg_upgrade
> documentation. Sure it's nice that optimizer statistics are carried
> over by pg_upgrade, but the pg_stat_user_tables statistics are not
> carried over, and afaict these are the numbers that determine when the
> next autovacuum or autoanalyze run is going to happen. By removing the
> "please run vacuumdb on all tables" step from the pg_upgrade docs, we
> are effectively telling everyone that they should be starting with
> these numbers all 0, postponing the next run to some indeterminate
> point. Running `vacuumdb --missing-stats-only` does not fix that
> because it's skipping the tables. Is that the message we want to send?
>
> (If I am misinterpreting the situation the docs should still explain
> why this is ok.)
relation_needs_vacanalyze() uses dead_tuples, ins_since_vacuum, and
mod_since_analyze. IIUC a full post-upgrade vacuumdb run would only set
dead_tuples to a nonzero value, so the worst-case scenario is that it would
take longer before a vacuum is triggered based on
autovacuum_vacuum_{threshold,max_threshold,scale_factor}. To address this,
I think we'd need to recommend using "vacuumdb --all --analyze-only"
instead. We could alternatively suggest first running "vacuumdb --all
--analyze-in-stages --missing-stats-only" (to fill in any missing stats)
followed by "vacuumdb --all --analyze-only" (to update dead_tuples).
However, I'm not sure how concerned to be about this. It does seem bad
that it might take longer for tables to be vacuumed for the first time
after upgrade, but I believe that's already the case for any type of
unclean shutdown (e.g., immediate shutdown, server crash, starting from a
base backup, point-in-time recovery). I see that we do recommend running
ANALYZE after pg_stat_reset(), though. 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.
--
nathan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Update guidance for running vacuumdb after pg_upgrade.
2025-03-18 21:33 pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathan@postgresql.org>
2025-04-22 19:43 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
2025-04-22 20:54 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathandbossart@gmail.com>
@ 2025-04-22 21:03 ` Christoph Berg <myon@debian.org>
2025-04-22 21:29 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Berg @ 2025-04-22 21:03 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
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.
Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Update guidance for running vacuumdb after pg_upgrade.
2025-03-18 21:33 pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathan@postgresql.org>
2025-04-22 19:43 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
2025-04-22 20:54 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathandbossart@gmail.com>
2025-04-22 21:03 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
@ 2025-04-22 21:29 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:10 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2025-04-22 21:29 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
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
Attachments:
post_upgrade_guidance.patch (text/plain, 2.4K) download
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,
* Re: pgsql: Update guidance for running vacuumdb after pg_upgrade.
2025-03-18 21:33 pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathan@postgresql.org>
2025-04-22 19:43 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
2025-04-22 20:54 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathandbossart@gmail.com>
2025-04-22 21:03 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Christoph Berg <myon@debian.org>
2025-04-22 21:29 ` Re: pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathandbossart@gmail.com>
@ 2025-04-23 14:10 ` Christoph Berg <myon@debian.org>
0 siblings, 0 replies; 6+ messages in thread
From: Christoph Berg @ 2025-04-23 14:10 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
Re: Nathan Bossart
> pg_log(PG_REPORT,
> + "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\n"
> + " %s/vacuumdb %s--all --analyze-only",
I would make it "Once you start the new server, run these two commands:"
to make it explicit that both should be run, not alternatively either.
This patch addresses my concern, thanks.
Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-04-23 14:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-18 21:33 pgsql: Update guidance for running vacuumdb after pg_upgrade. Nathan Bossart <nathan@postgresql.org>
2025-04-22 19:43 ` Christoph Berg <myon@debian.org>
2025-04-22 20:54 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-22 21:03 ` Christoph Berg <myon@debian.org>
2025-04-22 21:29 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:10 ` Christoph Berg <myon@debian.org>
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