PostgreSQL 組態調校：從預設值到生產級效能 | PostgreSQL
2026/07/10
PostgreSQL 預設組態刻意設得極為保守——它必須能在 256MB RAM 的環境上啟動。這意味著在生產環境中，幾乎 每一台 資料庫伺服器都需要根據硬體規格與工作負載進行系統性調校。正確的 組態調校（Configuration Tuning） 可使效能提升數倍，而錯誤的設定則可能導致 OOM、Checkpoint 風暴與查詢計劃退化。
組態層級與生效機制
PostgreSQL 提供四個層級的組態覆蓋機制，優先順序由低到高：
優先順序（由低到高）：
1. postgresql.conf ← 主設定檔，DBA 手動編輯
↓ 被覆蓋
2. postgresql.auto.conf ← ALTER SYSTEM 寫入，不可手動編輯
↓ 被覆蓋
3. SET session_level_param ← 會話/事務級別設定
↓ 被覆蓋
4. 環境變數（PGPASSWORD 等）← 特定參數適用
postgresql.auto.conf 的設定永遠覆蓋 postgresql.conf 中的同名參數。因此若你用 ALTER SYSTEM 設定了某參數，直接修改 postgresql.conf 的對應行將不會生效：
-- 查看某參數的當前值與來源
SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'shared_buffers';
-- 清除 ALTER SYSTEM 的設定（恢復 postgresql.conf 的值）
ALTER SYSTEM RESET shared_buffers;
ALTER SYSTEM RESET ALL; -- 清除所有 ALTER SYSTEM 設定
參數分類（Context）
每個參數都有一個 context 屬性，決定修改後如何生效：
| Context | 說明 | 生效方式 |
|---|---|---|
postmaster | 影響整個伺服器啟動行為 | 需要 完整重啟（restart） |
sighup | 可動態重載 | pg_reload_conf() 即可 |
superuser | Superuser 可在會話中修改 | SET 命令即時生效 |
user | 一般使用者可在會話中修改 | SET 命令即時生效 |
backend | 新連線建立時生效 | 需新連線 |
internal | 編譯時決定，不可修改 | 唯讀 |
-- 查詢需要 restart 的參數
SELECT name, setting, context
FROM pg_settings
WHERE context = 'postmaster'
ORDER BY name;
-- 找出所有需要 restart 的已修改參數
SELECT name, setting, reset_val, pending_restart
FROM pg_settings
WHERE pending_restart = true;
組態生效方式
# 方法一：SQL 函數（最方便，可在 psql 中執行）
psql -c "SELECT pg_reload_conf();"
# 方法二：pg_ctl reload（等同方法一）
pg_ctl reload -D /var/lib/postgresql/data
# 方法三：systemctl reload（推薦生產環境）
systemctl reload postgresql
# 方法四：完整重啟（postmaster 類參數必須）
systemctl restart postgresql
三者本質相同——都是向 postmaster 發送 SIGHUP 信號。差別只在觸發方式，效果完全一致。
記憶體參數：第一優先事項
記憶體設定是 PostgreSQL 調校的第一步，也是生產事故的最常見根源之一。
shared_buffers：共享緩衝池
shared_buffers 是所有連線共同使用的共享緩衝池，也是單一影響最大的參數。
# postgresql.conf — 起點：實體 RAM 的 25%
shared_buffers = 2GB # 8GB RAM
shared_buffers = 8GB # 32GB RAM
shared_buffers = 16GB # 64GB RAM
shared_buffers = 32GB # 128GB RAM
調校原則：
- 起點：設定為 RAM 的 25%
- 上限：通常不超過 RAM 的 40%，剩餘空間留給 OS Page Cache
- 效益遞減：超過 40% RAM 的
shared_buffers在多數場景下效益遞減 - 生效方式：需要 重啟（context:
postmaster）
-- 查看 Buffer Cache 命中率（應 > 99%）
SELECT
sum(heap_blks_read) AS heap_read,
sum(heap_blks_hit) AS heap_hit,
ROUND(
sum(heap_blks_hit)::numeric /
NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
2
) AS cache_hit_ratio
FROM pg_statio_user_tables;
若命中率低於 99%，可能需要增加 shared_buffers，但也要考慮是否存在全表掃描的查詢需要優化。
work_mem：排序與 Hash 操作記憶體
work_mem 控制每個排序、Hash Join、Hash Aggregate 操作可使用的記憶體量。它是 最容易被誤解 的參數——因為一個查詢可能同時使用多個 work_mem。
# 謹慎設定：總記憶體消耗 ≈ max_connections × 並行操作數 × work_mem
work_mem = 4MB # 預設值，過於保守
work_mem = 16MB # OLTP 一般建議
work_mem = 64MB # 複雜分析查詢
work_mem = 256MB # 大型 DW 查詢（需控制並發數）
安全估算公式：
可用記憶體 = RAM - shared_buffers - OS 保留記憶體
並發記憶體 = max_connections × 平均操作數（約 3-5）× work_mem
範例（32GB RAM，shared_buffers = 8GB）：
可用 ≈ 32GB - 8GB - 4GB(OS) = 20GB
若 max_connections = 100，平均 4 個操作：
work_mem ≤ 20GB / (100 × 4) = 50MB → 設定 32MB 較安全
進階技巧——會話級別動態調整：
-- 批次處理前臨時提升 work_mem
SET work_mem = '512MB';
-- 執行複雜分析查詢
EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;
-- 重置為預設值
RESET work_mem;
這種模式特別適合 ETL 作業或報表查詢——全域保持保守值，特定場景臨時放大。
maintenance_work_mem：維護操作記憶體
maintenance_work_mem 用於 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 等維護操作。
# 可設定較大，因為維護操作通常不並發
maintenance_work_mem = 256MB # 一般建議
maintenance_work_mem = 1GB # 大表快速建 Index
maintenance_work_mem = 2GB # 超大表維護
# 自動 VACUUM Worker 的記憶體限制（獨立控制）
autovacuum_work_mem = -1 # -1 表示使用 maintenance_work_mem
autovacuum_work_mem = 128MB # 單獨設定，避免多個 Worker 吃光記憶體
-- 臨時提升以加速建 Index
SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY idx_orders_created ON orders(created_at);
effective_cache_size：Query Planner 的提示
effective_cache_size 不分配任何實際記憶體——它只是告訴 Query Planner：「OS Page Cache 大約有這麼多」。Planner 據此判斷 Index Scan 的成本是否划算。
# 估算：RAM - shared_buffers - 其他程序記憶體
# 一般設定為 RAM 的 50%-75%
effective_cache_size = 6GB # 8GB RAM
effective_cache_size = 24GB # 32GB RAM
effective_cache_size = 48GB # 64GB RAM
effective_cache_size = 96GB # 128GB RAM
- 設定偏低 → Planner 高估 Index Scan 成本 → 傾向 Seq Scan
- 設定偏高 → Planner 低估 Index Scan 成本 → 傾向 Index Scan
huge_pages：大頁面（Linux）
在 Linux 環境使用 Huge Pages 可減少 TLB（Translation Lookaside Buffer）miss，對大 shared_buffers 的機器效益顯著。
huge_pages = try # 預設：嘗試使用，不成功則繼續
huge_pages = on # 強制使用（啟動失敗若 OS 未設定）
huge_pages = off # 停用（容器環境通常需要）
# Linux 設定 Huge Pages（以 shared_buffers=8GB 為例）
# 計算所需 pages 數：8GB / 2MB = 4096 pages（加 10% 緩衝）
echo 4500 > /proc/sys/vm/nr_hugepages
# 持久化
echo "vm.nr_hugepages = 4500" >> /etc/sysctl.conf
WAL 參數：寫入效能與持久性
WAL 參數控制資料持久性與 Checkpoint 行為，對寫入密集的工作負載影響極大。
Checkpoint 控制
Checkpoint 將 Dirty Buffer 刷到磁碟。設定不當會造成週期性 I/O 風暴：
# 延長 Checkpoint 間隔，減少 I/O 壓力
checkpoint_timeout = 15min # 預設 5min，建議 15-30min
checkpoint_timeout = 30min # 寫入密集環境
# Checkpoint 在 timeout 的多少比例內完成（分散 I/O）
checkpoint_completion_target = 0.9 # 預設 0.9，通常不需調整
# 例：timeout=15min, target=0.9 → 在 13.5min 內分散完成
# WAL 佔用的磁碟空間上限（Checkpoint 觸發條件之一）
max_wal_size = 4GB # 預設 1GB，建議 4-16GB
max_wal_size = 16GB # 大量寫入環境
# WAL 回收的下限（避免頻繁分配/刪除 WAL 檔案）
min_wal_size = 512MB # 預設 80MB，建議 512MB-2GB
WAL Buffer 與 WAL Level
# WAL Buffer：記錄在寫入磁碟前的緩衝
wal_buffers = -1 # 預設自動設定為 shared_buffers 的 1/32
wal_buffers = 16MB # 高並發寫入環境建議明確設定
# WAL Level：控制記錄詳細程度
wal_level = minimal # 最少資訊，不支援 Replication
wal_level = replica # 預設，支援 Streaming Replication
wal_level = logical # 支援 Logical Replication（需要時才開）
Checkpoint 健康診斷
-- 查看 Checkpoint 統計
SELECT
checkpoints_timed, -- 時間觸發的 Checkpoint 次數
checkpoints_req, -- WAL 量觸發的 Checkpoint 次數
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_backend -- 後端直接寫入的 Buffer 數（應盡量低）
FROM pg_stat_bgwriter;
警告信號：若 checkpoints_req 遠大於 checkpoints_timed，表示 WAL 增長速度超過 max_wal_size，需要調大。
連線參數
max_connections：少即是多
max_connections 設定過大是最常見的反模式。過多的連線反而會降低效能——每個連線消耗約 5-10MB 記憶體，且大量連線競爭鎖、CPU 排程與 Buffer 會導致整體吞吐量下降。
# 參考值（搭配連線池）
max_connections = 100 # 小型應用
max_connections = 200 # 中型應用
max_connections = 500 # 搭配 PgBouncer（最外層控制）
# 建議公式：CPU 核心數 × 4（純 OLTP）
# 8C 機器：max_connections ≈ 32-50（實際查詢）+ 20（備用）= 100
最佳架構：
應用程式 → PgBouncer（連線池）→ PostgreSQL
100 個應用連線 10-20 個實際 DB 連線
閒置連線保護
# PG14+ 強烈建議啟用
idle_in_transaction_session_timeout = 30s # 閒置事務 30 秒後終止
idle_session_timeout = 600s # 完全閒置連線 10 分鐘後終止
# 查詢與鎖的超時保護
statement_timeout = 30000 # 查詢 30 秒超時（單位毫秒）
lock_timeout = 5000 # 等鎖 5 秒後放棄
# 保留給 Superuser 的連線槽
superuser_reserved_connections = 3 # 確保 DBA 隨時能連入
-- 檢查當前閒置事務
SELECT pid, usename, state,
now() - state_change AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;
Query Planner 參數：成本模型校準
Query Planner 使用成本模型選擇執行計劃。這些參數調整成本估算，間接影響計劃選擇。
SSD 環境必調：random_page_cost
# 預設值（假設傳統 HDD）
seq_page_cost = 1.0 # 順序讀取成本（基準）
random_page_cost = 4.0 # 隨機讀取成本（預設 4 倍）
# SSD 環境強烈建議
random_page_cost = 1.1 # SSD 隨機存取接近順序存取
# NVMe SSD
random_page_cost = 1.0 # 與順序讀取幾乎相同
在 SSD 環境不調整 random_page_cost 是最常見的調校遺漏。 預設值 4.0 會讓 Planner 高估 Index Scan 成本，導致本應走索引的查詢退化為全表掃描。
I/O 併發與統計精度
# I/O 併發（Bitmap Heap Scan 使用）
effective_io_concurrency = 1 # HDD（預設）
effective_io_concurrency = 200 # SSD 建議
effective_io_concurrency = 500 # NVMe RAID
# 維護操作的 I/O 並發
maintenance_io_concurrency = 10 # HDD
maintenance_io_concurrency = 200 # SSD
# 統計精度
default_statistics_target = 100 # 預設
default_statistics_target = 200 # 提升估算精準度（ANALYZE 變慢）
-- 針對特定欄位提高統計精度
ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE users
ALTER COLUMN country SET STATISTICS 300;
Logging 設定：排障的基礎
合理的日誌設定是排障的基礎，但過量日誌本身也會成為效能瓶頸。
慢查詢記錄（最有價值的單一設定）
# 記錄執行超過 1 秒的查詢
log_min_duration_statement = 1000 # 單位毫秒
log_min_duration_statement = 500 # 更嚴格
log_min_duration_statement = 0 # 記錄所有查詢（僅調試用！）
# PG13+：隨機抽樣（降低日誌量）
log_min_duration_sample = 500 # 超過 500ms 才進入抽樣池
log_statement_sample_rate = 0.1 # 抽 10% 記錄
日誌格式與輸出
# 日誌格式
log_destination = 'stderr' # 預設
log_destination = 'csvlog' # CSV 格式，方便程式解析
log_destination = 'jsonlog' # JSON 格式，PG15+，適合 ELK Stack
# 啟用日誌收集
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
# 日誌前綴（生產環境建議）
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
其他重要日誌參數
log_statement = 'ddl' # 只記錄 DDL（CREATE/DROP/ALTER）
log_connections = on # 記錄新連線
log_disconnections = on # 記錄斷線（含會話時長）
log_lock_waits = on # 等鎖超過 deadlock_timeout 時記錄
log_temp_files = 10MB # 記錄超過 10MB 的臨時檔案
log_autovacuum_min_duration = 1000 # 記錄超過 1 秒的 Autovacuum
常見陷阱：生產環境千萬不要設定 log_statement = 'all'——日誌量會爆炸，磁碟很快就滿。用 log_min_duration_statement 取代才是正解。
PGTune：自動化起點
PGTune 是 PostgreSQL 組態生成工具，根據硬體規格與工作負載類型自動生成推薦設定。
工作負載 Profile
| Profile | 說明 | 典型場景 |
|---|---|---|
| Web | 大量短查詢，高並發讀寫 | Web 後端、API 服務 |
| OLTP | 線上交易處理，混合讀寫 | ERP、電商交易系統 |
| DW | 複雜分析查詢，大量掃描 | 報表系統、BI 平台 |
| Mixed | 混合 OLTP 與分析 | 通用應用 |
| Desktop | 單使用者工作站 | 開發/測試環境 |
PGTune 輸出範例
以下為 32GB RAM、8C、SSD、OLTP 的 PGTune 建議：
max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 20971kB # 約 20MB
huge_pages = try
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
重要提醒：PGTune 的輸出是起點，不是終點。需根據實際查詢模式與監控數據進行迭代調整。
不同規格的調校模板
以下為各規格的 OLTP 工作負載起始模板：
4C8G（小型/開發環境）
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 8MB
maintenance_work_mem = 256MB
max_connections = 50
max_wal_size = 2GB
min_wal_size = 256MB
checkpoint_timeout = 10min
random_page_cost = 1.1
effective_io_concurrency = 100
max_worker_processes = 4
max_parallel_workers = 2
max_parallel_workers_per_gather = 1
8C32G（中型生產環境）
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 1GB
max_connections = 100
max_wal_size = 8GB
min_wal_size = 1GB
checkpoint_timeout = 15min
wal_buffers = 16MB
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
huge_pages = try
16C64G（大型生產環境）
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 64MB
maintenance_work_mem = 2GB
autovacuum_work_mem = 512MB
max_connections = 200
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 20min
wal_buffers = 64MB
random_page_cost = 1.1
effective_io_concurrency = 300
max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
huge_pages = on
32C128G（超大型/DW 環境）
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 128MB
maintenance_work_mem = 4GB
autovacuum_work_mem = 1GB
max_connections = 100 # DW 並發低
max_wal_size = 32GB
min_wal_size = 4GB
checkpoint_timeout = 30min
wal_buffers = 64MB
random_page_cost = 1.0 # NVMe
effective_io_concurrency = 500
max_worker_processes = 32
max_parallel_workers = 16
max_parallel_workers_per_gather = 8
max_parallel_maintenance_workers = 8
huge_pages = on
default_statistics_target = 200
SSD vs HDD 調校差異
HDD 環境： SSD 環境：
random_page_cost = 4.0 random_page_cost = 1.1
effective_io_concurrency = 2 effective_io_concurrency = 200
↑ 隨機 I/O 昂貴，傾向 Seq Scan ↑ 隨機 I/O 便宜，傾向 Index Scan
SSD 環境另需注意：
checkpoint_completion_target = 0.9：分散寫入避免 SSD 寫入放大- 定期
VACUUM更重要：SSD 的碎片化寫入模式需要更及時的清理
容器環境特殊考量
在 Docker/Kubernetes 環境中，PostgreSQL 無法直接感知 Container 的記憶體限制：
# 根據 Container Memory Limit 設定，而非 Node RAM
# 若 Container Memory Limit = 4GB：
shared_buffers = 1GB # Container Limit 的 25%
effective_cache_size = 3GB # Container Limit 的 75%
work_mem = 16MB # 保守設定
huge_pages = off # 容器通常無權使用
max_connections = 50 # 搭配 PgBouncer
# Kubernetes — Memory Request 必須大於 shared_buffers
# 否則 OOM Killer 會在記憶體壓力時殺掉 PostgreSQL
resources:
requests:
memory: "4Gi" # 至少 > shared_buffers
limits:
memory: "4Gi" # 建議 Request = Limit（Guaranteed QoS）
常見陷阱速查表
| 陷阱 | 現象 | 解法 |
|---|---|---|
shared_buffers 設太大 | OOM Killer 殺掉 PostgreSQL | 不超過 RAM 的 40% |
work_mem 設太大 | 高並發時 OOM | 降低全域值，用 SET 處理大查詢 |
max_connections 設太高 | 連線開銷拖慢效能 | 搭配 PgBouncer，降低 max_connections |
未調 random_page_cost（SSD） | Planner 偏愛 Seq Scan | 設定 1.1 |
conf 與 auto.conf 衝突 | 修改 conf 無效 | 用 pg_settings 確認來源 |
| 忘記 reload | 修改後參數未生效 | pg_reload_conf() + pending_restart 檢查 |
| Checkpoint 風暴 | 週期性 I/O 飆高 | 調大 checkpoint_timeout 與 max_wal_size |
log_statement = 'all' 上生產 | 日誌爆炸，磁碟滿 | 改用 log_min_duration_statement |
組態管理最佳實踐
-- 查詢當前值與預設值的差異
SELECT name, setting, boot_val, source
FROM pg_settings
WHERE setting != boot_val
AND source NOT IN ('default', 'override')
ORDER BY name;
-- 查看所有修改過的參數及其來源
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY source, name;
實務建議：
- 手動管理：使用
postgresql.conf，版控友善 - 自動化/腳本：使用
ALTER SYSTEM，無需編輯檔案 - 不要混用：同一參數只在一個地方修改，避免
auto.conf靜默覆蓋
版本演進
| 版本 | 組態相關改動 |
|---|---|
| PG9.4 | ALTER SYSTEM 指令正式引入 |
| PG10 | log_transaction_sample_rate；平行查詢擴展 |
| PG12 | recovery.conf 整合進 postgresql.conf |
| PG13 | log_min_duration_sample；Autovacuum 改進 |
| PG14 | idle_in_transaction_session_timeout；jsonlog |
| PG15 | JSON 格式日誌完整支援；recovery_prefetch |
| PG16 | allow_alter_system（可禁用 ALTER SYSTEM）；IO 統計 |
| PG17 | maintenance_io_concurrency 影響範圍擴大 |
總結
PostgreSQL 組態調校的核心原則：
- 記憶體第一：
shared_buffers（25% RAM）、work_mem（安全估算）、effective_cache_size（50-75% RAM） - SSD 必調：
random_page_cost = 1.1、effective_io_concurrency = 200 - WAL 要寬：
max_wal_size調大、checkpoint_timeout延長 - 連線要少：搭配連線池，
max_connections不要盲目加大 - 日誌要精：用
log_min_duration_statement，不用log_statement = 'all' - PGTune 起步：用工具生成基準，再根據監控迭代
下一篇，我們將探討 PostgreSQL 擴充模組生態——從 pg_stat_statements 到 PostGIS、pgvector，了解如何透過擴充模組為 PostgreSQL 賦予更強大的能力。