The Six Hour Session

A PostgreSQL table holds a steady two million live rows, yet its on-disk size keeps climbing and sequential scans get slower every day. Monitoring shows one connection sitting idle in transaction for six hours. How are the two facts connected?

Autovacuum rewrites the whole table on every pass, and the rewrite doubles the file until the session commits

That session's snapshot may still need old row versions, so vacuum cannot reclaim dead tuples and the table bloats

That session holds an exclusive lock on the table, so autovacuum skips it and no space is ever reclaimed

The open transaction's unflushed WAL records are buffered inside the table's own files, inflating its size