Re: db grows and grows

REINDEX causes a lock, which pauses selects on that table, bad thing if your
DB is 24/7, or tables are very large so REINDEX takes a long time to run.

DROP only momentarily (if at all) locks the table.
CREATE does not cause a lock because the index being CREATE'd is "invisible"
until it is fully created.

Down side of DROP/CREATE is your shrinking script has to know the exact code
to recreate the index, which is an issue if your database is evolving,
you'll have to keep updating the script as you add tables/indexes.

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Robert Treat
> Sent: Thursday, June 20, 2002 3:58 PM
> To: Bruce Momjian
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] db grows and grows
>
>
> On Thu, 2002-06-20 at 11:40, Bruce Momjian wrote:
> > Bjoern Metzdorf wrote:
> >
> > Yes, but the problem is that we don't have a plan on how to fix the
> > index growth problem right now, so if people want to prevent index
> > growth, reindex is the only solution.
> >
> > The TODO item says:
> >
> > * Certain indexes will not shrink, e.g. indexes on
> ever-increasing
> > columns and indexes with many duplicate keys
> >
> > but in fact that wording is misleading. >80% of index are on
> > ever-increasing columns, so it isn't really 'certain index' but more
> > accurately 'most indexes'.
> >
> > I am planning to add the reindex script to /contrib,
> document its need
> > in the maintenance docs, and add an FAQ item. If it gets
> fixed in 7.3,
> > great. If not, we will have communicated to users and
> given them the
> > tools then need.
> >
>
> Would you say the reindex command/script is the recommended way of
> dealing with the issue, rather than the create/rename method some have
> suggested? Or maybe the difference is negligible? Does either method
> have an upside in regards to the query planner statistics
> generated via
> routine vacuum analyzes?
>
> Robert Treat
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>