Skip to content

.tip

· one of the small things

Tune autovacuum per table, not globally

By default a vacuum starts once 20 percent of a table has changed. On a 100 million row table that's 20 million dead rows before anything happens, and in the meantime the table doubles in size.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2
);

Use one percent on the hot tables and leave the defaults everywhere else. n_dead_tup in pg_stat_user_tables shows you which tables need it.

postgresqloperations