22

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

Mat
  • 10,289
  • 4
  • 43
  • 40
kissgyorgy
  • 345
  • 1
  • 3
  • 8

4 Answers4

17

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:

Community
  • 1
pygrac
  • 766
  • 5
  • 7
8

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.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
4

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.

Mat
  • 10,289
  • 4
  • 43
  • 40
ETL
  • 988
  • 6
  • 18
1

I don't think you need to manually vacuum, unless you start to see performance degradation. However, I would strongly recommend to review your vacuum and autovacuum settings and tweak it to your needs

To see your current settings, run this query:

SELECT *
FROM pg_settings 
WHERE name LIKE '%vacuum%'

Most fields are self-explanatory, but here is documentation on them: https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

I would say, that your goal should be to configure autovacuum to clean the garbage consistently, but don't run autovacuum constantly

Most important settings are:

  • autovacuum_vacuum_scale_factor - determines percentage of tuples that can be dead before a cleanup is triggered. Default value = 0.2
  • autovacuum_vacuum_threshold - minimum number of dead tuples before cleanup is triggered. Default value = 50

Threshold helps to prevent cleanup process to be triggered way too often for small tables.

Default settings work okay, unless you have very large tables. Simply put, if you happen to have table which takes 100GB, you are going to accumulate 20GB garbage, before autovacuum will be triggered. Thus, I usually recommend to set scale factor low. How low you should determine for yourself. I use 0.05 on my current project

Thresholds can also be increased. Many applications have a couple of tables, which are frequently being updated and 50 tuples is not that much. Increasing that to 1000 shouldn't lead to any problem, but of course, you should consider your own case

You can also fine tune autovacuum and have different settings for some of your tables

ALTER TABLE your_table SET (autovacuum_vacuum_scale_factor = 0.05);

If you configure scale_factor and thresholds you should be fine. You may also increase autovacuum_vacuum_cost_limit, which by default equals to vacuum_cost_limit, which is set to 200. This is a very important feature of vacuum, which doesn't allow it to eat up all the resources and allows your application to operate with data even during vacuuming process, but default value is too low. Increasing it to 1000 shouldn't lead to any significant delays, but will allow vacuum process to finish much faster

Of course, you can also run vacuum manually. In a most simple case, you can have a simple cron job, which will make a full clean up every night, when your DB is not frequently accessed

Hope that helps!

Hasan Ammori
  • 111
  • 3