How to check database age in Greenplum

Products

VMware Tanzu Greenplum Greenplum VMware Tanzu Data Suite VMware Tanzu Data Suite

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:

At a database level, which is dependent on the age of the oldest objects in the database.
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  (age >= (2^31-1 - current_setting('xid_stop_limit')::int - current_setting('xid_warn_limit')::int)) 
              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 age DESC, 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.

SELECT
    n.nspname,
    relname,
    relkind,
    age(relfrozenxid)
FROM
    pg_class c
    LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
    relkind in ('r', 'm')
    AND age(relfrozenxid) <> 2^31-1
ORDER BY age(relfrozenxid) DESC;

See article "Vacuuming old tables in a database to reduce its age" for a procedure to scan the database for relations that are keeping the database age high and automatically generate "VACUUM FREEZE" commands to be used to VACUUM the tables.