PostgreSQL 效能調優完全指南

数据库

為什麼 2026 年選擇 PostgreSQL？

PostgreSQL 在 2026 年已經穩居全球最受歡迎的開源關聯式資料庫之首。憑藉其強大的擴展性、豐富的資料型別和卓越的 SQL 標準相容性，它已成為從新創公司到大型企業的首選資料庫。

PostgreSQL 核心優勢

維度 PostgreSQL MySQL SQL Server Oracle
擴展性 極強（擴展框架） 中等 中等 強
資料型別 極豐富（JSONB、GIS、陣列） 中等 豐富 豐富
SQL 標準相容 最接近標準 中等 高 高
開源 完全開源（PostgreSQL License） GPL 部分開源 閉源
並行控制 MVCC（優秀） MVCC MVCC MVCC
分區能力 原生宣告式分區 原生分區 原生分區 原生分區

2026 年 PostgreSQL 的關鍵進展

  1. PostgreSQL 18：平行查詢進一步增強，邏輯複製效能大幅提升
  2. 增量排序（Incremental Sort）優化：多欄排序場景效能提升 3-5 倍
  3. JSON 路徑表達式增強：更強大的 JSONB 查詢與索引支援
  4. 非同步 I/O 支援：底層 I/O 架構改進，大幅提升高並行場景吞吐
  5. 邏輯複製改進：支援 DDL 複製，大幅簡化資料同步架構

💡 使用 SQL 格式化 工具美化 PostgreSQL 查詢語句，便於分析執行計畫。

EXPLAIN ANALYZE 深度解析

EXPLAIN ANALYZE 是 PostgreSQL 效能調優最核心的工具，它不僅展示查詢計畫，還執行查詢並返回實際耗時統計。

基礎用法

-- 檢視執行計畫（不執行）
EXPLAIN SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- 執行查詢並返回實際統計
EXPLAIN ANALYZE SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- 檢視詳細輸出（含緩衝區統計）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'shipped';

掃描型別詳解

順序掃描（Seq Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 1000;
-- Seq Scan on orders  (cost=0.00..15432.00 rows=100000 width=88)
--   Filter: (amount > 1000)
  • 適用場景：表資料量小、查詢返回大部分行、無合適索引
  • 優化方向：若返回行數 < 總行數 5%，考慮新增索引

索引掃描（Index Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 12345;
-- Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=88)
--   Index Cond: (order_id = 12345)
  • 適用場景：等值查詢、高選擇性條件、返回少量行
  • 優化方向：確保索引欄順序與查詢條件匹配

點陣圖掃描（Bitmap Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
-- Bitmap Heap Scan on orders  (cost=523.12..8234.56 rows=15000 width=88)
--   Recheck Cond: (status = 'shipped')
--   -> Bitmap Index Scan on idx_orders_status  (cost=0.00..519.37 rows=15000)
  • 適用場景：返回中等數量行（表的 5%-15%）、多條件組合查詢
  • 優化方向：Bitmap Index Scan + Bitmap Heap Scan 組合，適合中等選擇性

連線型別詳解

巢狀迴圈連線（Nested Loop）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = 100;
-- Nested Loop  (cost=0.85..234.56 rows=50 width=200)
--   -> Index Scan using idx_orders_customer on orders o
--   -> Index Scan using idx_items_order on order_items oi
  • 適用場景：小表驅動大表、內表有索引、返回少量行
  • 優化方向：確保內表連線欄有索引

雜湊連線（Hash Join）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Hash Join  (cost=345.67..12345.89 rows=100000 width=200)
--   Hash Cond: (o.customer_id = c.customer_id)
--   -> Seq Scan on orders o
--   -> Hash
--       -> Seq Scan on customers c
  • 適用場景：大表連線大表、等值連線、無排序需求
  • 優化方向：確保 Build 側（Hash 表）能放入 work_mem

合併連線（Merge Join）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.customer_id;
-- Merge Join  (cost=0.86..15678.90 rows=100000 width=200)
--   Merge Cond: (o.customer_id = c.customer_id)
--   -> Index Scan using idx_orders_customer on orders o
--   -> Index Scan using customers_pkey on customers c
  • 適用場景：兩側資料已排序、等值連線、大資料量
  • 優化方向：確保兩側連線欄有索引（已排序）

EXPLAIN 輸出關鍵欄位

欄位 含義 關注點
cost 估算代價（啟動..總代價） 總代價過高需優化
rows 估算返回行數 與實際行數偏差大需 ANALYZE
width 平均行寬度（位元組） 過寬考慮減少 SELECT 欄
actual time 實際耗時（毫秒） 關注慢節點
actual rows 實際返回行數 與估算偏差大需更新統計
loops 執行次數 巢狀迴圈中 loops × time = 總耗時
Buffers 緩衝區命中統計 shared hit 高表示快取友好

💡 使用 JSON 格式化 工具解析 FORMAT JSON 輸出的 EXPLAIN 結果。

索引型別與選型實戰

PostgreSQL 提供了豐富的索引型別，選擇合適的索引型別是效能調優的關鍵。

B-tree 索引（預設）

-- 建立 B-tree 索引
CREATE INDEX idx_orders_date ON orders (order_date);

-- 複合 B-tree 索引
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

-- 適用場景：等值查詢、範圍查詢、排序、LIKE 'prefix%'
-- 最常用索引型別，覆蓋 90%+ 場景

GIN 索引（倒排索引）

-- JSONB 欄位索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 陣列欄位索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 全文搜尋索引
CREATE INDEX idx_docs_content ON documents USING GIN (to_tsvector('english', content));

-- 查詢範例
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tuning');
  • 適用場景：JSONB、陣列、全文搜尋
  • 注意：GIN 索引建構慢，查詢快；考慮 fastupdate = on 加速寫入

GiST 索引（通用搜尋樹）

-- 地理空間索引（PostGIS）
CREATE INDEX idx_stores_location ON stores USING GiST (location);

-- 範圍型別索引
CREATE INDEX idx_events_period ON events USING GiST (time_range);

-- 查詢範例
SELECT * FROM stores WHERE ST_DWithin(location, ST_Point(116.4, 39.9), 5000);
SELECT * FROM events WHERE time_range && '[2026-01-01, 2026-06-30)'::daterange;
  • 適用場景：地理空間、範圍型別、樹結構
  • 注意：GiST 支援自訂操作符類別

BRIN 索引（塊範圍索引）

-- 時序資料索引
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 自訂區塊大小
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

-- 查詢範例
SELECT * FROM logs WHERE created_at >= '2026-06-01' ORDER BY created_at DESC LIMIT 100;
  • 適用場景：大表中自然排序的欄（時序資料、自增 ID）
  • 優勢：索引體積極小（通常為 B-tree 的 1/100）
  • 注意：僅適合資料物理有序的欄，選擇性查詢不如 B-tree

Hash 索引

-- 建立 Hash 索引
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- 適用場景：純等值查詢
-- 注意：PostgreSQL 10+ 後 Hash 索引已支援 WAL，可安全使用
-- 但通常 B-tree 已足夠，Hash 索引使用場景有限

索引型別選型決策表

場景 推薦索引 原因
等值/範圍查詢 B-tree 最通用，效能穩定
JSONB 包含查詢 GIN 原生支援 @> 操作符
全文搜尋 GIN 配合 tsvector 高效檢索
地理空間查詢 GiST PostGIS 標準搭配
範圍重疊查詢 GiST 原生支援 && 操作符
時序資料範圍查詢 BRIN 索引極小，適合物理有序資料
純等值查詢（超大表） Hash 等值查詢略快於 B-tree

部分索引與表達式索引

部分索引（Partial Index）

-- 僅索引未完成的訂單（減少索引體積）
CREATE INDEX idx_orders_pending ON orders (customer_id, order_date)
WHERE status = 'pending';

-- 僅索引活躍使用者
CREATE INDEX idx_users_active ON users (last_login_at)
WHERE is_active = true;

-- 查詢自動使用部分索引
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 100;
  • 優勢：索引體積大幅減小，寫入開銷降低
  • 注意：查詢條件必須滿足 WHERE 子句才能使用索引

表達式索引

-- 不區分大小寫查詢
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 日期擷取查詢
CREATE INDEX idx_orders_date_trunc ON orders (DATE(order_date));

-- JSONB 特定鍵索引
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- 查詢範例
SELECT * FROM users WHERE LOWER(email) = 'admin@example.com';
SELECT * FROM orders WHERE DATE(order_date) = '2026-06-11';
SELECT * FROM products WHERE attributes->>'color' = 'red';
  • 優勢：避免查詢時計算表達式，直接走索引
  • 注意：查詢條件必須與索引表達式完全一致

