PostgreSQL VACUUM – Should Manual VACUUM be Done if Autovacuum is On?

maintenancepostgresqlvacuum

I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default is autovacuum turned on.

Should I vacuum/analyze at all? What are the benefits? What's the difference between automatic and manual vacuum

For example, in Pgadmin3, I have this:
enter image description here

Best Answer

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only.

Yet, two things seem important to me:

  • There seems to be a consensus, that autovacuum should never be switched off, unless you have a very well defined workload on your database and you know exactly what you are doing. But, naturally, you could do additional VACUUM and/or ANALYZE runs.

  • Before considering additional VACUUM runs, I would check how autovacuum keeps up. You can check whether any tables are beyond the autovacuum threshold by querying pg_stat_user_tables and pg_class. I posted such a query on another thread, that might be of interest: Aggressive Autovacuum on PostgreSQL.

    Unfortunately, it is not as easy (i.e. not possible at the moment) to do a similar check for autoanalyze thresholds. However, autoanalyze kicks in long before autovacuum by default and it is much cheaper. So, basically if your database can keep up with autovacuum, it will probably be fine with autoanalyze too. The last autoanalyze dates can also be queried from pg_stat_user_tables.

Some parts of the (most excellent) PostgreSQL documentation, that I found helpful:

Related Question