Improving Query Performance for Azure Database for PostgreSQL flexible server with Temp Tablespace on Local SSD

In PostgreSQL, temp_tablespaces parameter determines the placement of temporary tables and indexes, as well as temporary files that are used for purposes such as sorting large data sets. Temp files are session scoped and are not needed to be durable.

By default, temporary tables and objects are placed in the default tablespace pg_default in Azure Database for PostgreSQL flexible server. You can achieve faster query processing by placing the temporary tables that are generated by PostgreSQL on the local ssd storage, which reduces the traffic to the data disk over the network. This also reduces the IO contention between the temp tablespace read/writes and the writes to the database files.

Why temp_tablespaces on a different drive than the main database files?

Here are some reasons why it is advised to store the temp_tablespaces on a separate drive from the main database file

• Performance: The temp_tablespaces are used to store temporary data, such as intermediate results of queries. Keeping them on a separate drive can improve performance by reducing the amount of contention for I/O resources between the temp_tablespaces and the main database files (Data and WAL).
• Availability: A rogue application can sometimes fill up the available space on the data drive, which can lead to a…