引言

在PostgreSQL的運(yùn)行過(guò)程中，臨時(shí)文件（temporary files）是性能下降和I/O壓力激增的重要信號(hào)。當(dāng)查詢(xún)所需內(nèi)存超過(guò)配置限制時(shí)，PostgreSQL會(huì)將中間數(shù)據(jù)（如排序結(jié)果、哈希表、位圖等）溢出到磁盤(pán)，生成臨時(shí)文件。這些文件不僅顯著拖慢查詢(xún)速度（磁盤(pán)I/O比內(nèi)存慢幾個(gè)數(shù)量級(jí)），還會(huì)占用大量磁盤(pán)空間，甚至導(dǎo)致磁盤(pán)寫(xiě)滿(mǎn)、服務(wù)中斷。

尤其在高并發(fā)或復(fù)雜分析場(chǎng)景下，臨時(shí)文件的爆發(fā)式增長(zhǎng)往往是系統(tǒng)“突然變慢”的根本原因。本文將系統(tǒng)性地解析臨時(shí)文件的產(chǎn)生機(jī)制、監(jiān)控手段、優(yōu)化策略及架構(gòu)級(jí)解決方案，幫助你徹底掌控這一性能隱患。

一、臨時(shí)文件是什么？何時(shí)產(chǎn)生？

1.1 臨時(shí)文件的定義

臨時(shí)文件是PostgreSQL在執(zhí)行SQL過(guò)程中，因內(nèi)存不足而寫(xiě)入pg_tblspc或base/pgsql_tmp目錄下的磁盤(pán)文件，用于存儲(chǔ)無(wú)法完全放入內(nèi)存的中間結(jié)果。常見(jiàn)于以下操作：

• 排序（ORDER BY, DISTINCT, GROUP BY, 窗口函數(shù)）
• 哈希連接（Hash Join）
• 哈希聚合（Hash Aggregate）
• 位圖堆掃描（Bitmap Heap Scan）中的位圖過(guò)大
• 物化CTE 或 子查詢(xún)

這些操作在規(guī)劃階段會(huì)預(yù)估所需內(nèi)存，若實(shí)際需求超過(guò)work_mem，則觸發(fā)磁盤(pán)溢出。

1.2 臨時(shí)文件的生命周期

• 查詢(xún)開(kāi)始時(shí)創(chuàng)建；
• 查詢(xún)結(jié)束（無(wú)論成功或失?。┖笞詣?dòng)刪除；
• 若數(shù)據(jù)庫(kù)異常崩潰，重啟時(shí)會(huì)清理殘留臨時(shí)文件；
• 文件名格式：pgsql_tmp<backend_pid>.<seq>。

注意：臨時(shí)文件不寫(xiě)入WAL，也不參與備份。

二、為什么臨時(shí)文件是性能殺手？

2.1 性能影響

• 延遲飆升：內(nèi)存排序時(shí)間復(fù)雜度 O(n log n)，磁盤(pán)外部排序需多次I/O，延遲增加10–100倍；
• I/O爭(zhēng)用：大量臨時(shí)文件寫(xiě)入與業(yè)務(wù)數(shù)據(jù)I/O競(jìng)爭(zhēng)磁盤(pán)帶寬；
• CPU浪費(fèi)：頻繁的頁(yè)面換入換出消耗CPU資源。

2.2 資源風(fēng)險(xiǎn)

• 磁盤(pán)空間耗盡：?jiǎn)蝹€(gè)查詢(xún)可生成GB級(jí)臨時(shí)文件；
• inode耗盡：大量小臨時(shí)文件可能耗盡文件系統(tǒng)inode；
• SSD壽命損耗：高寫(xiě)入負(fù)載加速SSD磨損。

實(shí)測(cè)案例：
某報(bào)表查詢(xún)?cè)?code>work_mem=4MB時(shí)生成12GB臨時(shí)文件，耗時(shí)8分鐘；調(diào)整至work_mem=512MB后，無(wú)臨時(shí)文件，耗時(shí)僅9秒。

三、監(jiān)控臨時(shí)文件：發(fā)現(xiàn)問(wèn)題是第一步

3.1 查看全局臨時(shí)文件統(tǒng)計(jì)

-- 查看各數(shù)據(jù)庫(kù)的臨時(shí)文件使用情況
SELECT 
    datname,
    temp_files AS temp_files_count,
    pg_size_pretty(temp_bytes) AS temp_bytes_total
FROM pg_stat_database
WHERE datname = 'your_db';

• temp_files：自上次統(tǒng)計(jì)重置以來(lái)的臨時(shí)文件總數(shù)；
• temp_bytes：臨時(shí)文件總字節(jié)數(shù)（PG 9.6+ 支持）。

