To improve performance and release index space from database, We are running FULL Vacuum on monthly basis.
On PostgreSQL website it is not recommended to run FULL Vacuum on Production Database and this also requires long downtime along with huge log space requirement.
What are the recommendations regarding vacuum. Can we run FULL Vacuum on monthly basis or we should be running Online Auto Vacuum instead.
Autovacuum should be tuned to be more aggressive if it is not keeping up. Never turn it off. Decrease scale factor and cost_delay to get more throughput. Perhaps increase the number of workers, particularly if there are multiple databases in the cluster. Be aware that the cost limit is shared among workers so that cost limit may need to be increased when increasing workers or else you will be working on more tables concurrently, but not getting anymore work done in total per minute. Consider customizing parameters on very large tables (100 million rows or more?) to have a smaller scale factor than your new default even. Your goal should be to reach a "steady state" with rows being removed, that space marked as re-usable by autovacuum, and then the new updates/inserts using that space. If you are oscillating between 1GB and 10GB for storing a table as it bloats and then vacuum full is done periodically, then you are doing things wrong. If it hurts to clean up, do it more often and a little at a time.
Oh, and an old blog post I read mentioned that autovacuum reserves the full maintenance_work_mem at the start. I don't know if that is changed (fixed) now, but I like to have maintenance_work_mem high for index creation and such, but set autovacuum_work_mem to be lower such that perhaps it has to re-scan some large indexes multiple times to finish its work, but I'm not constantly holding large amounts of memory when doing vacuum on smaller tables.
