部分內容參考自 digoal (德哥) blog，經大幅改寫、補充 PG 9~18 版本演進、Mermaid 圖與新手導向說明。 適合 Developer 閱讀，著重觀念理解而非深入原始碼。每份 md 均由多篇原始筆記合併而成，依由淺到深排列，含標題層級（
# 一、→## 1.→### I.）與 Mermaid 圖輔助說明。
| 章節 | 檔案 | 主題 |
|---|---|---|
| Transaction 隔離級別 | transaction/transaction.md |
MVCC Snapshot 深度解析、Read Committed/Repeatable Read/Serializable 底層原理（SSI/SIREAD）、Write Skew 重現、VACUUM 影響、.NET Dapper/Polly Retry 實戰 |
| 索引全解析 | index.md |
六種掃描類型全解析（Seq/Index/Bitmap/Parallel/Index-Only）、Bitmap Heap Scan 詳解、BRIN/Bloom/GIN/GiST/SP-GiST/RUM、Covering Index、索引失效 20 場景 |
| 查詢深度解析 | query.md |
查詢生命週期、CBO 與 pg_hint_plan、GROUP BY 策略、IN/ANY/VALUES、分頁與計數、Recursive CTE 優化、死循環防禦 |
| 鎖（Lock） | lock.md |
隱式鎖、Lock Wait 追蹤、秒殺 Advisory Lock、高並發更新、Lock Flooding、max_locks_per_transaction、OLTP advisory lock、無間隙 ID 生成 |
| 監控與追溯 | monitoring.md |
pg_stat_activity 生產實戰（5 場景 + 決策圖）、wait_event Top 10、auto_explain、pg_stat_io、track_commit_timestamp |
| Vacuum / Bloat | vacuum/vacuum.md |
MVCC 可見性判斷、Bloat 8 大成因與測試驗證、預防措施、VACUUM FULL vs pg_repack vs pg_squeeze 三方案對比 |
| 資料型別 | datatype.md |
Float vs Numeric 效能對比（360x）、SIMD 向量化、AT TIME ZONE 語法解析與型別轉換陷阱 |
| JSON/JSONB | json/json.md |
JSONB Value Types、Type I/O 機制、陣列提取與 GIN Index、JSONPath / SQL/JSON / json_table（PG 12→17） |
| 全文檢索 | fulltext/fulltext.md |
zhparser 中文分詞、Whole-Row FTS（Generated Column）、record_out + SCWS 逗號問題與解法 |
| 系統底層 | system.md |
Column Order 與 Byte Alignment 全鏈路效能、Bit 位運算標籤系統、Linux Page Fault 與 huge_pages / NUMA |
| 擴充功能 | extensions/extensions.md |
十大 Extension 全解析：FDW 跨庫連線、pg_partman 自動分區、Citus 12 分散式、PgBouncer 連接池、pg_stat_statements 查詢統計、auto_explain 計畫記錄、pg_repack 在線重組、pg_cron 定時任務、pg_stat_kcache IO 統計、hypopg 假設索引 |
| 分頁查詢 | pagination.md |
OFFSET 效能退化、CURSOR 方案、Keyset Pagination、分頁優化策略 |
| 其他進階 | others/others.md |
PG 17 開發規範、Trigger Audit（DML+DDL）、JOIN 冗餘 Early DISTINCT、pgcrypto 加密、千億級 pg_trgm Regex、12306 搶票架構設計 |
- 一、Float vs Numeric：Benchmark（四則/開根號/Pi 計算）、硬體加速 vs 軟體模擬的根本差異、SIMD 向量化優勢、選型建議、版本演進
- 二、AT TIME ZONE：EXTRACT epoch 的時區陷阱、
gram.y語法規則、timestamptz_part()vstimestamp_part()的 overload 選擇、三種 Case 逐步分解
- 一、Value Types 與構造方法：Scalar Types（大小寫敏感）、jsonb 內部無 Type 概念、Type I/O Function 機制、
format() + jsonb_in()構造法、BYTEA Escape 限制、json_lex()Tokenizer 完整邏輯 - 二、陣列提取與查詢：
->/->>操作符、json_array_elements+ ARRAY 構造器、@>/&&陣列操作、GIN on Expression、JSONPath（PG 12+）、SQL/JSON 標準函數（PG 15+）、json_table（PG 17+）
- 一、zhparser 中文全文檢索：SCWS 分詞引擎、Token Type Mapping（實詞 vs 虛詞）、分詞效果測試、完整部署流程、效能調校（GIN/GiST/RUM 選擇）
- 二、Whole-Row FTS（PG 17 視角）：Generated Column + GIN 現代方案、Legacy IMMUTABLE 繞過法、
t::text格式限制、加權檢索、中文分詞 Extension 選擇 - 三、record_out + SCWS 逗號問題：
record_out序列化格式、SCWS 將逗號解析為 auxiliary token 導致截斷、replace(, → ' ')解法、分詞效能基準（4.44 萬字/s）
- 一、IMPORT FOREIGN SCHEMA：
LIMIT TO/EXCEPT過濾、View/Materialized View/Foreign Table 一併導入、PG 14-17 演進（postgres_fdw 2.0、async_append、MERGE pushdown、parallel_foreign_scan） - 二、pg_partman：PG 10-17 原生分區演進、自動分區創建/清理、Retention Policy、Background Worker 驅動的 partition lifecycle、按天/月/年分區管理
- 三、Citus 12：分散式 SQL 引擎、Hash/Range Sharding、Co-Located Join、Schema-Based Sharding、非阻塞 Rebalancing、Query from Any Node
- 四、PgBouncer：Transaction vs Session vs Statement Pooling、生產級 HAProxy 拓撲、PgBouncer 1.22+ prepared statement 追蹤
- 五、pg_stat_statements：查詢歸一化原理、queryid 計算、Top-N 慢查詢/JIT/WAL 分析、PG 16 新增 JIT 計數器
- 六、auto_explain：自動記錄執行計劃、log_analyze/log_buffers/log_triggers/log_nested_statements、生產調校策略
- 七、pg_repack：四階段在線重組原理、vs VACUUM FULL / pg_squeeze 對比、配合 pg_cron 定時執行
- 八、pg_cron：PG 內建排程、定時 VACUUM / 分區維護 / 物化視圖刷新、與 pg_partman 協同
- 九、pg_stat_kcache：getrusage() 實體 IO 統計、CPU 耗時分析、寫入放大檢測、與 pg_stat_statements 聯表診斷
- 十、hypopg：假設索引 zero-cost 試錯、分區表索引測試、結合 pg_stat_statements Top-N 慢查詢最佳實踐
- 一、Vacuum 原理與防止 Bloat：8 大 Bloat 成因（Long Transaction 為核心）、6 組測試驗證（XID/游標/長查詢/隔離級別/批量更新/naptime）、10 項預防措施、
OldestXmin原始碼分析 - 二、收縮膨脹表：VACUUM FULL vs pg_repack vs pg_squeeze 三方案對比（鎖定時長/Delta 捕捉/效能影響/成熟度）、現代最佳實踐、
REINDEX CONCURRENTLY（PG 12+）
- 一、Column Order & Byte Alignment：ADD COLUMN 永遠在末尾、Simple View 虛擬重排、Byte Alignment 對 Row Size 的影響（padding 可佔 41%）、全鏈路效能鏈式反應
- 二、Bit 位運算標籤系統：5000 萬用戶/200 標籤實測、
bitand()無法用 Index 的瓶頸、替代方案（intarray + RD-Tree、roaringbitmap）、生產環境建議 - 三、Linux Page Fault：MMU 與虛擬記憶體、Major/Minor/Invalid 三種 Page Fault、大 shared_buffers 啟動低潮案例（minor fault 風暴）、huge_pages / pg_prewarm / NUMA 現代化解方
- 一、Lock 機制全景：8 級 Lock Mode 衝突矩陣、Lock Queue 排隊機制（pending lock 可堵死後續請求）、Object-Level Lock 在 Transaction 結束才釋放、idle in transaction 偵測與預防
- 二、Advisory Lock 應用場景：秒殺（231K TPS）、高並發全表更新（18x 加速）、OLTP 排隊控制、無間隙 ID 生成與 Lock Flooding 防禦、
max_locks_per_transaction配置
- 一、PG 17 開發規範：命名/設計/Query/管理/穩定性五大類 50+ 條規則，標記 4 條已過時規則
- 二、Trigger Audit：DML 審計（hstore + Row Trigger 記錄欄位級變更）+ DDL 審計（Event Trigger + hstore）
- 三、JOIN 冗餘膨脹 Early DISTINCT：笛卡爾乘積膨脹的根因、每層 JOIN 後立即去重的解法、重現實驗與 CTE 簡化寫法
- 四、pgcrypto 加密：密碼儲存（crypt+gen_salt）、PGP 對稱/公鑰加密、三種方案選擇矩陣
- 五、千億級 Regex 模糊查詢：1,008 億行 pg_trgm + GIN 效能實測、Trigram 原理、四種查詢模式（Prefix/Suffix/中間/Regex）、pg_bigm 替代方案
- 六、12306 搶票架構：varbit 座位區段銷售狀態、Array+GIN 車次查詢、SKIP LOCKED 避免 Lock 衝突、pgrouting 路徑規劃、10 大法寶
- 一、查詢生命週期：Client Request → Parser → Analyzer → Planner → Executor 六階段逐層拆解、Process-per-Connection 架構設計、三層 Tree 轉換（Parse/Plan/Executor）
- 二、CBO 與 pg_hint_plan：Cost-Based Optimizer 盲區分析、何時需要 Hint 介入、pg_hint_plan 使用方法與注意事項
- 三、GROUP BY 策略：Sort (GroupAgg) vs Hash (HashAgg) 兩種實作路徑、Planner 選擇邏輯與調校
- 四、IN / =ANY / VALUES 效能對決：四種等效寫法的底層差異、JOIN VALUES 展開陷阱
- 五、分頁與計數優化：count(*) 替代策略、OFFSET 退化分析、Keyset 位點
- 六、Recursive CTE 優化：Index Skip Scan 模擬、Top-N Per Group（44x 加速）
- 七、Recursive CTE 死循環防禦：CYCLE 語法（PG 14+）、Production 防禦體系
- 一、慢查詢追溯體系：pg_stat_activity 生產實戰（5 場景 + 30 秒決策圖 + 鎖阻塞/Plan 不穩定/連線池滿/間歇性慢查詢）、wait_event Top 10、Snapshot vs Time-Series、應用層 backend_pid 記錄
- 二、track_commit_timestamp：SLRU 儲存結構、logical replication / snapshot too old / CDC 應用場景、效能影響與 Production 取捨
- 一、隔離級別的底層：MVCC 與 Snapshot：Snapshot 結構（xmin/xmax/xip[]）深度回顧、
GetTransactionSnapshot()內部機制、RC vs RR 的 Snapshot 獲取時序對比（stateDiagram）、PG 為何用 Snapshot 而非 Lock - 二～五、四種隔離級別完整解析：Read Uncommitted（PG 中等於 RC 的 MVCC 根本原因）、Read Committed（每個 Statement 新 Snapshot / 幻讀重現 / 生產場景選擇表）、Repeatable Read（gantt 時序圖 / PG 額外防止 Phantom Read 的 snapshot 邊界原理 / Write Skew 完整重現 + Mermaid)、Serializable（SSI 原理 / SIREAD lock page-level 限制 / wr/ww/rw 三種依賴 / serialization failure 觸發流程 / 效能 overhead 量化）
- 六、隔離級別對 VACUUM 的影響：OldestXmin 計算、RR transaction 如何阻止 dead tuple 回收（Mermaid 災難鏈）、找出卡住 VACUUM 的 session、
old_snapshot_threshold/idle_in_transaction_session_timeout解法 - 生產環境場景：轉帳 Phantom（FOR UPDATE / RR / Serializable 解法矩陣）、Write Skew 案例（值班醫生 SQL 重現）、隔離級別選擇決策圖（Mermaid flowchart + 速查對照表）
- .NET 實戰：Npgsql IsolationLevel enum 完整對照、Dapper 正確/錯誤寫法對比（FOR UPDATE + transaction 傳遞）、Polly Retry Pattern（
SqlState 40001serialization failure）
- 一、六種掃描類型全解析：Seq Scan / Index Scan / Bitmap Heap Scan / Index-Only Scan / Parallel Scan / TID Scan 的 Planner 選擇邏輯與成本模型
- 二、Index 核心機制：B-Tree / Hash 內部結構、Covering Index（INCLUDE）、Leaf Page 圖解、Recheck Cond 詳解
- 三、BRIN Index：Block Range Index 原理（540x 小於 B-tree）、適用場景（時序/append-only）
- 四、Bloom Index：單一索引支撐任意 Column 組合查詢、簽名長度調校
- 五～七、模糊查詢索引全景：GIN / GiST / SP-GiST / RUM 內部機制、全文檢索排序、GIN+LIMIT 慢的原因與 RUM 解法
- 八、索引失效 20 場景：每個場景附 EXPLAIN ANALYZE 輸出對比與可行解法
- 一、OFFSET 基本原理：OFFSET 不是「跳過」而是「計算後丟棄」、VOLATILE function 的放大效應、SQL 邏輯執行順序
- 二、OFFSET 的質變：數據斷層引發掃描量跳升、Rows Removed by Filter 暴增、Production 真實案例
- 三、分頁優化方案：Keyset Pagination / CURSOR / ismax 標記列四種方案對比與選擇指南
部分內容參考自 digoal (德哥) 的 PostgreSQL blog，經大幅改寫、去除重複、補充 PG 9~18 版本演進資訊、Mermaid 圖視覺化與新手導向說明。
- 每份 md 為該主題的完整學習手冊，由淺到深排列
images/目錄存放相關圖片AGENTS.md為本專案的協作規範（標題層級、Mermaid 風格、提交規範等）- 專業名詞使用英文（TID、Recheck Cond、OldestXmin 等）
- 版本標注：
> 更新於 2026-05-17，補充 PG X~X 新增能力
MIT License