Postgres Pro
Downloads
Home   >   mailing lists

Re: autovacuum freeze recommendations at table level - Mailing list pgsql-general

From semab tariq
Subject Re: autovacuum freeze recommendations at table level
Date
Msg-id CAG=z8NRYzV2buVyBz5Rb-SQWt9fU8+H0oioaA2SejPKo=qELOg@mail.gmail.com
Whole thread Raw
In response to autovacuum freeze recommendations at table level  (Durgamahesh Manne <maheshpostgres9@gmail.com>)
Responses Re: autovacuum freeze recommendations at table level
List pgsql-general
Tree view
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@gmail.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 

pgsql-general by date:

Previous
From: 王瞿
Date:
Subject: 回复：searching for libpq5-14.1-42PGDG.rhel8.x86_64
Next
From: Devrim Gündüz
Date:
Subject: Re: 回复：searching for libpq5-14.1-42PGDG.rhel8.x86_64