PostgreSQL 16 on RDS: Excessive Temporary Objects Warning — How Should I Tackle This?

Question

I'm running a PostgreSQL 16 database on an RDS instance (16 vCPUs, 64 GB RAM). Recently, I got a medium severity recommendation from AWS.

It says Your instance is creating excessive temporary objects. We recommend tuning your workload or switching to an instance class with RDS Optimized Reads.

What would you check first in Postgres to figure out the root cause of excessive temp objects?

Any important settings you'd recommend tuning?

Note: The table is huge and there are heavy joins and annotations.

re:Post Agent · Answer

When dealing with excessive temporary objects in PostgreSQL 16 on RDS, there are several areas to investigate and tune:

1. Query Analysis:
First, examine your queries, especially those involving heavy joins and large tables. Use the EXPLAIN ANALYZE command to identify queries that are creating large temporary objects. Look for operations like large sorts, hash joins, or aggregations that might be causing this issue.

2. Work_mem Setting:
Check and possibly increase the work_mem parameter. This setting determines how much memory PostgreSQL can use for internal sort operations and hash tables before writing to temporary files. Increasing work_mem can reduce the need for temporary files, but be cautious not to set it too high as it applies per operation in a query.

3. Indexing Strategy:
Ensure your tables, especially the large ones involved in heavy joins, are properly indexed. Missing or ineffective indexes can lead to full table scans and excessive temporary object creation.

4. Autovacuum Configuration:
Review your autovacuum settings. Proper table maintenance can improve query performance and reduce the need for temporary objects. Ensure autovacuum is running frequently enough on your large tables.

5. Query Optimization:
Consider rewriting complex queries to break them into smaller, more manageable parts. This can sometimes reduce the need for large temporary objects.

6. Monitoring Temporary File Usage:
Use PostgreSQL's built-in functions like pg_ls_tmpdir() to view current temporary file usage. You can also enable the log_temp_files parameter to log information about temporary file creation.

7. Performance Insights:
If you're using Amazon RDS Performance Insights, enable the temp_bytes and temp_files metrics to get a better view of temporary file usage over time.

If after tuning these areas you still see excessive temporary object creation, consider AWS's recommendation to switch to an instance class with RDS Optimized Reads. These instance types use local NVMe SSD storage for temporary objects, which can significantly improve performance for workloads with heavy temporary object usage.

Remember to test any changes in a non-production environment first, as some of these adjustments (especially to work_mem) can have significant impacts on overall database performance.  
**Sources**  
[getting recommendation on RDS POSTGRESQL: "Use a DB instance type with RDS Optimized Reads" | AWS re:Post](https://repost.aws/questions/QUKtwaKkW2Q9ufbEA7jivvRQ/getting-recommendation-on-rds-postgresql-use-a-db-instance-type-with-rds-optimized-reads)  
[Managing temporary files with PostgreSQL - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ManagingTempFiles.html)  
[Managing temporary files with PostgreSQL - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL.ManagingTempFiles.html)

PostgreSQL 16 on RDS: Excessive Temporary Objects Warning — How Should I Tackle This?

Relevant content