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

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

There are several approaches to address 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 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).
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

Via logs

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.

Via pg_stat_statements

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,
left(query, 45) 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;

As total_time isn't present in Postgres 13+ versions, replace total_time with 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,
left(query, 45) 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_statements only captures successfully executed queries, and not queries that were cancelled or otherwise terminated.

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.