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

Поиск
Список
Период
Сортировка
От Slava Mudry
Тема why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?
Дата
Msg-id CAEFxPe3pd5B1ssKvAkUuKULRd+YdYpXTpteZzPtQ88xvrJT5OQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?
Список pgsql-performance
Дерево обсуждения
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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: working around JSONB's lack of stats?
Следующее
От: Jim Nasby
Дата:
Сообщение: Re: why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?