why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?

[Slava Mudry <slava44@xxxxxxxxx>]

Hi,

I have to deal with badly written system which regularly suffers from transaction wraparound issue. This issue is happenning every 10-14 days and forces me to take system offline and vacuum in single-user mode.

Main causes for this are (afaik):

1) heavy transaction traffic + 100+GB of stale tables

2) slow i/o (rotational drives)

3) autovacuum can't keep up.

Basically the database stores events data in daily partitioned table "daily_events".

What I did, was - I ran vaccum freeze on all partitions (the tables are never touched after they're done for a day). I have also scheduled vacuum-freeze for a partition after it's done writing.

This essentially set xmin in each partition to "frozen" value of "2".

However, to my surprise, this was not enough!

Postgres stores relfrozenxid in pg_class and this value apparently is getting old pretty fast (due to high volume of transactions).

And it seems that it doesn't really matter that xmin is frozen for a table, the relfrozenxid is what causing transaction wraparound.

Why is that? and most importantly - why updating pg_class.relfrozenxid requires huge amount of i/o by vacuum process for tables that are never updated?

Is it safe to just update pg_class.relfrozenxid for tables where xmin=2 for all rows? Same for linked toast table?

Thank you.