Re: How to REINDEX in high volume environments?

Shridhar Daithankar wrote:
<snip>
> And what happens when index starts splitting when it grows beyond 1GB in size?

Having an index directory:

i.e. $PGDATA/data/<oid>/indexes/

(that's the kind of thing you mean isn't it?)

Sounds workable, and sounds better than the present approach.

The reason that I was thinking of having a different path per index
would be for high volume situations like this:

/dev/dsk1 : /pgdata <- data here
/dev/dsk2 : /pgindexes1 <- some indexes here
/dev/dsk3 : /pgindexes2 <- some ultra-high volume activity here

Let's say that there's a bunch of data on /dev/dsk1, and for performance
reasons it's been decided to move the indexes to another drive
/dev/dsk2.

Now, if just one of those indexes is getting *a lot* of the drive
activity, it would make sense to move it to it's own dedicated drive.
Having an um... PGINDEX (that's just an identifier for this example, not
an environment variable suggestion) directory location defined would
mean that each time a REINDEX operation occurs, then all new indexes
would be created in the same spot. That sounds better than the present
approach thus far, but wouldn't work for situations where indexes are
spread across multiple disk drives.

The suggestion of having some kind of path info for each index is merely
a thought of how to meet that potential future need, not necessarily the
best method anyone has ever thought of. Like someone might pipe up and
say "Nah, it could be done better XYZ way", etc.

:-)

Regards and best wishes,

Justin Clift

> Putting indexes into a separate subdirectoy and mount/link that directory on a
> device that is on a separate SCSI channel is what I can think of as last drop
> of performance out of it..
>
> Just a thought, as usual..
>
> I don't know how much efforts it would take but if we have pg_xlog in separte
> configurable dir. now, putting indexes as well and having per database pg_xlog
> should be on the same line. The later aspect is also important IMO..
>
> Bye
> Shridhar
>
> --
> VMS, n.: The world's foremost multi-user adventure game.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi