Temporary files

Certain query operations such assort orhash table require some memory facility. This memory is provided by a runtime configwork_mem.
From the official documentationwork_mem

work_mem (integer)
Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.
Note that for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies before it starts to write data into temporary files.
Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, result cache nodes, and hash-based processing of IN subqueries.

Once the setwork_mem is exceeded, the operation starts writing to the temporary disk files.

Another operation that can write temp files isCREATE INDEX. This is controlled by a different runtime configmaintenance_work_mem.
From the official documentationmaintenance_work_mem

maintenance_work_mem (integer)
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

The particular location where these temporary disk files are written to is controlled by runtime configtemp_tablespaces
Again from the official documentationtemp_tablespaces

temp_tablespaces (string)
This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace.Temporary files for purposes such as sorting large data sets are also created in these tablespaces.
The value is a list of names of tablespaces.When there is more than one name in the list, PostgreSQL chooses a random member of the list each time a temporary object is to be created; except that within a transaction, successively created temporary objects are placed in successive tablespaces from the list.If the selected element of the list is an empty string, PostgreSQL will automatically use the default tablespace of the current database instead.
Whentemp_tablespaces is set interactively, specifying a nonexistent tablespace is an error, as is specifying a tablespace for which the user does not have CREATE privilege. However, when using a previously set value,nonexistent tablespaces are ignored, as are tablespaces for which the user lacks CREATE privilege. In particular, this rule applies when using a value set inpostgresql.conf.
The default value is an empty string, which results in all temporary objects being created in the default tablespace of the current database.

Default tablespace vs Specific temp tablespaces

Default installation of PostgreSQL has default tablespace(pg_default) set as thetemp_tablespaces.
You might want to separate writes of actual data from temp files, improving disk IO. That is wheretemp_tablespaces is most useful.
Also, you can point yourtemp_tablespaces to a faster disk such as nvme which is faster.

Setting up a tablespace for temporary files

You can use the following sample code to set up temp_tablespaces

• Create a new tablespace:

CREATETABLESPACEtempLOCATION'actual_location';

• Set cluster-widetemp_tablespaces:

ALTERSYSTEMSETtemp_tablespaces='temp';SELECTpg_reload_conf();

Caveats of temp files

1. Temp files can use up the available disk space when a query needs to create a lot of temp files, with PostgreSQL reporting an error such ascould not write block .... of temporary file no space left on device ... and the query being canceled.
2. Temp files are only kept around for the duration of a query. Once the query finishes or cancels, the temp files are cleaned up.Once in a while DBAs experience (1) and when they check the disk usage, they find out there is still space and start troubleshooting for where the issue might be. The issue is described in (2). When this happens, it might be high time you optimized your queries. The other workaround would be, using a larger free disk as yourtemp_tablespaces.

Monitoring temp files usage

We can log temp files with the help of a runtime configlog_temp_files

log_temp_files (integer)
Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results.If enabled by this setting, a log entry is emitted for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified amount of data. If this value is specified without units, it is taken as kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.

Whilelog_temp_files logs information about a temporary file when it is deleted, you might want to monitor the temporary file while it's being created as well, keeping an eye on things.
Also, you might want to know which queries are making use of temporary files and maybe optimize them for the fasterwork_mem.

We would be doing this from SQL.
Query:

