# MVCC, VACUUM and Monitoring — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/m-vacuum

> Old row versions need cleaning.

## Dead tuples, autovacuum, statistics

Because of **MVCC**, updates and deletes leave old row versions (**dead tuples**) that autovacuum later cleans up and makes reusable, while ANALYZE refreshes planner statistics. Long-running transactions block this cleanup, causing table bloat. Monitor with system views: `pg_stat_user_tables` (dead tuples, last autovacuum), `pg_stat_activity` (running and idle-in-transaction sessions), and the `pg_stat_statements` extension (slowest and most frequent queries). Tune autovacuum for very busy tables instead of disabling it.

## Keep it healthy as it grows

Understand MVCC housekeeping, change schemas safely, and review with a checklist.

![Three ideas: vacuum and monitoring, safe migrations, checklist.](assets/figures/postgresql/section-8-map.svg) — Figure 8.1 — Maintenance, migrations and checklist.

## Useful monitoring queries (sketch)

Common queries for a running system; pg_stat_statements must be enabled. Not run here.

```sql
-- tables with the most dead rows
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

-- sessions stuck idle inside a transaction
SELECT pid, state, now() - xact_start AS open_for, left(query, 60)
FROM pg_stat_activity WHERE state = 'idle in transaction';

-- most time-consuming queries
SELECT calls, round(total_exec_time) AS ms, left(query, 60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
```

## Set idle-in-transaction timeouts

idle_in_transaction_session_timeout ends forgotten transactions that would otherwise block vacuum and hold locks.

**Quiz:** What blocks autovacuum from cleaning up dead rows?

- [x] Long-running or idle-in-transaction sessions
- [ ] Too many indexes only
- [ ] Small tables
- [ ] Using numeric columns

*Answer:* Long-running or idle-in-transaction sessions. Old snapshots keep old row versions alive.