DEV Community

Cover image for PostgreSQL temp files usage
Bolaji Wahab
Bolaji Wahab

Posted on • Updated on

PostgreSQL temp files usage

Certain query operations such as sort or hash table require some memory facility. This memory is provided by a runtime config work_mem.
From the official documentation work_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 set work_mem is exceeded, the operation starts writing to the temporary disk files.

Another operation that can write temp files is CREATE INDEX. This is controlled by a different runtime config maintenance_work_mem.
From the official documentation maintenance_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 config temp_tablespaces
Again from the official documentation temp_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.
When temp_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 in postgresql.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 the temp_tablespaces.
You might want to separate writes of actual data from temp files, improving disk IO. That is where temp_tablespaces is most useful.
Also, you can point your temp_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: ```

CREATE TABLESPACE temp LOCATION 'actual_location';

- Set cluster-wide `temp_tablespaces`:
Enter fullscreen mode Exit fullscreen mode

ALTER SYSTEM SET temp_tablespaces = 'temp';
SELECT pg_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 as
`could 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 your `temp_tablespaces`. 

##### Monitoring temp files usage
We can log temp files with the help of a runtime config [log_temp_files](https://www.postgresql.org/docs/14/runtime-config-logging.html#:~:text=change%20this%20setting.-,log_temp_files,-(integer))
> `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.

While `log_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 faster `work_mem`.

We would be doing this from SQL.
Query:
Enter fullscreen mode Exit fullscreen mode

WITH tablespaces AS (
SELECT
spcname AS tbl_name,
coalesce(nullif(pg_tablespace_location(oid), ''), (current_setting('data_directory') || '/base')) AS tbl_location
FROM pg_tablespace
),
tablespace_suffix AS (
SELECT
tbl_name,
tbl_location || '/pgsql_tmp' AS path
FROM tablespaces
WHERE tbl_name = 'pg_default'
UNION ALL
SELECT
tbl_name,
tbl_location || '/' || path || '/pgsql_tmp'
FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))
),
stat AS (
SELECT
substring(file from '\d+\d') AS pid,
tbl_name AS temp_tablespace,
pg_size_pretty(sum(pg_size_bytes(size))) AS size
FROM tablespace_suffix, LATERAL pg_ls_dir(path, true, false) AS file,
LATERAL pg_size_pretty((pg_stat_file(path || '/' || file, true)).size) AS size
GROUP BY pid, temp_tablespace
)
SELECT
a.datname,
a.pid,
coalesce(size, '0 MB') AS temp_size_written,
coalesce(temp_tablespace, 'not using temp files') AS temp_tablespace,
a.application_name,
a.client_addr,
a.usename,
(clock_timestamp() - a.query_start)::interval(0) AS duration,
(clock_timestamp() - a.state_change)::interval(0) AS duration_since_state_change,
trim(trailing ';' FROM left(query, 1000)) AS query,
a.state,
a.wait_event_type || ':' || a.wait_event AS wait
FROM pg_stat_activity AS a
LEFT JOIN stat ON a.pid = stat.pid::int
WHERE a.pid != pg_backend_pid()
ORDER BY temp_size_written DESC;

Example output:
Enter fullscreen mode Exit fullscreen mode
datname pid temp_size_written temp_tablespace application_name client_addr usename duration duration_since_state_change query state wait
bolaji 4943 1911 MB temp bolaji-psql bolaji 00:00:51 00:00:51 CREATE INDEX CONCURRENTLY ON folder (id) active
bolaji 7518 1338 MB pg_default bolaji-psql bolaji 00:00:48 00:00:48 select generate_series(1,100000000) as a order by a active
3819 0 MB not using temp files Activity:AutoVacuumMain
3818 0 MB not using temp files Activity:WalWriterMain
3816 0 MB not using temp files Activity:CheckpointerMain
3821 0 MB not using temp files bolaji Activity:LogicalLauncherMain
bolaji 5935 0 MB not using temp files bolaji-psql bolaji 00:31:39 00:31:39 show work_mem idle Client:ClientRead
3817 0 MB not using temp files Activity:BgWriterHibernate

(8 rows)

Time: 2.960 ms

Finally, there is a new function [pg_ls_tmpdir](https://www.postgresql.org/docs/12/functions-admin.html#:~:text=pg_ls_tmpdir(%5Btablespace%20oid%5D)) available from PG12. I decided not to use this function in the above query because it is not available in lower versions.

Enter fullscreen mode Exit fullscreen mode

Top comments (19)

Collapse
 
dineshparva profile image
dinesh reddy

path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))

what exactly is this part doing in the query especially FROM '^(?:\d.\d\d?|\d+)')??? query is failing with msg "ERROR: absolute path not allowed"

Collapse
 
bolajiwahab profile image
Bolaji Wahab • Edited

For this part of the query

path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))
Enter fullscreen mode Exit fullscreen mode

When you create a tablespace, a directory is created in format PG_server_version_some_other_digit.
We check all tablespaces except pg_global for temp files.

For the error "ERROR: absolute path not allowed".

I cannot figure where the error seems to be coming from currently but it seems to be related to tablespace setup.
Are you using tablespaces? And if so, can you confirm the setup of the tablespaces?

Thank you.

Collapse
 
dineshparva profile image
dinesh reddy

Image description

Thread Thread
 
dineshparva profile image
dinesh reddy

Anything wrong with the tablespace setup

