Re: How to REINDEX in high volume environments?

On Sat, 2002-09-28 at 02:16, Shridhar Daithankar wrote:
> On 28 Sep 2002 at 17:08, Justin Clift wrote:
>
> > Have moved the indexes to another drive, then created symlinks to them.
> > Ran a benchmark against the database, REINDEX'd the tables, VACUUM FULL
> > ANALYZE'd, prepared to re-run the benchmark again and guess what?
> >
> > The indexes were back on the original drive.
> > Is there a way to allow REINDEX to work without having this side affect?
> >
> > Pre-creating a bunch of dangling symlinks doesn't work (tried that, it
> > gives a "ERROR: cannot create accounts_pkey: File exists" on FreeBSD
> > 4.6.2 when using the REINDEX).
>
> Looks like we should have a subdirectory in database directory which stores
> index.
>
> May be transaction logs, indexes goes in separte directory which can be
> symlinked. Linking a directory is much simpler solution than linking a file.
>
> I suggest we have per database transaction log and indexes created in separate
> subdirectories for each database. Furhter given that large tables are segmented
> after one GB size, a table should have it's own subdirectory optionally..
>
> At the cost of few inodes, postgresql would gain much more flexibility and
> hence tunability..
>
> May be TODO for 7.4? Anyone?

Very neat idea! Sounds like an excellent way of gaining lots of
granularity!

I can't even think of a reason not to use the directory per table scheme
all the time. Perhaps simply allowing for a script/tool that will
automatically perform such a physical table migration to a distinct
directory would be in order too.

Either way, sounds like a good idea.

Greg