Re: db grows and grows - Mailing list pgsql-general

On Thu, Jun 20, 2002 at 05:33:28PM -0400, terry@greatgulfhomes.com wrote:
> 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.

My script-that-makes-the-script used pg_dump to get the appropriate
statement. As someone pointed out, it is also stored in plain text within
the database.

> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Robert Treat
> > Sent: Thursday, June 20, 2002 3:58 PM
> > To: Bruce Momjian
> > Cc: pgsql-general@postgresql.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@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.