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:
WHERE name LIKE '%vacuum%'
Most fields are self-explanatory, but here is documentation on them:
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!