Pivotal Knowledge Base

Follow

Single query would cause all database age increase

Environment

Product Version
Pivotal Greenplum (GPDB) 4.2.x, 4.3.x
OS All supported OS

Purpose
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

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.

Note
Please refer to the below Postgres document to understand more about this process:
https://www.postgresql.org/docs/8.2/static/routine-vacuuming.html

Please also refer below KB for how to reduce DB age.
https://discuss.zendesk.com/hc/en-us/articles/218874537--WARNING-database-xxx-must-be-vacuumed-within-XXXX-transactions-message-seen-in-Pivotal-Greenplum-

 

Comments

Powered by Zendesk