Subject: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal

From: Guillaume Cottenceau <[email-masked]>
Date: 15 May 2007 18:43:50 +0200

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.

--- doc/src/sgml/ref/vacuum.sgml	2007-02-01 00:26:04.000000000 +0100
+++ /tmp/vacuum.sgml	2007-05-15 18:32:14.000000000 +0200
@@ -164,8 +164,8 @@
    <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
+    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
--- doc/src/sgml/maintenance.sgml	2007-05-03 17:47:48.000000000 +0200
+++ /tmp/maintenance.sgml	2007-05-15 18:29:29.000000000 +0200
@@ -157,7 +157,8 @@
     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
+    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,12 +169,15 @@
    <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
+    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.
+    heavily-updated tables. However, if some heavily-updated tables
+    have gone too long with infrequent <command>VACUUM</>, you can
+    use <command>VACUUM FULL</> to get performance back (it is much
+    slower to scan a table containing almost only dead rows).
    </para>
 
    <para>

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

---

From: Michael Stone <[email-masked]>
Date: Tue, 15 May 2007 13:44:29 -0400

On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote:
>properly since the database was put in production.

It's also possible to overestimate the benefit of vacuum full, leading 
to people vacuum full'ing almost constantly, then complaining about 
performance due to the associated overhead. I think there have been more 
people on this list whose performance problems were caused by 
unnecessary full vacs than by those whose performance problems were 
caused by insufficient full vacs.

Mike Stone

---

From: Guillaume Cottenceau <[email-masked]>
Date: 16 May 2007 09:41:46 +0200

Michael Stone <mstone+postgres 'at' mathom.us> writes:

> performance due to the associated overhead. I think there have been
> more people on this list whose performance problems were caused by

Come on, I don't suggest to remove several bold warnings about
it, the best one being "Therefore, frequently using VACUUM FULL
can have an extremely negative effect on the performance of
concurrent database queries." My point is to add the few
additional mentions; I don't think the claims that VACUUM FULL
physically compacts the data, and might be useful in case of too
long time with infrequent VACUUM are incorrect, are they?

---

From: "Jim C. Nasby" <[email-masked]>
Date: Wed, 16 May 2007 10:48:09 -0500

> long time with infrequent VACUUM are incorrect, are they?

Unfortunately they are, to a degree. VACUUM FULL can create a
substantial amount of churn in the indexes, resulting in bloated
indexes. So often you have to REINDEX after you VACUUM FULL.

Long term I think we should ditch 'VACUUM FULL' altogether and create a
COMPACT command (it's very easy for users to get confused between
"vacuum all the databases in the cluster" or "vacuum the entire
database" and "VACUUM FULL").
-- 
Jim Nasby                                      [email-masked]
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

---

From: Guillaume Cottenceau <[email-masked]>
Date: 16 May 2007 18:00:19 +0200

> On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote:

[...]

> indexes. So often you have to REINDEX after you VACUUM FULL.

Ok, VACUUM FULL does his job (it physically compacts the data and
might be useful in case of too long time with infrequent VACUUM),
but we are going to not talk about it because we often needs a
REINDEX after it? The natural conclusion would rather be to
document the fact than REINDEX is needed after VACUUM FULL, isn't
it?

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 16 May 2007 12:09:26 -0400

> document the fact than REINDEX is needed after VACUUM FULL, isn't

Maybe, but we should also mention that CLUSTER is a likely faster
workaround.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---

From: "Jim C. Nasby" <[email-masked]>
Date: Wed, 16 May 2007 11:17:02 -0500

> Maybe, but we should also mention that CLUSTER is a likely faster

What this boils down to is that there should probably be a separate
subsection that deals with "Oh noes! My tables are too big!"
-- 
Jim Nasby                                      [email-masked]
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

---

From: Michael Stone <[email-masked]>
Date: Wed, 16 May 2007 12:20:38 -0400

>Maybe, but we should also mention that CLUSTER is a likely faster

Unless, of course, you don't particularly care about the order of the 
items in your table; you might end up wasting vastly more time rewriting 
tables due to unnecessary clustering than for full vacuums on a table 
that doesn't need it.

Mike Stone

---

From: Chris Browne <[email-masked]>
Date: Wed, 16 May 2007 15:34:42 -0400

[email-masked] (Michael Stone) writes:
> Unless, of course, you don't particularly care about the order of
> the items in your table; you might end up wasting vastly more time
> rewriting tables due to unnecessary clustering than for full vacuums
> on a table that doesn't need it.

Actually, this is irrelevant.

If CLUSTER is faster than VACUUM FULL (and if it isn't, in all cases,
it *frequently* is, and probably will be, nearly always, soon), then
it's a faster workaround.
-- 
output = ("cbbrowne" "@" "linuxfinances.info")
http://cbbrowne.com/info/oses.html
"What if you slept?  And what if, in your sleep, you dreamed?
 And what if, in your dream, you went to heaven and there
 plucked a strange and beautiful flower?  And what if, when
 you awoke, you had the flower in your hand?  Ah, what then?"
    --Coleridge

---

From: Michael Stone <[email-masked]>
Date: Wed, 16 May 2007 17:17:16 -0400

>Actually, this is irrelevant.

I think it's perfectly relevant.

>it's a faster workaround.

Cluster reorders the table. If a table doesn't have any dead rows and 
you tell someone to run cluster or vacuum full, the vaccuum basically 
won't do anything and the cluster will reorder the whole table. Cluster 
is great for certain access patterns, but I've been noticing this odd 
tendency lately to treat it like a silver bullet.

Mike Stone

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 16 May 2007 17:25:50 -0400

> tendency lately to treat it like a silver bullet.

Well, it's certainly not a silver bullet; you would use VACUUM (not
full) for most of your needs, and CLUSTER for the rare other cases.  Of
course you would not pick an index at random each time, but rather keep
using the same one, which would supposedly be faster.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: Tom Lane <[email-masked]>
Date: Wed, 16 May 2007 17:30:51 -0400

> tendency lately to treat it like a silver bullet.

Sure, but VACUUM FULL looks even less like a silver bullet.

There's been talk of providing an operation that uses the same
infrastructure as CLUSTER, but doesn't make any attempt to re-order the
table: just seqscan the old heap, transfer still-live tuples into a new
heap, then rebuild indexes from scratch.  This is clearly going to be a
lot faster than a VACUUM FULL under conditions in which the latter would
have to move most of the tuples.  Heikki just fixed one of the major
objections to it (ie, CLUSTER not being MVCC-safe).  The other objection
is that peak transient disk space usage could be much higher than VACUUM
FULL's, but still for a lot of scenarios this'd be better.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 30 May 2007 15:45:37 -0400 (EDT)

Patch attached and applied.  Thanks.

I added a mention of CLUSTER.

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

> might help others in my situation in the future.

[ Attachment, skipping... ]

> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

-- 
  Bruce Momjian  <[email-masked]>          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</> &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>