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_tablespaces linked to a separate temporary disk on the database. The size of the temporary disk is fixed for all Heroku Postgres plans. The Heroku Postgres metrics logs offer specific information on temporary disk utilization through the sample#tmp-disk-used and sample#tmp-disk-available metrics.

If your database connections generate a high volume of temporary files/data, this temporary disk can run out of space and you can start seeing errors reporting "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 reduce its 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. Modify the PostgreSQL temp_tablespaces setting, which determines the location where temporary files are created in your database, to configure the database to write temporary files in your main database disk. This approach requires caution to prevent impacting your database's performance.

We recommend analyzing and optimizing your queries even though you might decide to follow the other approaches.

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

Search 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 all the logs from this process that will include the query that's the source of the temporary file creation.

Via pg_stat_statements

Heroku Postgres includes the pg_stat_statements extension installed by default. Use the following queries, depending on your database's Postgres version, to inspects the pg_stat_statements view and find queries that make heavy usage of temporary files:

For PostgreSQL 17 databases::

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' * (shared_blk_read_time + shared_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;

For PostgreSQL 14/15/16 databases:

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.

2. Adjusting 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.

Check the Help article How can I check and configure work_mem on my Heroku Postgres database? for more details.

3. Setting a different tablespace for temporary files

Following suggestion 3 above, you can also modify PostgreSQL's temp_tablespaces setting to change the tablespace where your database's temporary files are written.

By default, Heroku Postgres databases are configured to use the ephemeral tablespace that is located in a separate disk whose intended usage is meant for temporary tables and other transient data.

By setting temp_tablespaces to the pg_default tablespace, you configure PostgreSQL to write temporary files in your main database disk.

ALTER DATABASE <DATABASE_NAME> SET temp_tablespaces = 'pg_default';

After running this command you need to reconnect to the database for this change to take effect.

You must be careful when modifying temp_tablespaces for your database. If you configure your temporary tablespace to use the pg_default tablespace, it becomes possible for temporary file creation to utilize all of the IO of your instance and slow down your database significantly. Or, potentially, cause your main database disk to run out of space. We suggest to only change the tablespace temporarily if needed and then setting it back to ephemeral as soon as the expensive query completes.

You can check the current temp_tablespaces configuration by running SHOW temp_tablespaces;, or show the list of available tablespaces with \db in a psql session.

DATABASE=> SHOW temp_tablespaces;
 temp_tablespaces
------------------
 ephemeral
(1 row)

DATABASE=> \db
             List of tablespaces
    Name    |  Owner   |      Location
------------+----------+---------------------
 ephemeral  | postgres | /tmp/tmp_tablespace
 pg_default | postgres |
 pg_global  | postgres |
(3 rows)