Pivotal Knowledge Base

Follow

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

Environment

Pivotal Greenplum Database (GPDB) 4.x

Purpose
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

Cause

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

 

Comments

Powered by Zendesk