Is regular vacuuming with autovacuum needed?

From: Peter Koczan <pjkoczan(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Is regular vacuuming with autovacuum needed?
Date: 2010-08-16 18:08:31
Message-ID: AANLkTik63hP0v1wpstYJptr_ixiHaMjCF3tNpK+5XOZW@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I have an database server that is experiencing some lock contention
and deadlock. It's infrequent, maybe once every two months, but
time-consuming to deal with.

The issue was that a routine VACUUM process (vacuumdb -az, called
nightly via cron) was locking a table and wasn't completing. This
server is also running autovacuum. This wasn't the source of the
deadlock, but I'm wondering if regular vacuuming is necessary or even
desirable with autovacuum running. Is there any reason for me to not
disable the vacuum cron job and just tweak autovacuum parameters (or
even just to leave the defaults)?

Details:
PostgreSQL version 8.4.4
Default vacuum/autovacuum parameters in postgresql.conf:
#vacuum_cost_delay = 0ms
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200
...
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1
...
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000

Cheers,
Peter

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Scott Marlowe 2010-08-16 18:34:04 Re: Is regular vacuuming with autovacuum needed?
Previous Message Kevin Grittner 2010-08-16 16:39:27 Re: trigger AFTER INSERT