Skip site navigation (1) Skip section navigation (2)

Configuring autovacuum for the first time...

From: "Nagle, Gail A \(US SSA\)" <gail(dot)nagle(at)baesystems(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Configuring autovacuum for the first time...
Date: 2009-06-24 00:48:03
Message-ID: 7naet7$ (view raw or whole thread)
Lists: pgsql-novice


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

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,



postgresql.conf contents:





# - 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 = 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

autovacuum_naptime = 1min                  # time between autovacuum

autovacuum_vacuum_threshold = 50      # min number of row updates before

                                                            # vacuum

autovacuum_analyze_threshold = 50      # min number of row updates

                                                            # analyze

autovacuum_vacuum_scale_factor = 0.2            # fraction of table size
before vacuum

autovacuum_analyze_scale_factor = 0.1 # fraction of table size before

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


autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for

autovacuum, -1 means use





pgsql-novice by date

Next:From: Bhushan VermaDate: 2009-06-24 05:59:12
Subject: psql: FATAL: the database system is in recovery mode
Previous:From: Alexandru MaximciucDate: 2009-06-23 21:26:22
Subject: table's last update time

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group