Re: db grows and grows

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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+