複合索引欄順序原則

-- 遵循：等值條件欄 → 範圍條件欄 → 排序欄
CREATE INDEX idx_orders_optimal ON orders (status, customer_id, order_date DESC);

-- 覆蓋索引：包含所有查詢欄，避免回表
CREATE INDEX idx_orders_covering ON orders (status, order_date)
INCLUDE (customer_id, amount);

查詢優化模式

避免 SELECT *

-- 反模式
SELECT * FROM orders WHERE customer_id = 100;

-- 優化：只查需要的欄
SELECT order_id, order_date, amount
FROM orders WHERE customer_id = 100;

分頁優化

-- 反模式：OFFSET 在大偏移量時效能極差
SELECT * FROM orders ORDER BY order_id OFFSET 100000 LIMIT 20;

-- 優化 1：Keyset 分頁（游標分頁）
SELECT * FROM orders WHERE order_id > 100000 ORDER BY order_id LIMIT 20;

-- 優化 2：覆蓋索引 + JOIN
SELECT o.* FROM orders o
JOIN (SELECT order_id FROM orders ORDER BY order_id OFFSET 100000 LIMIT 20) t
ON o.order_id = t.order_id;

子查詢優化

-- 反模式：相關子查詢
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id AND oi.price > 100);

-- 優化：改寫為 JOIN
SELECT DISTINCT o.* FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.price > 100;

CTE 優化

-- PostgreSQL 12+：CTE 預設可內聯優化
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT ro.*, c.customer_name
FROM recent_orders ro
JOIN customers c ON ro.customer_id = c.customer_id;

-- 需要物化 CTE 時顯式指定
WITH MATERIALIZED expensive_calc AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE total > 10000;

批次操作優化

-- 批次插入（單條 INSERT 多值）
INSERT INTO orders (customer_id, amount, status)
VALUES
  (1, 100.00, 'pending'),
  (2, 200.00, 'shipped'),
  (3, 150.00, 'pending');

-- 批次更新使用 CASE
UPDATE orders SET status = CASE
  WHEN order_id = 1 THEN 'shipped'
  WHEN order_id = 2 THEN 'delivered'
  WHEN order_id = 3 THEN 'cancelled'
  ELSE status
END
WHERE order_id IN (1, 2, 3);

-- 使用 COPY 快速匯入
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

連線池配置（PgBouncer）

PgBouncer 是 PostgreSQL 最流行的連線池，能有效解決連線數過多導致的效能問題。

為什麼需要連線池？

每個 PostgreSQL 連線會 fork 一個程序，消耗約 10MB 記憶體。1000 個連線 = 10GB 記憶體開銷，且程序切換成本高。

PgBouncer 安裝與配置

# 安裝 PgBouncer
sudo apt install pgbouncer

# 啟動 PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

; /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 300
server_lifetime = 3600
log_connections = 0
log_disconnections = 0
stats_period = 60

連線池模式

模式 特點 適用場景
session 工作階段級複用，連線綁定到客戶端 需要工作階段變數、暫存表
transaction 交易級複用，交易結束後連線歸還 大多數 Web 應用（推薦）
statement 陳述式級複用，每條陳述式後歸還 短查詢、無交易場景

連線數規劃

-- 檢視目前連線數
SELECT count(*) FROM pg_stat_activity;

-- 按狀態分組
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count DESC;

-- 檢視 PostgreSQL 最大連線數
SHOW max_connections;

-- 推薦公式
-- PgBouncer default_pool_size = CPU 核心數 × 2 + 磁碟數
-- PostgreSQL max_connections = 所有 PgBouncer pool_size 之和 + 預留

核心參數調優

shared_buffers

-- 共享緩衝區大小，PostgreSQL 最核心參數
-- 推薦值：系統總記憶體的 25%，但不超過 8GB（Linux）
ALTER SYSTEM SET shared_buffers = '4GB';

work_mem

-- 排序和雜湊操作的記憶體上限
-- 推薦值：總記憶體 / (max_connections × 3)，通常 16MB-256MB
ALTER SYSTEM SET work_mem = '64MB';

-- 檢視目前 work_mem 導致的磁碟排序
SELECT query, calls, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 10;

effective_cache_size

-- 查詢最佳化器對系統快取的估算值（不實際分配記憶體）
-- 推薦值：系統總記憶體的 50%-75%
ALTER SYSTEM SET effective_cache_size = '12GB';

