Обсуждение: A bit confused about "pgsql_tmp" vs "temp tablespace"
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 holdall 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, sortingetc) would wind up there.
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
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 inthe >> 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?
Thomas Kellerer <spam_eater@gmx.net> writes:
> 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.
regards, tom lane
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 inthe >>> 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.klaver@aklaver.com