Actions for heavy users of temporary tables
If you are a heavy user of temporary tables in your applications, then there are some additional actions you may need to perform.
How to do it…
There are four main things to check, which are as follows:
Make sure you run
VACUUM
on system tables, or enable autovacuum to do this for you.Monitor running queries to see how many temporary files are active and how large they are.
Tune the memory parameters. Think about increasing the
temp_buffers
parameter, but be careful not to overallocate memory by doing so.Separate the temp table's I/O. In a query-intensive system, you may find that reads/writes to temporary files exceed reads/writes on permanent data tables and indexes. In this case, you should create new tablespace(s) on separate disks, and ensure that the
temp_tablespaces
parameter is configured to use the additional tablespace (s).
How it works…
In PostgreSQL 9.4, when we create a temporary table, we insert entries into the pg_class
, pg_type
, and pg_attribute...