|Pivotal Greenplum (GPDB)||4.3.0 and higher|
|HAWQ||1.2.0 and higher|
How to detect which tables are keeping the age of a database high?
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 who's 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 on 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.