Postgres Pro
Downloads
Home   >   mailing lists

Thread: Figuring out the correct age of datfrozenxid

Figuring out the correct age of datfrozenxid

From
Yuri Niyazov
Date:
Trying to figure out how to handle the following:

Our alerts that check whether a table is coming close to the 2 billion point mark started firing on a database. We noticed that the table that had the biggest age had a vacuum running on it that hasn't finished in 85 days. It was a table that was no longer use, so instead of restarting the vacuum, we truncated and deleted the table, expecting that would get rid of the need to vacuum the table. 

Afterwards, running the two queries from the documentation at https://www.postgresql.org/docs/9.4/routine-vacuuming.html produces the resutls below, the TL;DR of which is: 

all the tables have a very reasonable age, but the database itself still has an age approaching two billion. So, what do we do now? Were we wrong to truncate and drop this unneeded table without letting a vacuum on it finish?

academia_notifications=# SELECT c.oid::regclass as table_name,

       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age

FROM pg_class c

LEFT JOIN pg_class t ON c.reltoastrelid = t.oid

WHERE c.relkind IN ('r', 'm') and greatest(age(c.relfrozenxid),age(t.relfrozenxid)) > 100000000;

             table_name             |    age    

------------------------------------+-----------

 notification_messages              | 227283989

 information_schema.sql_features    | 177276271

 information_schema.sql_parts       | 177276271

 user_notification_message_activity | 159132783

 bundles_tmp                        | 177276271

 bundles_old_int_id                 | 244381510

 bundles                            | 146576938

(7 rows)


academia_notifications=# select datname, age(datfrozenxid) FROM pg_database;

        datname         |    age     

------------------------+------------

 template1              | 1901010993

 template0              | 1901010993

 academia_notifications | 1951010993

 postgres               | 1186462760

(4 rows)



Our alerts that check whether a table is coming close to the 2 billion point mark started firing on a database. We noticed that the table that had the biggest age had a vacuum running on it that hasn't finished in 85 days. It was a table that was no longer use, so instead of restarting the vacuum, we truncated and deleted the table, expecting that would get rid of the need to 

Re: Figuring out the correct age of datfrozenxid

From
Alvaro Herrera
Date:
On 2019-Jul-25, Yuri Niyazov wrote:

> all the tables have a very reasonable age, but the database itself still
> has an age approaching two billion. So, what do we do now?

I think vacuuming any table will update the database age.  For instance,
try creating an empty table and do "vacuum freeze" on it.

> Were we wrong to truncate and drop this unneeded table without letting
> a vacuum on it finish?

That seems an appropriate measure to have taken  You just need to have
the system figure out that it's gone.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services