提示：可通過(guò)pg_stat_reset()重置統(tǒng)計(jì)（謹(jǐn)慎使用）。

3.2 定位具體查詢(xún)

方法1：?jiǎn)⒂萌罩居涗?/h4>

在postgresql.conf中配置：

log_temp_files = 0  # 記錄所有生成臨時(shí)文件的查詢(xún)（單位：KB）
# 或
log_temp_files = 1024  # 僅記錄 >1MB 的臨時(shí)文件

日志示例：

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp12345.0", size 2147483648
STATEMENT:  SELECT * FROM large_table ORDER BY some_column;

方法2：結(jié)合pg_stat_statements

安裝pg_stat_statements擴(kuò)展，關(guān)聯(lián)臨時(shí)文件與SQL：

SELECT 
    query,
    calls,
    total_time,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 10;

注：temp_blks_*字段需PG 13+，早期版本需依賴(lài)日志。

3.3 實(shí)時(shí)監(jiān)控文件系統(tǒng)

# 查看臨時(shí)目錄大小
du -sh $PGDATA/base/pgsql_tmp/

# 監(jiān)控實(shí)時(shí)寫(xiě)入
iotop -p $(pgrep postgres)

四、核心優(yōu)化策略一：合理配置 work_mem

4.1 work_mem 的作用機(jī)制

work_mem 控制單個(gè)操作（非單個(gè)會(huì)話(huà)）可使用的最大內(nèi)存量。一個(gè)查詢(xún)可能包含多個(gè)操作，總內(nèi)存 ≈ 操作數(shù) × work_mem。

例如：

• SELECT ... ORDER BY ... GROUP BY ... → 至少2個(gè)操作；
• 復(fù)雜JOIN + 子查詢(xún) → 可能5個(gè)以上操作。

4.2 安全計(jì)算 work_mem 上限

設(shè)：

• total_ram = 物理內(nèi)存（如 64GB）；
• shared_buffers = 已分配（如 16GB）；
• os_reserve = 預(yù)留OS及其他進(jìn)程（建議20%）；
• max_active_sessions = 實(shí)際活躍并發(fā)連接數(shù)（非max_connections）；
• avg_operations_per_query = 平均操作數(shù)（保守取2–3）。

則：

available_mem = total_ram × 0.8 - shared_buffers
work_mem ≈ available_mem / (max_active_sessions × avg_operations_per_query)

示例：

• 64GB RAM，shared_buffers=16GB；
• 活躍連接=20；
• 則 available_mem ≈ 64×0.8 - 16 = 35.2GB；
• work_mem ≈ 35.2GB / (20 × 2) = 896MB → 可設(shè)為 512MB–1GB。

切勿按max_connections=1000計(jì)算！否則work_mem只能設(shè)為幾MB，失去意義。

4.3 動(dòng)態(tài)調(diào)整策略

• 會(huì)話(huà)級(jí)：SET work_mem = '1GB';
• 用戶(hù)級(jí)：ALTER ROLE analyst SET work_mem = '2GB';
• 事務(wù)級(jí)：BEGIN; SET LOCAL work_mem = '512MB'; ... COMMIT;

適用于ETL、報(bào)表等已知高內(nèi)存需求場(chǎng)景。

五、核心優(yōu)化策略二：優(yōu)化SQL與執(zhí)行計(jì)劃

5.1 減少不必要的排序

• 避免SELECT *，只取必要字段；
• 若無(wú)需全局排序，改用LIMIT + 索引；
• 使用UNION ALL代替UNION（避免去重排序）。

5.2 利用索引避免排序

-- 低效：全表掃描 + 排序
SELECT id, name FROM users ORDER BY created_at DESC LIMIT 10;

-- 高效：創(chuàng)建索引
CREATE INDEX idx_users_created ON users(created_at DESC);
-- 執(zhí)行計(jì)劃變?yōu)?Index Scan Backward，無(wú)排序

5.3 控制GROUP BY與DISTINCT規(guī)模

• 先過(guò)濾再聚合：WHERE條件提前；
• 使用GROUP BY字段的前綴索引；
• 對(duì)超高基數(shù)列（如UUID）慎用DISTINCT。

5.4 避免大結(jié)果集的哈希操作

• 哈希連接在右表過(guò)大時(shí)易溢出；
• 可強(qiáng)制使用嵌套循環(huán)（Nested Loop）或合并連接（Merge Join）：

SET enable_hashjoin = off;
-- 僅用于測(cè)試，生產(chǎn)需謹(jǐn)慎

5.5 分頁(yè)查詢(xún)優(yōu)化

• 避免OFFSET 100000 LIMIT 10（需跳過(guò)10萬(wàn)行）；
• 改用游標(biāo)（Cursor）或基于主鍵的分頁(yè)：

SELECT * FROM logs 
WHERE id > last_seen_id 
ORDER BY id 
LIMIT 10;

