Re: A bit confused about "pgsql_tmp" vs "temp tablespace"

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 to hold 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(dot)klaver(at)aklaver(dot)com