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 <[EMAIL PROTECTED]> 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>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
