Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 3D9909FBF5E
	for <pgsql-docs-postgresql.org@postgresql.org>;
	Wed, 30 May 2007 16:45:50 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 66598-03 for <pgsql-docs-postgresql.org@postgresql.org>;
	Wed, 30 May 2007 16:45:43 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from momjian.us (momjian.us [70.90.9.53])
	by postgresql.org (Postfix) with ESMTP id C3CFF9FBE9D
	for <pgsql-docs@postgresql.org>; Wed, 30 May 2007 16:45:43 -0300 (ADT)
Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id
 l4UJjbp01401;
	Wed, 30 May 2007 15:45:37 -0400 (EDT)
From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200705301945.l4UJjbp01401@momjian.us>
Subject: Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc
	improvement proposal
In-Reply-To: <87lkfqavg9.fsf@meuh.mnc.lan>
To: Guillaume Cottenceau <gc@mnc.ch>
Date: Wed, 30 May 2007 15:45:37 -0400 (EDT)
CC: PostgreSQL-documentation <pgsql-docs@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL123]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1180554337-19139-1_"
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/60
X-Sequence-Number: 4289


--ELM1180554337-19139-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


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

--ELM1180554337-19139-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/diff"

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</> &mdash; 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</> &mdash; 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>

--ELM1180554337-19139-1_--