PGError: could not write block blockid of temporary file: No space left of device explains the issue well, but I need to use more temporary storage.
There are several solutions to this problem:
- Optimize the query to see if you can reduce the usage of temporary files
work_memto see if it will use less temporary files (see document for how
work_memworks with temporary files)
- Use different tablespace for
temp_tablespaces, where used by temporary files
For 2, you can use the following command to change
work_mem at the database level:
ALTER DATABASE yourdatabasename SET work_mem = '64MB';
However, take care when upping
work_mem in this fashion as this applies to each plan node, and high values can quickly exhaust the available memory on your Postgres instance.
You can also set
work_mem per-session with
SET work_mem = '64MB';, or for a transaction with
SET LOCAL work_mem = '64MB'; within the transaction.
For 3, you can use the following command to change at the database level:
ALTER DATABASE yourdatabasename SET temp_tablespaces = 'pg_default';
This will allow
temp_tablespaces to use the main database disk that we provide. You can check out the current
temp_tablespaces by running
show temp_tablespaces;, or show the list of tablespaces with
We recommend trying 1 even though you might decide to go for 2 and/or 3. Even though you might decide to do 2 or 3, and it stops running out of disk space, the query could take a long time to run without the optimization. 2 may not help much, but it is relatively easy to try out.
Finding queries generating expensive queries
On databases with
pg_stat_statements installed, you can use the following query to find queries that make heavy usage of temporary files:
SELECT interval '1 millisecond' * total_time AS total_exec_time, to_char(calls, 'FM999G999G999G990') AS ncalls, total_time / calls AS avg_exec_time_ms, interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time, temp_blks_written, query AS query FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1) AND temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 20;
pg_stat_statments only captures successfully executed queries, and not queries that were cancelled or otherwise terminated.
You can also parse your logs looking for Postgres log lines containing "temporary file:". These log lines are output when the files are cleaned up on exit of the backend that generated the files, and contain the PID of the backend. You can use this PID to search for the originating query, which will also be logged.