PostgreSQL / Intermediate: concurrency and maintainable SQL / Lesson 11

VACUUM, autovacuum and planner statistics

Explain why version cleanup and statistics maintenance are normal PostgreSQL work. Diagnose cleanup delays without disabling safeguards or treating VACUUM FULL as routine.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Distinguish dead versions, reusable space and returned disk space.
Inspect maintenance evidence and long transaction effects.

MVCC creates a cleanup obligation

UPDATE and DELETE leave older tuple versions that may still be visible to existing snapshots. Once no transaction needs them, vacuum can make their space reusable. Ordinary VACUUM does not generally shrink every relation file back to its minimum size, so an unchanged operating-system file size is not proof that cleanup failed. Visibility-map maintenance also supports index-only scans, while freezing protects transaction-ID interpretation over time. These are normal lifecycle tasks, not evidence that PostgreSQL has forgotten to delete logical rows.

Read counters as estimates with timestamps

pg_stat_user_tables provides useful counters and last-maintenance timestamps for visible user tables. n_live_tup and n_dead_tup are estimates, not exact forensic counts or direct measurements of reclaimable bytes. Counters can reset and statistics visibility has timing rules, so compare samples and context rather than treating one row as a diagnosis. A large updated table with recent cleanup can still need attention if its write rate outruns maintenance. Begin with evidence before proposing per-table tuning.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT schemaname, relname, n_live_tup, n_dead_tup,
 last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'quay_lab' ORDER BY relname;

Actual counters are environment-dependent. NULL timestamps do not by themselves prove maintenance is disabled.

Autovacuum is workload maintenance

Autovacuum normally performs cleanup and automatic analyze as tables change, using thresholds and scale factors whose effective values should be inspected for the deployed release. Different churn rates can justify reviewed per-table settings; a universal copied scale factor is not a capacity plan. Even when routine autovacuum is disabled, PostgreSQL can still start anti-wraparound work. Do not disable it to hide latency without a replacement strategy. Freeze-age risks and disk headroom require monitoring before they become an availability incident.

Identify sessions that retain old history

Long-running transactions and idle-in-transaction sessions can keep row versions needed and obstruct effective cleanup. Replication slots and standby feedback can also retain history or WAL under their own rules. The query below lists transaction ages for the current database without terminating anything. Access to complete activity details depends on monitoring privileges. Investigate the owning application and intended transaction before deciding on an authorized cancellation; killing a backend blindly can roll back important work without solving the workload cause.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT pid, usename, state, xact_start, backend_xmin,
 clock_timestamp() - xact_start AS transaction_age
FROM pg_stat_activity
WHERE datname = current_database() AND xact_start IS NOT NULL
ORDER BY xact_start;

This is observation only. The oldest transaction is a lead to investigate, not automatic permission to terminate it.

Keep ANALYZE and rewrites distinct

ANALYZE samples data to maintain planner statistics; it does not perform the same cleanup task as VACUUM, although they can be requested together. VACUUM FULL rewrites the table, needs extra working space and takes an ACCESS EXCLUSIVE lock, so it is not ordinary background autovacuum. Partitioned parents and temporary tables have special maintenance considerations; do not assume automatic behavior covers every relation equally. Plan any manual maintenance from the exact manual, acceptable lock budget and recovery evidence rather than a hardcoded daily rewrite ritual.

Common errors and fixes

Disk files do not shrink after ordinary VACUUM

Why it happens: Reusable internal space was confused with space returned to the OS.
How to fix it: Check workload reuse and relation growth before considering a disruptive rewrite.
Prevent it next time: Set separate goals for cleanup, bloat control and physical shrinkage.

Dead-version estimates keep increasing

Why it happens: Write churn, retained snapshots or maintenance capacity may outpace cleanup.
How to fix it: Correlate transaction age, vacuum progress and write rate.
Prevent it next time: Monitor trends and evaluate per-table policy from evidence.

VACUUM cannot run inside a transaction block (25001)

Why it happens: Maintenance was wrapped in an ordinary business transaction.
How to fix it: Separate the reviewed maintenance procedure from transactional DML.
Prevent it next time: Check each administrative command’s transaction restrictions.

Check your understanding

1. A table file remains 2 GB after many deletes and ordinary vacuum. Does that prove deleted rows are still visible?

Show worked solution

No. Logical visibility and physical file size are different facts. The space may be reusable within the relation while the file remains allocated. Verify logical row results and maintenance evidence, then assess whether future writes can reuse the space. A rewrite solely to make a file look smaller can impose unnecessary locks and temporary storage pressure.

2. An application leaves a transaction idle for hours after a SELECT. Why should a maintenance investigation include it?