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

On 07/18/2018 06:57 AM, Thomas Kellerer wrote:
> Adrian Klaver schrieb am 18.07.2018 um 15:06:
>>> 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.
>
> Thanks.
>
> I understand the relation between explicitly CREATEd objects and the temp tablespace(s).
>
> But what about the (temp) space needed for e.g. sorting, grouping or intermediate results from CTEs or derived tables?
> Is that also controlled through the temp_tablespaces?

Yes, all setting temp_tablespace from '' to some_tablespace(s) does is
redirect the creation of unspecified temp files from the db
default_namespace/pgsql_tmp to the named some_tablespace(s)/pgsql_tmp.

To verify this create a tablespace and add it to temp_tablespace and
then do temp operations and look at the_tablespace/pgsql_tmp/.

>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com