7

What is the difference between:

vacuum;

and

vacuum analyze; 

I read the postgresql manual, but this is still not clear 100% for me.

Do I need to run both, or one of them is sufficient?

1 Answer 1

4

tl;dr running vacuum analyze is sufficient

Because vacuum analyze is complete superset of vacuum. If you run vacuum analyze you don't need to run vacuum separately. See the discussion on the mailing list archive.

Analyze is an additional maintenance operation next to vacuum. It is supposed to keep the statistics up to date on the table.

Vacuuming isn't the only periodic maintenance your database needs. You also need to analyze the database so that the query planner has table statistics it can use when deciding how to execute a query. Simply put: Make sure you're running ANALYZE frequently enough, preferably via autovacuum. And increase the default_statistics_target (in postgresql.conf) to 100.

More info: https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT

3
  • 2
    That wiki page seems to be a bit out of date. ("And increase the default_statistics_target (in postgresql.conf) to 100") 100 has been the default since at least version 9.1 Sep 28, 2016 at 14:58
  • And I would said 100 is insane to use today. For my case since PostreSQL 9.6, I was unable to generate good plans using a default_statistics_target < 2000 Aug 21, 2020 at 18:36
  • The recommendation from AWS is a minumum of 256 I believe. Jan 4, 2021 at 15:54

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.