Pivotal Knowledge Base


Vacuuming Old Tables in a Database to Reduce its Age


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.

Additional Information

[1] Database age in Greenplum
[2] About XIDs and XID wraparound in Greenplum



  • Avatar
    Brian Honohan

    Hi Mike,

    Would it be possible to amend the 2nd line of the script, to the following:

    SELECT 'VACUUM FREEZE ' || nspname || '.' || relname || ';'

    So that the output produced is:

     VACUUM FREEZE information_schema.sql_languages;
     VACUUM FREEZE information_schema.sql_packages;
     VACUUM FREEZE information_schema.sql_parts;
     VACUUM FREEZE information_schema.sql_sizing;
     VACUUM FREEZE information_schema.sql_sizing_profiles;

    Instead of the current:

     VACUUM TABLE information_schema.sql_languages;
     VACUUM TABLE information_schema.sql_packages;
     VACUUM TABLE information_schema.sql_parts;
     VACUUM TABLE information_schema.sql_sizing;
     VACUUM TABLE information_schema.sql_sizing_profiles;
  • Avatar
    Mike Roth

    Script has been updated to use VACUUM FREEZE

  • Avatar
    Krzysztof Byszewski

    Hi Mike, now it says vacuum freeze table, should be vacuum freeze

  • Avatar
    Brian Honohan

    Hi Mike,

    The script still creates statements that VACUUM FREEZE TABLE, instead of the desired VACUUM FREEZE.

  • Avatar
    Brian Honohan

    Script updated to remove VACUUM FREEZE TABLE. Replaced with VACUUM FREEZE.

Powered by Zendesk