Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

On Thu, May 7, 2020 at 8:50 AM Ashish Chugh <
ashish(dot)chugh(at)lavainternational(dot)in> wrote

> 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.