Re: vacuum

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Guillaume Houssay <ghoussay(at)noos(dot)fr>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: vacuum
Date: 2004-01-08 22:30:58
Message-ID: Pine.LNX.4.33.0401081525120.3612-100000@css120.ihs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, 8 Jan 2004, Guillaume Houssay wrote:

> I am using a 7.3.4.
>
> just simple questions ;=)
>
> 1.when I launch a vacuum verbose analyze on my database. Does it
> re-cretae all the indexes of each table ?

No, vacuuming (harvesting dead tuples from tables) and analyzing
(determining the statistical layout of your data) do not cause any form of
reindexing.

> 2.In addition to the vacuum, do I have to re-index my database using
> REINDEX

Not usually. Generally reindexing is used to fix broken / corrupted
indexes, like the 7.3 docs say. However...

> 3.Is REINDEX only used for created corrupt indexes (as I understood
> from 7.3 documentation) or can it be used to re-index a database or a
> table (as I understood from documentation for 7.4)

somewhere before 7.3 came out an issue with index growth in monotonically
increasing indexes was noted and the two standard ways to fix it were to
either drop / recreate the index or reindex the index. the problem was
that with an index that increased in only one direction, the old pages
that held the older entries were never freed or collapsed down. I had a
1.5 Meg table that was updated every day, and it had an 85 meg index
hanging on the side due to this problem.

The short term solution for postgresql up to and including version 7.3.5
is to reindex indexes that experience this problem. As of 7.4, empty
pages are now collapsed and reclaimed. Note that sparsely populated pages
are NOT collapsed, and therefore there is still the possiblity that you
could be hit by sparsely populated indexes that are larger than optimal,
but that chance is greatly reduced for most, if not nearly all,
situations now in 7.4.

In response to

  • vacuum at 2004-01-08 21:42:40 from Guillaume Houssay

Browse pgsql-general by date

  From Date Subject
Next Message Eric Freeman 2004-01-08 23:13:19 Re: 7.3.3 drop table takes very long time
Previous Message Andrew Bartley 2004-01-08 22:11:48 Re: beginner query help