How to resolve running out temporary space on my Heroku Postgres instance?

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

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;

Note that 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.

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 © 2019 Salesforce.com