A reporting transaction open for hours, and n_dead_tup on the hot table climbing all day while autovacuum ran non-stop and changed nothing.
That was one project. PostgreSQL 17 came out yesterday, and the headline work is exactly this plumbing: vacuum got a new memory structure for tracking dead tuples, far less memory and no longer capped the old way, plus better throughput for high-concurrency writes and bulk loads. Maintenance internals. Which is why backend people should care.
Short recap. Postgres never updates a row in place. UPDATE writes a new version, DELETE only marks. The old versions, dead tuples, stay in the table until vacuum reclaims them. When vacuum cannot keep up, tables and indexes bloat, every scan reads pages that are mostly corpses, and your API latency degrades because of a background process you never think about.
You can watch it:
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
On that project the villain was not vacuum. Vacuum cannot reclaim row versions some old transaction might still need to see, so one idle-in-transaction connection silently disabled cleanup for the whole database. Since then idle_in_transaction_session_timeout is a mandatory setting for me, not an option.
What 17 changes in practice: vacuum passes over big tables need dramatically less memory, so they finish instead of thrashing, and the same maintenance budget goes further. After the upgrade check your autovacuum stats again rather than assuming. Bloat you already have does not disappear, you still repack the table once.
A table has a metabolism. Feed it long transactions and it gets sick quietly.
The forgotten report was mine.