database_wraparound_age_datfrozenxid

Datfrozenxid wraparound age

Dimensions:None

Available on:

Prometheus (1)

Interface Metrics (1)

Prometheus

database_wraparound_age_datfrozenxid_seconds

Age of the oldest transaction ID that has not been frozen.

Dimensions:None

Sources

database_wraparound_age_datfrozenxid_secondsgithub.com

Technical Annotations (26)

Configuration Parameters (7)

autovacuum_vacuum_threshold

Minimum number of updated/deleted tuples before autovacuum triggers

autovacuum_vacuum_scale_factor

Fraction of table size used to calculate autovacuum trigger threshold

autovacuum_max_workers

Maximum number of concurrent autovacuum processes

autovacuum_naptime

Time between autovacuum runs on each database

autovacuum_freeze_max_agerecommended: 200000000

Default maximum age before forced vacuum to prevent wraparound; can only be set at server start

vacuum_freeze_min_agerecommended: 50000000 (default)

minimum age before rows are frozen during vacuum

vacuum_freeze_table_agerecommended: 0.95 * autovacuum_freeze_max_age

age threshold for aggressive vacuum scanning all pages

Error Signatures (2)

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 (5)

VACUUMremediation

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

gcloud sql instances patch INSTANCE_NAME --maintenance-window-day=SUN --maintenance-window-hour=2Schedule maintenance during low-transaction period to minimize XID advancement while autovacuum paused

VACUUM (FREEZE, VERBOSE) tablename;Manually freeze transaction IDs after maintenance if wraparound age became critical

Technical References (12)

autovacuumcomponenttransaction ID wraparoundconceptdead tuplesconceptVACUUMcomponentcheck_postgrescomponentpg_class.relfrozenxidcomponentpg_xactfile pathpg_prepared_xactscomponentpg_stat_activitycomponentpg_stat_replicationcomponentrelfrozenxidcomponentdatfrozenxidcomponent

Related Insights (6)

Inefficient autovacuum configuration causes table bloat and transaction ID wraparound riskcritical

▸

Transaction ID wraparound prevents new transactions and causes database shutdowncritical

▸

Transaction ID wraparound forces emergency autovacuum despite disabled autovacuumcritical

▸

Transaction ID wraparound prevents new transactionscritical

▸

Autovacuum disabled on static tables delays anti-wraparound vacuumwarning

▸

Cloud SQL autovacuum disabled during maintenance allows transaction ID wraparoundcritical

Cloud SQL maintenance windows can disable or suspend autovacuum for extended periods (1-4 hours) to perform updates. On high-transaction databases, this suspension allows transaction ID age to advance toward the 2-billion wraparound limit. PostgreSQL's autovacuum_freeze_max_age cannot prevent wraparound if Cloud SQL has paused autovacuum externally.

▸