Section 2 of 3 · 7 minutes
Read vacuum and analyze health
Your goal
What you will be able to do
Use table statistics to find delayed vacuum, delayed analyze, and transaction-ID risk.
Why this matters at work
The practical reason
Dead-row count alone does not show whether maintenance is healthy or urgent.
Learn
The idea in plain English
Review last manual and automatic vacuum and analyze times together with row estimates and change counts. Analyze updates planner statistics; vacuum handles cleanup and transaction-ID maintenance.
Transaction ID age matters even on tables with few updates. PostgreSQL must freeze old row metadata before wraparound. Treat wraparound warnings as urgent safety signals.
Remember these points
- Vacuum and analyze solve different problems.
- Statistics reset time affects cumulative counters.
- Transaction-ID age can be urgent even without obvious bloat.
See it in SQL
Review maintenance times and transaction age
Combine routine cleanup evidence for user tables.
SELECT
relname AS table_name,
n_dead_tup,
last_autovacuum,
last_autoanalyze,
age(relfrozenxid) AS transaction_id_age
FROM pg_stat_user_tables AS stats
JOIN pg_class AS classes ON classes.oid = stats.relid
ORDER BY age(relfrozenxid) DESC
LIMIT 20;The oldest transaction-ID age appears first. Review it against the server's freeze settings and alerts.
A missing recent autovacuum may be expected on an unchanged small table, so include workload context.
The 20 user tables with the oldest transaction metadata, including dead rows and recent automatic maintenance times.
Useful words
Important terms
- Freeze
- Vacuum work that makes old row transaction metadata safe from transaction-ID wraparound.
Quick check · Not graded
Check your understanding
What does ANALYZE primarily update?
Your progress is saved to your signed-in account.