Vacuum and bloat

Symptom: a table keeps growing though rows are deleted, queries slow down over time, or autovacuum runs constantly without finishing the job.

Confirm

select relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) as dead_pct,
       last_autovacuum, last_autoanalyze
from pg_stat_user_tables order by n_dead_tup desc limit 20;

Find what is holding cleanup back

Vacuum cannot remove a dead row that any open transaction might still see. Check all four:

select pid, now() - xact_start as open_for, state from pg_stat_activity
where backend_xmin is not null order by age(backend_xmin) desc limit 5;   -- long transactions

select slot_name, active, xmin, catalog_xmin from pg_replication_slots;    -- abandoned slots
select gid, prepared from pg_prepared_xacts;                                -- forgotten prepared transactions
show hot_standby_feedback;                                                  -- replicas holding the horizon

Fix the blocker first. Tuning autovacuum will not help while something pins old rows.

Tune autovacuum for big tables

The default triggers at 20% of a table changing. On a 100-million-row table that is 20 million dead rows.

alter table big_table set (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.01);

If vacuum starts on time but is slow, raise autovacuum_vacuum_cost_limit (for example 2000). Add workers with autovacuum_max_workers (restart needed) if many tables compete.

Reclaim space

Plain VACUUM makes space reusable but rarely shrinks the file. To return it to the OS, use pg_repack (online) or VACUUM FULL (rewrites the table under an exclusive lock, so it blocks all access).

Prevent

Keep transactions short, and lower fillfactor on heavily updated tables so updates stay on the same page (HOT updates).