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