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 that 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 so that it will 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 over-allocate memory.
• 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 because some queries don’t fit in working memory and have to spill over into temp files on disk. In this case, you should create a new tablespace(s) on separate disks, and ensure that the temp_tablespaces parameter...