Parameter · Client Connection Defaults / Statement Behavior
temp_tablespaces
Sets the tablespace(s) to use for temporary tables and sort files. An empty string means use the database's default tablespace.
- Default:
'' (empty) - Type: string
- Applies: Session (any user)
In plain words
Big sorts and hash operations that do not fit in memory write temporary files, and temporary tables also need disk space. This setting lets you put all of that on a different disk, for example a fast local SSD.
For DBAs and operators
A list of tablespace names. With several entries, PostgreSQL picks a random one for each temporary object, and within one transaction rotates through the list. Empty (default) means the database's default tablespace (files land in base/pgsql_tmp; in a tablespace they go to pg_tblspc/<oid>/PG_18_…/pgsql_tmp).
Covers temporary tables and their indexes and temp files from sorts, hashes and similar spills (see work_mem, temp_file_limit, log_temp_files). Users need CREATE privilege on the tablespace. A session SET of a non-existent tablespace (or one without CREATE privilege) fails: ERROR: tablespace "nosuch" does not exist; when the value comes from postgresql.conf, such entries are silently ignored.
A dedicated temp tablespace on ephemeral storage is a common pattern in the cloud, but the directory must exist (with the right PG_18 subdirectory) after a reboot: otherwise spill files silently fall back to the database's default tablespace, and temporary tables cannot be created there.
How to change it
Any user can change it for their session with SET.
See it in context
Local (per-backend) memory Physical layout on disk Logical structure