1

Am newbie in PostgreSQL(Version 9.2) Database development. While looking one of my table a saw an option called autovaccum.
many of my table contains 20000+ rows.For testing purpose I've altered one of that table like below

 ALTER TABLE theTable SET (
 autovacuum_enabled = true
 );
  • So,I wish to know the benefits/advantages/disadvantages(if any) autovacuuming a table ?
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • I'd really like to see an answer here for Azure hosting. Apparently autovacuum is "by table only" there. – mckenzm Jun 21 '22 at 07:37

1 Answers1

1

Autovacuum is enabled by default in current versions of Postgres (and has been for a while). It's generally a good thing to have enabled for performance and other reasons.

Prior to autovacuuming, you would need to explicitly vacuum tables yourself (via cronjobs which executed psql commands to vacuum them, or similar) in order to get rid of dead tuples, etc. Postgres has for a while now managed this for you via autovacuum.

I have in some cases, with tables that have immense churn (i.e. very high rates of insertions and deletions) found it necessary to still explicitly vacuum via a cron in order to keep the dead tuple count low and performance high, because the autovacuum doesn't kick in fast enough, but this is something of a niche case.

More info: http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

khampson
  • 14,700
  • 4
  • 41
  • 43
  • which is better enabling `autovaccum` for each table or `vaccumdb` (`vacuumdb.exe -F -U postgres myDB`) ? –  Aug 08 '14 at 06:38
  • @keet: Depends on your situation, but in most cases autovacuum works fine. 20.000 rows is in my database an almost empty table, most tables (partitions actually) are well above 500 million records. – Frank Heikens Aug 08 '14 at 07:44