Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from serverfault.com Feb 24 '13 at 6:30

This question came from our site for professional system and network administrators.

3 Answers

up vote 2 down vote accepted

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:

share|improve this answer
add comment

Autovacuum should pretty well cover it, unless you mis-configured something. Other answers cover that already.

There is one clearly defined case for manual VACUUM (and more importantly: manual ANALYZE) though: temporary tables, they are not considered by the autovacuum demon. I quote the manual on CREATE TABLE here:

The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated.

share|improve this answer
add comment

There is no short answer to that as it depends on a lot of factor. Is the system slow? Is the auto-vacuum actually touching this table? etc.

Here are some good links on this subject:

To make a clear decision requires an understanding of the database itself and more details on what's going on.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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