public inbox for [email protected]
help / color / mirror / Atom feed[doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
13+ messages / 7 participants
[nested] [flat]
* [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-15 16:43 Guillaume Cottenceau <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Guillaume Cottenceau @ 2007-05-15 16:43 UTC (permalink / raw)
To: [email protected]
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.
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
Attachments:
[text/x-patch] pg_vacuum.patch (2.5K, 2-pg_vacuum.patch)
download | inline diff:
--- 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</> — 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>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-15 17:44 Michael Stone <[email protected]>
parent: Guillaume Cottenceau <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Michael Stone @ 2007-05-15 17:44 UTC (permalink / raw)
To: [email protected]
On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote:
>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.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 07:41 Guillaume Cottenceau <[email protected]>
parent: Michael Stone <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Guillaume Cottenceau @ 2007-05-16 07:41 UTC (permalink / raw)
To: Michael Stone <[email protected]>; +Cc: [email protected]
Michael Stone <mstone+postgres 'at' mathom.us> writes:
> On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote:
> >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.
>
> 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.
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?
--
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 15:48 Jim C. Nasby <[email protected]>
parent: Guillaume Cottenceau <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Jim C. Nasby @ 2007-05-16 15:48 UTC (permalink / raw)
To: Guillaume Cottenceau <[email protected]>; +Cc: Michael Stone <[email protected]>; [email protected]
On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote:
> Michael Stone <mstone+postgres 'at' mathom.us> writes:
>
> > On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote:
> > >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.
> >
> > 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.
>
> 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?
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 protected]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 16:00 Guillaume Cottenceau <[email protected]>
parent: Jim C. Nasby <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Guillaume Cottenceau @ 2007-05-16 16:00 UTC (permalink / raw)
To: Jim C. Nasby <[email protected]>; +Cc: Michael Stone <[email protected]>; [email protected]
"Jim C. Nasby" <decibel 'at' decibel.org> writes:
> On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote:
[...]
> > 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?
>
> 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.
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?
--
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 16:09 Alvaro Herrera <[email protected]>
parent: Guillaume Cottenceau <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Alvaro Herrera @ 2007-05-16 16:09 UTC (permalink / raw)
To: Guillaume Cottenceau <[email protected]>; +Cc: Jim C. Nasby <[email protected]>; Michael Stone <[email protected]>; [email protected]
Guillaume Cottenceau wrote:
> "Jim C. Nasby" <decibel 'at' decibel.org> writes:
>
> > On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote:
>
> [...]
>
> > > 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?
> >
> > 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.
>
> 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?
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 16:17 Jim C. Nasby <[email protected]>
parent: Alvaro Herrera <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Jim C. Nasby @ 2007-05-16 16:17 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Guillaume Cottenceau <[email protected]>; Michael Stone <[email protected]>; [email protected]
On Wed, May 16, 2007 at 12:09:26PM -0400, Alvaro Herrera wrote:
> Guillaume Cottenceau wrote:
> > "Jim C. Nasby" <decibel 'at' decibel.org> writes:
> >
> > > On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote:
> >
> > [...]
> >
> > > > 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?
> > >
> > > 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.
> >
> > 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?
>
> Maybe, but we should also mention that CLUSTER is a likely faster
> workaround.
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 protected]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 16:20 Michael Stone <[email protected]>
parent: Alvaro Herrera <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Michael Stone @ 2007-05-16 16:20 UTC (permalink / raw)
To: [email protected]
On Wed, May 16, 2007 at 12:09:26PM -0400, Alvaro Herrera wrote:
>Maybe, but we should also mention that CLUSTER is a likely faster
>workaround.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 19:34 Chris Browne <[email protected]>
parent: Michael Stone <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Chris Browne @ 2007-05-16 19:34 UTC (permalink / raw)
To: [email protected]
[email protected] (Michael Stone) writes:
> On Wed, May 16, 2007 at 12:09:26PM -0400, Alvaro Herrera wrote:
>>Maybe, but we should also mention that CLUSTER is a likely faster
>>workaround.
>
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 21:17 Michael Stone <[email protected]>
parent: Chris Browne <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Michael Stone @ 2007-05-16 21:17 UTC (permalink / raw)
To: [email protected]
On Wed, May 16, 2007 at 03:34:42PM -0400, Chris Browne wrote:
>[email protected] (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.
I think it's perfectly relevant.
>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.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 21:25 Alvaro Herrera <[email protected]>
parent: Michael Stone <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2007-05-16 21:25 UTC (permalink / raw)
To: [email protected]
Michael Stone wrote:
> On Wed, May 16, 2007 at 03:34:42PM -0400, Chris Browne wrote:
> >[email protected] (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.
>
> I think it's perfectly relevant.
>
> >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.
>
> 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.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-16 21:30 Tom Lane <[email protected]>
parent: Michael Stone <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2007-05-16 21:30 UTC (permalink / raw)
To: Michael Stone <[email protected]>; +Cc: [email protected]
Michael Stone <[email protected]> writes:
> On Wed, May 16, 2007 at 03:34:42PM -0400, Chris Browne wrote:
>> 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.
> 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.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
@ 2007-05-30 19:45 Bruce Momjian <[email protected]>
parent: Guillaume Cottenceau <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2007-05-30 19:45 UTC (permalink / raw)
To: Guillaume Cottenceau <[email protected]>; +Cc: 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 <[email protected]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
[text/x-diff] /rtmp/diff (4.5K, 2-%2Frtmp%2Fdiff)
download | inline 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</> — 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>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-05-30 19:45 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-15 16:43 [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal Guillaume Cottenceau <[email protected]>
2007-05-15 17:44 ` Michael Stone <[email protected]>
2007-05-16 07:41 ` Guillaume Cottenceau <[email protected]>
2007-05-16 15:48 ` Jim C. Nasby <[email protected]>
2007-05-16 16:00 ` Guillaume Cottenceau <[email protected]>
2007-05-16 16:09 ` Alvaro Herrera <[email protected]>
2007-05-16 16:17 ` Jim C. Nasby <[email protected]>
2007-05-16 16:20 ` Michael Stone <[email protected]>
2007-05-16 19:34 ` Chris Browne <[email protected]>
2007-05-16 21:17 ` Michael Stone <[email protected]>
2007-05-16 21:25 ` Alvaro Herrera <[email protected]>
2007-05-16 21:30 ` Tom Lane <[email protected]>
2007-05-30 19:45 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox