Re: [HACKERS] Still recommending daily vacuum...

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>