PostgreSQL Vacuum Failed: Cannot Freeze Tuples

# PostgreSQL Vacuum Failed: Cannot Freeze Tuples

Your PostgreSQL logs show a critical vacuum error:

``` ERROR: database "mydb" must be vacuumed within 1000000 transactions HINT: To avoid a database shutdown, execute a database-wide VACUUM of that database. You might also need to commit or roll back old prepared transactions.

ERROR: could not freeze tuple (1234, 5) in relation 16384 DETAIL: Transaction 123456789 would precede transaction wraparound limit. ```

This is a serious situation. PostgreSQL uses 32-bit transaction IDs, and without proper vacuuming, you can run into transaction wraparound protection limits that will force the database into read-only mode.

Understanding Transaction Freeze

PostgreSQL transaction IDs are 32-bit integers that wrap around at about 4 billion. To prevent data loss during wraparound, PostgreSQL "freezes" old tuples, marking them as valid for all transactions. Vacuum performs this freezing.

Key thresholds:

Check your database's freeze status:

```sql -- View transaction ID age SELECT datname, age(datfrozenxid) AS xid_age, max(age(datfrozenxid)) OVER () AS max_age, current_setting('autovacuum_freeze_max_age')::bigint AS freeze_limit FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- Check individual table freeze status SELECT relname, age(relfrozenxid) AS xid_age, current_setting('autovacuum_freeze_max_age')::bigint - age(relfrozenxid) AS transactions_to_emergency, pg_size_pretty(pg_total_relation_size(oid)) AS size FROM pg_class WHERE relkind IN ('r', 't', 'm') AND age(relfrozenxid) > 100000000 ORDER BY age(relfrozenxid) DESC; ```

Immediate Action Required

If Database is in Emergency Mode

```sql -- Check if read-only mode is active SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database WHERE age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint;

-- You MUST run vacuum as superuser VACUUM FREEZE VERBOSE; ```

If regular vacuum fails:

```bash # Run vacuum in single-user mode postgres --single -D /var/lib/postgresql/16/main mydb

# At the prompt: VACUUM FREEZE VERBOSE ANALYZE;

# Exit with Ctrl+D ```

Run Aggressive Vacuum

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

-- Or target specific high-age tables VACUUM FREEZE VERBOSE ANALYZE my_large_table;

-- For specific schema VACUUM FREEZE VERBOSE ANALYZE my_schema.*; ```

Troubleshooting Vacuum Failures

Check for Blocking Locks

```sql -- Find what's blocking vacuum SELECT l.pid, l.locktype, l.mode, l.granted, a.query, a.state, a.usename FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE l.granted = false;

-- Kill blocking sessions if necessary SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query NOT LIKE '%autovacuum%' AND state = 'idle in transaction' AND now() - query_start > interval '1 hour'; ```

Check for Long-Running Transactions

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

-- Minimum transaction ID in use SELECT min(transaction_id) FROM pg_stat_activity; ```

Long-running transactions prevent vacuum from freezing tuples. Terminate them:

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

Check for Prepared Transactions

```sql -- Prepared transactions hold back the freeze horizon SELECT * FROM pg_prepared_xacts;

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

Check for Replication Slots

```sql -- Inactive replication slots prevent cleanup 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;

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

Cannot Freeze Tuple Errors

Error: "could not freeze tuple"

This error occurs when a tuple cannot be frozen due to concurrent access or corruption:

ERROR:  could not freeze tuple (0,1) in relation 16384

Step 1: Identify the problematic table:

-- Find relation name from OID
SELECT relname, relkind, nspname
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.oid = 16384;

Step 2: Check for corruption:

```sql -- Install pgstattuple if not present CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Check for dead tuples SELECT * FROM pgstattuple('problem_table');

-- Check for corruption SELECT ctid, xmin, xmax, * FROM problem_table WHERE xmin::text::bigint > (txid_current() - 1000000)::text::bigint; ```

Step 3: Fix corrupted tuples:

