Re: autovacuum recommendations for Large tables

Could you help me by explaining in simple words, as I am new to postgres.

What value of which parameter should I set and why.

I only have this one big table in the database of size 3113 GB with rows 7661353111.

Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}

Please help.

Regards

Atul

On Tuesday, November 17, 2020, Olivier Gautherot <ogautherot@gautherot.net> wrote:

Hi Atul,

I would start with a factor of 0.00001 (10 parts per million) and explore down to 0.000001.

I did some massive updates on a partition with 12 millions rows and my factor was 0.001.

Depending on the number of big tables you have in the database, you may wish to reduce the number of parallel workers.

Hope it helps

--

Olivier Gautherot

Tel: +33 6 02 71 92 23 

https://www.linkedin.com/in/ogautherot/

Libre de virus. www.avast.com

On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar <akumar14871@gmail.com> wrote:

Ok,

Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}


So Please suggest, how much should i set atleast, to avoid increasing
in dead tuple.



Regards,
Atul



On 11/16/20, Olivier Gautherot <ogautherot@gautherot.net> wrote:
> Hi Atul,
>
> Le lun. 16 nov. 2020 à 18:38, Atul Kumar <akumar14871@gmail.com> a écrit :
>
>> Hi,
>>
>> I have a large table having no. of live tuples approx 7690798868 and
>> no. of dead tuples approx 114917737.
>>
>> So Please share autovacuum tuning recommendations for this table so
>> that our time can be spent better than repeatedly vacuuming large
>> tables.
>>
>
> Vacuuming will affect you when it has a lot of work to do. I would try to
> trigger an autovacuum every 10,000 insert/update to minimize the impact.
> You can play with the parameter autovacuum_vacuum_scale_factor for that
> table.
>
> Good luck
> Olivier
>