Re: autovacuum recommendations for Large tables

Hi,

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(at)gautherot(dot)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/
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Libre
> de virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#m_-4472748644679516424_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar <akumar14871(at)gmail(dot)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(at)gautherot(dot)net> wrote:
>> > Hi Atul,
>> >
>> > Le lun. 16 nov. 2020 à 18:38, Atul Kumar <akumar14871(at)gmail(dot)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
>> >
>>
>