Where PostgreSQL puts temporary objects in the host directory structure.
PostgreSQL creates temporary files on the file system during larger, complex transactions. This happens when explicitly requested by a query or when there's not enough working memory to so interim or final results must overflow to disk. In some cases, the size of the directory PostgreSQL uses for temporary files can grow significantly and checking it is one of logical steps when investigating sudden decrease in free disk space.
By default, the pgsql_tmp directory is placed in $PGDATA/base directory, e.g.
/var/lib/postgresql/11/main/base
If there are no other than default tablespaces defined, that's also the only occurrence of this directory.
If more tablespaces are in use, the pgsql_tmp directory can be created
- in the directory assigned to given tablespace that is set as default for given tables
- in the directory assigned to the tablespace defined in
temp_tablespacesparameter, should this one be set (by default it's an empty string and the default tablespace is used)
Individual files inside the pgsql_tmp directory are named pgsql_tmpPPP.NNN, where PPP is the PID of the given backend process and the NNN is an incremental number.
Important: PostgreSQL will also write temporary files to other tablespaces during index builds and table and index rebuilds. Anything that rewrites a relation or creates a pre-populated relation will do so - CREATE INDEX, REINDEX, VACUUM FULL, CLUSTER, CREATE TABLE ... AS SELECT ..., etc. These are not affected by temp_tablespaces.