|Pivotal Greenplum (GPDB)||4.2.x, 4.3.x|
|OS||All supported OS|
When checking Database (DB) age, we normally found all DB age at the same level, even for those not used as DB (postgres, template1). Some users might be wondering why all the DB have the same age, even if I only used one of them.
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.
Please refer to the below Postgres document to understand more about this process:
Please also refer below KB for how to reduce DB age.