PostgreSQL 10 Administration Cookbook - Fourth Edition

How to do it…

Perform the following steps:

1. First, check whether your database defines special tablespaces for temporary files, as follows:

SELECT current_setting('temp_tablespaces');

2. As explained later on in this recipe, if the setting is empty, it means PostgreSQL is not using temporary tablespaces, and temporary objects will be located in the default tablespace for each database.
3. On the other hand, if temp_tablespaces has one or more tablespaces, then your task is easy because all temporary files, both those used for temporary tables and those used for query processing, are inside the directories of these tablespaces. The following query (which uses WITH queries and string and array functions) demonstrates how to check the space used ...