On 07/18/2018 12:53 AM, Thomas Kellerer wrote:
> In the chapter "Database File layout" the pgsql_tmp is explained as follows:
>
> Temporary files (for operations such as sorting more data than can fit in memory)
> are created within PGDATA/base/pgsql_tmp, or within a pgsql_tmp subdirectory of
> a tablespace directory
>
> However the documentation for "temp_tablespaces" states:
>
> Temporary files for purposes such as sorting large data sets are also created
> in these tablespaces.
>
>
> How do these two things related to each other?
>
> Does this mean that if I do not explicitly create a dedicated "temp tablespace" then the pgsql_tmp subdirectory is
used.
> But _if_ I do create a temp tablespace (by creating one, and adding it to temp_tablespaces) then the sorting is done
*there*?
Yes, for those objects that do not have a tablespace specified in their
CREATE statement.
>
> So far I thought that a temp tablespace is only used for temporary tables (and indexes on them) but that paragraph in
the
> temp_tablespaces documentation seems to indicate otherwise.
The Database File Layout section you quoted above says the same thing.
Basically setting temp_tablespaces just overrides where temp objects and
operation files are placed when a tablespace is not specified in their
creation.
>
> Background: we are setting up a new server that has a regular (large) SSD and very fast NVMe SSD (which is too small
tohold all tables).
> So we would like to put anything that is "temporary" onto the NVMe drive.
>
> But I'm unsure if that is better done through a symlink for pgsql_tmp or a temp tablespace.
> Currently no temporary tables are used (but that might change in the future), so only intermediate results (e.g.
CTEs,sorting etc) would wind up there.
>
>
>
>
>
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com