When performing an upgrade, postgresql generates a script with the following command in it:
"/usr/lib/postgresql/11/bin/vacuumdb" --all --analyze-in-stages
On the page: https://www.postgresql.org/docs/11/app-vacuumdb.html
The following options are listed:
-a
–allVacuum all databases.
–analyze-in-stages
Only calculate statistics for use by the optimizer (no vacuum), like –analyze-only. Run several (currently three) stages of analyze with different configuration settings, to produce usable statistics faster.
This option is useful to analyze a database that was newly populated from a restored dump or by pg_upgrade. This option will try to create some statistics as fast as possible, to make the database usable, and then produce full statistics in the subsequent stages.
The page also states:
vacuumdb is a wrapper around the SQL command VACUUM. There is no
effective difference between vacuuming and analyzing databases via
this utility and via other methods for accessing the server.
However, after running this script and then going into a database and running:
VACUUM VERBOSE;
The output of that is showing me many unused item pointers, index row versions being removed and per index how many rows it removed.
So it looks to me like that command from the generated script didn't vacuum the tables at all.
Is this right? Or is something else going on?
EDIT:
I just now ran:
"/usr/lib/postgresql/11/bin/vacuumdb" --all --echo --verbose
And it's showing me the same output as VACUUM VERBOSE;
, which didn't get printed while running the command generated by the upgrade, even with echo and verbose on.
So it seems to me like it's only executing --analyze-in-stages
. Is this a bug that should be reported or am I seeing things wrong here?
EDIT: To clarify, --all
clearly is documented to mean Vacuum all databases
. And when I run it alone, it does in fact do that. But the second I add an extra option like --analyze-in-stages
it skips vacuuming entirely.
I would expect it to perform all provided flags and not ignore --all
(which is a vacuum command and it does in fact perform a vacuum if it is provided to vacuumdb without --analyze-in-stages
.
Best Answer
That's not a
VACUUM
script, it is anANALYZE
script. That's why it isn't vacuuming. The confusion arises becausevacuumdb
can also be used forANALYZE
ing a database.After
pg_upgrade
you have no table statistics at all, so most queries will perform very badly. That's why it is essential to mend that as soon as possible.Since
pg_upgrade
copies (or links) the table files, there will be as many dead tuples after the upgrade as there were before. That is not alarming, and autovacuum will take care of it in due course.