How to check database age in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article explains how to check the database age in Greenplum.
 

What is database age

The database age in Greenplum refers to the same concept as a database age in PostgreSQL. Database age is dependent on the age of the oldest object in the database and can be calculated using the age() function.

It is important to check the age in all segments and master when working in Greenplum. The age may differ between segments or between segments and the master.
 

There are two potential ways of looking at the database age:
 

1. At a database level, which is dependent on the age of the oldest objects in the database.
2. At a table level, which tests a specific relation xid and determines its age.

Environment

Resolution

How to check database age

This section provides a few different ways of checking the database age and the individual relations age:
 

At a database level

To determine the age of all databases and have a visual hint pertaining to the wraparound limit, the following query can be used:

WITH cluster AS (
	SELECT gp_segment_id, datname, age(datfrozenxid) age FROM pg_database
	UNION ALL
	SELECT gp_segment_id, datname, age(datfrozenxid) age FROM gp_dist_random('pg_database')
)
SELECT  gp_segment_id, datname, age,
        CASE
                WHEN age < (2^31-1 - current_setting('xid_stop_limit')::int - current_setting('xid_warn_limit')::int) THEN 'BELOW WARN LIMIT'
                WHEN  ((2^31-1 - current_setting('xid_stop_limit')::int - current_setting('xid_warn_limit')::int) < age) AND (age <  (2^31-1 - current_setting('xid_stop_limit')::int)) THEN 'OVER WARN LIMIT and UNDER STOP LIMIT'
                WHEN age > (2^31-1 - current_setting('xid_stop_limit')::int ) THEN 'OVER STOP LIMIT'
                WHEN age < 0 THEN 'OVER WRAPAROUND'
        END
FROM cluster
ORDER BY datname, gp_segment_id;

At a table level

Running the following SQL query will generate an ordered list of tables that are old in a database. A LIMIT clause might be useful to see only a specific number of tables, or use a WHERE clause to show only tables with ages greater than some constant. This query needs to run in utility mode in all segments where the database age has been identified as high.

SELECT 
    coalesce(n.nspname, ''), 
    relname, 
    relkind, 
    relstorage, 
    age(relfrozenxid)
FROM 
    pg_class c 
    LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE 
    relkind = 'r' AND relstorage NOT IN ('x')
ORDER BY 5 DESC

This article shows a procedure to scan the database for relations that are keeping the database age high and automatically generate "VACUUM FREEZE" commands to be used in those tables.