PostgreSQL Transaction Wraparound Prevention

# PostgreSQL Transaction Wraparound Prevention

The most critical PostgreSQL error you can encounter:

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

Transaction wraparound is a database-ending event if not handled properly. Understanding and preventing it is essential for any PostgreSQL administrator.

Understanding Transaction IDs

PostgreSQL uses 32-bit transaction IDs (XIDs), creating a circular range from 0 to about 4.2 billion. Without management, transactions would eventually "wrap around" and data could become invisible or corrupted.

How PostgreSQL Handles Wraparound

1. 1.Freezing: Old tuples are marked as "frozen" - visible to all transactions
2. 2.Vacuum: The VACUUM process performs freezing and advances the freeze horizon
3. 3.Autovacuum: Automatic process that triggers freezing when needed

Critical Thresholds

-- View current thresholds
SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE '%freeze%' OR name = 'autovacuum_freeze_max_age'
ORDER BY name;

Check Current Wraparound Status

Database-Level Status

-- Check transaction age for all databases
SELECT
    datname AS database,
    age(datfrozenxid) AS current_age,
    current_setting('autovacuum_freeze_max_age')::bigint AS max_age,
    current_setting('autovacuum_freeze_max_age')::bigint - age(datfrozenxid) AS transactions_remaining,
    CASE
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint THEN 'STOPPED'
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.95 THEN 'CRITICAL'
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.8 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Table-Level Status

-- Check tables closest to wraparound
SELECT
    n.nspname AS schema,
    c.relname AS table,
    age(c.relfrozenxid) AS xid_age,
    current_setting('autovacuum_freeze_max_age')::bigint - age(c.relfrozenxid) AS transactions_to_emergency,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
    CASE
        WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.95 THEN 'CRITICAL'
        WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.8 THEN 'WARNING'
        WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.6 THEN 'ELEVATED'
        ELSE 'OK'
    END AS status,
    s.last_vacuum,
    s.last_autovacuum
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
  AND n.nspname NOT LIKE 'pg_%'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

View Running Autovacuum Processes

-- Check if autovacuum is working on freeze
SELECT
    pid,
    datname,
    relid::regclass AS table,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    index_vacuum_count
FROM pg_stat_progress_vacuum
WHERE phase IS NOT NULL;

Immediate Prevention Steps

1. Run Aggressive Vacuum

```sql -- Run vacuum freeze on all tables VACUUM FREEZE VERBOSE ANALYZE;

-- Target specific high-age tables VACUUM FREEZE VERBOSE ANALYZE my_schema.large_table; ```

2. Kill Blocking Transactions

```sql -- Find long-running transactions SELECT pid, usename, datname, now() - xact_start AS duration, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

-- Kill transactions running over 1 hour SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE xact_start < now() - interval '1 hour' AND pid <> pg_backend_pid(); ```

3. Handle Prepared Transactions

```sql -- Find prepared transactions SELECT transaction, gid, prepared, now() - prepared AS age, owner, database FROM pg_prepared_xacts;

-- Commit or rollback old prepared transactions COMMIT PREPARED 'old_transaction_gid'; -- or ROLLBACK PREPARED 'old_transaction_gid'; ```

4. Check Replication Slots

```sql -- Replication slots can prevent freeze advancement SELECT slot_name, slot_type, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_size FROM pg_replication_slots WHERE NOT active;

-- Drop inactive slots SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE NOT active; ```

Emergency Recovery

If the database has already stopped accepting commands:

Step 1: Enter Single-User Mode

```bash # Stop PostgreSQL sudo systemctl stop postgresql

# Start in single-user mode sudo -u postgres postgres --single -D /var/lib/postgresql/16/main mydb

# At the PostgreSQL prompt, run: VACUUM FREEZE VERBOSE ANALYZE;

# Exit with Ctrl+D ```

Step 2: If Single-User Mode Fails

```bash # Check for corruption pg_resetwal -D /var/lib/postgresql/16/main --latest

# WARNING: This resets the transaction log # Only use as last resort pg_resetwal -D /var/lib/postgresql/16/main

# Restart PostgreSQL sudo systemctl start postgresql ```

Step 3: Immediate Vacuum After Recovery

```sql -- After restarting, immediately vacuum VACUUM FREEZE VERBOSE ANALYZE;

-- Verify status SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC; ```

Configure Proper Autovacuum

System-Wide Settings

```conf # postgresql.conf

# Enable autovacuum (must be on) autovacuum = on track_counts = on

# Freeze thresholds autovacuum_freeze_max_age = 200000000 # 200M transactions vacuum_freeze_table_age = 150000000 # Start freezing at 150M vacuum_freeze_min_age = 50000000 # Freeze tuples older than 50M

# Autovacuum tuning for performance autovacuum_vacuum_cost_delay = 2ms autovacuum_vacuum_cost_limit = 200 autovacuum_work_mem = 1GB

# Logging for monitoring log_autovacuum_min_duration = 0 ```

Per-Table Tuning

For high-transaction tables:

```sql -- More aggressive freezing for high-churn tables ALTER TABLE my_busy_table SET ( autovacuum_freeze_max_age = 150000000, autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000 );

-- Less aggressive for large, static tables ALTER TABLE archive_table SET ( autovacuum_freeze_max_age = 500000000, autovacuum_vacuum_scale_factor = 0.1 ); ```

Monitor and Alert

Create Monitoring View

