Fixing temporary file size exceeds temp_file_limit by Tuning the Query, Not Just the Limit

A YugabyteDB query can sometimes fail with an error like this:

				
					ERROR: temporary file size exceeds temp_file_limit (1048576kB)

At first glance, this looks like a configuration problem.

And technically, it is related to configuration. The temp_file_limit setting controls how much temporary disk space a single YSQL connection can use for operations that spill to disk.

But in many cases, the real issue is not that temp_file_limit is too small.

The real issue is that the query plan is doing too much work.

For example, a query like this can become expensive when the tables are large:

				
					SELECT DISTINCT e.external_id
FROM entity_records e
JOIN entity_locations l
  ON e.id = l.entity_record_id
WHERE e.is_current
  AND array_length(e.category_tags, 1) IS NULL
  AND l.location_status = 'ACTIVE'
ORDER BY e.external_id
LIMIT 100001;

The query looks reasonable:

● Find current parent records.
● Exclude records that have category tags.
● Join to active child rows.
● Return distinct IDs.
● Sort the result.
● Stop after a limit.

But at scale, this combination can create a very expensive plan if the indexes do not line up with the filter, join, and sort requirements.

What `temp_file_limit` Is Protecting You From

temp_file_limit limits the amount of temporary disk space a single YSQL connection can consume.

Temporary files can be created by operations such as:

● Large sorts
● Hash joins
● Hash aggregates
● Materialized intermediate results
● Cursor-related storage

When an operation cannot stay within memory, it may spill to disk. If the temporary files for that session exceed temp_file_limit, YugabyteDB cancels the query.

That is usually a good thing.

Without this safety valve, one bad query could consume a large amount of disk space and create I/O pressure for other workloads running on the same cluster.

Tip: A temp_file_limit error does not automatically mean you should raise the limit. First, check whether the query plan is doing a large sort, hash join, hash aggregate, or another operation that could be avoided with a better access path.

How to See Which Queries Hit the Limit

YugabyteDB includes the yb_terminated_queries view, which can help identify queries that were terminated because they exceeded temp_file_limit.

				
					SELECT backend_pid,
       query_text,
       termination_reason
FROM yb_terminated_queries;

You may see a termination reason similar to:

				
					temporary file size exceeds temp_file_limit (1048576kB)

That tells you which query failed, but it does not tell you why the query needed so much temporary space.

For that, look at the execution plan.

The Problem Query Shape

A query with JOIN, DISTINCT, ORDER BY, and a large LIMIT can be especially sensitive to index design.

A simplified bad plan may look like this:

				
					Limit
  -> Unique
       -> Sort
            Sort Key: e.external_id
            -> Hash Join
                 Hash Cond: (l.entity_record_id = e.id)
                 -> Seq Scan on entity_locations
                      Storage Filter: location_status = 'ACTIVE'
                 -> Hash
                      -> Index Only Scan on entity_records
                           Filter: array_length(category_tags, 1) IS NULL

The critical part is this:

				
					Unique
  -> Sort
       Sort Key: e.external_id
       -> Hash Join

This means YugabyteDB has to:

				
					Read many parent rows
  ↓
Read many child rows
  ↓
Hash join them
  ↓
Sort the joined result
  ↓
Remove duplicates
  ↓
Apply the LIMIT

If the joined result contains millions of rows, the sort can become large enough to spill to disk. Once the spill exceeds temp_file_limit, the query fails.

The fix is not simply to make the spill larger.

The better fix is to avoid the spill.

Why Existing Indexes May Not Help

A schema can have many indexes and still not have the right index for this query.

For example, these index patterns may look useful but still fail to eliminate the large sort.

Index Pattern	Why It May Not Help
`external_id HASH`	Useful for equality lookups like `external_id = ?`, but not useful for returning rows in `external_id` order.
`id HASH, external_id ASC`	The leading key is hash-partitioned by `id`, so the index cannot globally satisfy `ORDER BY external_id`.
Partial index with a similar, but not identical, predicate	A partial index only helps when its predicate matches what the query needs. Similar predicates are not always interchangeable.
Invalid index	The planner cannot use an invalid index, even if the index definition looks perfect for the query.

One common mistake is assuming that any index on the ORDER BY column will help.

In YugabyteDB, an index like this:

				
					CREATE INDEX idx_entity_external_id_hash
ON entity_records (external_id HASH);

is great for direct lookups:

				
					WHERE external_id = 'ABC123'

But it does not help much for:

				
					ORDER BY external_id

To avoid a sort, the rows need to be available in the order requested by the query.

Build the Index Around the Query

For the example query, the parent table needs an access path that matches the parent-side filter and the requested order.

A better parent-side index looks like this:

				
					CREATE INDEX CONCURRENTLY idx_entity_records_current_no_tags_extid
ON entity_records (external_id ASC)
INCLUDE (id)
WHERE is_current = true
  AND array_length(category_tags, 1) IS NULL;

Why this helps:

● external_id ASC matches the ORDER BY.
● The partial predicate matches the parent-side filters.
● INCLUDE (id) provides the join key without requiring a read from the base table.
● The index only contains rows relevant to this workflow.

Now the query has a way to read matching parent rows in the desired order.

Next, add a child-side index that makes the join check efficient:

				
					CREATE INDEX CONCURRENTLY idx_entity_locations_active_record
