DocsICP FilingConsole
Documentation
Home Page ApsaraDB RDS Support FAQ SQL

What do I do if the "ERROR: temporary file size exceeds temp_file_limit (1024kB)" error message is displayed when I run queries on my ApsaraDB RDS for PostgreSQL instance?

Updated at:
Copy as MD
Product
My Favorites

When a query runs sort or hash operations that exceed the temp_file_limit set for your RDS instance, PostgreSQL writes a temporary file to disk. If that file exceeds the limit, PostgreSQL cancels the query immediately.

Explicit temporary tables created with CREATE TEMP TABLE do not count toward temp_file_limit. Only behind-the-scenes temporary files from sort, hash, and cursor operations are counted.

Diagnose the issue

Before changing any parameters, confirm that your query is spilling to disk:

  1. Run EXPLAIN ANALYZE on the slow query.

  2. Look for a line similar to the following in the output:

    Sort Method: external merge  Disk: 7526kB

    This means the sort operation overflowed to disk. The disk usage shown indicates roughly how much memory (work_mem) that operation needs to stay in memory.

  3. To log all future temporary file creations, set log_temp_files:

    SET log_temp_files = 0;  -- Log all temporary files regardless of size

Solution

Use both approaches together for the best result.

Fix the symptom: increase temp_file_limit

Raise temp_file_limit so that the query can complete even when it spills to disk.

Global modification (applies to all sessions):

Log on to the ApsaraDB RDS console and modify temp_file_limit. For instructions, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

The default value of temp_file_limit equals the memory capacity of the instance, calculated as {DBInstanceClassMemory/1024}.

Session-level modification (applies to the current session only):

Connect to your RDS instance and run:

SET temp_file_limit = '1GB';

Fix the root cause: increase work_mem

work_mem controls how much memory each sort or hash operation can use before spilling to disk. If your queries regularly hit temp_file_limit, increasing work_mem reduces or eliminates temporary file generation.

Use the EXPLAIN ANALYZE output to determine the disk usage for the overflowing operation, then set work_mem to at least that amount:

SET work_mem = '64MB';  -- Adjust based on your EXPLAIN ANALYZE output

For a global change, modify work_mem in the ApsaraDB RDS console.

Usage notes

Setting temp_file_limit to -1 removes the size cap entirely. Avoid this — a single complex query can exhaust all available disk space and affect the entire instance. Similarly, avoid setting temp_file_limit to an excessively large value, as runaway queries can still exhaust disk space during complex operations.

Previous：LogsNext：Parameter