```sql CREATE OR REPLACE VIEW wraparound_status AS WITH db_status AS ( SELECT datname, age(datfrozenxid) AS db_age, current_setting('autovacuum_freeze_max_age')::bigint AS max_age FROM pg_database WHERE datname NOT IN ('template0', 'template1') ), table_status AS ( SELECT n.nspname AS schema_name, c.relname AS table_name, age(c.relfrozenxid) AS table_age, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%' ) SELECT 'DATABASE' AS level, datname AS name, db_age AS xid_age, max_age - db_age AS transactions_remaining, ROUND(db_age::numeric / max_age * 100, 2) AS percent_used, CASE WHEN db_age > max_age THEN 'CRITICAL - STOPPED' WHEN db_age > max_age * 0.95 THEN 'CRITICAL' WHEN db_age > max_age * 0.8 THEN 'WARNING' ELSE 'OK' END AS status FROM db_status UNION ALL SELECT 'TABLE' AS level, schema_name || '.' || table_name AS name, table_age AS xid_age, (SELECT max_age FROM db_status LIMIT 1) - table_age AS transactions_remaining, ROUND(table_age::numeric / (SELECT max_age FROM db_status LIMIT 1) * 100, 2) AS percent_used, CASE WHEN table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.95 THEN 'CRITICAL' WHEN table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.8 THEN 'WARNING' WHEN table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.6 THEN 'ELEVATED' ELSE 'OK' END AS status FROM table_status WHERE table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.5 ORDER BY xid_age DESC;

-- Use it SELECT * FROM wraparound_status WHERE status != 'OK'; ```

Create Alert Function

```sql CREATE OR REPLACE FUNCTION check_wraparound_risk() RETURNS TABLE(alert_level text, details text) LANGUAGE plpgsql AS $$ DECLARE threshold_warning bigint; threshold_critical bigint; max_age bigint; BEGIN max_age := current_setting('autovacuum_freeze_max_age')::bigint; threshold_warning := (max_age * 0.8)::bigint; threshold_critical := (max_age * 0.95)::bigint;

-- Check databases RETURN QUERY SELECT CASE WHEN age(datfrozenxid) > threshold_critical THEN 'CRITICAL' ELSE 'WARNING' END, format('Database %s at %s transactions (%.1f%% of max)', datname, age(datfrozenxid), age(datfrozenxid)::numeric / max_age * 100) FROM pg_database WHERE age(datfrozenxid) > threshold_warning AND datname NOT IN ('template0', 'template1');

-- Check tables RETURN QUERY SELECT CASE WHEN age(c.relfrozenxid) > threshold_critical THEN 'CRITICAL' ELSE 'WARNING' END, format('Table %s.%s at %s transactions (%.1f%% of max)', n.nspname, c.relname, age(c.relfrozenxid), age(c.relfrozenxid)::numeric / max_age * 100) FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%' AND age(c.relfrozenxid) > threshold_warning;

RETURN; END; $$;

-- Run check SELECT * FROM check_wraparound_risk(); ```

Calculate Time to Wraparound

-- Estimate time until wraparound
WITH transaction_rate AS (
    SELECT
        stat_reset,
        now() - stat_reset AS duration,
        xact_commit + xact_rollback AS total_transactions
    FROM pg_stat_database
    WHERE datname = current_database()
),
current_age AS (
    SELECT age(datfrozenxid) AS current_age
    FROM pg_database
    WHERE datname = current_database()
)
SELECT
    (SELECT current_age FROM current_age) AS current_xid_age,
    current_setting('autovacuum_freeze_max_age')::bigint AS max_age,
    current_setting('autovacuum_freeze_max_age')::bigint - (SELECT current_age FROM current_age) AS remaining_transactions,
    CASE
        WHEN total_transactions > 0 AND (SELECT duration FROM transaction_rate) > interval '0'
        THEN (current_setting('autovacuum_freeze_max_age')::bigint - (SELECT current_age FROM current_age))
             * EXTRACT(EPOCH FROM (SELECT duration FROM transaction_rate))
             / total_transactions / 86400
        ELSE NULL
    END AS estimated_days_until_emergency
FROM transaction_rate;

Transaction Rate Monitoring

```sql -- Create table to track transaction rates CREATE TABLE IF NOT EXISTS transaction_tracking ( sample_time timestamptz PRIMARY KEY, xid_age bigint, transactions_used bigint );

-- Insert sample INSERT INTO transaction_tracking (sample_time, xid_age, transactions_used) SELECT now(), age(datfrozenxid), 0 FROM pg_database WHERE datname = current_database();

-- Or update with transaction count INSERT INTO transaction_tracking (sample_time, xid_age, transactions_used) SELECT now(), (SELECT age(datfrozenxid) FROM pg_database WHERE datname = current_database()), xact_commit + xact_rollback FROM pg_stat_database WHERE datname = current_database();

-- Calculate rate SELECT t1.sample_time, t2.sample_time - t1.sample_time AS time_diff, t2.xid_age - t1.xid_age AS xid_increase, EXTRACT(EPOCH FROM (t2.sample_time - t1.sample_time)) AS seconds, (t2.xid_age - t1.xid_age) / NULLIF(EXTRACT(EPOCH FROM (t2.sample_time - t1.sample_time)), 0) AS xids_per_second FROM transaction_tracking t1 JOIN transaction_tracking t2 ON t2.sample_time > t1.sample_time ORDER BY t2.sample_time DESC LIMIT 10; ```

Verification

After implementing prevention measures:

```sql -- 1. Verify autovacuum is running SELECT datname, state, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%';

-- 2. Check current ages are healthy SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- 3. Verify tables are being frozen SELECT schemaname, relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE autovacuum_count > 0 ORDER BY last_autovacuum DESC;

-- 4. Check no tables near wraparound SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' AND age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.7; ```

Quick Reference