WITHtablespacesAS(SELECTspcnameAStbl_name,COALESCE(NULLIF(pg_tablespace_location(oid),''),current_setting('data_directory')||'/base')AStbl_locationFROMpg_tablespace),tablespace_suffixAS(SELECTtbl_name,tbl_location||'/pgsql_tmp'ASpathFROMtablespacesWHEREtbl_name='pg_default'UNIONALLSELECTtbl_name,((tbl_location||'/')||path)||'/pgsql_tmp'FROMtablespaces,LATERALpg_ls_dir(tbl_location)ASpathWHEREpath~('PG_'||substring(current_setting('server_version')FROM'^(?:\d\.\d\d?|\d+)'))),statAS(SELECTsubstring(fileFROM'\d+\d')ASpid,tbl_nameAStemp_tablespace,pg_size_pretty(sum(pg_size_bytes(size)))ASsizeFROMtablespace_suffix,LATERALpg_ls_dir(path,TRUE,FALSE)ASfile,LATERALpg_size_pretty(((pg_stat_file((path||'/')||file,TRUE))).size)ASsizeGROUPBYpid,temp_tablespace)SELECTa.datname,a.pid,COALESCE(size,'0 MB')AStemp_size_written,COALESCE(temp_tablespace,'not using temp files')AStemp_tablespace,a.application_name,a.client_addr,a.usename,CAST(clock_timestamp()-a.query_startASinterval(0))ASduration,CAST(clock_timestamp()-a.state_changeASinterval(0))ASduration_since_state_change,trim(TRAILING';'FROM"left"(query,1000))ASquery,a.state,(a.wait_event_type||':')||a.wait_eventASwaitFROMpg_stat_activityASaLEFTJOINstatONa.pid=CAST(stat.pidASinteger)WHEREa.pid<>pg_backend_pid()ORDERBYtemp_size_writtenDESC;

Example output:

datname|pid|temp_size_written|temp_tablespace|application_name|client_addr|usename|duration|duration_since_state_change|query|state|wait---------+------+-------------------+----------------------+------------------+-------------+---------+----------+-----------------------------+-----------------------------------------------------+--------+------------------------------bolaji|4943|1911MB|temp|bolaji-psql|<null>|bolaji|00:00:51|00:00:51|CREATEINDEXCONCURRENTLYONfolder(id)|active|<null>bolaji|7518|1338MB|pg_default|bolaji-psql|<null>|bolaji|00:00:48|00:00:48|selectgenerate_series(1,100000000)asaorderbya|active|<null><null>|3819|0MB|notusingtempfiles||<null>|<null>|<null>|<null>||<null>|Activity:AutoVacuumMain<null>|3818|0MB|notusingtempfiles||<null>|<null>|<null>|<null>||<null>|Activity:WalWriterMain<null>|3816|0MB|notusingtempfiles||<null>|<null>|<null>|<null>||<null>|Activity:CheckpointerMain<null>|3821|0MB|notusingtempfiles||<null>|bolaji|<null>|<null>||<null>|Activity:LogicalLauncherMainbolaji|5935|0MB|notusingtempfiles|bolaji-psql|<null>|bolaji|00:31:39|00:31:39|showwork_mem|idle|Client:ClientRead<null>|3817|0MB|notusingtempfiles||<null>|<null>|<null>|<null>||<null>|Activity:BgWriterHibernate(8rows)Time:2.960ms

Finally, there is a new functionpg_ls_tmpdir available from PG12. I decided not to use this function in the above query because it is not available in lower versions.

UPDATES: 27-05-2025
The above query does not work in a restricted environment (an example isRDS) due to access to the OS files. We can use this modified query to achieve the same in such environments but this requires that you are running on at least PostgreSQL 12.

WITHtablespacesAS(SELECToidAStbl_oid,spcnameAStbl_name,COALESCE(NULLIF(pg_tablespace_location(oid),''),current_setting('data_directory')||'/base')AStbl_locationFROMpg_tablespace-- Exclude pg_global as it resides in the same directory as pg_defaultWHEREspcname<>'pg_global'),statAS(SELECTsubstring(nameFROM'\d+\d')ASpid,pg_size_pretty(sum(size))ASsize,tbl_nameAStemp_tablespaceFROMtablespacesINNERJOINLATERALpg_ls_tmpdir(tbl_oid)ONTRUEGROUPBYpid,tbl_name)SELECTa.datname,a.pid,COALESCE(size,'0 MB')AStemp_size_written,COALESCE(temp_tablespace,'not using temp files')AStemp_tablespace,a.application_name,a.client_addr,a.usename,CAST(clock_timestamp()-a.query_startASinterval(0))ASduration,CAST(clock_timestamp()-a.state_changeASinterval(0))ASduration_since_state_change,trim(TRAILING';'FROM"left"(query,1000))ASquery,a.state,(a.wait_event_type||':')||a.wait_eventASwaitFROMpg_stat_activityASaLEFTJOINstatONa.pid=CAST(stat.pidASinteger)WHEREa.pid<>pg_backend_pid()ORDERBYtemp_size_writtenDESC;