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.
All Heroku Postgres databases are configured by default with
temp_tablespace pointing to a specific, separate temporary disk. The size of the temporary disk is fixed for all Heroku Postgres plans, and the Heroku Postgres metrics logs offer specific information on temporary disk utilization.
In cases where your application or clients connected to the database are generating a high amount of temporary files/data, this temporary disk can run out of space and you can start seeing "No space left on device" for
There are several approaches to address this problem:
- Find and optimize the query causing this to see if you can reduce the usage of temporary files.
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
To find queries that are generating temporary files, one of the easiest ways is to inspect your database logs.
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. Then, use this PID to search for the originating query, which will also be logged.
On databases with
pg_stat_statements installed, you can use the following query to find queries that make heavy usage of temporary files:
Use the below query on DBs where the DB version is below 13:
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;
total_time isn't present in Postgres 13+ versions, replace
total_plan_time. Below is the query:
SELECT interval '1 millisecond' * total_plan_time AS total_exec_time, to_char(calls, 'FM999G999G999G990') AS ncalls, total_plan_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_statements only captures successfully executed queries, and not queries that were cancelled or otherwise terminated.
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