<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.83 2007/10/06 17:45:49 alvherre Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.84 2007/10/07 01:16:42 alvherre Exp $ -->

<chapter id="maintenance">

<title>Routine Database Maintenance Tasks</title>

</indexterm>

<para>

<productname>PostgreSQL</productname>'s <command>VACUUM</> (<xref

linkend="sql-vacuum">) command has to run on a regular basis for several

reasons:

<productname>PostgreSQL</productname>'s

<xreflinkend="sql-vacuum" endterm="sql-vacuum-title"> command has to run on

a regular basis for severalreasons:

<orderedlist>

<listitem>

</para>

<para>

Fortunately, autovacuum (<xref linkend="autovacuum">) monitors table

Fortunately, <xref linkend="autovacuum" endterm="autovacuum-title">

monitors table

activity and performs <command>VACUUM</command>s when necessary.

Autovacuum works dynamically so it is often better

administration-scheduled vacuuming.

</para>

<para>

Fortunately, autovacuum (<xref linkend="autovacuum">) monitors table

Fortunately, <xref linkend="autovacuum" endterm="autovacuum-title">

monitors table

activity and performs <command>VACUUM</command>s when necessary. This

eliminates the need for administrators to worry about disk space

recovery in all but the most unusual cases.

The <productname>PostgreSQL</productname> query planner relies on

statistical information about the contents of tables in order to

generate good plans for queries. These statistics are gathered by

the <command>ANALYZE</> command, which can be invoked by itself or

the <xref linkend="sql-analyze" endterm="sql-analyze-title"> command,

which can be invoked by itself or

as an optional step in <command>VACUUM</>. It is important to have

reasonably accurate statistics, otherwise poor choices of plans might

degrade database performance.

</tip>

<para>

Fortunately, autovacuum (<xref linkend="autovacuum">) monitors table

Fortunately, <xref linkend="autovacuum" endterm="autovacuum-title">

monitors table

activity and performs <command>ANALYZE</command>s when necessary. This

eliminates the need for administrators to manually schedule

<command>ANALYZE</command>.

when it was last vacuumed.

If it were to go unvacuumed for longer than that,

data loss could result. To ensure that this does not

happen,the <firstterm>autovacuum</> facility described in

<xref linkend="autovacuum">is invoked on any table

happen,<xref linkend="autovacuum" endterm="autovacuum-title">

is invoked on any table

that might contain XIDs older than the age specified by the

configuration parameter

<xref linkend="guc-autovacuum-freeze-max-age">. (This will happen

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/analyze.sgml,v 1.22 2007/05/15 19:13:55 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/analyze.sgml,v 1.23 2007/10/07 01:16:42 alvherre Exp $

PostgreSQL documentation

-->

<title>Notes</title>

<para>

It is a good idea to run <command>ANALYZE</command> periodically, or

In the default <productname>PostgreSQL</productname> configuration,

<xref linkend="autovacuum" endterm="autovacuum-title">

takes care of automatic analyzing of tables when they are first loaded

with data, and as they change throughout regular operation.

When autovacuum is disabled,

it is a good idea to run <command>ANALYZE</command> periodically, or

just after making major changes in the contents of a table. Accurate

statistics will help the planner to choose the most appropriate query

plan, and thereby improve the speed of query processing. A common

</para>

<para>

Unlike <command>VACUUM FULL</command>,<command>ANALYZE</command>

<command>ANALYZE</command>

requires only a read lock on the target table, so it can run in

parallel with other activity on the table.

</para>

There is no <command>ANALYZE</command> statement in the SQL standard.

</para>

</refsect1>

<refsect1>

<title>See Also</title>

<simplelist type="inline">

<member><xref linkend="sql-vacuum" endterm="sql-vacuum-title"></member>

<member><xref linkend="app-vacuumdb" endterm="app-vacuumdb-title"></member>

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

<member><xref linkend="autovacuum" endterm="autovacuum-title"></member>

</simplelist>

</refsect1>

</refentry>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.48 2007/05/30 19:45:01 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.49 2007/10/07 01:16:42 alvherre Exp $

PostgreSQL documentation

-->

<simplelist type="inline">

<member><xref linkend="app-vacuumdb" endterm="app-vacuumdb-title"></member>

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

<member><xref linkend="autovacuum" endterm="autovacuum-title"></member>

</simplelist>

</refsect1>

</refentry>