Thread: creating an index with tablespace on a dedicated filesystem (postgresql 8.1.5)

Hello,

we use tablespace feature to keep indexes on a dedicated raid array
(for performance). What I've noticed is that when the index were
created, based on the i/o activity it seemed that postgres was
creating the index file somewhere on the array where the default table
space is hosted and the data is stored. Then, when the index was
completed, it moved index file to array for indexes.

If my observation is accurate, and it really works this way, then it's
definitely point for improvement imho...

--
Vlad

Vlad <marchenko@gmail.com> writes:
> we use tablespace feature to keep indexes on a dedicated raid array
> (for performance). What I've noticed is that when the index were
> created, based on the i/o activity it seemed that postgres was
> creating the index file somewhere on the array where the default table
> space is hosted and the data is stored.

Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/.  If you have a
mind to, you can replace that subdirectory with a symlink to a (suitably
secure) directory elsewhere.

            regards, tom lane

so I guess for performance sake it makes sense do that under

$TABLESPACEPATH/yourdb/pgsql_tmp/

in case $TABLESPACEPATH for the object is being created is set to a
non-default value

>
> Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/.  If you have a
> mind to, you can replace that subdirectory with a symlink to a (suitably
> secure) directory elsewhere.
>

-- Vlad

Tom Lane wrote:
> Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/.  If you have a
> mind to, you can replace that subdirectory with a symlink to a (suitably
> secure) directory elsewhere.

Tom, is it safe to assume that the contents of the temp directory do not
need to persist across Postgres restarts?  I may move ours to an
alternate volume, but the answer to this question will have an impact on
my backup strategy.

-Glen

Glen Parker <glenebob@nwlink.com> writes:
> Tom, is it safe to assume that the contents of the temp directory do not
> need to persist across Postgres restarts?

Yup, they're only temp files (in fact, I think there is code somewhere
that actually runs around and cleans out the temp directories during a
restart).

            regards, tom lane

Tom,

which means that earlier suggestion you've made - to host temp dir on
a separate hd linked to temp dir under data tree it's not quite a
straight solution .... I assume that link will be erased by postgres
every time it finishes operating with temp directory.

> Yup, they're only temp files (in fact, I think there is code somewhere
> that actually runs around and cleans out the temp directories during a
> restart).
>


-- vlad

Vlad wrote:
> which means that earlier suggestion you've made - to host temp dir on
> a separate hd linked to temp dir under data tree it's not quite a
> straight solution .... I assume that link will be erased by postgres
> every time it finishes operating with temp directory.

Why would the postmaster delete the entire temp dir/link at any point?

-Glen

Vlad <marchenko@gmail.com> writes:
> which means that earlier suggestion you've made - to host temp dir on
> a separate hd linked to temp dir under data tree it's not quite a
> straight solution .... I assume that link will be erased by postgres
> every time it finishes operating with temp directory.

No, it just deletes the contents of the directory, it has no occasion to
destroy a symlink.

It is true that you'd have to set the symlink up manually any time you
created a database, but after that it should be a non-issue.

            regards, tom lane