Configuring autovacuum for the first time...

Hello,

We are moving from a development environment to a production testing
environment. We are complete novices!

Clearly, we now need to pay more attention to DB maintenance.

We are running PostGreSQL 8.3 on Windows XP, 32 bit. We currently have a
small template database with only one trigger and two tables plus
postgiis.

We expect to have up to 25 replications of the database and 25 users in
the future.

In preparation to use the recommended auto-vacuum capability, we first
checked that there were no entries in the pg_autovacuum table.

We then used pgAdmin III to manually vaccum each existing database. This
reduced file system memory use by about 1.5 MB.

Finally, we stopped the server, edited the postgresql.conf file as shown
below, and restarted the server.

With only one exception, we took the default values in the
postgresql.conf file. That exception was for log_autovacuum_min_duration
which we set to 0.

The log says "autovacuum launcher started".

Should we see a particular process running to be sure we have activated
auto vacuuming correctly by the above actions?

Assuming the default values a reasonable starting place, how will we
know if we need to modify these configuration settings in the future?

Thank you for your answers and advice,

Gail

postgresql.conf contents:

#-----------------------------------------------------------------------
-------

# RUNTIME STATISTICS

#-----------------------------------------------------------------------
-------

# - Query/Index Statistics Collector -

#track_activities = on

track_counts = on

#update_process_title = on

# - Statistics Monitoring -

#log_parser_stats = off

#log_planner_stats = off

#log_executor_stats = off

#log_statement_stats = off

#-----------------------------------------------------------------------
-------

# AUTOVACUUM PARAMETERS

#-----------------------------------------------------------------------
-------

autovacuum = on # Enable
autovacuum subprocess? 'on'

# requires
track_counts to also be on.

log_autovacuum_min_duration = 0 # -1 disables, 0
logs all actions and

# their
durations, > 0 logs only

# actions
running at least that time.

autovacuum_max_workers = 3 # max number of autovacuum
subprocesses

autovacuum_naptime = 1min # time between autovacuum
runs

autovacuum_vacuum_threshold = 50 # min number of row updates before

# vacuum

autovacuum_analyze_threshold = 50 # min number of row updates
before

# analyze

autovacuum_vacuum_scale_factor = 0.2 # fraction of table size
before vacuum

autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze

autovacuum_freeze_max_age = 200000000 # maximum XID age before
forced vacuum

# (change
requires restart)

autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for

#
autovacuum, -1 means use

#
vacuum_cost_delay

autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for

#
autovacuum, -1 means use

#
vacuum_cost_limit