Thread Thread
 
bolajiwahab profile image
Bolaji Wahab

Can you try break down the query? That way it would be easier to debug.
The first part

WITH tablespaces AS (
    SELECT
        spcname AS tbl_name,
        coalesce(nullif(pg_tablespace_location(oid), ''), (current_setting('data_directory') || '/base')) AS tbl_location
    FROM pg_tablespace
),
tablespace_suffix AS (
    SELECT
        tbl_name,
        tbl_location || '/pgsql_tmp' AS path
    FROM tablespaces
    WHERE tbl_name = 'pg_default'
    UNION ALL
    SELECT
        tbl_name,
        tbl_location || '/' || path || '/pgsql_tmp'
    FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
    WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d\.\d\d?|\d+)'))
)
SELECT * FROM tablespace_suffix;
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
bolajiwahab profile image
Bolaji Wahab

Btw, which version of PostgreSQL is it and is it native PostgreSQL?

Thread Thread
 
dineshparva profile image
dinesh reddy

Yes native postgresql version is 10.7 on oracle el 6 and query is failing here and when i comment second part after union all error not getting reported then

tablespace_suffix AS (
SELECT
tbl_name,
tbl_location || '/pgsql_tmp' AS path
FROM tablespaces
WHERE tbl_name = 'pg_default'
UNION ALL
SELECT
tbl_name,
tbl_location || '/' || path || '/pgsql_tmp'
FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))
),

Thread Thread
 
dineshparva profile image
dinesh reddy

Image description

Thread Thread
 
dineshparva profile image
dinesh reddy

after commenting the second part of union all , am i missing anything

postgres=# WITH tablespaces AS (
postgres(# SELECT
postgres(# spcname AS tbl_name,
postgres(# coalesce(nullif(pg_tablespace_location(oid), ''), (current_setting('data_directory') || '/base')) AS tbl_location
postgres(# FROM pg_tablespace
postgres(# ),
postgres-# tablespace_suffix AS (
postgres(# SELECT
postgres(# tbl_name,
postgres(# tbl_location || '/pgsql_tmp' AS path
postgres(# FROM tablespaces
postgres(# WHERE tbl_name = 'pg_default'
postgres(# --UNION ALL
postgres(# -- SELECT
postgres(# -- tbl_name,
postgres(# -- tbl_location || '/' || path || '/pgsql_tmp'
postgres(# -- FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
postgres(# -- WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))
postgres(# )
postgres-# SELECT * FROM tablespace_suffix;
tbl_name | path

------------+---------------------------------------
pg_default | /nfs-mnt/postgres/data/base/pgsql_tmp
(1 row)

Thread Thread
 
bolajiwahab profile image
Bolaji Wahab

Okay. Can you run

SELECT pg_ls_dir('/nfs-mnt/postgres/tablespaces/omnia');
Enter fullscreen mode Exit fullscreen mode

This postgresql.org/docs/10/functions-a.... seems to be the issue but I believe you are using superuser.

Thread Thread
 
dineshparva profile image
dinesh reddy

Yes iam using postgres as login user

postgres=# SELECT pg_ls_dir('/nfs-mnt/postgres/tablespaces/omnia');
ERROR: absolute path not allowed
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Thread Thread
 
dineshparva profile image
dinesh reddy

looks like pg_ls_dir works only on postgresql datadirectory $PGDATA

postgres=# SELECT pg_ls_dir('/nfs-mnt/postgres/data/base');

pg_ls_dir

13457
1
147972689
13456
124079171
(5 rows)

postgres=# SELECT pg_ls_dir('/nfs-mnt/postgres/tablespaces/omnia');
ERROR: absolute path not allowed
postgres=#

Thread Thread
 
bolajiwahab profile image
Bolaji Wahab

Oh yeah. I just confirmed the functions were limited to only the cluster directory and log directory in 9.6, 10. And they were only changed to support files outside cluster directory later from 11.

Thread Thread
 
bolajiwahab profile image
Bolaji Wahab • Edited
Collapse
 
kmohsoe profile image
KMohsoe

If the temporary file size is coming greater, please could you tell me how could I delete the temporary file in postgreSQL with the command?

Image description

Collapse
 
bolajiwahab profile image
Bolaji Wahab • Edited

Temporary files are cleaned up automatically once the queries using them are done processing or canceled. Deleting an in-use temporary files can lead to backend crashes or even server crashes.
Why do you want to delete the files? Are they stale/orphaned? You should only have orphaned/stale temporary files when there are crashes.

Collapse
 
stephen_price profile image
Stephen Price

You mentioned that you avoided using "pg_ls_tmpdir" in the query since it's new as of PG12, but the query is using it. Is there an earlier version of the query that does not use it?
I ask because I'm trying to wrap my head around which queries are eating up my temp space, and due to the fact that I'm running Aurora PostgreSQL in AWS RDS, I cannot use "pg_ls_tmpdir" (the "rds_superuser" permissions explicitly deny it).

Collapse
 
bolajiwahab profile image
Bolaji Wahab • Edited

Hi, I believe you mean pg_ls_dir and pg_stat_file? Both functions are restricted to only superusers by default but execute can be granted to any user by a superuser.
I am not totally conversant with rds_superuser so you might not be able to call these functions.

Collapse
 
jtorral profile image
JT

One simple solution if resource are available is to create a ram based file system and use that for your temp file destination.