Vacuuming old tables in a database to reduce its age

Products

VMware Tanzu Greenplum Greenplum Pivotal Data Suite Non Production Edition VMware Tanzu Data Suite VMware Tanzu Data Suite

Issue/Introduction

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.

This article will target the oldest tables rather than requiring a complete database vacuum.

Possible messages that indicate urgent VACUUM is needed:

FATAL: database is not accepting commands to avoid 
wraparound data loss in database "database_name"

or

WARNING: database "database_name" must be vacuumed within 10994670 transactions 
HINT: To avoid a database shutdown, execute a full-database VACUUM in "database_name".

Environment

Resolution

There are a number of objects which can keep the age of a database high in addition to base relations. In addition to base objects and TOAST objects, these objects include visimaps, segments, and block objects created/used by AppendOptimized(AO) tables

However, only top-level relation objects are valid targets for VACUUM and finding the relation associated with a sub-object can cause issues.

To that end, the vac_freeze.sql query below 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_warn_limit (defaults to 500,000,000 transactions).

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 database.

Note: Some tables will not be vacuumed using the default VACUUM command in psql. This is a result of orphan temporary tables. To find more about orphan temporary schemas, see the following article: Orphan Temporary Schemas.

Additional Information

Refer to the following links and the attachment for more information:

Database age in Greenplum
About XIDs and XID wraparound in Greenplum

vac_freeze.sql query:

SELECT 'VACUUM FREEZE ' || nspname || '.' || relname || '; --' AS command,
       age(relfrozenxid)
FROM   pg_class c
       join pg_namespace n
         ON ( c.relnamespace = n.oid )
WHERE  ( c.relkind = 'r' )
       AND ( age(relfrozenxid) < 2147483647 )
       AND ( age(relfrozenxid) > 2147483647 - (SELECT current_setting('xid_warn_limit'))::int )
       AND ( nspname NOT IN ( 'pg_catalog', 'information_schema' ) )
UNION ALL
SELECT 'VACUUM FREEZE ' || n.nspname || '.' || c1.relname || '; --' AS command,
       age(c2.relfrozenxid)
FROM   pg_class c1
       join pg_namespace n
         ON ( c1.relnamespace = n.oid )
       join (SELECT *
             FROM   pg_class
             WHERE  relkind = 'o') c2
         ON ( c1.oid = Ltrim(c2.relname, 'pg_aocsseg') :: oid )
WHERE  ( age(c2.relfrozenxid) < 2147483647 )
       AND ( age(c2.relfrozenxid) > 2147483647 - (SELECT current_setting('xid_warn_limit'))::int )
ORDER  BY 2 DESC;