Postgresql upgrade generated vaccumdb script not vacuuming

postgresqlvacuum

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
–all

Vacuum 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 an ANALYZE script. That's why it isn't vacuuming. The confusion arises because vacuumdb can also be used for ANALYZEing 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.