Error: "temporary file size exceeds temp_file_limit"

My database is suddenly hanging on some queries that join many tables and I see:

temporary file size exceeds temp_file_limit (1025563kB)

A sample query and query plan can be seen here:

https://pastebin.com/wug86Wrr

http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

This query normally takes less than 100ms but hangs when hit temporary file size limit.

When I run:

SELECT temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM pg_stat_database db;

I see:

Temporary files  Size of temporary files
---
22550            10651900248

How do I resolve this?

I was able to enable more of the problem. Say a user lists they can speak 2 languages - the query runs ok. Then they edit their profile to say they know 20 languages. The query ends up exceeding the temp_file_limit and hanging.

You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Either way, no wonder you amass a lot of temporary files.

Also note that all temp files of the same session count against the limit. Bound cursors can become a problem that way. Or multiple large queries in the same transaction.

By default the parameter temp_file_limit is set to -1, which means "no limit". So either sanitize your query or remove or extend the limit. (Only superusers can do that.)

If you cannot change either, setting more work_mem might help to keep more operations in RAM, so less temp space is needed.

Don't confuse this setting with temp_buffers, which is mostly unrelated and sets the amount of RAM that can be used for temporary objects (like in CREATE TEMP TABLE ...)

And you did not bother to mention your version of Postgres. Maybe outdated?

StackExchange Database Administrators Q#232927, answer score: 4