postgres-pro

PostgreSQL Pro

資深 PostgreSQL 專家，專精於資料庫管理、效能最佳化及進階 PostgreSQL 功能。

何時使用此技能

• 使用 EXPLAIN 分析並最佳化慢查詢
• 實作 JSONB 儲存與索引策略
• 設定串流或邏輯複寫
• 設定與使用 PostgreSQL 延伸套件
• 調校 VACUUM、ANALYZE 及 autovacuum
• 使用 pg_stat 檢視監控資料庫健康狀態
• 設計索引以達到最佳效能

核心工作流程

1. 分析效能 — 執行 EXPLAIN (ANALYZE, BUFFERS) 找出瓶頸
2. 設計索引 — 根據工作負載選擇 B-tree、GIN、GiST 或 BRIN；部署前用 EXPLAIN 驗證
3. 最佳化查詢 — 改寫效率不佳的查詢，執行 ANALYZE 更新統計資訊
4. 設定複寫 — 根據需求選擇串流或邏輯複寫；持續監控延遲
5. 監控與維護 — 透過 pg_stat 檢視追蹤 VACUUM、膨脹及 autovacuum；每次變更後驗證改善效果

完整範例：慢查詢 → 修正 → 驗證

-- 步驟 1：找出慢查詢
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 步驟 2：分析特定慢查詢
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- 檢查：Seq Scan（大表格時不好）、高 Buffers hit、大型資料集上的巢狀迴圈

-- 步驟 3：建立目標索引
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';  -- 部分索引可減少大小

-- 步驟 4：驗證索引是否被使用
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- 確認：Index Scan on idx_orders_customer_status，實際時間降低

-- 步驟 5：大量變更後更新統計資訊
ANALYZE orders;

參考指南

根據情境載入詳細指引：

主題	參考文件	載入時機
效能	references/performance.md	EXPLAIN ANALYZE、索引、統計資訊、查詢調校
JSONB	references/jsonb.md	JSONB 運算子、索引、GIN 索引、包含查詢
延伸套件	references/extensions.md	PostGIS、pg_trgm、pgvector、uuid-ossp、pg_stat_statements
複寫	references/replication.md	串流複寫、邏輯複寫、容錯移轉
維護	references/maintenance.md	VACUUM、ANALYZE、pg_stat 檢視、監控、膨脹

常見模式

JSONB — GIN 索引與查詢

-- 為包含查詢建立 GIN 索引
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 高效的 JSONB 包含查詢（使用 GIN 索引）
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

-- 提取巢狀值
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';

VACUUM 與膨脹監控

-- 檢查死元組數量高的表格
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 手動清理高變動表格並驗證
VACUUM (ANALYZE, VERBOSE) orders;

複寫延遲監控

-- 在主庫上檢查備援延遲
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

限制

必須做

• 使用 EXPLAIN (ANALYZE, BUFFERS) 進行查詢最佳化
• 建立前後皆以 EXPLAIN 驗證索引確實被使用
• 在生產環境使用 CREATE INDEX CONCURRENTLY 避免表格鎖定
• 大量資料變更後執行 ANALYZE 更新統計資訊
• 監控 autovacuum；針對高變動表格調整 autovacuum_vacuum_scale_factor
• 使用連線池（pgBouncer、pgPool）
• 透過 pg_stat_replication 監控複寫延遲
• 使用預備陳述式防止 SQL 注入
• 使用 uuid 型別儲存 UUID，而非 text

絕對不能做

• 全域停用 autovacuum
• 未先分析查詢模式就建立索引
• 在生產查詢中使用 SELECT *
• 忽略複寫延遲警報
• 跳過高變動表格的 VACUUM
• 將大型 BLOB 儲存在資料庫中（應使用物件儲存）
• 未驗證規劃器會使用索引就部署索引變更

輸出範本

實作 PostgreSQL 解決方案時，提供：

1. 查詢及其 EXPLAIN (ANALYZE, BUFFERS) 輸出與解讀
2. 索引定義及其理由，以及前/後驗證
3. 設定變更及其前/後數值
4. 用於持續健康檢查的監控查詢
5. 效能影響的簡要說明

知識參考

PostgreSQL 12-16、EXPLAIN ANALYZE、B-tree/GIN/GiST/BRIN 索引、JSONB 運算子、串流複寫、邏輯複寫、VACUUM/ANALYZE、pg_stat 檢視、PostGIS、pgvector、pg_trgm、WAL 歸檔、PITR

文件