Pivotal Knowledge Base


One Single Query Causes the Age to Increase in All the Databases


Pivotal Greenplum Database (GPDB) 4.x

When checking database age, we normally find all ages roughly at the same level, even for those not normally used (i.e. postgres, template1).

gpadmin=# SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
postgres | 13964393
gpadmin | 13964393
template1 | 13964393
template0 | 13964393
bsdbsp01 | 13964393
gpperfmon | 13964393


It's due to Postgres stores XID statistics in the system tables pg_class and pg_database, as pg_database is a shared catalog table, every access to the pg_database table will be recorded in pg_class table (relfrozenxid column), then affect the DB age calculation for each DB.

Additional Information
[1] Database age in Greenplum
[2] About XIDs and XID wraparound in Greenplum



Powered by Zendesk