Re: tablespaces for temporary files

Greg Stark wrote:
>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > On the whole I'm unconvinced that this is worth the trouble. One of the
> > reasons for allowing people to move databases around is to determine
> > where their temp files go.
>
> The one scenario I would expect to see is having the temp files on filesystem
> all to themselves separate from the database. So using the database's location
> seems like it would never really satisfy that need.
>
> Actually the sort algorithm postgres uses would be much more efficient if it
> could get access to two or three locations guaranteed to be on different
> spindles. Last I read the comments it talked about a three tape polyphase sort
> emulated on a single tape. It's a _lot_ less efficient emulated on a single
> tape than it would be on three separate tapes. And for large sorts drive
> really do behave like tapes.
>
> Personally I am inclined to think that sorting and hash table spills really
> belong in a location specified completely separate from tablespaces.
>
> Others may be thinking of this more in terms of enforcing resource quotas in
> which case the current regime makes more sense. But from a performance point
> of view the current system is pointless.

Agreed, and I was going to mention the idea of a round-robin allocation
setup where the system cycles through a list of possible locations for
both sort files and temporary tables. One trick is that the cycle
pointer has to be global controlled so once one session uses an area the
next session uses the next location.

Updated TODO item:

* Add a GUC variable to control the tablespace for temporary objects and
sort files

This perhaps should use a round-robin allocation system where several
tablespaces are used in a cycle. The cycle pointer should be global.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073