18

How does one enable autovacuum in PostgreSQL? I understand the purpose, I just can't find a simple answer regarding how to enable it.

JTW
  • 3,546
  • 8
  • 35
  • 49

2 Answers2

39

Autovacuum is on by default. For small databases just do nothing and everything will work fine. To confirm, check

SHOW autovacuum;

in psql. It should report on.

Large and busy databases sometimes require tuning to make autovacuum run more often, or focus more on busy queue tables. See the manual for details on tuning autovacuum.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    I checked the status as suggested, and in fact the command returned "on". Maybe I'm not understanding how autovacuum works though; I keep getting prompts while using pgAdmin to run VACUUM... I would have assumed that *auto*vacuum *automatically* ran VACUUM on tables periodically. – JTW Oct 29 '15 at 00:30
  • 1
    @woogy It does. Maybe pgadmin thinks particular tables need more? There are also situations with some workloads where autovacuum can't keep up at default settings. Mostly busy queue tables. It would help if y8u showed the messages. .. – Craig Ringer Oct 29 '15 at 00:36
1

You can start the vacuum manually also. By running psql command vacuum full analyze verbose. It will take some time.

Tobias
  • 7,238
  • 10
  • 46
  • 77
Neeraj Bansal
  • 380
  • 7
  • 23
  • 10
    Beware that this exclusively locks the tables and therefore might not be recommended for large productive databases. `autovacuum` in contrast runs throttled. – Peopleware Dec 16 '19 at 10:40