Re: autovacuum freeze recommendations at table level

Hi Semab

ALTER TABLE table SET (
autovacuum_freeze_max_age = 60000000,(6 crores)
autovacuum_multixact_freeze_max_age = 60000000,(6 crores)
autovacuum_freeze_min_age = 0
);
I set this but autovacuum to prevent wraparound runs for every 10 minutes
on the table being modified in this case
How to minimize the impact of this ?
There is no option to set naptime at table level
I could see total records around 40 lakhs that are being modified
Toast considers large objects but I use parameters without toast.

Regards,
Durga Mahesh Manne

On Mon, Aug 12, 2024 at 10:07 PM semab tariq <semabtariq1(at)gmail(dot)com> wrote:

> Hi Durga
>
> *autovacuum_freeze_max_age* specifies the maximum age (in transactions)
> that a table's tuples can reach before a vacuum is forced to prevent
> transaction ID wraparound. when the age of the oldest tuple in the table
> exceeds this value, an autovacuum is triggered to freeze the tuples.
> *Recommendation = 20000000 -> 150000000 *
>
> *autovacuum_multixact_freeze_max_age *It is similar to above, but applies
> to multi-transaction IDs (used for shared row locks). when the age of the
> oldest multi-transaction ID exceeds this value, an autovacuum is triggered
> to freeze the multi-transaction IDs.
> *Recommendation = 20000000 -> 150000000*
>
> *autovacuum_freeze_min_age* specifies the minimum age (in transactions)
> that a tuple must reach before it is considered for freezing. Lowering this
> value can cause more frequent freezing, which can increase the overhead of
> autovacuum.
> *Recommendation = 0 -> 50000000*
>
> Thanks, Semab
>
> On Sun, Aug 11, 2024 at 11:12 AM Durgamahesh Manne <
> maheshpostgres9(at)gmail(dot)com> wrote:
>
>> Hi Respected Team,
>>
>> Could you please let me know that how this freeze parameters work
>> Update query runs on table through which data being modified daily in
>> this case
>> Total records in table is about 20lakhs
>> current setting for this table is
>> Access method: heap
>> if it reaches > 0.1*2000000+1000 = 2,10,000 as per the formula autovacuum
>> triggers
>> Options: fillfactor=85, autovacuum_vacuum_cost_delay=0,
>> autovacuum_vacuum_cost_limit=3000, parallel_workers=6,
>> autovacuum_vacuum_scale_factor=0.1, autovacuum_vacuum_threshold=1000,
>> autovacuum_freeze_max_age=20000000,
>> autovacuum_multixact_freeze_max_age=20000000, autovacuum_freeze_min_age=0
>>
>> How autovacuum freeze parameters work.Give me some recommendations to
>> improve the performance better than now
>> Ex :ALTER TABLE table SET (
>> autovacuum_freeze_max_age = 20000000,(2 crores)
>> autovacuum_multixact_freeze_max_age = 20000000,(2 crores)
>> autovacuum_freeze_min_age = 0
>> );
>> Regards,
>> Durga Mahesh
>>
>