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

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: A bit confused about "pgsql_tmp" vs "temp tablespace"
Date: 2018-07-18 07:53:30
Message-ID: pimrhm$c41$1@blaine.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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.

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hans Schou 2018-07-18 08:26:21 Shared buffers increased but cache hit ratio is still 85%
Previous Message Jamison, Kirk 2018-07-18 00:42:58 RE: Slow WAL recovery for DROP TABLE