Home / Database / PostgreSQL Vacuum Failed: Cannot Freeze Tuples

Database

PostgreSQL Vacuum Failed: Cannot Freeze Tuples

Fix PostgreSQL vacuum failures when tuples cannot be frozen and prevent transaction wraparound issues.

Published: Nov 16, 202510 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

# 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.

Introduction

This article covers troubleshooting steps and solutions for PostgreSQL Vacuum Failed: Cannot Freeze Tuples. The error typically occurs in production environments and can cause service disruptions if not addressed promptly.

Symptoms

Common error messages include:

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

```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; ```

```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; ```

Common Causes

Configuration misconfiguration
Missing or incorrect credentials
Network connectivity issues
Version compatibility problems
Resource exhaustion or limits
Permission or access denied

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

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:

Threshold	Default Value	Meaning
`vacuum_freeze_min_age`	50 million	Minimum age before freezing
`vacuum_freeze_table_age`	150 million	Force vacuum to prevent wraparound
`autovacuum_freeze_max_age`	200 million	Database becomes read-only if exceeded

Check your database's freeze status:

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:

sql

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:

bash

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

Step 1: Identify the problematic table:

sql

-- 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; ```

Verification

Check	Command
Database age	`SELECT age(datfrozenxid) FROM pg_database;`
Table age	`SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind='r';`
Freeze limit	`SHOW autovacuum_freeze_max_age;`
Run vacuum freeze	`VACUUM FREEZE VERBOSE ANALYZE;`
Single-user mode	`postgres --single -D /data/dbname`
Prepared transactions	`SELECT * FROM pg_prepared_xacts;`
Kill long transaction	`SELECT pg_terminate_backend(pid);`
Min transaction	`SELECT min(transaction_id) FROM pg_stat_activity;`

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

PostgreSQL Vacuum Failed: Cannot Freeze Tuples PostgreSQL Vacuum Failed: Cannot Freeze Tuples Database PostgreSQL Vacuum Failed: Cannot Freeze Tuples troubleshooting PostgreSQL Vacuum Failed: Cannot Freeze Tuples fix Fix PostgreSQL vacuum failures when tuples cannot be frozen and prevent transaction wraparound issues Database Fix PostgreSQL vacuum failures when tuples cannot be frozen and prevent transaction wraparound issues

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Nov 16, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

PostgreSQL Vacuum Failed: Cannot Freeze Tuples

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding Transaction Freeze

Immediate Action Required

If Database is in Emergency Mode

Run Aggressive Vacuum

Troubleshooting Vacuum Failures

Check for Blocking Locks

Check for Long-Running Transactions

Check for Prepared Transactions

Check for Replication Slots

Cannot Freeze Tuple Errors

Error: "could not freeze tuple"

Error: "lock conflict"

Configure Proper Autovacuum

Per-Table Autovacuum Tuning

Monitoring Freeze Status

Create Monitoring Query

Alert Function

Emergency Recovery

Verification

Verification

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

PostgreSQL Vacuum Failed: Cannot Freeze Tuples

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding Transaction Freeze

Immediate Action Required

If Database is in Emergency Mode

Run Aggressive Vacuum

Troubleshooting Vacuum Failures

Check for Blocking Locks

Check for Long-Running Transactions

Check for Prepared Transactions

Check for Replication Slots

Cannot Freeze Tuple Errors

Error: "could not freeze tuple"

Error: "lock conflict"

Configure Proper Autovacuum

Per-Table Autovacuum Tuning

Monitoring Freeze Status

Create Monitoring Query

Alert Function

Emergency Recovery

Verification

Verification

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading