How to resolve "ERROR: could not write block xxx of temporary file: No space left on device"?

Issue

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.

Resolution

There are several solutions to this problem:

  1. Optimize the query to see if you can reduce the usage of temporary files
  2. Tweak work_mem to see if it will use less temporary files (see document for how work_mem works with temporary files)
  3. Use different tablespace for temp_tablespaces, where used by temporary files

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 \db.

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.

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;

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support
Terms of Service Privacy Cookies © 2018 Salesforce.com