Why this problem matters
Updates and deletes create dead row versions, and lagging cleanup can make scans and storage less efficient. Rising n_dead_tup does not always mean autovacuum is broken.
How to diagnose it
Compare dead-row estimates with n_tup_upd, n_tup_del, vacuum times, and long transactions. Evaluate generation and cleanup rates over time instead of relying on one sample.
The query shows cumulative activity counters and row estimates for user tables. Statistics resets can leave timestamps and counters with incomplete history.
SELECT schemaname, relname, n_live_tup, n_dead_tup,
n_tup_upd, n_tup_del, last_vacuum, last_autovacuum,
vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 25;A safe solution approach
Correct long transactions and workloads obstructing vacuum, then assess maintenance capacity against table behavior. Avoid scheduling aggressive manual maintenance before establishing the cause.
Why continuous monitoring matters
moon helps continuously compare dead-row and vacuum trends with low overhead. Persistent divergence can be routed through Slack, PagerDuty, or webhooks.
Frequently asked questions
Does high n_dead_tup prove table bloat?
No, the value is an estimate and does not directly measure reusable free space. Physical bloat assessment requires additional safe investigation.
How does moon help with this problem?
moon continuously observes SQL Server, PostgreSQL, and MongoDB signals, helping teams evaluate the problem as a trend instead of relying on a one-time check.