PostgreSQL temp files and Rails pagination

The warning

A managed PostgreSQL database can report a warning about temporary file usage even when the application appears healthy. A common first check is to inspect database-level statistics:

SELECT
  datname,
  temp_files,
  pg_size_pretty(temp_bytes) AS total_temp_bytes,
  stats_reset
FROM pg_stat_database
WHERE datname = current_database();

The important detail is that temp_files is a count, while temp_bytes is a byte total. Formatting temp_files with pg_size_pretty is misleading because it makes a count look like a size.

PostgreSQL writes temporary files when an operation needs more memory than available for that operation. Sorts and hash tables are the classic examples. The work_mem setting controls the base amount of memory available to operations such as sorts and hash tables before they write to temporary disk files. A single complex query can use several such operations at once, and each operation can use up to that amount before spilling.

Where temp files come from

Temporary files are not automatically bad. They are a mechanism PostgreSQL uses to complete large operations safely. The problem appears when temp files become large, frequent, or concentrated in an interactive path such as an API index endpoint.

The usual causes are:

• large ORDER BY operations without a supporting index,
• DISTINCT over a large intermediate result,
• GROUP BY or aggregate queries that exceed memory,
• hash joins or hash aggregates that spill,
• one-to-many joins that multiply rows before pagination,
• ORM-generated eager loading queries that are broader than the page needs.

The diagnostic goal is not merely to find that temp files exist. The goal is to find the statement that produced them.

Find the statement behind the spill

On platforms that expose PostgreSQL logs, a temp-file line usually contains a backend process ID, a session identifier, a file path, and a size. The adjacent log lines often include the SQL statement and bind parameters.

temporary file: path ".../pgsql_tmp/...", size 27377664

That size is roughly 26 MB. A single 26 MB temporary file is not catastrophic. But it proves that a request caused PostgreSQL to spill to disk. If the same pattern repeats across many requests, it can explain a much larger cumulative temp_bytes value.

The useful next step is to filter logs by the session or process identifier, not only by the words "temporary file". This usually reveals the triggering SQL statement.

# Example shape; adapt to the platform and log tooling.
logs --num 1500 | grep 'session-id-or-process-id'

The expensive query shape

The problematic statement often looks structurally like this:

SELECT DISTINCT
  primary_records.sort_timestamp,
  primary_records.id
FROM primary_records
JOIN required_lookup ON required_lookup.id = primary_records.lookup_id
LEFT OUTER JOIN optional_metadata ON optional_metadata.primary_record_id = primary_records.id
LEFT OUTER JOIN join_table_a ON join_table_a.primary_record_id = primary_records.id
LEFT OUTER JOIN associated_records_a ON associated_records_a.id = join_table_a.associated_record_id
LEFT OUTER JOIN join_table_b ON join_table_b.primary_record_id = primary_records.id
LEFT OUTER JOIN associated_records_b ON associated_records_b.id = join_table_b.associated_record_id
WHERE ...
ORDER BY primary_records.sort_timestamp DESC
LIMIT 20 OFFSET 0;

This is a bad shape for a paginated index. It asks the database to join a primary record to several optional and many-to-many associations, produce a large intermediate result, deduplicate the parent records, sort them, and only then return one page.

The multiplication is the core issue:

1 primary record
x 5 associated records of one kind
x 8 associated records of another kind
= 40 intermediate rows for one logical record

For twenty visible records, the database may need to process hundreds or thousands of joined rows. The application wanted a page of primary records; the query asked for a joined graph and then tried to collapse it back into a page.

How Rails gets there

Rails provides several ways to avoid N+1 queries: includes, preload, and eager_load. They are not equivalent.

A public index endpoint can accidentally combine several expensive ingredients:

PrimaryRecord
  .visible
  .with_lookup_filter(...)
  .includes(
    :small_association,
    :metadata,
    :many_to_many_group_a,
    :many_to_many_group_b,
    nested_association: :nested_records
  )
  .order(sort_timestamp: :desc)
  .page(page)
  .per(per_page)

If includes turns into joined eager loading, pagination becomes expensive. Rails may generate a SELECT DISTINCT over the primary key and ordering column to avoid duplicate parent records. That deduplication step is exactly the kind of operation that can create temporary files.

Separate selection from loading

For an index endpoint, the database should first find the page of primary records. Associations should be loaded only for the records on that page.

The safer Rails shape is:

relation = PrimaryRecord
  .visible
  .with_lookup_filter(...)
  .order(sort_timestamp: :desc, id: :desc)
  .page(page)
  .per(per_page)
  .preload(
    :small_association,
    :metadata,
    :many_to_many_group_a,
    :many_to_many_group_b,
    nested_association: :nested_records
  )

The stable secondary order by id matters for pagination. Ordering only by a timestamp can produce unstable pages when multiple records share the same timestamp.

After this change, the main SQL should become closer to:

SELECT primary_records.*
FROM primary_records
JOIN required_lookup ON required_lookup.id = primary_records.lookup_id
WHERE ...
ORDER BY primary_records.sort_timestamp DESC, primary_records.id DESC
LIMIT 20 OFFSET 0;

Then Rails should issue separate association queries scoped to those twenty IDs. This may increase the number of SQL statements, but each statement is simpler and avoids row multiplication in the main pagination query.

When associated filters are required

Sometimes an index page really does need filters based on associated records. In that case, broad outer joins followed by DISTINCT are still often the wrong default. A better option is to use EXISTS or ID subqueries.

SELECT primary_records.id
FROM primary_records
WHERE EXISTS (
  SELECT 1
  FROM association_join
  JOIN associated_records
    ON associated_records.id = association_join.associated_record_id
  WHERE association_join.primary_record_id = primary_records.id
    AND associated_records.some_status = 'active'
)
ORDER BY primary_records.sort_timestamp DESC, primary_records.id DESC
LIMIT 20 OFFSET 0;

