Pivotal Greenplum Database (GPDB) 4 and 5
This article describes how to detect which tables are keeping the age of a database high and how to generate the VACUUM FREEZE commands that need to be run in order to reduce their age.
There are a number of objects, which can keep the age of a database high in addition to base relations due to the fact that UAO tables are now counted. These objects include UAO visimaps, segments and block objects in addition to base objects and TOAST objects.
However, only top-level relation objects are valid targets for VACUUM and finding the relation associated with a sub-object can be problematic.
To that end, the script attached can be used to generate a list of
VACUUM FREEZE commands which can then be run against the database to bring down the age. The script will scan for objects in the database whose age is greater than
xid_cutoff which can be configured (defaults to 100,000,000 transactions).
One note, this script will use the age of the objects as found on the master. It is possible that the age of individual segments will remain high. In order to address this issue, run the attached script against the databases on the individual segments and the resultant commands against the same.
Note- Some table won't be vacuumed using the default VACUUM command in psql. Under this category falls the orphan temporary tables. To find more about orphan temporary schemas, see the following article: Orphan Temporary Schemas.