<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.113 2005/09/08 20:07:41 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.114 2005/09/13 01:51:18 alvherre Exp $

-->

<chapter id="catalogs">

<primary>pg_autovacuum</primary>

</indexterm>

<indexterm zone="catalog-pg-autovacuum">

<primary>autovacuum</primary>

<secondary>table-specific configuration</secondary>

</indexterm>

<para>

The catalog <structname>pg_autovacuum</structname> stores optional

per-relation configuration parameters for <quote>autovacuum</>.

per-relation configuration parameters for <xref linkend="autovacuum" endterm="autovacuum-title">.

If there is an entry here for a particular relation, the given

parameters will be used for autovacuuming that table. If no entry

is present, the system-wide defaults will be used.

<!--

$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.15 2005/09/12 23:09:05 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.16 2005/09/13 01:51:18 alvherre Exp $

-->

<chapter Id="runtime-config">

<title>Run-time Configuration</title>

<sect1 id="runtime-config-autovacuum">

<title>Automatic Vacuuming</title>

<indexterm>

<primary>autovacuum</primary>

<secondary>global configuration parameters</secondary>

</indexterm>

<para>

Beginning in <productname>PostgreSQL</> 8.1, there is an optional server

process called the <firstterm>autovacuum daemon</>, whose purpose is

to automate the issuance of periodic <command>VACUUM</> and

<command>ANALYZE</> commands. When enabled, the autovacuum daemon

runs periodically and checks for tables that have had a large number

of updated or deleted tuples. This check uses the row-level statistics

collection facility; therefore, the autovacuum daemon cannot be used

unless <xref linkend="guc-stats-start-collector"> and

<xref linkend="guc-stats-row-level"> are set TRUE. Also, it's

important to allow a slot for the autovacuum process when choosing

the value of <xref linkend="guc-superuser-reserved-connections">.

These settings control the default behavior for the <firstterm>autovacuum

daemon</firstterm>. Please refer to <xref linkend="autovacuum"> for

more information.

</para>

<variablelist>

The default is 1000.

This option can only be set at server start or in the

<filename>postgresql.conf</filename> file.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

</para>

</listitem>

</varlistentry>

The default is 500.

This option can only be set at server start or in the

<filename>postgresql.conf</filename> file.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

</para>

</listitem>

</varlistentry>

The default is 0.4.

This option can only be set at server start or in the

<filename>postgresql.conf</filename> file.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

</para>

</listitem>

</varlistentry>

The default is 0.2.

This option can only be set at server start or in the

<filename>postgresql.conf</filename> file.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

</para>

</listitem>

</varlistentry>

<!--

$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.45 2005/06/22 21:14:28 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.46 2005/09/13 01:51:18 alvherre Exp $

-->

<chapter id="maintenance">

<xref linkend="runtime-config-resource-vacuum-cost">.

</para>

<para>

An automated mechanism for performing the necessary <command>VACUUM</>

operations has been added in <productname>PostgreSQL</productname> 8.1.

See <xref linkend="autovacuum">.

</para>

<sect2 id="vacuum-for-space-recovery">

<title>Recovering disk space</title>

the program <filename>vacuumdb</> may be helpful.

</para>

<tip>

<para>

The <filename>contrib/pg_autovacuum</> program can be useful for

automating high-frequency vacuuming operations.

</para>

</tip>

<para>

<command>VACUUM FULL</> is recommended for cases where you know

you have deleted the majority of rows in a table, so that the

</para>

</warning>

</sect2>

<sect2 id="autovacuum">

<title id="autovacuum-title">The auto-vacuum daemon</title>

<indexterm>

<primary>autovacuum</primary>

<secondary>general information</secondary>

</indexterm>

<para>

Beginning in <productname>PostgreSQL </productname> 8.1, there is a

separate optional server process called the <firstterm>autovacuum

daemon</firstterm>, whose purpose is to automate the issuance of

<command>VACUUM</command> and <command>ANALYZE </command> commands.

When enabled, the autovacuum daemon runs periodically and checks for

tables that have had a large number of inserted, updated or deleted

tuples. These checks use the row-level statistics collection facility;

therefore, the autovacuum daemon cannot be used unless <xref

linkend="guc-stats-start-collector"> and <xref

linkend="guc-stats-row-level"> are set <literal>true</literal>. Also, it's

important to allow a slot for the autovacuum process when choosing the

value of <xref linkend="guc-superuser-reserved-connections">.

</para>

<para>

The autovacuum daemon, when enabled, runs every <xref

linkend="guc-autovacuum-naptime"> seconds and determines which database

to process. Any database which is close to transaction ID wraparound

is immediately processed. In this case, autovacuum issues a

database-wide <command>VACUUM</command> call, or <command>VACUUM

FREEZE</command> if it's a template database, and then terminates. If

no database fulfills this criterion, the one that was least recently

processed by autovacuum itself is chosen. In this mode, each table in

the database is checked for new and obsolete tuples, according to the

applicable autovacuum parameters. If a <link linkend="catalog-pg-autovacuum">

<structname>pg_autovacuum</structname></link> tuple is found for this

table, these settings are applied; otherwise the global values in

<filename>postgresql.conf</filename> are used. See <xref linkend="runtime-config-autovacuum">

for more details on the global settings.

</para>

<para>

For each table, two conditions are used to determine which operation to

apply. If the number of obsolete tuples since the last

<command>VACUUM</command> exceeds the <quote>vacuum threshold</quote>, the

table is vacuumed and analyzed. The vacuum threshold is defined as:

<programlisting>

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

</programlisting>

where the vacuum base threshold is

<structname>pg_autovacuum</structname>.<structfield>vac_base_thresh</structfield>,

the vacuum scale factor is

<structname>pg_autovacuum</structname>.<structfield>vac_scale_factor</structfield>

and the number of tuples is

<structname>pg_class</structname>.<structfield>reltuples</structfield>.

The number of obsolete tuples is taken from the statistics

collector, which is a semi-accurate count updated by each

<command>UPDATE</command> and <command>DELETE</command> operation. (It

is only semi-accurate because some information may be lost under heavy

load.) For analyze, a similar condition is used: the threshold, calculated

by an equivalent equation to that above, is compared to the number of

new tuples, that is, those created by the <command>INSERT</command> and

<command>COPY</command> commands.

</para>

<para>

Note that if any of the values in <structname>pg_autovacuum</structname>

is set to a negative number, or if a tuple is not present at all in

<structname>pg_autovacuum</structname> for any particular table, the

equivalent value from <filename>postgresql.conf</filename> is used.

</para>

<para>

Besides the base threshold values and scale factors, there are three

parameters that can be set for each table in <structname>pg_autovacuum</structname>:

the vacuum cost delay

(<structname>pg_autovacuum</structname>.<structfield>vac_cost_delay</structfield>)

and the vacuum cost limit

(<structname>pg_autovacuum</structname>.<structfield>vac_cost_limit</structfield>).

They are used to set table-specific values for the

<xref linkend="runtime-config-resource-vacuum-cost" endterm="runtime-config-resource-vacuum-cost-title">

feature. The above note about negative values also applies here, but

also note that if the <filename>postgresql.conf</filename> variables

<varname>autovacuum_vacuum_cost_limit</varname> and

<varname>autovacuum_vacuum_cost_delay</varname> are also set to negative

values, the <varname>vacuum_cost_limit</varname> and

<varname>vacuum_cost_delay</varname> values will be used instead.

The other parameter, <structname>pg_autovacuum</>.<structfield>enabled</>,

can be used to instruct the autovacuum daemon to skip any particular table

by setting it to <literal>false</literal>.

</para>

</sect2>

</sect1>