Temp Table and Temporary File Management

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you diagnose whether you need to increase `work_mem` or optimize queries. High `temp_bytes` means queries are spilling to disk when running operations like sorts, hashes, and aggregations. ## Diagnostic Steps ### 1. Identify which queries are using temp space ```sql -- Find queries with the most temp usage (requires pg_stat_statements) SELECT query, calls, temp_blks_written, temp_blks_read, pg_size_pretty(temp_blks_written * 8192::bigint) AS temp_written, ROUND((100 * temp_blks_written / NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS temp_pct FROM pg_stat_statements WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 20; ``` ### 2. Check current work_mem and see actual query memory usage ```sql -- Current work_mem setting SHOW work_mem; -- For specific slow queries, run EXPLAIN (ANALYZE, BUFFERS) EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <your slow query here>; ``` Look for these indicators in the execution plan: - **"Sort Method: external merge Disk:"** - sorting spilled to disk - **"Hash Buckets: ... Batches: > 1"** - hash join spilled to disk - **"Temp File:"** lines showing disk usage ### 3. Check overall temp usage patterns ```sql -- Database-level temp usage SELECT datname, pg_size_pretty(temp_bytes) as temp_size, temp_files, ROUND(temp_bytes::numeric / NULLIF(temp_files, 0), 2) AS avg_temp_file_size FROM pg_stat_database WHERE temp_files > 0 ORDER BY temp_bytes DESC; ``` ## Decision Framework **Increase work_mem if:** - Only a few specific queries cause the issue - Queries are well-written but processing large result sets - You have available RAM (remember: work_mem × max_connections = potential max usage) - Execution plans show operations *just barely* spilling to disk **Optimize queries if:** - Many different queries are spilling - Queries lack proper indexes - Seeing operations like "Sequential Scan" on large tables - Queries have unnecessary JOINs, subqueries, or are selecting too many rows - Hash joins have very unbalanced bucket distributions ## Safe Approach 1. **Start with query optimization** - it's free and often more effective 2. **Test work_mem per session** before changing globally: ```sql SET work_mem = '256MB'; -- test in your session -- run your query ``` 3. **Increase work_mem gradually** - start with 2-4x current value for specific problematic queries using: ```sql ALTER USER your_app_user SET work_mem = '128MB'; -- per-user -- or in application connection string ``` Would you like me to help analyze specific queries or set up monitoring to track which queries are the culprits?

I'll help you diagnose whether this is a work_mem configuration issue or a query optimization problem. Here's a systematic approach: ## 1. First, identify which queries are creating temp files Run this to see current temp file usage by query: ```sql SELECT query, calls, total_exec_time / 1000 / 60 AS total_min, mean_exec_time / 1000 AS mean_sec, temp_blks_written, temp_blks_written * 8192 / 1024 / 1024 AS temp_mb, (temp_blks_written * 8192 / 1024 / 1024) / calls AS temp_mb_per_call FROM pg_stat_statements WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 20; ``` ## 2. Check what operations are causing temp file usage For the top queries, run `EXPLAIN (ANALYZE, BUFFERS)` to see: ```sql EXPLAIN (ANALYZE, BUFFERS) <your slow query here>; ``` Look for these patterns in the output: **Indicates work_mem issue:** - `Sort Method: external merge Disk: XXXkB` - sorting spilling to disk - `Hash Buckets: XXX Batches: YYY` where Batches > 1 - hash join spilling - `Work_mem: XXXkB` shown as insufficient **Indicates query optimization needed:** - `Seq Scan` on large tables (missing indexes) - `Hash Join` or `Nested Loop` with massive row estimates - `Sort` on millions of rows that could be avoided with an index - Large difference between estimated rows and actual rows ## 3. Decision criteria **Increase work_mem if:** - Queries are generally well-optimized (using indexes appropriately) - Temp usage is modest per query (< 100MB per call) - Only a few concurrent queries - You have available RAM (work_mem × max_connections should be < 25% of total RAM) **Optimize queries if:** - Missing obvious indexes on JOIN/WHERE/ORDER BY columns - Returning/processing far more rows than needed - Temp usage is massive (> 500MB per query) - Many concurrent queries (increasing work_mem would exhaust RAM) ## 4. Safe work_mem tuning approach Instead of changing global work_mem, set it per-query or per-session: ```sql -- For a specific session SET work_mem = '256MB'; -- Or in the query itself SET LOCAL work_mem = '256MB'; SELECT ...; ``` This lets you test without risking OOM from many concurrent queries. --- **Can you share the output of the first query?** That will tell us exactly what's happening and whether this is a few problematic queries or a systemic issue.

Check `postgresql-temp-bytes` and `postgresql-temp-files` in pg_stat_database to establish a baseline. If you're seeing hundreds of GB in temp_bytes or thousands of temp files per hour, you have a real problem. The `excessive-temporary-file-creation` pattern confirms this is causing disk I/O instead of memory-based operations. Look at the rate of growth — is it consistent or spiking with specific workloads?

Query pg_stat_statements ordered by temp_blks_written to find the offenders. Look at `postgresql-queries-temp-blks-written` to see which queries are writing the most temporary blocks. A single query writing millions of blocks is a different problem than many queries each writing a few thousand. Also check `postgresql-queries-duration-max` to correlate slow queries with temp file usage — queries that both run long and write temp files are your primary targets.

Run EXPLAIN (ANALYZE, BUFFERS) on the problematic queries. Look for 'Sort Method: external merge' or 'Hash Buckets: ... Batches: X' where X > 1 — these indicate disk-based operations. The `insufficient-work-mem` insight explains that sorts, aggregations, and hash joins spill to disk when work_mem is too low. Pay attention to the 'Disk:' line in EXPLAIN output showing exactly how much temp space was used.

Before changing anything globally, SET work_mem = '128MB' (or higher) for a specific session and re-run the problematic query with EXPLAIN (ANALYZE, BUFFERS). If the temp file usage drops to zero and the query speeds up significantly, you've confirmed work_mem is the issue. The `need-for-per-query-work-mem-tuning` pattern suggests this is often the right solution — tune work_mem per-query or per-session rather than globally. Reset work_mem afterward to avoid session memory bloat.

Even if increasing work_mem helps, check whether the query itself is inefficient. Missing indexes on JOIN or WHERE clauses force large hash joins or nested loops that consume memory. Unnecessary ORDER BY clauses or redundant sorts can be eliminated. The `queries-need-optimization-to-reduce-temp-usage` insight reminds us that fixing the root cause — the query — is better than just throwing memory at it. Can you add an index to avoid a sort, or rewrite a CTE to reduce the working set?

The danger of increasing work_mem is that PostgreSQL can allocate up to work_mem × max_connections in the worst case. If you have 200 connections and set work_mem to 256MB, you could theoretically need 50GB of RAM. The `risk-of-memory-over-allocation-if-work-mem-increased` insight highlights this tradeoff. For OLTP workloads with many connections, keep work_mem modest (16-64MB) and use per-query tuning. For OLAP workloads with few connections, you can safely go higher (128MB+).

Check `postgresql-io-write-time` to see if temp file creation is causing I/O contention. High write times correlate with temp file spikes indicate your storage is struggling to keep up. The `disk-i-o-impact-from-temp-files` pattern shows that excessive temp files don't just slow the query itself — they can degrade overall system performance by saturating disk bandwidth. If I/O wait times are high, prioritizing work_mem increases or query optimization becomes even more urgent.