Usage of disk space by temporary data
In addition to ordinary persistent tables, you can also create temporary tables. Temporary tables have disk files for their data, just as persistent tables do, but those files will be stored in one of the tablespaces listed in the temp_tablespaces parameter or, if not set, in the default tablespace.
PostgreSQL may also use temporary files for query processing for sorts, hash joins, or hold cursors if they are larger than your current work_mem parameter setting.
Getting ready
So, how do you find out how much data is being used by temporary tables and files? You can do this by using any untrusted embedded language or by checking the disk usage directly on the database host. You have to use an untrusted language because trusted languages run in a sandbox, which prohibits them from directly accessing the host filesystem.
How to do it…
Perform the following steps:
- First, check whether your database defines special...
