Postgres partition on VMware Cloud Director primary cell has usage spikes in storage

Products

• VMware Cloud Director

Issue/Introduction

• The postgres partition /dev/mapper/database_vg-vpostgres on the primary cell get 98% full for 20 minutes.
• These files are automatically removed by postgres.
• The directory /var/vmware/vpostgres/14/pgdata/base/pgsql_tmp takes most of the space and gets full with pgsql_tmp.####.# files.

Environment

VMware Cloud Director 10.x

Cause

When memory is not sufficient for running queries for operations such as JOINS, ORDER BY, DISTINCT etc, temporary files are created during query execution.
These files are automatically removed by postgres after the completion of query.

By default, temporary files are created in data directory /var/vmware/vpostgres/14/pgdata/base/pgsql_tmp and if you have huge  temporary file(s) it could fill up the data directory.
Apart from space issues, usage of temp files frequently could impact app performance.

Resolution

To reduce the likelihood of these temporary files being created, you can improve the database performance by following the steps outlined in Improve Cloud Director Database Performance by reducing the size of the audit_trail table.