WAL 相關參數

-- WAL 寫入方式：open_datasync（Linux 預設）、fdatasync、fsync 等
ALTER SYSTEM SET wal_sync_method = 'open_datasync';

-- WAL 緩衝區大小
ALTER SYSTEM SET wal_buffers = '64MB';

-- 檢查點間隔
ALTER SYSTEM SET checkpoint_timeout = '10min';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET min_wal_size = '512MB';

-- 完整性保證（正式環境務必開啟）
ALTER SYSTEM SET fsync = 'on';
ALTER SYSTEM SET synchronous_commit = 'on';

關鍵參數速查表

參數 推薦值（16GB 記憶體） 說明
shared_buffers 4GB 共享緩衝區
work_mem 64MB 排序/雜湊記憶體
effective_cache_size 12GB 最佳化器快取估算
maintenance_work_mem 1GB 維護操作記憶體
wal_buffers 64MB WAL 緩衝區
max_wal_size 2GB WAL 最大大小
checkpoint_timeout 10min 檢查點間隔
random_page_cost 1.1（SSD） 隨機頁代價
effective_io_concurrency 200（SSD） 並行 I/O 數
max_connections 100-200 最大連線數 
# 使用 pgtune 自動產生推薦配置
pgtune -i postgresql.conf -M 16GB -T web -c 200

分區策略

PostgreSQL 10+ 支援原生宣告式分區，是處理大表的核心手段。

範圍分區（Range）

-- 按月分區訂單表
CREATE TABLE orders (
  order_id BIGINT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  status TEXT
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_q1 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026_q2 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE orders_2026_q3 PARTITION OF orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE orders_2026_q4 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

列表分區（List）

-- 按地區分區
CREATE TABLE customers (
  customer_id BIGINT,
  name TEXT,
  region TEXT,
  created_at TIMESTAMP
) PARTITION BY LIST (region);

CREATE TABLE customers_north PARTITION OF customers
  FOR VALUES IN ('北京', '天津', '河北');
CREATE TABLE customers_south PARTITION OF customers
  FOR VALUES IN ('廣東', '福建', '海南');
CREATE TABLE customers_default PARTITION OF customers DEFAULT;

雜湊分區（Hash）

-- 雜湊分區（均勻分佈）
CREATE TABLE access_logs (
  log_id BIGINT,
  user_id INT,
  path TEXT,
  created_at TIMESTAMP
) PARTITION BY HASH (user_id);

CREATE TABLE access_logs_p0 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分區維護

-- 自動建立未來分區（使用 pg_partman 擴展）
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table := 'public.orders',
  p_control := 'order_date',
  p_type := 'range',
  p_interval := '1 month',
  p_premake := 6
);

-- 分區裁剪驗證
EXPLAIN SELECT * FROM orders WHERE order_date = '2026-06-11';
-- 應只掃描 orders_2026_q2 分區

-- 刪除舊分區（歸檔後刪除）
DROP TABLE orders_2024_q1;

平行查詢

PostgreSQL 9.6+ 引入平行查詢，18 版本已大幅增強。

啟用平行查詢

-- 開啟平行查詢
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;
ALTER SYSTEM SET parallel_setup_cost = 100;

-- 最小表大小觸發平行（預設 8MB）
ALTER SYSTEM SET min_parallel_table_scan_size = '8MB';

-- 平行工作者數隨表大小增長
ALTER SYSTEM SET min_parallel_index_scan_size = '512kB';

平行查詢驗證

EXPLAIN ANALYZE SELECT COUNT(*), SUM(amount)
FROM orders WHERE order_date >= '2026-01-01';
-- Gather Merge
--   -> Sort
--       -> Partial Aggregate
--           -> Parallel Seq Scan on orders

平行查詢適用場景

場景 平行效果 說明
大表全表掃描 極好 平行 Seq Scan
大表聚合 極好 平行 Aggregation
大表 JOIN 好 平行 Hash Join
索引掃描 一般 平行 Bitmap Scan
小表查詢 無效果 低於 min_parallel_table_scan_size
CTE（物化） 不支援 MATERIALIZED CTE 不平行

VACUUM 與 autovacuum 調優

PostgreSQL 的 MVCC 機制會產生死元組（dead tuples），必須透過 VACUUM 回收空間。

