Open
Description
This can be used to highlight transaction id exhaustion/wraparound, in case of vacuum is not running and/or there are pending transactions (more info here).
Example query to expose the metric per database:
WITH max_age AS (
SELECT 2000000000 as max_old_xid
, setting AS autovacuum_freeze_max_age
FROM pg_catalog.pg_settings
WHERE name = 'autovacuum_freeze_max_age' )
SELECT
datname,
age(datfrozenxid) AS age_datfrozenxid,
ROUND(100*(age(datfrozenxid)/max_old_xid::float)) AS percent_towards_wraparound,
ROUND(100*(age(datfrozenxid)/autovacuum_freeze_max_age::float)) AS percent_towards_emergency_autovac,
FROM max_age, pg_database
;
Example query to expose the metric per table:
SELECT
n.nspname AS schemaname,
c.relname,
age(c.relfrozenxid) AS table_age
FROM pg_class c
WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])