<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.196 2009/02/07 19:27:25 momjian Exp $ -->

<row>

<entry><link linkend="catalog-pg-autovacuum"><structname>pg_autovacuum</structname></link></entry>

<entry>per-relation autovacuum configuration parameters</entry>

</row>

<sect1 id="catalog-pg-autovacuum">

<title><structname>pg_autovacuum</structname></title>

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

<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 the autovacuum daemon.

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. For more information

about the autovacuum daemon, see <xref linkend="autovacuum">.

</para>

<note>

<para>

It is likely that <structname>pg_autovacuum</structname> will disappear

in a future release, with the information instead being kept in

<structname>pg_class</>.<structfield>reloptions</> entries.

</para>

</note>

<table>

<title><structname>pg_autovacuum</> Columns</title>

<tgroup cols="4">

<thead>

<row>

<entry>Name</entry>

<entry>Type</entry>

<entry>References</entry>

<entry>Description</entry>

</row>

</thead>

<tbody>

<row>

<entry><structfield>vacrelid</structfield></entry>

<entry><type>oid</type></entry>

<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>

<entry>The table this entry is for</entry>

</row>

<row>

<entry><structfield>enabled</structfield></entry>

<entry><type>bool</type></entry>

<entry></entry>

<entry>If false, this table will not be autovacuumed, except

to prevent transaction ID wraparound</entry>

</row>

<row>

<entry><structfield>vac_base_thresh</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Minimum number of modified tuples before vacuum</entry>

</row>

<row>

<entry><structfield>vac_scale_factor</structfield></entry>

<entry><type>float4</type></entry>

<entry></entry>

<entry>Multiplier for <structfield>reltuples</> to add to

<structfield>vac_base_thresh</></entry>

</row>

<row>

<entry><structfield>anl_base_thresh</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Minimum number of modified tuples before analyze</entry>

</row>

<row>

<entry><structfield>anl_scale_factor</structfield></entry>

<entry><type>float4</type></entry>

<entry></entry>

<entry>Multiplier for <structfield>reltuples</> to add to

<structfield>anl_base_thresh</></entry>

</row>

<row>

<entry><structfield>vac_cost_delay</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Custom <varname>vacuum_cost_delay</> parameter</entry>

</row>

<row>

<entry><structfield>vac_cost_limit</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Custom <varname>vacuum_cost_limit</> parameter</entry>

</row>

<row>

<entry><structfield>freeze_min_age</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Custom <varname>vacuum_freeze_min_age</> parameter</entry>

</row>

<row>

<entry><structfield>freeze_max_age</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Custom <varname>autovacuum_freeze_max_age</> parameter</entry>

</row>

<row>

<entry><structfield>freeze_table_age</structfield></entry>

<entry><type>integer</type></entry>

<entry></entry>

<entry>Custom <varname>vacuum_freeze_table_age</> parameter</entry>

</row>

</tbody>

</tgroup>

</table>

<para>

The autovacuum daemon will initiate a <command>VACUUM</> operation

on a particular table when the number of updated or deleted tuples

exceeds <structfield>vac_base_thresh</structfield> plus

<structfield>vac_scale_factor</structfield> times the number of

live tuples currently estimated to be in the relation.

Similarly, it will initiate an <command>ANALYZE</> operation

when the number of inserted, updated or deleted tuples

exceeds <structfield>anl_base_thresh</structfield> plus

<structfield>anl_scale_factor</structfield> times the number of

live tuples currently estimated to be in the relation.

</para>

<para>

Also, the autovacuum daemon will perform a <command>VACUUM</> operation

to prevent transaction ID wraparound if the table's

<structname>pg_class</>.<structfield>relfrozenxid</> field attains an age

of more than <structfield>freeze_max_age</> transactions, whether the table

has been changed or not, even if

<structname>pg_autovacuum</>.<structfield>enabled</> is set to

<literal>false</> for it. The system will launch autovacuum to perform

such <command>VACUUM</>s even if autovacuum is otherwise disabled.

See <xref linkend="vacuum-for-wraparound"> for more about wraparound

prevention.

</para>

<para>

Any of the numerical fields can contain <literal>-1</> (or indeed

any negative value) to indicate that the system-wide default should

be used for this particular value. Observe that the

<structfield>vac_cost_delay</> variable inherits its default value from the

<xref linkend="guc-autovacuum-vacuum-cost-delay"> configuration parameter,

or from <xref linkend="guc-vacuum-cost-delay"> if the former is set to a

negative value. The same applies to <structfield>vac_cost_limit</>.

Also, autovacuum will ignore attempts to set a per-table

<structfield>freeze_max_age</> larger than the system-wide setting (it can

only be set smaller), and the <structfield>freeze_min_age</> value will be

limited to half the system-wide <xref

linkend="guc-autovacuum-freeze-max-age"> setting. Note that while you

can set <structfield>freeze_max_age</> very small, or even zero, this

is usually unwise since it will force frequent vacuuming.

</para>

</sect1>

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.206 2009/01/16 13:27:23 heikki Exp $ -->

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

can be reduced for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.89 2009/01/16 13:27:23 heikki Exp $ -->

vacuumed. Otherwise, if the number of tuples obsoleted since the last

on a table-by-table basis by making entries in the system catalog

<link

linkend="catalog-pg-autovacuum"><structname>pg_autovacuum</></link>.

If a <structname>pg_autovacuum</structname> row exists for a particular

table, the settings it specifies are applied; otherwise the global

settings are used. See <xref linkend="runtime-config-autovacuum"> for

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

more parameters that can be set for each table in

<structname>pg_autovacuum</structname>.

The first, <structname>pg_autovacuum</>.<structfield>enabled</>,

The next two parameters, 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>),

are used to set table-specific values for the

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

The last two parameters,

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

and

(<structname>pg_autovacuum</structname>.<structfield>freeze_max_age</structfield>),

are used to set table-specific values for

<xref linkend="guc-vacuum-freeze-min-age"> and

<xref linkend="guc-autovacuum-freeze-max-age"> respectively.

<para>

If any of the values in <structname>pg_autovacuum</structname>

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

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

corresponding values from <filename>postgresql.conf</filename> are used.

</para>

<para>

There is not currently any support for making

<structname>pg_autovacuum</structname> entries, except by doing

manual <command>INSERT</>s into the catalog. This feature will be

improved in future releases, and it is likely that the catalog

definition will change.

</para>

<caution>

<para>

The contents of the <structname>pg_autovacuum</structname> system

catalog are currently not saved in database dumps created by the

tools <application>pg_dump</> and <application>pg_dumpall</>. If

you want to preserve them across a dump/reload cycle, make sure

you dump the catalog manually.

</para>

</caution>