手動 VACUUM

-- 普通 VACUUM（不回收磁碟空間，只標記可複用）
VACUUM orders;

-- VACUUM FULL（回收磁碟空間，鎖表！）
VACUUM FULL orders;

-- 平行 VACUUM（PostgreSQL 12+，不鎖表）
VACUUM (PARALLEL 4) orders;

-- 分析統計資訊
ANALYZE orders;

-- 同時 VACUUM + ANALYZE
VACUUM ANALYZE orders;

autovacuum 調優

-- 開啟 autovacuum（預設開啟）
ALTER SYSTEM SET autovacuum = 'on';

-- autovacuum 工作者數量
ALTER SYSTEM SET autovacuum_max_workers = 4;

-- 觸發閾值（當死元組數超過此值時觸發）
-- 公式：autovacuum_vacuum_scale_factor × 表行數 + autovacuum_vacuum_threshold
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;

-- 分析觸發閾值
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;

-- autovacuum 單次處理成本限制
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

單表 autovacuum 調優

-- 對頻繁更新的表降低觸發閾值
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 5000
);

-- 檢視表的 VACUUM 統計
SELECT relname, n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

監控 VACUUM 進度

-- PostgreSQL 12+ 檢視 VACUUM 進度
SELECT pid, datname, relid::regclass, phase,
  heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
  index_vacuum_count, num_dead_item_ids
FROM pg_stat_progress_vacuum;

pg_stat_statements 慢查詢分析

pg_stat_statements 是 PostgreSQL 最強大的慢查詢分析工具。

安裝與啟用

-- 載入擴展
CREATE EXTENSION pg_stat_statements;

-- 修改 postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000

Top N 慢查詢

-- 按總耗時排序
SELECT query, calls, total_exec_time, mean_exec_time,
  min_exec_time, max_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- 按平均耗時排序
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC LIMIT 10;

-- 按磁碟 I/O 排序
SELECT query, calls, shared_blks_read, shared_blks_hit,
  ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0), 4) AS cache_hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC LIMIT 10;

-- 重置統計
SELECT pg_stat_statements_reset();

快取命中率分析

-- 整體快取命中率（應 > 99%）
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- 單表快取命中率
SELECT relname,
  heap_blks_hit, heap_blks_read,
  ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0), 4) AS cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC;

常見效能反模式

反模式 1：過度索引

-- 問題：每個索引都會增加寫入開銷
-- 檢查未使用的索引
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 刪除未使用索引
DROP INDEX CONCURRENTLY idx_orders_unused;

反模式 2：大交易

-- 問題：長交易阻止 VACUUM 回收死元組，導致表膨脹
-- 檢查長交易
SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
ORDER BY duration DESC;

-- 設定交易逾時（PostgreSQL 17+）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60000';

反模式 3：型別不匹配

-- 問題：隱式型別轉換導致無法使用索引
-- 反模式
SELECT * FROM orders WHERE order_id = '12345';  -- VARCHAR 與 BIGINT 不匹配

-- 優化：確保型別一致
SELECT * FROM orders WHERE order_id = 12345;

反模式 4：OR 條件濫用

-- 反模式
SELECT * FROM orders WHERE customer_id = 100 OR amount > 5000;

-- 優化：使用 UNION ALL
SELECT * FROM orders WHERE customer_id = 100
UNION ALL
SELECT * FROM orders WHERE amount > 5000 AND customer_id != 100;

反模式 5：函式導致索引失效

-- 反模式：對索引欄使用函式
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-11';

-- 優化：使用範圍查詢
SELECT * FROM orders WHERE created_at >= '2026-06-11' AND created_at < '2026-06-12';

-- 或使用表達式索引
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));

Prometheus + Grafana 監控

postgres_exporter 部署

# 安裝 postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz

# 配置資料來源
export DATA_SOURCE_NAME="postgresql://monitor:password@localhost:5432/mydb?sslmode=disable"

# 啟動
./postgres_exporter --web.listen-address=:9187

Prometheus 配置

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metric_relabel_configs:
      - source_labels: [__name__]
        regex: 'pg_.*'
        action: keep

關鍵監控指標

指標 含義 告警閾值
pg_stat_activity_count 目前連線數 > max_connections × 80%
pg_stat_database_deadlocks 死結數 > 0
pg_stat_user_tables_n_dead_tup 死元組數 > 總行數 × 10%
pg_stat_statements_mean_exec_time 平均查詢耗時 P99 > 500ms
pg_replication_lag 複製延遲 > 30s
pg_database_size_bytes 資料庫大小 > 磁碟 85%

