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

On Wed, 19 Jun 2002, Tom Lane wrote:

> REINDEX is really intended for disaster recovery, not routine space
> management.

That's nice.  Except the fact Postgres is so bad at maintaining its own
indexes makes us have to *use* it as routine space management.  Having a
200MB index that's still getting bigger, and really should only be 200k,
tells me that there's no better use for it.  Sure, I could wait until
the index takes up my entire disk, and then consider it disaster
recovery, but that's just silly.

Besides that, if REINDEX isn't meant for this, what is?  Nothing?  So if
nothing is meant to fix this, we're just supposed to let this continue
and only use things for their intended purpose?  Sure, writing something
that reads the pg_* tables to get index information and drops/creates
them from scratch is nice, but that would also mean recreating foreign
keys.  Basically everything aside from basic database structure would
have to be abandoned while your script was running.  Or it would have to
be in one huge transaction or something.  Either way, that sounds like a
lot of work I'd have to do to fix something that is inherantly
Postgres's fault.

So, I'll at least continue to use REINDEX.  I'll encourage other people
to use REINDEX, and I'll even tell my dog to use REINDEX, because right
now, that's the only adequate tool we have.

Besides, I'm not talking about the "REINDEX DATABASE blah" command that
redoes the system-table indexes.  That, I agree, is really only
system-recovery, because I can only run it in standalone mode.  But
REINDEX TABLE and REINDEX INDEX do not have that problem, and so I'll
use them.  Bob will use them, Fred will use them, and my dog will use
them until something better comes along, or it's no longer necessary.

So enjoy the vacuumdb script everyone, it's likely all you'll get for a
long time, since not even the developers themselves know how to keep
indexes from growing.  Maybe over the next few weeks, I'll delve into
the code and see if I can't come up with something.  More eyes, right?

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Administrator           |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : www.townnews.com                                             |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+