Re: I don't want to back up index files

On Wed, 2009-03-11 at 16:57 -0700, Glen Parker wrote:
> Scott Marlowe wrote:

> That's two people now who have called the idea "silly" without even a
> hint of a supporting argument. Why would it be "silly" to improve the
> performance of a highly valuable tool set without compromising its
> utility? Am I missing something here? That's certainly possible, but
> the idea didn't just hatch last night; I've put enough thought into this
> to have reason to believe it's more than just "silly".

O.k. a couple of things:

1. You could put all your indexes into a table space, this would allow
you to "try" different things with the indexes.

2. Even though my peer Alvaro doesn't think the idea is silly, I still
do and here is why. If you can invalidate the indexes you will have to
reindex (or recreate) to make them valid (as you mentioned). That is an
exclusive lock.

If your database has any level of concurrency the cost to
recreate/reindex those indexes right when you are attempting to get your
standby into production is going to be very high. Basically you are
trading 25% hard disk space for a longer, possibly excessively longer
outage. Hard disk space is so darn cheap that it doesn't seem to make
any sense.

Creating indexes concurrently is also out because while you are creating
those indexes your performance will tank because everything is
sequential scanning and there is a possibility that the concurrent
creation will fail.

Sincerely,

Joshua D. Drake

>
> -Glen
>
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997