告警規則範例

groups:
  - name: postgresql_alerts
    rules:
      - alert: PostgreSQLHighConnections
        expr: pg_stat_activity_count > 160
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 連線數過高"
          description: "目前連線數超過 160，持續 5 分鐘"

      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 30
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL 複製延遲過高"
          description: "複製延遲超過 30 秒"

      - alert: PostgreSQLDeadTuplesHigh
        expr: pg_stat_user_tables_n_dead_tup / pg_stat_user_tables_n_live_tup > 0.1
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 死元組比例過高"
          description: "死元組佔比超過 10%，需要 VACUUM"

💡 使用 雜湊加密 工具產生監控告警 webhook 的簽名金鑰。

常見問題 FAQ

Q1：shared_buffers 設定多大合適？

推薦設定為系統總記憶體的 25%，但不超過 8GB（Linux）。因為 PostgreSQL 依賴作業系統的頁面快取，過大的 shared_buffers 會與 OS 快取爭搶記憶體。例如 16GB 記憶體的伺服器，設定 shared_buffers = 4GB。

Q2：什麼時候應該使用 BRIN 索引而非 B-tree？

當表資料量極大（> 1 億行）且欄值與物理儲存順序相關時（如時間戳、自增 ID），BRIN 索引體積僅為 B-tree 的 1/100，但查詢效能接近。典型場景：日誌表的時間欄、時序資料的採集時間。

Q3：如何判斷是否需要分區？

當單表資料量超過 1000 萬行，且查詢通常只涉及部分資料（如按時間範圍查詢），就應該考慮分區。分區裁剪可以大幅減少掃描資料量。

Q4：VACUUM FULL 什麼時候使用？

VACUUM FULL 會鎖表並重建整張表，回收所有碎片空間。僅在表膨脹嚴重（死元組 > 50%）且無法透過普通 VACUUM 回收時使用。推薦使用 pg_repack 擴展作為線上替代方案。

Q5：PgBouncer 的 transaction 模式有什麼限制？

transaction 模式下，交易結束後連線會被回收，因此：不能使用暫存表（跨交易）、不能使用 SET 設定工作階段級參數（跨交易）、不能使用 LISTEN/NOTIFY（跨交易）、不能使用預備陳述式（跨交易）。如需這些功能，使用 session 模式。

Q6：如何快速定位慢查詢？

  1. 啟用 pg_stat_statements 擴展
  2. 查詢 pg_stat_statements 按 total_exec_time 排序
  3. 對慢查詢使用 EXPLAIN ANALYZE 分析執行計畫
  4. 檢查是否有 Seq Scan、高 cost 節點
  5. 針對性新增索引或改寫查詢

Q7：random_page_cost 應該設定多少？

  • SSD/NVMe：設定為 1.1（接近順序 I/O 代價）
  • HDD：保持預設 4.0
  • 這個參數直接影響最佳化器選擇索引掃描還是順序掃描的決策

Q8：PostgreSQL 的連線數上限怎麼定？

每個連線約消耗 10MB 記憶體。推薦公式：max_connections = 可用記憶體(GB) × 100，但實際連線數應透過 PgBouncer 池化控制。PostgreSQL 的 max_connections 設定為 100-200 即可，外部連線由 PgBouncer 管理。

總結

PostgreSQL 在 2026 年依然是功能最強大的開源關聯式資料庫，其效能調優需要系統性地從查詢分析、索引設計、參數配置、架構優化四個維度入手。關鍵要點回顧：

  1. EXPLAIN ANALYZE 是效能調優的起點，理解每種掃描和連線型別的適用場景
  2. 索引選型 至關重要：B-tree 通用、GIN 處理 JSONB/陣列、BRIN 適合時序資料
  3. 參數調優 四大核心：shared_buffers、work_mem、effective_cache_size、WAL 配置
  4. 分區策略 是大表治理的基石：範圍分區最常用、雜湊分區保均勻
  5. VACUUM 調優 防止表膨脹：autovacuum 必須開啟並合理配置
  6. 監控體系 是保障：pg_stat_statements + postgres_exporter + Grafana 三件套
#PostgreSQL#性能调优#数据库#SQL优化#教程