This expresses the actual requirement: return primary records for which a matching association exists. It avoids returning one row per association and then deduplicating the result.

N+1 after the temp-file fix

Replacing joined eager loading with separate preloading often removes the temp-file problem. It may also expose another problem: associations or model methods that still load data lazily during JSON rendering.

The new logs may look cleaner at the main query level:

SELECT primary_records.*
FROM primary_records
JOIN required_lookup ON ...
WHERE ...
ORDER BY primary_records.sort_timestamp DESC
LIMIT 1 OFFSET 0;

But rendering may still trigger many queries:

SELECT metadata.* FROM metadata WHERE metadata.primary_record_id = ?
SELECT join_table_a.* FROM join_table_a WHERE join_table_a.primary_record_id = ?
SELECT associated_records.* FROM associated_records WHERE associated_records.id = ?
SELECT translations.* FROM translations WHERE translations.parent_id = ? AND translations.lookup_id = ? LIMIT 1

This is a different issue. The temp-file spill was caused by a large joined query. The remaining latency is caused by hidden lazy loads during rendering.

Common causes include:

• the JSON template calls associations not present in the preload tree,
• model methods call find_by on an association instead of using already-loaded in-memory records,
• nested associations are missing from the preload tree,
• external-schema or integration-backed associations are loaded one by one,
• the index serializer renders fields that are more appropriate for a show endpoint.

Using strict loading to find hidden lazy loads

Rails can help identify lazy loads. Temporarily applying strict_loading to the index relation in development or staging makes missing preloads visible.

relation = PrimaryRecord
  .visible
  .with_lookup_filter(...)

relation = relation.strict_loading if Rails.env.development? || Rails.env.staging?

relation
  .order(sort_timestamp: :desc, id: :desc)
  .page(page)
  .per(per_page)
  .preload(...)

If rendering tries to load an association that was not preloaded, Rails can raise instead of silently issuing another query. The fix is then concrete: add the association to the preload tree, change the model method to use loaded records, or stop rendering that field in the index response.

Indexes worth checking

A cleaner query shape still needs supporting indexes. For a filtered, sorted, paginated index, the most useful index often combines the filtering key and the sort key:

CREATE INDEX CONCURRENTLY index_primary_records_listing
ON primary_records (lookup_id, sort_timestamp DESC, id DESC)
WHERE lifecycle_state = 'public'
  AND removed_at IS NULL;

The exact predicate must match the real scopes. A partial index that does not match the query predicate may not be used. The pattern is:

• include the lookup or language/account/tenant filter if it is always used,
• include the sort column and primary key tie-breaker,
• use a partial predicate only for stable, common filters,
• verify with EXPLAIN (ANALYZE, BUFFERS).

For join tables, the usual indexes are:

CREATE INDEX CONCURRENTLY index_join_table_on_primary_record_id
ON join_table (primary_record_id);

CREATE INDEX CONCURRENTLY index_join_table_on_associated_record_id
ON join_table (associated_record_id);

Verify the fix

The before/after check should use both application logs and database-level counters.

1. Confirm the bad SQL disappeared

The index request should no longer produce a query shaped like this:

SELECT DISTINCT primary_records.sort_timestamp, primary_records.id
FROM primary_records
LEFT OUTER JOIN ...
ORDER BY primary_records.sort_timestamp DESC
LIMIT ...

2. Check temp counters before and after a request

SELECT
  now(),
  temp_files,
  pg_size_pretty(temp_bytes) AS temp_bytes,
  stats_reset
FROM pg_stat_database
WHERE datname = current_database();

3. Inspect the plan

EXPLAIN (ANALYZE, BUFFERS)
-- paste the full SQL here

The old plan will often show one or more of:

Unique
Sort Method: external merge  Disk: ...
HashAggregate
Hash Batches: ...
Buffers: temp read=... written=...

The improved plan should process far fewer rows before applying LIMIT, avoid the large DISTINCT, and ideally show no temp reads or writes for the normal page request.

Do not start here

The tempting reaction is to raise work_mem. That can help in controlled cases, but it is not the first fix for a web request with a bad query shape.

For a one-off administrative job, a session-local memory increase can be reasonable:

SET work_mem = '128MB';
-- run the specific operation

For an API endpoint, query shape should be fixed first. Memory tuning should follow measurement, not precede it.

The pattern

The pattern reduces to a reusable rule:

A paginated index should select the page first and load the graph second.

When the graph is loaded first, one-to-many associations multiply rows. Pagination then requires deduplication. Deduplication requires sorting or hashing. Sorting and hashing may spill to temporary files. The database warning is therefore not merely about disk. It is a symptom of a query doing the wrong amount of work at the wrong stage.

The cleaner architecture is:

1. filter the primary records,
2. order by a stable key,
3. limit to the requested page,
4. preload only the associations needed for that response,
5. remove serializer/model-method lazy loads,
6. support the primary selection query with a matching index.

Checklist

• Do not format temp_files as bytes; it is a count.
• Use temp_bytes to understand cumulative temporary data written.
• Find the SQL statement adjacent to the temp-file log line.
• Look for DISTINCT, wide LEFT OUTER JOIN graphs, and pagination in the same query.
• Prefer preload for index endpoints when associated tables are not needed for filtering or ordering.
• Use EXISTS or ID subqueries when associated filters are required.
• Add a deterministic tie-breaker to pagination order.
• Use strict_loading to detect lazy loads during rendering.
• Optimize the count query separately if pagination metadata is expensive.
• Tune memory only after query shape and indexing have been addressed.