PostgreSQL 72000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 72000: Snapshot Too Old

The 72000 error in PostgreSQL — "snapshot too old" — occurs when a transaction attempts to access row versions that have already been cleaned up by VACUUM. Introduced alongside the old_snapshot_threshold configuration parameter in PostgreSQL 9.6, this error is a deliberate safeguard that allows the database engine to reclaim storage more aggressively without being blocked indefinitely by long-running transactions.

---

Top 3 Causes

1. old_snapshot_threshold Is Set Too Low

When old_snapshot_threshold is configured (e.g., 60min), PostgreSQL is permitted to vacuum away old row versions even if an active snapshot still references them — once that snapshot exceeds the threshold age. Any transaction using an expired snapshot will hit this error when it tries to read a cleaned-up row version.

-- Check current setting
SHOW old_snapshot_threshold;

-- Increase the threshold to reduce frequency of this error
ALTER SYSTEM SET old_snapshot_threshold = '120min';
SELECT pg_reload_conf();

-- Verify the change
SELECT name, setting, unit
FROM pg_settings
WHERE name = 'old_snapshot_threshold';

---

2. Long-Running Transactions

Analytical queries, batch jobs, or poorly managed transactions that stay open for hours are the most common culprit. While the transaction holds its original snapshot, autovacuum continues to clean up dead row versions in the background. By the time the query tries to read an old row version, it may already be gone.

-- Find transactions running longer than 5 minutes
SELECT
    pid,
    usename,
    state,
    now() - xact_start AS xact_duration,
    left(query, 120) AS query_preview
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_duration DESC;

-- Cancel a specific long-running query safely
SELECT pg_cancel_backend(<pid>);

-- Forcefully terminate if cancel doesn't work
SELECT pg_terminate_backend(<pid>);

---

3. Aggressive autovacuum Settings

Tables with high UPDATE/DELETE activity can trigger autovacuum to run very aggressively, quickly reclaiming old row versions. If a long-running transaction's snapshot references those versions, the error surfaces. Tuning per-table autovacuum settings can reduce the collision rate.

-- Check autovacuum-related settings
SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

-- Slow down autovacuum on a specific high-traffic table
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_vacuum_cost_delay = 10
);

-- Check last autovacuum activity per table
SELECT
    schemaname,
    relname,
    last_autovacuum,
    last_autoanalyze,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST
LIMIT 10;

---

Quick Fix Solutions

-- 1. Set timeouts to kill idle/long transactions automatically
ALTER SYSTEM SET idle_in_transaction_session_timeout = '300000'; -- 5 minutes
ALTER SYSTEM SET statement_timeout = '3600000';                  -- 1 hour
SELECT pg_reload_conf();

-- 2. For long read workloads, use cursors to limit snapshot exposure
BEGIN;
DECLARE analysis_cursor CURSOR FOR
    SELECT * FROM large_fact_table WHERE event_date > '2024-01-01';
FETCH 5000 FROM analysis_cursor;
-- process batch, then fetch next
FETCH 5000 FROM analysis_cursor;
CLOSE analysis_cursor;
COMMIT;

-- 3. Disable old_snapshot_threshold if not needed (default behavior)
ALTER SYSTEM SET old_snapshot_threshold = -1;
SELECT pg_reload_conf();

---

Prevention Tips

1. Separate OLAP from OLTP using a read replica. Run long analytical queries on a Hot Standby (streaming replication replica) so they don't interfere with VACUUM on the primary. On the replica, you can set old_snapshot_threshold = -1 for unlimited snapshot retention without impacting primary-side storage reclamation.

2. Always configure transaction and statement timeouts in production. Setting idle_in_transaction_session_timeout and statement_timeout prevents runaway transactions from holding stale snapshots indefinitely. This is one of the simplest and most effective guardrails against snapshot too old, lock contention, and connection pool exhaustion.
   

-- Recommended production baseline
ALTER SYSTEM SET idle_in_transaction_session_timeout = '300000';
ALTER SYSTEM SET statement_timeout = '3600000';
ALTER SYSTEM SET lock_timeout = '30000';
SELECT pg_reload_conf();

---

Related Errors

Code	Name	Relation
40001	serialization_failure	MVCC snapshot conflict under SERIALIZABLE isolation
55P03	lock_not_available	Often co-occurs with long transactions holding locks
57014	query_canceled	Triggered by timeouts set to prevent snapshot too old

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.