A table with a steady 10 million rows keeps growing on disk, and queries on it get slower every week. Autovacuum runs, but nothing improves. What do you look for?

Dead rows that vacuum is not allowed to remove. In PostgreSQL an UPDATE or DELETE leaves the old version of the row in the table, and vacuum can only clear it once no open transaction could still need to see it. One session that opened a transaction and never finished it, often hours or days ago, holds that cleanup back for the whole database. The table fills with dead rows, scans read more pages, and queries slow down.

1. Confirm it is dead rows

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

10 million live rows and 40 million dead ones, with a recent last_autovacuum, means vacuum runs but cannot remove them.

2. Find what is holding them

SELECT pid, usename, application_name, state, xact_start, backend_xmin, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

The usual suspects:

• idle in transaction sessions: an application or a notebook ran BEGIN, did some work and stopped without COMMIT. Connection pools and BI tools left open are common sources.
• Very long queries, such as a reporting extract that runs for hours.
• Abandoned replication slots or prepared transactions, which hold back cleanup in the same way. Check pg_replication_slots and pg_prepared_xacts if the sessions look clean.

3. Fix now

• End the offending session (pg_terminate_backend(pid)) once you know what it is.
• Let autovacuum run, or run VACUUM (VERBOSE) table and check that the dead rows are removed.
• Plain VACUUM makes the space reusable but rarely shrinks the file. If the table is badly bloated and you need the disk back, VACUUM FULL rewrites it, but it holds an ACCESS EXCLUSIVE lock that blocks even reads, so plan a maintenance window.

4. Prevent it

• Set idle_in_transaction_session_timeout (for example 10 minutes) for application roles, so forgotten transactions are ended automatically.
• Alert on transactions older than an hour and on dead rows as a share of live rows per table.
• Keep transactions short in application code, and run long analytical extracts on a replica rather than the primary.
• For very update-heavy tables, make autovacuum run more often on that table, but only after the blocker is gone.

What interviewers want: MVCC dead rows and why vacuum cannot remove them while an old transaction is open, finding the blocker with pg_stat_activity, the idle-in-transaction timeout, and knowing that VACUUM and VACUUM FULL do different things.