08. Vacuuming, Bloat & Production Operations¶

Because PostgreSQL does not write rollback logs, dead tuple cleanup and space reclamation are handled by VACUUM. Failing to properly tune vacuum operations leads to table bloat, disk exhaustion, query degradation, and in catastrophic cases, complete database shutdown from Transaction ID wraparound.

1. The Anatomy of Bloat: Dead Tuples in Heap and Index¶

When a tuple is deleted or updated (non-HOT), its old physical version remains in the 8KB page.

8KB Heap Page:
[ ItemId 1 (Live) ] [ ItemId 2 (Dead) ] [ ItemId 3 (Dead) ] [ ItemId 4 (Live) ]
                      ▲                   ▲
                      └──── DEAD TUPLES ──┘
                    (Occupying disk space & memory buffers!)

Standard `VACUUM` vs `VACUUM FULL` vs `pg_repack`¶

Tool	Lock Acquired	Disk Shrinkage	Mechanics
Standard `VACUUM`	`SHARE UPDATE EXCLUSIVE` (Non-blocking)	No (unless pages at physical end of file are completely empty)	Marks dead tuples as unused, frees line pointers, updates Free Space Map (FSM) so future `INSERT` queries reuse the empty space
`VACUUM FULL`	`ACCESS EXCLUSIVE` (Blocks all reads & writes!)	Yes	Rebuilds the entire table from scratch into a new file; requires double disk space
`pg_repack`	Minimal transient lock	Yes	Online table rebuilder using triggers and shadow tables; zero read/write downtime

2. Autovacuum Tuning Equations¶

Autovacuum triggers a cleanup pass when the count of dead tuples exceeds the threshold:

\[\text{Trigger Threshold} = \text{autovacuum\_vacuum\_threshold} + (\text{autovacuum\_vacuum\_scale\_factor} \times N_{\text{live\_tuples}})\]

The Large-Table Hazard¶

With the default scale_factor = 0.20: * On a table with 1,000 rows, vacuum triggers after 250 dead tuples. * On a table with 100,000,000 rows, vacuum will not trigger until 20,000,000 dead tuples accumulate! This causes hundreds of gigabytes of bloat before autovacuum ever kicks in.

Production Table-Level Tuning¶

Override scale factors on high-churn tables:

-- Aggressive autovacuum settings for high-throughput write tables:
ALTER TABLE high_churn_events SET (
    autovacuum_vacuum_scale_factor = 0.02,     -- Trigger at 2% dead tuples
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_cost_limit = 2000,       -- Allow higher I/O budget
    autovacuum_vacuum_cost_delay = 2           -- Reduce sleep between I/O cycles
);

Global Cost-Based Vacuum Delay¶

Autovacuum throttles itself to prevent saturating disk bandwidth: * Every page read from shared_buffers costs vacuum_cost_page_hit = 1. * Every page read from disk costs vacuum_cost_page_miss = 2. * Every dirty page written to disk costs vacuum_cost_page_dirty = 20. * Once accumulated cost hits autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay (default 2ms).

Production Recommendation: On NVMe SSDs, increase autovacuum_vacuum_cost_limit from the default 200 to 2000 or 5000 to ensure workers keep up with write rates.

3. Transaction ID (XID) Wraparound Catastrophe¶

Because XIDs are 32-bit integers, after \(2^{31} \approx 2.14\) billion transactions, the circular number space wraps around. Without intervention, past transactions would suddenly appear to be in the future, rendering all historical data invisible.

The Freezing Mechanism¶

PostgreSQL prevents this catastrophe by Freezing old tuples: * The special FrozenTransactionId (\(XID = 2\)) is defined to be older than every normal XID. * When autovacuum scans a page older than vacuum_freeze_min_age, it sets the HEAP_XMIN_FROZEN flag in t_infomask (or assigns XID 2). * Once frozen, the tuple is permanently visible to all current and future transactions forever.

The Emergency Wraparound Mode¶

If a table approaches wraparound limit (autovacuum_freeze_max_age = 200,000,000): 1. PostgreSQL spawns aggressive autovacuum workers that ignore cost delay throttling (autovacuum_vacuum_cost_delay = 0). 2. If the database reaches \(2^{31} - 3,000,000\), PostgreSQL shuts down completely and refuses all connections to prevent data loss, requiring manual single-user mode VACUUM FREEZE recovery!

-- Monitor database distance to catastrophic wraparound:
SELECT 
    datname, 
    age(datfrozenxid) AS xid_age,
    2147483648 - age(datfrozenxid) AS xids_remaining_until_shutdown,
    round(100.0 * age(datfrozenxid) / 2147483648, 2) AS pct_towards_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

4. Connection Pooling Architecture: PgBouncer¶

Because each PostgreSQL connection spawns a dedicated operating system process consuming 5-15 MB of RAM and contending for process scheduler slots, connecting thousands of client threads directly to PostgreSQL degrades throughput.

Application Instances (e.g. 5,000 HTTP threads)
       │
       ▼
   PgBouncer (Lightweight event-driven multiplexer / epoll)
       │ (Maintains persistent pool of ~50-100 real connections)
       ▼
   PostgreSQL Server (Zero context-switch starvation!)

PgBouncer Pooling Modes¶

Session Pooling: Connection assigned to client for entire lifespan of TCP session. Minimal concurrency gains; safe for all features.
Transaction Pooling (Production Standard): Connection returned to pool the moment COMMIT or ROLLBACK executes. 1,000 microservice threads can be served by 50 server connections.
Caveat: Session-level state (SET search_path, temporary tables, prepared statements without protocol-level naming) cannot be used.
Statement Pooling: Connection returned to pool after each SQL statement. Transactions with multiple queries are not supported.