Re: autovacuum freeze recommendations at table level

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
>