I'm running a PostgreSQL 16 database on an RDS instance that has 16 vCPUs and 64 GB of RAM. Recently, I received a medium severity alert from AWS indicating that my instance is generating excessive temporary objects. They suggested that I adjust my workload or consider switching to an instance type with RDS Optimized Reads.
I'm curious about where to start in Postgres to identify the root cause of these excessive temporary objects. Are there specific settings or configurations I should look into tuning? Just for context, I'm working with a large table that involves heavy joins and annotations.
1 Answer
It sounds like your queries are hitting the temp working memory hard. A good place to start is to use Postgres Analyze to check your joins, particularly for large table scans without filters. If possible, try to filter before joining to reduce the amount of data loaded into memory. If tuning doesn't help, you might consider upping the work_mem setting to allocate more memory for each query, which can help with temp object usage.
What would be a good target for the work_mem setting?