autovacuum

The daemon that removes dead rows and refreshes the planner's statistics, on by default provided track_counts is on too. It does not run continuously, and that is where the intuition fails. It wakes once a minute (autovacuum_naptime) and vacuums a table only when its dead rows pass 50 + 0.2 × the table's row count — autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times that count. On a ten-million-row table: two million dead rows before the first pass. So a large, heavily written table bloats well before autovacuum touches it. The fix is per table, ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02), not server-wide — otherwise every small table is vacuumed constantly, for nothing. And no setting helps while a session sits idle in transaction: a dead row still visible to an older transaction does not go.