before_xid_wraparound

Txns before wraparound

Dimensions:None

Available on:

Datadog (1)

Interface Metrics (1)

Datadog

postgresql.before_xid_wraparound

The number of transactions that can occur until a transaction wraparound. This metric is tagged with db.

Dimensions:None

Sources

postgresql.before_xid_wraparoundgithub.com

Technical Annotations (30)

Configuration Parameters (4)

autovacuum_vacuum_scale_factorrecommended: 0.02

prevents XID wraparound by triggering vacuum more frequently

autovacuum_max_workersrecommended: 5-8

more workers reduce risk of tables falling behind on vacuum

autovacuum_freeze_max_agerecommended: 200000000

Default threshold controlling when PostgreSQL enforces wraparound protection

vacuum_freeze_min_agerecommended: 50000000 (default)

minimum age before rows are frozen during vacuum

Error Signatures (4)

WARNING: oldest xmin is far in the pastlog pattern

WARNING: database "mydb" must be vacuumed within 10000000 transactionslog pattern

WARNING: database "mydb" must be vacuumed within 39985967 transactionslog pattern

ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"log pattern

CLI Commands (10)

SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, ROUND(age(c.relfrozenxid)::numeric / 2000000000 * 100, 2) AS pct_to_wraparound FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY age(c.relfrozenxid) DESC LIMIT 20;

diagnostic

SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age, 200000000 - age(c.relfrozenxid) AS headroom FROM pg_class c WHERE c.relkind = 'r' AND age(c.relfrozenxid) > 150000000 ORDER BY age(c.relfrozenxid) DESC;

monitoring

VACUUM (FREEZE, VERBOSE) events;remediation

SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC;diagnostic

VACUUM FREEZEremediation

SHOW autovacuum_freeze_max_age;diagnostic

vacuumdb --analyze --verbose --jobs=4 mydbremediation

SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm');

diagnostic

SELECT datname, age(datfrozenxid) FROM pg_database;diagnostic

VACUUMremediation

Technical References (12)

transaction ID wraparoundconceptage(relfrozenxid)conceptpg_classcomponentVACUUMcomponentcheck_postgrescomponentrelfrozenxidcomponentautovacuum_freeze_max_agecomponenttransaction ID (XID)conceptpg_prepared_xactscomponentpg_stat_activitycomponentpg_stat_replicationcomponentdatfrozenxidcomponent

Related Insights (5)

Transaction ID wraparound risk causes database shutdowncritical

PostgreSQL's 32-bit transaction ID counter approaches exhaustion (2 billion limit), risking forced database shutdown. Tables with age(relfrozenxid) approaching autovacuum_freeze_max_age require aggressive freezing VACUUMs to prevent wraparound.

▸

Transaction ID wraparound prevents new transactions and causes database shutdowncritical

▸

Transaction ID wraparound causes complete write outagecritical

▸

Transaction ID wraparound shutdown riskcritical

▸

Transaction ID wraparound prevents new transactionscritical

▸