Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal - Mailing list pgsql-docs

Patch attached and applied.  Thanks.

I added a mention of CLUSTER.

---------------------------------------------------------------------------


Guillaume Cottenceau wrote:
> Dear all,
>
> After some time spent better understanding how the VACUUM process
> works, what problems we had in production and how to improve our
> maintenance policy[1], I've come up with a little documentation
> patch - basically, I think the documentation under estimates (or
> sometimes misses) the benefit of VACUUM FULL for scans, and the
> needs of VACUUM FULL if the routine VACUUM hasn't been done
> properly since the database was put in production. Find the patch
> against snapshot attached (text not filled, to ease reading). It
> might help others in my situation in the future.
>

[ Attachment, skipping... ]

>
> Ref:
> [1] http://archives.postgresql.org/pgsql-performance/2006-08/msg00419.php
>     http://archives.postgresql.org/pgsql-performance/2007-05/msg00112.php
>
> --
> Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
> Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
  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.74
diff -c -c -r1.74 maintenance.sgml
*** doc/src/sgml/maintenance.sgml    15 May 2007 15:52:40 -0000    1.74
--- doc/src/sgml/maintenance.sgml    30 May 2007 19:39:44 -0000
***************
*** 157,163 ****
      command. This uses a more aggressive algorithm for reclaiming the
      space consumed by dead row versions. Any space that is freed by
      <command>VACUUM FULL</command> is immediately returned to the
!     operating system. Unfortunately, this variant of the
      <command>VACUUM</command> command acquires an exclusive lock on
      each table while <command>VACUUM FULL</command> is processing
      it. Therefore, frequently using <command>VACUUM FULL</command> can
--- 157,164 ----
      command. This uses a more aggressive algorithm for reclaiming the
      space consumed by dead row versions. Any space that is freed by
      <command>VACUUM FULL</command> is immediately returned to the
!     operating system, and the table data is physically compacted on
!     the disk. Unfortunately, this variant of the
      <command>VACUUM</command> command acquires an exclusive lock on
      each table while <command>VACUUM FULL</command> is processing
      it. Therefore, frequently using <command>VACUUM FULL</command> can
***************
*** 168,179 ****
     <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.
     </para>

     <para>
--- 169,184 ----
     <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>
Index: doc/src/sgml/ref/vacuum.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/vacuum.sgml,v
retrieving revision 1.47
diff -c -c -r1.47 vacuum.sgml
*** doc/src/sgml/ref/vacuum.sgml    31 Jan 2007 23:26:04 -0000    1.47
--- doc/src/sgml/ref/vacuum.sgml    30 May 2007 19:39:44 -0000
***************
*** 164,173 ****
     <para>
      The <option>FULL</option> option is not recommended for routine use,
      but might be useful in special cases.  An example is when you have deleted
!     most of the rows in a table and would like the table to physically shrink
!     to occupy less disk space.  <command>VACUUM FULL</command> will usually
!     shrink the table more than a plain <command>VACUUM</command> would.
!     The <option>FULL</option> option does not shrink indexes; a periodic
      <command>REINDEX</> is still recommended.  In fact, it is often faster
      to drop all indexes, <command>VACUUM FULL</>, and recreate the indexes.
     </para>
--- 164,174 ----
     <para>
      The <option>FULL</option> option is not recommended for routine use,
      but might be useful in special cases.  An example is when you have deleted
!     or updated most of the rows in a table and would like the table to
!     physically shrink to occupy less disk space and allow faster table
!     scans. <command>VACUUM FULL</command> will usually shrink the table
!     more than a plain <command>VACUUM</command> would.  The
!     <option>FULL</option> option does not shrink indexes; a periodic
      <command>REINDEX</> is still recommended.  In fact, it is often faster
      to drop all indexes, <command>VACUUM FULL</>, and recreate the indexes.
     </para>