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

From: Slava Mudry <slava44(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?
Date: 2015-01-30 23:44:00
Message-ID: CAEFxPe3pd5B1ssKvAkUuKULRd+YdYpXTpteZzPtQ88xvrJT5OQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jim Nasby 2015-01-31 01:28:26 Re: why pg_class.relfrozenxid needs to be updated for frozen tables (where all rows have xmin=2)?
Previous Message Josh Berkus 2015-01-30 20:26:42 Re: working around JSONB's lack of stats?