Figuring out the correct age of datfrozenxid

--0000000000000a24cf058e8e9aea
Content-Type: text/plain; charset="UTF-8"

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

--0000000000000a24cf058e8e9aea
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Trying to figure out how to handle the following:<div><br>=
</div><div>Our alerts that check whether a table is coming close to the 2 b=
illion point mark started firing on a database. We noticed that the table t=
hat had the biggest age had a vacuum running on it that hasn&#39;t finished=
 in 85 days. It was a table that was no longer use, so instead of restartin=
g the vacuum, we truncated and deleted the table, expecting that would get =
rid of the need to vacuum the table.=C2=A0</div><div><br></div><div>Afterwa=
rds, running the two queries from the documentation at=C2=A0<a href=3D"http=
s://www.postgresql.org/docs/9.4/routine-vacuuming.html">https://www.postgre=
sql.org/docs/9.4/routine-vacuuming.html</a>=C2=A0produces the resutls below=
, the TL;DR of which is:=C2=A0</div><div><br></div><div>all the tables have=
 a very reasonable age, but the database itself still has an age approachin=
g two billion. So, what do we do now? Were we wrong to truncate and drop th=
is unneeded table without letting a vacuum on it finish?<br><div><br></div>=
<div><span style=3D"font-variant-ligatures:no-common-ligatures;color:rgb(0,=
0,0);font-family:Monaco">academia_notifications=3D# SELECT c.oid::regclass =
as table_name,</span><br></div><div>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0=C2=A0 =C2=A0 =C2=A0 </span>greatest(age(c.relfrozenxid),age(t.relfro=
zenxid)) as age</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">FROM pg_class c</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">LEFT JOIN pg_class t ON c.reltoastrelid =3D t=
.oid</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">WHERE c.relkind IN (&#39;r&#39;, &#39;m&#39;)=
 and greatest(age(c.relfrozenxid),age(t.relfrozenxid)) &gt; 100000000;</spa=
n></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>table_name <span cla=
ss=3D"gmail-Apple-converted-space">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 </span>|<span class=3D"gmail-Apple-converted-space">=C2=A0 =C2=A0 </spa=
n>age <span class=3D"gmail-Apple-converted-space">=C2=A0 =C2=A0</span></spa=
n></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">------------------------------------+--------=
---</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>notification_messages<span class=3D"gmail-Apple-converted-spac=
e">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 227283989</spa=
n></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>information_schema.sql_features<span class=3D"gmail-Apple-conv=
erted-space">=C2=A0 =C2=A0 </span>| 177276271</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>information_schema.sql_parts <span class=3D"gmail-Apple-conver=
ted-space">=C2=A0 =C2=A0 =C2=A0 </span>| 177276271</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>user_notification_message_activity | 159132783</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>bundles_tmp<span class=3D"gmail-Apple-converted-space">=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 </span>| 177276271</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>bundles_old_int_id <span class=3D"gmail-Apple-converted-space"=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 244381510=
</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>bundles<span class=3D"gmail-Apple-converted-space">=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 </span>| 146576938</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">(7 rows)</span></p>
<p class=3D"gmail-p2" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0);min-height:21px"><span class=3D"gmail-s1" style=
=3D"font-variant-ligatures:no-common-ligatures"></span><br></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">academia_notifications=3D# select datname, ag=
e(datfrozenxid) FROM pg_database;</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>datname <span class=3D"gmail-Apple-conve=
rted-space">=C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>|<span class=3D"gmail-Apple-=
converted-space">=C2=A0 =C2=A0 </span>age<span class=3D"gmail-Apple-convert=
ed-space">=C2=A0 =C2=A0 =C2=A0</span></span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">------------------------+------------</span><=
/p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>template1<span class=3D"gmail-Apple-converted-space">=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 1901010993</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>template0<span class=3D"gmail-Apple-converted-space">=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 1901010993</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>academia_notifications | 1951010993</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures"><span class=3D"gmail-Apple-converted-space">=
=C2=A0</span>postgres <span class=3D"gmail-Apple-converted-space">=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 </span>| 1186462760</span></p>
<p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-=
variant-east-asian:normal;font-stretch:normal;line-height:normal;font-famil=
y:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" style=3D"font-variant-l=
igatures:no-common-ligatures">(4 rows)</span></p><p class=3D"gmail-p1" styl=
e=3D"margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;=
font-stretch:normal;line-height:normal;font-family:Monaco;color:rgb(0,0,0)"=
><span class=3D"gmail-s1" style=3D"font-variant-ligatures:no-common-ligatur=
es"><br></span></p><p class=3D"gmail-p1" style=3D"margin:0px;font-variant-n=
umeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-heigh=
t:normal;font-family:Monaco;color:rgb(0,0,0)"><span class=3D"gmail-s1" styl=
e=3D"font-variant-ligatures:no-common-ligatures"><br></span></p><p class=3D=
"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-variant-eas=
t-asian:normal;font-stretch:normal;line-height:normal;font-family:Monaco;co=
lor:rgb(0,0,0)"><span style=3D"color:rgb(34,34,34);font-family:Arial,Helvet=
ica,sans-serif">Our alerts that check whether a table is coming close to th=
e 2 billion point mark started firing on a database. We noticed that the ta=
ble that had the biggest age had a vacuum running on it that hasn&#39;t fin=
ished in 85 days. It was a table that was no longer use, so instead of rest=
arting the vacuum, we truncated and deleted the table, expecting that would=
 get rid of the need to=C2=A0</span><br></p></div></div></div>

--0000000000000a24cf058e8e9aea--