6

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?

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 – a_horse_with_no_name Sep 28 '16 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 – Luciano Andress Martini Aug 21 '20 at 18:36
  • The recommendation from AWS is a minumum of 256 I believe. – David Aldridge Jan 4 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.