I received an alert about my database using ephemeral tablespace. How do I go about fixing this?

Issue

You've received an email from Heroku saying that a database you own is using up Ephemeral tablespace. The message likely looks something like the following:

Heroku Postgres monitoring has detected non-temporary usage of the ephemeral tablespace in follower database (DATABASE on my-app).

Postgres tablespaces are namespaced locations on the host filesystem which allow for segmenting object storage and workload. In Heroku Postgres the ephemeral tablespace inteded usage is meant for temporary tables and other transient data and no guarantees are made about the resiliency of the data stored in this tablespace. As such, any data stored there is at risk of being irrevocably lost.

We strongly suggest migrating any data out of the ephemeral tablespace which cannot be easily reproduced.

What are the best means to tell where I'm using ephemeral tablespace and how do I migrate away from using it?

Resolution

Determining where you're using ephemeral tablespace

First and foremost, you'll need to determine how you're using ephemeral tablespace in your app.

Running the following will list the tablespaces used by each relation in your database:

SELECT
  c.relname,
  t.spcname
FROM
  pg_class c
    JOIN pg_tablespace t ON c.reltablespace = t.oid;

Running this query will yield a list that might look like the following:

=> SELECT c.relname, t.spcname FROM pg_class c JOIN pg_tablespace t ON c.reltablespace = t.oid;
                 relname                 |  spcname  
-----------------------------------------+-----------
 relation_1                              | pg_default
 relation_1_index                        | pg_default
 relation_2                              | ephemeral
 relation_2_index                        | ephemeral
(4 rows)

Migrating data to another tablespace

Migrating indexes and tables are similar, but slightly different processes.

Indexes

For indexes, you can use Postgres' ALTER INDEX method to change the tablespace of an index.

ALTER INDEX relation_2_index SET TABLESPACE pg_default;

Please note that altering an index will temporarily lock up its availability. The period of time that the index is unavailable depends on how large the table its referencing is. It's best advised that you consider temporarily taking your app into maintenance mode to safely complete this procedure.

Tables

For tables, you can use Postgres' ALTER TABLE method to change the tablespace of a table with the argument of SET TABLESPACE.

For example:

ALTER TABLE relation_2 SET TABLESPACE pg_default;

As with indexes, please also note that altering a table will temporarily lock up its availability. The period of time that the table is unavailable depends on its size. It's best advised that you consider temporarily taking your app into maintenance mode to safely complete this procedure.