Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A query fills the disk with temporary files: could not write to pgsql_tmp

ErrorERROR: could not write to file "base/pgsql_tmp/pgsql_tmp32058.285": No space left on device

Short answer

A big sort or hash spilled to temporary files in base/pgsql_tmp and the disk filled up. Log temp files to find the query, tune work_mem or the query, cap runaways with temp_file_limit, and free space on the data disk. A full data disk blocks work but does not corrupt data.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    See how big the temporary files are right now, and how much room the database itself takes. pg_ls_tmpdir needs a superuser or pg_monitor.

    SELECT sum(size) AS temp_bytes FROM pg_ls_tmpdir();
    SELECT pg_size_pretty(pg_database_size(current_database()));
  2. 2Match what you see, then jump to its fix

    1. 1pg_ls_tmpdir shows large files, and the error appears with one report or joinOne heavy query spills sorts or hashes to temp filesFix
    2. 2A single runaway query consumed the whole disk; temp_file_limit is -1Nothing limits how much temp space one process may useFix
    3. 3pg_ls_tmpdir is small, but the volume is still at 100 percentThe disk is full of real data, not only temp filesFix

What does the error look like?

  • ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp32058.285": No space left on device
  • SQL state: 53100

Cause 1 of 3

One heavy query spills sorts or hashes to temp files

1Check

A query operation that exceeds work_mem writes to temporary disk files. Turn on logging for every temp file, run the query, then read the log lines with their size.

ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();

2Fix

Fix the query first (missing join condition, sorting millions of rows, a cross join). If the operation is legitimate, raise work_mem for that session only rather than globally.

SET work_mem = '256MB';

3Verify

Run the query and check the temp file log lines get smaller or disappear, then set logging back to its default.

ALTER SYSTEM RESET log_temp_files;
SELECT pg_reload_conf();

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

Nothing limits how much temp space one process may use

1Check

temp_file_limit caps the disk a process can use for temporary files. The default of -1 means no limit. A transaction that tries to exceed it is canceled.

SHOW temp_file_limit;

2Fix

Set a limit that leaves room for the rest of the system. Only superusers or roles with SET privilege can change it. It is checked per process, so size it with the number of parallel sessions in mind.

ALTER SYSTEM SET temp_file_limit = '20GB';
SELECT pg_reload_conf();

3Verify

The setting shows the new value.

SHOW temp_file_limit;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The disk is full of real data, not only temp files

1Check

The manual calls keeping the disk from filling up the most important monitoring task. Find the largest tables including indexes and TOAST.

SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

2Fix

Free space or grow the volume. A full data disk stops useful work but does not corrupt data; a full disk holding WAL files can cause a server panic and shutdown, so act before it is completely full.

-- remove data you no longer need, then confirm the size
SELECT pg_size_pretty(pg_database_size(current_database()));

3Verify

Retry the query that failed.

SELECT sum(size) FROM pg_ls_tmpdir();

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Alert at 80 percent disk use, because some file systems perform badly when almost full.
  • Keep log_temp_files at a sensible threshold so heavy queries show up in the log.
  • Set temp_file_limit on shared servers so one bad query cannot take everyone down.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.