PostgreSQL: How to enable autovacuum?

Question

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

Endodontics

Answer 1

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.

Ransome

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 autovacuum automatically ran VACUUM on tables periodically. – Endodontics

@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. .. – Ransome

Answer 2

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

Puppy

Beware that this exclusively locks the tables and therefore might not be recommended for large productive databases. autovacuum in contrast runs throttled. – Prochoras