PostgreSQL避免寫(xiě)入大量的臨時(shí)文件的解決方案
引言
在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
- 監(jiān)控先行:?jiǎn)⒂?code>log_temp_files,定期檢查
pg_stat_database; - 合理配置work_mem:基于活躍并發(fā)而非max_connections計(jì)算;
- SQL優(yōu)化:利用索引、減少結(jié)果集、避免大排序;
- 動(dòng)態(tài)調(diào)整:按角色/會(huì)話(huà)設(shè)置不同work_mem;
- 架構(gòu)解耦:大查詢(xún)走從庫(kù),使用物化視圖;
- 硬件保障:臨時(shí)文件目錄使用高速SSD;
- 應(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)文章!
相關(guān)文章
PostgreSQL數(shù)據(jù)庫(kù)中to_timestamp函數(shù)用法示例
PostgreSQL 的 to_timestamp 函數(shù)可以將字符串或整數(shù)轉(zhuǎn)換為時(shí)間戳,這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫(kù)中to_timestamp函數(shù)用法的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-08-08
PostgreSQL數(shù)據(jù)庫(kù)備份與恢復(fù)的四種辦法
在數(shù)據(jù)為王的時(shí)代,數(shù)據(jù)庫(kù)中存儲(chǔ)的信息堪稱(chēng)企業(yè)的生命線(xiàn),而PostgreSQL作為一款廣泛應(yīng)用的開(kāi)源數(shù)據(jù)庫(kù),學(xué)會(huì)如何妥善進(jìn)行備份與恢復(fù)操作,是每個(gè)開(kāi)發(fā)者與運(yùn)維人員必備的技能,今天,咱們就深入探究一下PostgreSQL相關(guān)的備份恢復(fù)策略,并附上豐富的代碼示例2025-01-01
PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法
這篇文章主要介紹了PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01
本地計(jì)算機(jī)上的 postgresql 服務(wù)啟動(dòng)后停止的問(wèn)題解決
這篇文章主要介紹了本地計(jì)算機(jī)上的 postgresql 服務(wù)啟動(dòng)后停止的問(wèn)題解決,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
postgresql合并string_agg函數(shù)的實(shí)例
這篇文章主要介紹了postgresql合并string_agg函數(shù)的實(shí)例,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Postgresql根據(jù)響應(yīng)數(shù)據(jù)反向?qū)崿F(xiàn)建表語(yǔ)句與insert語(yǔ)句的過(guò)程
根據(jù)已有數(shù)據(jù),可構(gòu)建名為products的表,包含id(自增主鍵)、title(非空字符串)、progress(非空整數(shù))三個(gè)字段,建表后,可通過(guò)insert語(yǔ)句插入數(shù)據(jù),這種反向操作有助于從現(xiàn)有數(shù)據(jù)結(jié)構(gòu)出發(fā),快速構(gòu)建數(shù)據(jù)庫(kù)表,并進(jìn)行數(shù)據(jù)填充,感興趣的朋友跟隨小編一起看看吧2022-02-02
Windows版?PostgreSQL?利用?pg_upgrade?進(jìn)行大版升級(jí)操作方法
最近?PostgreSQL?15?版本正式發(fā)布了,新版本的各種特性和好處本文就不展開(kāi)介紹了,主要介紹一下?Windows?環(huán)境下?PostgreSQL?大版本升級(jí)的方法,我們現(xiàn)在的幾個(gè)數(shù)據(jù)庫(kù)都是運(yùn)行在?Windows服務(wù)器的?PostgreSQL?14,需要的朋友可以參考下2022-10-10
PostgreSQL拼接字符串的幾種方法簡(jiǎn)單示例
在PostgreSQL中有多種方式可以拼接字符串,這篇文章主要給大家介紹了關(guān)于PostgreSQL拼接字符串的幾種方法,文中通過(guò)代碼示例介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01

