Figuring out the correct age of datfrozenxid

From: Yuri Niyazov <yuri(at)academia(dot)edu>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Figuring out the correct age of datfrozenxid
Date: 2019-07-26 05:12:57
Message-ID: CACuBw0hbStKjfKBEPE+weS=jp00iOxLkNCJ9XmXehwsanCg5Wg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2019-07-26 17:57:22 Re: Figuring out the correct age of datfrozenxid
Previous Message Thomas Kellerer 2019-07-24 18:59:35 Re: Restrict permissions on schema to hide pl/pgsql code