Database Reference
In-Depth Information
VACUUM
performs a range of clean up activities, some of them too complex to describe
without a whole sideline into internals.
VACUUM
has been heavily optimized over a 10-year
period to take the minimum required lock levels on tables, and execute in the most efficient
manner possible, skipping all unnecessary work, and using L2 cache CPU optimizations when
work is required.
Many experienced PostgreSQL DBAs will prefer to execute their own VACUUMs, though
autovacuum now provides a fine degree of control, and that can save you much time by
enabling and controlling it. Using both manual and automatic vacuuming gives you both
control and a safety net.
Controlling automatic database
maintenance
Autovacuum is enabled by default in PostgreSQL 9.0, and mostly does a great job of maintaining
your PostgreSQL database. I say mostly because it doesn't know everything you do about the
database, such as when would be the best time to perform maintenance actions.
Getting ready
Exercising control requires some thinking about what you actually want:
F
Which are the best times of day to do things? When are system resources
more available?
F
Which days are quiet, and are which not?
F
Which tables are critical to the application, and which are not?
How to do it...
The first thing to do is to make sure autovacuum is switched on. You must have both the
following parameters enabled in your
postgresql.conf
:
F
autovacuum
=
on
F
track_counts
=
on
PostgreSQL controls autovacuum with 35 individually tunable parameters. That provides a
wide range of options, though can be a little daunting.
The following parameters can be set in
postgresql.conf
:
F
autovacuum
F
autovacuum_analyze_scale_factor
F
autovacuum_analyze_threshold
