1

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.

2

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.

  • Still, --all is included and the official documentation clearly states for this option: Vacuum all databases. And if I perform it on its own, with echo and verbose on, it does actually do that. But the second I add --analyze-in-stages, vacuuming is skipped. I would expect that since both options are provided, they are to be executed in order? – KdgDev Jun 6 at 14:53
  • 1
    @KdgDev That is not how command line tools generally operate. The given options are generally unioned, not sequential operations. – jjanes Jun 6 at 15:28
  • 1
    I see your problem. That's a sloppiness of the documentation: It should be "Treat all databases" rather than "Vacuum all databases". So it runs "analyze in stages" on all databases. – Laurenz Albe Jun 6 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.