```sql -- Identify corrupt rows SELECT ctid, * FROM problem_table WHERE xmin::text::bigint > 1000000000; -- Adjust threshold

-- Rebuild the table -- WARNING: Requires exclusive lock BEGIN; LOCK TABLE problem_table IN ACCESS EXCLUSIVE MODE; CREATE TABLE problem_table_new AS SELECT * FROM problem_table; DROP TABLE problem_table; ALTER TABLE problem_table_new RENAME TO problem_table; -- Recreate indexes COMMIT;

-- Or use VACUUM FULL (also requires exclusive lock) VACUUM FULL VERBOSE problem_table; ```

Error: "lock conflict"

Vacuum cannot proceed due to lock conflicts:

```sql -- Check for conflicting locks SELECT relation::regclass, mode, granted, pid, query FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE relation = 'problem_table'::regclass;

-- Terminate conflicting sessions SELECT pg_terminate_backend(pid) FROM pg_locks WHERE relation = 'problem_table'::regclass AND pid <> pg_backend_pid(); ```

Configure Proper Autovacuum

Prevent future issues by configuring autovacuum correctly:

```conf # postgresql.conf

# Enable autovacuum (should be ON by default) autovacuum = on track_counts = on

# Freeze settings autovacuum_freeze_max_age = 200000000 vacuum_freeze_min_age = 50000000 vacuum_freeze_table_age = 150000000

# Aggressive autovacuum for tables near wraparound autovacuum_vacuum_cost_delay = 2ms autovacuum_vacuum_cost_limit = 200

# More frequent vacuum for high-churn tables autovacuum_vacuum_scale_factor = 0.1 autovacuum_vacuum_threshold = 1000 ```

Per-Table Autovacuum Tuning

```sql -- More aggressive settings for high-churn tables ALTER TABLE high_churn_table SET ( autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 500, autovacuum_freeze_max_age = 150000000 );

-- Check current per-table settings SELECT c.relname, c.reloptions FROM pg_class c WHERE c.relname = 'high_churn_table'; ```

Monitoring Freeze Status

Create Monitoring Query

```sql CREATE OR REPLACE VIEW freeze_monitoring AS SELECT n.nspname AS schema, c.relname AS table, pg_size_pretty(pg_total_relation_size(c.oid)) AS size, age(c.relfrozenxid) AS xid_age, current_setting('autovacuum_freeze_max_age')::bigint - age(c.relfrozenxid) AS transactions_until_emergency, CASE WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.9 THEN 'CRITICAL' WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.75 THEN 'WARNING' ELSE 'OK' END AS status, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid LEFT JOIN pg_stat_user_tables s ON c.relname = s.relname AND n.nspname = s.schemaname WHERE c.relkind = 'r' ORDER BY age(c.relfrozenxid) DESC;

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

Alert Function

```sql CREATE OR REPLACE FUNCTION check_freeze_status() RETURNS TABLE( alert_level text, schema_name text, table_name text, transactions_remaining bigint ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT 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' ELSE 'INFO' END, n.nspname, c.relname, current_setting('autovacuum_freeze_max_age')::bigint - age(c.relfrozenxid) FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.7 ORDER BY age(c.relfrozenxid) DESC; END; $$;

-- Run daily check SELECT * FROM check_freeze_status(); ```

Emergency Recovery

If the database shuts down due to wraparound:

```bash # 1. Stop PostgreSQL completely sudo systemctl stop postgresql

# 2. Start in single-user mode postgres --single -D /var/lib/postgresql/16/main postgres

# At the prompt, run: VACUUM FREEZE VERBOSE;

# Exit Ctrl+D

# 3. Restart normally sudo systemctl start postgresql ```

If single-user mode fails due to corruption:

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

# WARNING: This is a last resort - may cause data loss pg_resetwal -D /var/lib/postgresql/16/main --force ```

Verification

After vacuum freeze succeeds:

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

-- Check autovacuum is running SELECT schemaname, relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables ORDER BY last_autovacuum DESC NULLS LAST LIMIT 10;

-- Verify no tables near wraparound SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' AND age(relfrozenxid) > 150000000; ```

Quick Reference