Database Reference
In-Depth Information
9
Regular Maintenance
In this chapter, we will cover the following:
F
Controlling automatic database maintenance
F
Avoiding auto freezing and page corruptions
F
Avoiding transaction wraparound
F
Removing old prepared transactions
F
Actions for heavy users of temporary tables
F
Identifying and fixing bloated tables and indexes
F
Maintaining indexes
F
Finding unused indexes
F
Carefully removing unwanted indexes
F
Planning maintenance
Introduction
PostgreSQL prefers regular maintenance, and there is a recipe discussing
planning maintenance.
We recognize that you're here for a reason and are looking for a quick solution to your needs.
You're probably thinking fix me first and I'll plan later. So off we go.
PostgreSQL provides a utility command named
VACUUM
, which is a jokey name for a garbage
collector that sweeps up all the bad things and fixes them, or at least most of them. That's the
single most important thing you need to remember to do—I say single thing because closely
connected to that is the
ANALYZE
command, that collects optimizer statistics. It's possible to
run
VACUUM
and
ANALYZE
as a single joint command
VACUUM
ANALYZE
, plus those actions
are automatically executed for you when appropriate by autovacuum, a special background
process that forms part of the PostgreSQL server.
