6 / 9 ACID, Isolation Levels & MVCC

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?

A

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

B

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

C

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

D

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