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

<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.197 2009/02/09 20:57:59 alvherre Exp $ -->

<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

-->

<entry>authorization identifier membership relationships</entry>

</row>

<row>

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

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

</row>

<row>

<entry><link linkend="catalog-pg-cast"><structname>pg_cast</structname></link></entry>

<entry>casts (data type conversions)</entry>

</sect1>

<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>

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

<title><structname>pg_cast</structname></title>

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

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.207 2009/02/09 20:57:59 alvherre Exp $ -->

<chapter Id="runtime-config">

<title>Server Configuration</title>

The default is 50 tuples.

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by

changing storage parameters.

</para>

</listitem>

</varlistentry>

The default is 50 tuples.

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by

changing storage parameters.

</para>

</listitem>

</varlistentry>

The default is 0.2 (20% of table size).

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by

changing storage parameters.

</para>

</listitem>

</varlistentry>

The default is 0.1 (10% of table size).

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by

changing storage parameters.

</para>

</listitem>

</varlistentry>

autovacuum is otherwise disabled.

The default is 200 million transactions.

This parameter can only be set at server start, but the setting

can be reduced for individual tables by entries in

<structname>pg_autovacuum</>.

can be reduced for individual tables by

changing storage parameters.

For more information see <xref linkend="vacuum-for-wraparound">.

</para>

</listitem>

The default value is 20 milliseconds.

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by

changing storage parameters.

</para>

</listitem>

</varlistentry>

each worker never exceeds the limit on this variable.

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

This setting can be overridden for individual tables by entries in

<structname>pg_autovacuum</>.

This setting can be overridden for individual tables by

changing storage parameters.

</para>

</listitem>

</varlistentry>

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

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.90 2009/02/09 20:57:59 alvherre Exp $ -->

<chapter id="maintenance">

<title>Routine Database Maintenance Tasks</title>

<para>

Tables whose <structfield>relfrozenxid</> value is more than

<varname>autovacuum_freeze_max_age</> transactions old are always

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

vacuumed (this also applies to those tables whose freeze max age has

been modified via storage parameters; see below). Otherwise, if the

number of tuples obsoleted since the last

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

table is vacuumed. The vacuum threshold is defined as:

<programlisting>

<para>

The default thresholds and scale factors are taken from

<filename>postgresql.conf</filename>, but it is possible to override them

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

on a table-by-table basis; see

<xref linkend="sql-createtable-storage-parameters"

endterm="sql-createtable-storage-parameters-title"> for more information.

If asetting

has been changed via storage parameters, that value is used; otherwise the

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

more details on the global settings.

</para>

<para>

Besides the base threshold values and scale factors, there arefive

more parameters that can be set for each tablein

<structname>pg_autovacuum</structname>.

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

Besides the base threshold values and scale factors, there aresix

moreautovacuumparameters that can be set for each tablevia

storage parameters.

The first parameter, <literal>autovacuum_enabled</>,

can be set to <literal>false</literal> to instruct the autovacuum daemon

to skip that particular table entirely. In this case

autovacuum will only touch the table if it must do so

to prevent transaction ID wraparound.

The nexttwo parameters, the vacuum cost delay

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

Anothertwo parameters,

endterm="runtime-config-resource-vacuum-cost-title">

feature.

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.

<literal>autovacuum_freeze_min_age</literal>,

<literal>autovacuum_freeze_max_age</literal> and

<literal>autovacuum_freeze_table_age</literal> are used to set

values for <xref linkend="guc-vacuum-freeze-min-age">,

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

<xref linkend="guc-vacuum-freeze-table-age"> respectively.

</para>

<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>

<para>

When multiple workers are running, the cost limit is

<quote>balanced</quote> among all the running workers, so that the