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:
- Find and optimize the query causing this to see if you can reduce the usage of temporary files.
- Tweak the
work_memPostgreSQL setting to adjust the amount of memory that each query operation can use before it starts writing data into temporary files, which can help reduce the number of files a query generates. (Check the PostgreSQL documentation for further details).
- Use a different tablespace for
temp_tablespaces, which determines the location where temporary files are created in your database host.
We recommend trying to analyze and optimize your queries even though you might decide to follow the other approaches. Even if changing your database configuration as suggested might resolve the issue, the query could still take a long time to run if it's not optimized.
Finding expensive queries generating temporary files
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.
Following suggestion 2 above, 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.
Setting a different tablespace for temporary files
Following the 3rd suggestion above, you can use the following command to change at the database level:
ALTER DATABASE yourdatabasename SET temp_tablespaces = 'pg_default';
Once your run this command, you'll need to reconnect to the database or start a new
psql session for this change to take effect.
This will allow PostgreSQL to use the main database disk to store temporary files. By default,
temp_tablespaces is set to
ephemeral, whose intended usage is meant for temporary tables and other transient data.
You should be careful when modifying
temp_tablespaces for your database. If you point your temporary tablespace to the same drive as
pg_default, it becomes possible for temporary file creation to utilize all of the IO of your instance and slow down your database significantly. We suggest changing the tablespace temporarily if needed, and then changing it back to
ephemeral as soon as the expensive query completes.
You can check out the current
temp_tablespaces configuration by running
SHOW temp_tablespaces;, or show the list of available tablespaces with
\db in a