VACUUM - Clean and analyze a Postgres database
SYNOPSIS
VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]
VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
INPUTS
VERBOSE
Prints a detailed vacuum activity report for each
table.
ANALYZE
Updates column statistics used by the optimizer to
determine the most efficient way to execute a
query.
table The name of a specific table to vacuum. Defaults to
all tables.
column The name of a specific column to analyze. Defaults
to all columns.
OUTPUTS
VACUUM The command has been accepted and the database is
being cleaned.
NOTICE: --Relation table--
The report header for table.
NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0;
The analysis for table itself.
NOTICE: Index index: Pages 28;
The analysis for an index on the target table.
DESCRIPTION
VACUUM serves two purposes in Postgres as both a means to
reclaim storage and also a means to collect information
for the optimizer.
VACUUM opens every table in the database, cleans out
records from rolled back transactions, and updates statis
tics in the system catalogs. The statistics maintained
include the number of tuples and number of pages stored in
all tables.
VACUUM ANALYZE collects statistics representing the dis
persion of the data in each column. This information is
valuable when several query execution paths are possible.
database in processing user queries.
NOTES
The open database is the target for VACUUM.
We recommend that active production databases be VACUUM-ed
nightly, in order to remove expired rows. After copying a
large table into Postgres or after deleting a large number
of records, it may be a good idea to issue a VACUUM ANA
LYZE query. This will update the system catalogs with the
results of all recent changes, and allow the Postgres
query optimizer to make better choices in planning user
queries.
USAGE
The following is an example from running VACUUM on a table
in the regression database:
regression=> vacuum verbose analyze onek;
NOTICE: --Relation onek--
NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0;
Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188;
Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74.
Elapsed 0/0 sec.
NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 3000. Elapsed 0/0 sec.
NOTICE: Rel onek: Pages: 98 --> 25; Tuple(s) moved: 1000. Elapsed 0/1 sec.
NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 1000. Elapsed 0/0 sec.
VACUUM
COMPATIBILITY
SQL92
There is no VACUUM statement in SQL92.
Man(1) output converted with
man2html