ON entity_locations (entity_record_id HASH)
WHERE location_status = 'ACTIVE';

Why this helps:

● The query joins to the child table by entity_record_id.
● The partial predicate stores only active child rows.
● YugabyteDB can quickly check whether a matching active child row exists.
● The planner has a better chance of avoiding a large child table scan and hash join.

Together, the indexes give the planner a better path:

				
					Read matching parent rows in external_id order
  ↓
For each parent row, check for matching active child rows
  ↓
Return distinct external_id values
  ↓
Stop when the LIMIT is satisfied

That is much better than joining a huge intermediate result and then sorting it.

Related YugabyteDB Tip: For another example of using the right index to avoid unnecessary sorting, see Turning a Slow ORDER BY LIMIT Query into an Index Only Scan.

Verify the New Plan

After creating the indexes, rerun the query with EXPLAIN.

				
					EXPLAIN
SELECT DISTINCT e.external_id
FROM entity_records e
JOIN entity_locations l
  ON e.id = l.entity_record_id
WHERE e.is_current
  AND array_length(e.category_tags, 1) IS NULL
  AND l.location_status = 'ACTIVE'
ORDER BY e.external_id
LIMIT 100001;

Look for a plan that avoids the large sort and avoids scanning a large portion of the child table.

Plan Item	What You Want to See
`Sort`	The large sort should disappear or become much smaller.
`Hash Join`	Ideally replaced by a more targeted join strategy that uses the child-side index.
`Seq Scan` on the child table	Ideally replaced by an index scan using the partial child-side index.
`Index Only Scan` on the parent table	Should use the new parent-side partial index that matches the filter and sort order.

For a more detailed test, use:

				
					EXPLAIN (ANALYZE, DIST)
SELECT DISTINCT e.external_id
FROM entity_records e
JOIN entity_locations l
  ON e.id = l.entity_record_id
WHERE e.is_current
  AND array_length(e.category_tags, 1) IS NULL
  AND l.location_status = 'ACTIVE'
ORDER BY e.external_id
LIMIT 100001;

With EXPLAIN (ANALYZE, DIST), pay attention to:

● Actual row counts.
● Storage table reads.
● Whether the sort still exists.
● Whether the query spills to disk.
● Whether the child-side access is still scanning too much data.
● Whether the parent-side index is being used in the expected order.

Related YugabyteDB Tip: For a broader walkthrough of matching indexes to filters, joins, sort order, and returned columns, see Designing the Best Index for a Query in YugabyteDB.

Be Careful with Similar-Looking Predicates

Partial indexes are powerful, but the predicate matters.

For example, this predicate:

				
					WHERE category_tags = '{}'

is not the same as this predicate:

				
					WHERE array_length(category_tags, 1) IS NULL

An empty array and a NULL array length are different conditions.

If the query uses one predicate and the partial index uses another, the planner may not be able to use the index.

When creating a partial index for a specific query pattern, make sure the index predicate matches the query predicate as closely as possible.

Should You Increase `temp_file_limit`?

Maybe, but carefully.

For a one-time maintenance task or a known reporting workload, increasing temp_file_limit at the session level can be reasonable:

				
					SET temp_file_limit = '5GB';

You can also increase work_mem for a specific session if the query legitimately needs more memory for sorts or hash operations:

				
					SET work_mem = '256MB';

Tip: Increasing work_mem can reduce or eliminate temporary file usage for sorts and hash operations, but it should not be the first thing you reach for. If the query is sorting, hashing, or joining millions of unnecessary rows, a better index or query shape may reduce the work far more effectively than giving the same plan more memory. For more details, see Tuning work_mem in YugabyteDB.

But avoid making temp_file_limit unlimited unless you fully understand the risk:

				
					SET temp_file_limit = -1;

That removes the safety valve.

Warning: Setting temp_file_limit to -1 or to a very large value can hide the real problem. It may allow a query to finish, but it can also let one session consume excessive disk space and create I/O pressure for the rest of the cluster.

A Practical Tuning Checklist

When a query hits temp_file_limit, walk through the problem in this order:

Step	What to Check
1	Find the failed query in `yb_terminated_queries`.
2	Run `EXPLAIN` or `EXPLAIN (ANALYZE, DIST)`.
3	Look for large `Sort`, `Hash Join`, or `HashAggregate` nodes.
4	Check whether the query has an index that matches the filters and the `ORDER BY`.
5	Check whether the join table has an index that supports the join key and selective filters.
6	Only then consider session-level changes to `work_mem` or `temp_file_limit`.

Final Takeaway

A temporary file size exceeds temp_file_limit error is not always a sign that the limit is too low.

It can be a sign that the query plan is creating a huge intermediate result and spilling it to disk.

In this example pattern, the expensive plan was caused by a large join followed by a large sort for ORDER BY, with DISTINCT on top.

The better fix was to create indexes that matched the shape of the query:

● A parent-side partial index that filters the rows and returns them in the requested order.
● A child-side partial index that makes the join check cheap.
● Included columns to avoid unnecessary base table reads.
● A plan that can stream rows and stop when the LIMIT is reached.

Increasing temp_file_limit may make the error go away, but tuning the query plan can make the spill go away.

Have Fun!