My Heroku Postgres instance is running out of temporary space

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. Find and optimize the query causing this to see if you can reduce the usage of temporary files.
  2. Tweak the work_mem PostgreSQL setting to adjust the amount of memory that each query opetation 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).
  3. 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;

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.

Tuning the work_mem setting

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 psql session.

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