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
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