Jim C. Nasby wrote:
-- Start of PGP signed section.
> From
> http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html :
>
> "Recommended practice for most sites is to schedule a database-wide
> VACUUM once a day at a low-usage time of day, supplemented by more
> frequent vacuuming of heavily-updated tables if necessary. (Some
> installations with extremely high update rates vacuum their busiest
> tables as often as once every few minutes.) If you have multiple
> databases in a cluster, don't forget to VACUUM each one; the program
> vacuumdb might be helpful."
>
> Do we still want that to be our formal recommendation? ISTM it would be
> more logical to recommend a combination of autovac, daily vacuumdb -a if
> you can afford it and have a quiet period, and frequent manual vacuuming
> of things like web session tables.
>
> I'm happy to come up with a patch, but I figure there should be
> consensus first...
I have applied the following patch to emphasize autovacuum rather than
administrator-scheduled vacuums.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/maintenance.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v
retrieving revision 1.78
diff -c -c -r1.78 maintenance.sgml
*** doc/src/sgml/maintenance.sgml 19 Aug 2007 01:41:24 -0000 1.78
--- doc/src/sgml/maintenance.sgml 13 Sep 2007 23:37:50 -0000
***************
*** 59,66 ****
</indexterm>
<para>
! <productname>PostgreSQL</productname>'s <command>VACUUM</> command
! <emphasis>must</emphasis> be run on a regular basis for several reasons:
<orderedlist>
<listitem>
--- 59,67 ----
</indexterm>
<para>
! <productname>PostgreSQL</productname>'s <command>VACUUM</> (<xref
! linkend="sql-vacuum"> command has to run on a regular basis for several
! reasons:
<orderedlist>
<listitem>
***************
*** 78,91 ****
<firstterm>transaction ID wraparound</>.</simpara>
</listitem>
</orderedlist>
-
- The frequency and scope of the <command>VACUUM</> operations
- performed for each of these reasons will vary depending on the
- needs of each site. Therefore, database administrators must
- understand these issues and develop an appropriate maintenance
- strategy. This section concentrates on explaining the high-level
- issues; for details about command syntax and so on, see the <xref
- linkend="sql-vacuum" endterm="sql-vacuum-title"> reference page.
</para>
<para>
--- 79,84 ----
***************
*** 103,115 ****
</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>
<indexterm zone="vacuum-for-space-recovery">
<primary>disk space</primary>
--- 96,109 ----
</para>
<para>
! Fortunately, autovacuum (<xref linkend="autovacuum">) monitors table
! activity and performs <command>VACUUM</command>s when necessary.
! Autovacuum works dynamically so it is often better
! administration-scheduled vacuuming.
</para>
<sect2 id="vacuum-for-space-recovery">
! <title>Recovering Disk Space</title>
<indexterm zone="vacuum-for-space-recovery">
<primary>disk space</primary>
***************
*** 129,145 ****
</para>
<para>
- Clearly, a table that receives frequent updates or deletes will need
- to be vacuumed more often than tables that are seldom updated. It
- might be useful to set up periodic <application>cron</> tasks that
- <command>VACUUM</command> only selected tables, skipping tables that are known not to
- change often. This is only likely to be helpful if you have both
- large heavily-updated tables and large seldom-updated tables — the
- extra cost of vacuuming a small table isn't enough to be worth
- worrying about.
- </para>
-
- <para>
There are two variants of the <command>VACUUM</command>
command. The first form, known as <quote>lazy vacuum</quote> or
just <command>VACUUM</command>, marks dead data in tables and
--- 123,128 ----
***************
*** 167,196 ****
</para>
<para>
! The standard form of <command>VACUUM</> is best used with the goal
! of maintaining a fairly level steady-state usage of disk space. If
! you need to return disk space to the operating system, you can use
! <command>VACUUM FULL</> — but what's the point of releasing disk
! space that will only have to be allocated again soon? Moderately
! frequent standard <command>VACUUM</> runs are a better approach
! than infrequent <command>VACUUM FULL</> runs for maintaining
! heavily-updated tables. However, if some heavily-updated tables
! have gone too long with infrequent <command>VACUUM</>, you can
use <command>VACUUM FULL</> or <command>CLUSTER</> to get performance
back (it is much slower to scan a table containing almost only dead
rows).
</para>
<para>
! Recommended practice for most sites is to schedule a database-wide
! <command>VACUUM</> once a day at a low-usage time of day,
! supplemented by more frequent vacuuming of heavily-updated tables
! if necessary. (Some installations with extremely high update rates
! vacuum their busiest tables as often as once every few minutes.)
! If you have multiple databases
! in a cluster, don't forget to <command>VACUUM</command> each one;
! the program <xref linkend="app-vacuumdb" endterm="app-vacuumdb-title">
! might be helpful.
</para>
<para>
--- 150,185 ----
</para>
<para>
! Fortunately, autovacuum (<xref linkend="autovacuum">) 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.
! </para>
!
! <para>
! For administrators who want to control <command>VACUUM</command>
! themselves, the standard form of <command>VACUUM</> is best used to
! maintain a steady-state usage of disk space. If you need to return
! disk space to the operating system, you can use <command>VACUUM
! FULL</>, but this is unwise if the table will just grow again in the
! future. Moderately-frequent standard <command>VACUUM</> runs are a
! better approach than infrequent <command>VACUUM FULL</> runs for
! maintaining heavily-updated tables. However, if some heavily-updated
! tables have gone too long with infrequent <command>VACUUM</>, you can
use <command>VACUUM FULL</> or <command>CLUSTER</> to get performance
back (it is much slower to scan a table containing almost only dead
rows).
</para>
<para>
! For those not using autovacuum, one approach is to schedule a
! database-wide <command>VACUUM</> once a day during low-usage period,
! supplemented by more frequent vacuuming of heavily-updated tables if
! necessary. (Some installations with extremely high update rates vacuum
! their busiest tables as often as once every few minutes.) If you have
! multiple databases in a cluster, don't forget to
! <command>VACUUM</command> each one; the program <xref
! linkend="app-vacuumdb" endterm="app-vacuumdb-title"> might be helpful.
</para>
<para>