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