引言

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

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

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

1.1 臨時文件的定義

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

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

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

1.2 臨時文件的生命周期

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

注意：臨時文件不寫入WAL，也不參與備份。

二、為什么臨時文件是性能殺手？

2.1 性能影響

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

2.2 資源風(fēng)險

• 磁盤空間耗盡：單個查詢可生成GB級臨時文件；
• inode耗盡：大量小臨時文件可能耗盡文件系統(tǒng)inode；
• SSD壽命損耗：高寫入負(fù)載加速SSD磨損。

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

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

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

-- 查看各數(shù)據(jù)庫的臨時文件使用情況
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ì)重置以來的臨時文件總數(shù)；
• temp_bytes：臨時文件總字節(jié)數(shù)（PG 9.6+ 支持）。

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

3.2 定位具體查詢

方法1：啟用日志記錄

在postgresql.conf中配置：

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

日志示例：

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)臨時文件與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+，早期版本需依賴日志。

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

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

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

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

4.1 work_mem 的作用機(jī)制

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

例如：

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

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 動態(tài)調(diào)整策略

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

適用于ETL、報表等已知高內(nèi)存需求場景。

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

5.1 減少不必要的排序

• 避免SELECT *，只取必要字段；
• 若無需全局排序，改用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，無排序

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

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

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

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

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

5.5 分頁查詢優(yōu)化

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

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ì)算

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

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

-- 查詢直接查物化視圖，無臨時文件
SELECT * FROM daily_sales WHERE date > '2026-01-01';

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

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

6.3 異步處理大查詢

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

6.4 升級硬件：更快的I/O

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

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

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

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

7.1 maintenance_work_mem

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

7.2 effective_cache_size

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

7.3 huge_pages

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

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

8.1 快速定位并終止問題查詢

-- 查找正在寫臨時文件的后端
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)制斷開

8.2 清理殘留臨時文件

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

8.3 磁盤空間告警

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

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

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

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

記住：最好的臨時文件，是從未被寫入的臨時文件。

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

最新評論