Pivotal Knowledge Base


How to Check Database Age in Greenplum


Pivotal Greenplum Database (GPDB) 4 and 5


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

What is database age

When we talk about database age in Greenplum, we are referring to the same concept as 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 could differ between segments or between segments and the master.

There are two potential ways of looking at 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 just tests a specific relation xid and determines its age


How to check database age

This section will provide a few different ways of checking database and individual relations age:

At a database level

To determine the age of all databases and have a visual hint of where we are with respect to the wraparound limit, the following query can be used:

WITH cluster AS (
	SELECT gp_segment_id, datname, age(datfrozenxid) age FROM pg_database
	SELECT gp_segment_id, datname, age(datfrozenxid) age FROM gp_dist_random('pg_database')
SELECT  gp_segment_id, datname, age,
                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'
FROM cluster
ORDER BY datname, gp_segment_id;

Another way of checking the age using a shell script.

At a table level

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

    coalesce(n.nspname, ''), 
    pg_class c 
    LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
    relkind = 'r' AND relstorage NOT IN ('x')

This article shows a procedure to scan the database for relations that are keeping the database age high. It also provides the "VACUUM FREEZE" commands to be used. Again, it is important to remember that this needs to run in every segment (including the master), as the age is not necessarily consistent through segments.



Powered by Zendesk