六、核心優(yōu)化策略三：架構(gòu)與設(shè)計(jì)層面優(yōu)化

6.1 使用物化視圖預(yù)計(jì)算

對(duì)高頻復(fù)雜聚合，定期刷新物化視圖：

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date, sum(amount) FROM orders GROUP BY date;

-- 查詢(xún)直接查物化視圖，無(wú)臨時(shí)文件
SELECT * FROM daily_sales WHERE date > '2026-01-01';

6.2 分區(qū)表減少掃描范圍

• 按時(shí)間分區(qū)，查詢(xún)自動(dòng)剪枝；
• 每個(gè)分區(qū)數(shù)據(jù)量小，排序/聚合內(nèi)存需求降低。

6.3 異步處理大查詢(xún)

• 將報(bào)表、導(dǎo)出等任務(wù)移至從庫(kù)；
• 使用消息隊(duì)列解耦，避免沖擊主庫(kù)。

6.4 升級(jí)硬件：更快的I/O

• 臨時(shí)文件無(wú)法完全避免時(shí)，使用NVMe SSD可大幅降低I/O延遲；
• 將temp_tablespaces指向高速磁盤(pán)：

-- 創(chuàng)建專(zhuān)用表空間
CREATE TABLESPACE fasttmp LOCATION '/ssd/pgsql_tmp';

-- 設(shè)置臨時(shí)文件路徑
SET temp_tablespaces = 'fasttmp';

七、其他相關(guān)參數(shù)調(diào)優(yōu)

7.1 maintenance_work_mem

• 影響CREATE INDEX、VACUUM等維護(hù)操作；
• 雖不直接影響查詢(xún)臨時(shí)文件，但索引構(gòu)建快可減少后續(xù)查詢(xún)負(fù)載；
• 建議：1–4GB（不超過(guò)物理內(nèi)存25%）。

7.2 effective_cache_size

• 僅為規(guī)劃器提示，不影響實(shí)際內(nèi)存；
• 設(shè)高值（如物理內(nèi)存75%）可鼓勵(lì)使用索引，間接減少排序。

7.3 huge_pages

• 啟用大頁(yè)可提升內(nèi)存訪(fǎng)問(wèn)效率，間接改善大內(nèi)存操作性能；
• 需操作系統(tǒng)配合（Linux: vm.nr_hugepages）。

八、臨時(shí)文件應(yīng)急處理

8.1 快速定位并終止問(wèn)題查詢(xún)

-- 查找正在寫(xiě)臨時(shí)文件的后端
SELECT pid, query, state, backend_start
FROM pg_stat_activity
WHERE query LIKE '%ORDER BY%' OR query LIKE '%GROUP BY%';

-- 終止
SELECT pg_cancel_backend(pid);  -- 優(yōu)雅取消
-- 或
SELECT pg_terminate_backend(pid); -- 強(qiáng)制斷開(kāi)

8.2 清理殘留臨時(shí)文件

• 正常情況下PostgreSQL自動(dòng)清理；
• 若崩潰后殘留，可手動(dòng)刪除$PGDATA/base/pgsql_tmp/下文件（確保DB已停止）。

8.3 磁盤(pán)空間告警

• 監(jiān)控pg_tblspc和base/pgsql_tmp目錄大??；
• 設(shè)置閾值告警（如>80%）。

總結(jié)：避免臨時(shí)文件的Checklist

1. 監(jiān)控先行：?jiǎn)⒂?code>log_temp_files，定期檢查pg_stat_database；
2. 合理配置work_mem：基于活躍并發(fā)而非max_connections計(jì)算；
3. SQL優(yōu)化：利用索引、減少結(jié)果集、避免大排序；
4. 動(dòng)態(tài)調(diào)整：按角色/會(huì)話(huà)設(shè)置不同work_mem；
5. 架構(gòu)解耦：大查詢(xún)走從庫(kù)，使用物化視圖；
6. 硬件保障：臨時(shí)文件目錄使用高速SSD；
7. 應(yīng)急機(jī)制：具備快速定位和終止能力。

臨時(shí)文件是PostgreSQL內(nèi)存管理機(jī)制的“安全閥”，但頻繁觸發(fā)意味著系統(tǒng)處于亞健康狀態(tài)。通過(guò)科學(xué)配置、精細(xì)優(yōu)化與主動(dòng)監(jiān)控，完全可以將臨時(shí)文件控制在極低水平，保障系統(tǒng)穩(wěn)定高效運(yùn)行。

記住：最好的臨時(shí)文件，是從未被寫(xiě)入的臨時(shí)文件。

以上就是PostgreSQL避免寫(xiě)入大量的臨時(shí)文件的解決方案的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL避免寫(xiě)入臨時(shí)文件的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！