Pivotal Knowledge Base


One single query causes the age to increase in all other databases


Pivotal Greenplum 4

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