PostgreSQL 19.x 教學手冊

文件性質：企業內部教育訓練教材／團隊開發規範文件 適用對象：Junior/Backend/Frontend/Full Stack Developer、Database Developer、DBA、System Administrator、DevOps／DevSecOps Engineer、Software／Solution／Enterprise Architect、Data Engineer、AI Engineer、AI Agent Developer、PM、Technical Lead 內容取向：實戰與維運導向，可直接作為專案團隊內部規範採用 技術基準：PostgreSQL 18（現行正式支援版本）／PostgreSQL 19（Beta 3，尚未 GA） 查證基準日：2026-08-19（全書所有「截至目前」均指此日） 主要參考來源：

• PostgreSQL 官方網站：https://www.postgresql.org/
• PostgreSQL 官方 Documentation：https://www.postgresql.org/docs/
• PostgreSQL 19 Documentation：https://www.postgresql.org/docs/19/index.html
• PostgreSQL Release Notes：https://www.postgresql.org/docs/release/
• PostgreSQL 19 Release Notes：https://www.postgresql.org/docs/19/release-19.html
• PostgreSQL Versioning Policy：https://www.postgresql.org/support/versioning/
• PostgreSQL Development Roadmap：https://www.postgresql.org/developer/roadmap/
• PostgreSQL Beta Testing 說明：https://www.postgresql.org/developer/beta/

⚠️ 版本狀態重要聲明

請務必先讀完本節，再決定你的團隊要如何使用本手冊。

截至 2026-08-19，經查證 PostgreSQL 官方網站與官方公告，事實如下：

PostgreSQL 19 尚未正式 GA 時，不建議直接用於 Production；企業正式環境應依 PostgreSQL 官方支援版本與組織驗證政策決定。 本手冊中標示為「PostgreSQL 19（Beta）」的功能，其語法、參數名稱、預設值皆有可能在 RC／GA 階段調整。正式導入前，請務必重新比對當時的官方 Release Notes。

這對你的團隊代表什麼

本手冊的定位是 「PostgreSQL 18 生產可用 ＋ PostgreSQL 19 前瞻評估」 雙軌並行，而非「立即切換至 19」。具體建議：

內容可信度標記

本手冊區分「官方已確認事實」與「作者實務經驗補充」。凡非官方明文者，一律標記：

📖 本書使用方式

排版約定

• 英文專有名詞首次出現時附中文說明，例如：多版本併發控制（Multi-Version Concurrency Control，MVCC）
• 指令、檔名、參數、SQL 關鍵字一律以 等寬字體 標示
• 重要技術主題採用 What / Why / Architecture / How / Example / Production / Security / Performance / AI Agent / Common Mistakes / Best Practices / Troubleshooting 子結構；篇幅較小的主題則彈性精簡，避免流於填空式重複
• Production 範例一律優先展示 BEGIN / 驗證 / COMMIT 的可控寫法，破壞性 SQL（DROP DATABASE、無 WHERE 的 DELETE 等）僅作為「反面案例」出現，並標明風險
• 每章結尾提供「本章重點」與（適用章節）Checklist；章節標題旁 [↑ 回目錄] 可返回總目錄

建議閱讀路徑

📖 目錄

• ⚠️ 版本狀態重要聲明
• 📖 本書使用方式
• 第1章 PostgreSQL 簡介與定位
• 第2章 PostgreSQL 19.x 版本狀態與新功能
• 第3章 PostgreSQL 技術選型比較
• 第4章 PostgreSQL 系統架構總覽
• 第5章 PostgreSQL Internal Architecture：一條 SQL 的旅程
• 第6章 安裝與部署：Windows
• 第7章 安裝與部署：Linux
• 第8章 安裝與部署：Docker／Podman／Kubernetes
• 第9章 PostgreSQL 設定
• 第10章 Authentication 與 Security
• 第11章 Database／Schema／Role 設計
• 第12章 SQL 基礎語法
• 第13章 PostgreSQL Data Types
• 第14章 Table Design 與正規化
• 第15章 Index 設計與應用
• 第16章 Query Planner／Optimizer 與 EXPLAIN
• 第17章 Transaction／MVCC／Concurrency
• 第18章 VACUUM／Autovacuum／REPACK
• 第19章 WAL（Write-Ahead Logging）
• 第20章 Backup／Restore
• 第21章 High Availability
• 第22章 Logical Replication
• 第23章 Monitoring 與 Observability
• 第24章 Performance Tuning 方法論
• 第25章 Connection Management／Pooling
• 第26章 PostgreSQL Extensions
• 第27章 PostgreSQL＋JSON／JSONB
• 第28章 PostgreSQL＋Vector（pgvector）與 AI 應用
• 第29章 PostgreSQL＋RAG 架構
• 第30章 PostgreSQL＋AI Agent
• 第31章 PostgreSQL＋AI Coding Agent
• 第32章 PostgreSQL＋Reverse Engineering
• 第33章 PostgreSQL＋Software Framework Upgrade 知識庫
• 第34章 PostgreSQL 與 Spring Boot／Java 25
• 第35章 PostgreSQL 與前端框架的安全邊界
• 第36章 Database Migration（Flyway／Liquibase）
• 第37章 PostgreSQL DevOps
• 第38章 PostgreSQL DevSecOps
• 第39章 Multi-Tenant Architecture
• 第40章 Partitioning
• 第41章 Enterprise Reference Architecture
• 第42章 Production Checklist
• 第43章 DBA 日常／週期性維運
• 第44章 Troubleshooting
• 第45章 常用指令 Cheat Sheet
• 第46章 AI Agent Database Operations 與 SQL 安全分級
• 第47章 AI Agent＋PostgreSQL MCP
• 第48章 AI Agent Database Governance
• 第49章 AI Agent 逆向工程 Knowledge Graph
• 第50章 RAG＋Knowledge Graph 混合架構
• 第51章 Observability＋AI（Root Cause Analysis）
• 第52章 PostgreSQL Upgrade（Minor／Major）
• 第53章 PostgreSQL 18 → 19 Migration Runbook
• 第54章 Capacity Planning
• 第55章 Disaster Recovery
• 第56章 金融／銀行企業應用案例
• 第57章 大型企業微服務資料庫模式
• 第58章 Anti-Patterns
• 第59章 Coding Standards
• 第60章 AI Development Coding Standards
• 第61章 實戰 Lab
• 第62章 完整企業專案實例
• 第63章 Enterprise AI Coding Platform 實例
• 第64章 常見面試題
• 第65章 FAQ
• 第66章 技術選型決策表
• 第67章 Learning Roadmap
• 第68章 Enterprise Adoption Recommendation
• 第69章 PostgreSQL Enterprise Golden Rules
• References
• 附錄：新進成員快速上手 Checklist

第1章 PostgreSQL 簡介與定位

1.1 PostgreSQL 是什麼

PostgreSQL 是一套開源的物件關聯式資料庫管理系統（Object-Relational Database Management System，ORDBMS），由加州大學柏克萊分校的 POSTGRES 專案演化而來，1996 年起以 PostgreSQL 之名持續開發至今，是目前發展史最長、社群治理最成熟的開源資料庫之一。

它同時具備：

• 關聯式資料庫的嚴謹性：Schema、外鍵、Transaction、SQL 標準相容性
• 物件導向擴充性：自訂型別（Domain / Composite Type）、繼承式資料表、Operator Overloading
• 平台特性：透過 Extension 機制可以擴展成 GIS 資料庫（PostGIS）、時序資料庫（TimescaleDB）、向量資料庫（pgvector）等，而不需要換一套系統

1.2 授權與治理模式

PostgreSQL 採用 PostgreSQL License——一種與 MIT/BSD 精神相近的寬鬆授權，允許商業使用、修改、再散布，不要求開源衍生產品。這與 MySQL（GPL + 商業雙授權，實質由 Oracle 主導）或部分「開源後改為 Source Available」的資料庫（如 MongoDB 的 SSPL、Elastic 的 Elastic License）不同：PostgreSQL 沒有單一公司可以「收回」它的開源性質，這對企業長期技術選型是重要的風險考量。

1.3 核心能力總覽

1.4 PostgreSQL 生態系

flowchart TB
    subgraph Core["PostgreSQL 核心"]
        PG[PostgreSQL Server]
    end
    subgraph Ext["Extension 生態"]
        PGVECTOR[pgvector<br/>向量搜尋]
        POSTGIS[PostGIS<br/>空間資料]
        TSDB[TimescaleDB<br/>時序資料]
        FDW[Foreign Data Wrapper<br/>跨資料源查詢]
    end
    subgraph Ops["維運工具"]
        PATRONI[Patroni<br/>HA 協調]
        PGBOUNCER[PgBouncer<br/>連線池]
        BACKREST[pgBackRest / WAL-G<br/>備份]
    end
    subgraph Observe["監控生態"]
        PROM[Prometheus Exporter]
        GRAF[Grafana]
    end
    subgraph Cloud["雲端 / K8s"]
        CNPG[CloudNativePG]
        RDS[AWS RDS / Aurora]
        AZURE[Azure Database for PostgreSQL]
        GCP[Cloud SQL for PostgreSQL]
    end
    PG --> Ext
    PG --> Ops
    PG --> Observe
    PG --> Cloud

1.5 與其他資料庫的定位比較

What 情況適合選 PostgreSQL？ 需要交易一致性、複雜查詢、資料完整性、長期可稽核、不希望被單一廠商鎖定的系統——從一般企業 Web/後台系統、金融交易、到 AI/RAG 應用的 Metadata + Vector 儲存，PostgreSQL 都是合理的預設選擇。

What 情況不建議只用 PostgreSQL？ 超大規模（PB 級以上）分析型工作負載建議搭配欄式資料倉儲（如 ClickHouse / BigQuery）；超高吞吐的純 Key-Value 快取場景（微秒級延遲）仍以 Redis 更合適；PostgreSQL 可以「支援」這些場景的 Metadata / 交易一致部分，但不必然要取代專用系統。

本章重點

1. PostgreSQL 是採寬鬆授權、治理去中心化的物件關聯式資料庫，長期技術風險低。
2. 核心優勢是 ACID + MVCC + SQL 標準相容 + Extension 擴充性，讓它能透過擴充套件涵蓋 GIS、向量、時序等多種工作負載，而不必更換系統。
3. 選型時要區分「PostgreSQL 本身能力」與「透過 Extension 才具備的能力」，兩者的維運與風險屬性不同（詳見各章節）。

↑ 回目錄

第2章 PostgreSQL 19.x 版本狀態與新功能

2.1 版本狀態總表

Beta 版本代表功能已凍結（Feature Freeze），但仍在進行相容性與效能測試，官方明確表示不建議用於 Production；RC（Release Candidate）代表已鎖定候選版本，僅接受重大缺陷修補。依 PostgreSQL 官方 Roadmap（https://www.postgresql.org/developer/roadmap/），19 的正式發布目標為 2026 年 9 月；本手冊查證基準日（2026-08-19）尚未有 RC 版本釋出，GA 精確日期仍以屆時官方公告為準。

Beta 3 公告同時提到一項過渡期限定機制：Beta 階段內建「Protocol Grease Mode」（協定灰測模式），刻意在連線協定中加入非標準參數，用於及早發現生態圈（驅動程式、連線池、中介軟體）尚未正確處理未知協定欄位的相容性問題；此機制僅存在於 Beta 期間，GA 版會移除，企業測試 19 Beta 時若遇到連線層異常，應先確認是否為此機制觸發，而非直接視為正式回歸問題。

2.2 PostgreSQL 19（Beta）重點新功能

以下依官方 19 Release Notes（https://www.postgresql.org/docs/19/release-19.html）整理，全數標記為 Beta 階段功能，GA 前語法與預設值可能調整。為方便查閱，依「SQL 語法／查詢」「維運與儲存」「複寫」「效能與 I/O」「監控」「安全性」「資料型別與函式」「工具程式」七大分類呈現。

2.2.1 SQL 語法與查詢

2.2.2 維運與儲存指令

2.2.3 複寫（Replication）

2.2.4 效能與 I/O

2.2.5 監控與可觀測性

2.2.6 安全性

2.2.7 資料型別、函式與工具程式

🔍 待官方確認：以上功能於 GA 前仍可能因 RC 階段回饋而調整參數名稱或預設值，正式導入前請以 https://www.postgresql.org/docs/19/release-19.html 當時內容為準。

2.3 Breaking Changes／不相容變更完整清單

這是 PostgreSQL 19 對企業升級評估最關鍵的一節——多數團隊只關注「新功能」，卻在上線後才發現既有系統因「行為變更」而故障。以下依官方 Release Notes「不相容性」章節整理：

升級評估鐵律：任何大版本升級（尤其是含 Breaking Change 的版本）都必須先在非生產環境以正式資料的等量副本執行完整回歸測試，並使用 pg_upgrade --check 提前偵測 inet/cidr 索引、特殊命名等會被直接拒絕的情境。詳細升級步驟見第52、53章。

2.4 Stable / Beta / Deprecated 標示原則（本手冊通用）

2.5 Enterprise Impact 對照表

本章重點

1. 撰寫本手冊當下（2026-08-19），PostgreSQL 18 是唯一可放心用於 Production 的版本，19 仍在 Beta 3，官方 Roadmap 目標為 2026 年 9 月 GA。
2. PostgreSQL 19 的 Breaking Change 清單遠比多數團隊預期的長——standard_conforming_strings 強制開啟、inet/cidr 預設 Opclass 改變、max_locks_per_transaction／JIT／TOAST 壓縮預設值調整、RADIUS 移除，都可能讓「看似能直接升級」的系統在 pg_upgrade --check 階段就失敗，或上線後行為不符預期。
3. Parallel Autovacuum、原生 REPACK、Logical Replication 複寫 Sequence、Async I/O 自動擴縮，是本次最具維運價值的改動，建議優先納入 19 GA 後的升級評估重點；SQL/PGQ 與 Temporal Table（FOR PORTION OF）則是最具應用面想像空間的新能力。

↑ 回目錄

第3章 PostgreSQL 技術選型比較

3.1 RDBMS 選型比較表

3.2 PostgreSQL vs MongoDB：JSONB 能否取代文件資料庫？

flowchart LR
    A[需求：半結構化資料] --> B{需要交易一致性<br/>與關聯式查詢?}
    B -->|是| C[PostgreSQL + JSONB]
    B -->|否，且需要水平擴展<br/>與 Schema-less 彈性極致] --> D[MongoDB / 文件資料庫]
    C --> E[可混合 Relational + JSONB<br/>單一系統維運]
    D --> F[需另外處理跨文件一致性]

• PostgreSQL JSONB 適合：Schema 大致穩定但有少量彈性欄位（如商品屬性、表單資料、事件 Payload），且需要與關聯式資料一起查詢、交易。
• MongoDB 適合：真正 Schema-less、需要水平分片擴展到單一叢集無法負荷的規模、文件間幾乎不需要 Join。
• 實務上很多團隊誤用 MongoDB 存放「其實有明確關聯性」的資料，之後才發現需要應用層自行處理一致性——這類情境改用 PostgreSQL + JSONB 通常是更省維運成本的選擇。

3.3 PostgreSQL vs 專用 Vector Database（延伸見第28章）

本章重點

1. 選型不是「PostgreSQL 好不好」，而是「工作負載的一致性需求、查詢複雜度、擴展規模」三者的權衡。
2. JSONB 能覆蓋多數「以為需要文件資料庫」的場景，但不代表 PostgreSQL 可以無限水平擴展。
3. AI/向量場景，中小規模與需要一致性者優先 PostgreSQL + pgvector；十億級向量、極致水平擴展需求才考慮專用 Vector DB。

↑ 回目錄

第4章 PostgreSQL 系統架構總覽

4.1 Process-per-Connection 架構

PostgreSQL 採用 Process-per-Connection（每連線一個作業系統程序）架構，不同於 MySQL 常見的 Thread-per-Connection。這個決策從 1990 年代延續至今，優缺點都很明確：

flowchart TB
    APP[Application] --> DRIVER[Client Driver / libpq]
    DRIVER --> POSTMASTER[Postmaster<br/>主程序]
    POSTMASTER -->|fork| BE1[Backend Process 1]
    POSTMASTER -->|fork| BE2[Backend Process 2]
    POSTMASTER -->|fork| BE3[Backend Process N]
    POSTMASTER --> BGWRITER[Background Writer]
    POSTMASTER --> WALWRITER[WAL Writer]
    POSTMASTER --> CHECKPOINTER[Checkpointer]
    POSTMASTER --> AUTOVACUUM[Autovacuum Launcher]
    POSTMASTER --> STATS[Statistics Collector]
    BE1 & BE2 & BE3 --> SHAREDBUF[Shared Buffers]
    SHAREDBUF --> DATAFILES[(Data Files)]
    BE1 & BE2 & BE3 --> WAL[WAL Buffer]
    WAL --> WALFILES[(WAL Segment Files)]

• 優點：單一連線當機不會拖垮整個資料庫進程；作業系統層級的資源隔離與安全邊界清楚。
• 缺點：每個連線的記憶體開銷較高（相較 Thread 模型），因此 max_connections 不能無限拉高——這正是第25章 Connection Pooling 存在的原因。

4.2 核心行程說明

4.3 從 Client 到 Storage 的資料路徑

flowchart LR
    A[Application] --> B[Client Driver]
    B --> C[Backend Process]
    C --> D[Query Parser]
    D --> E[Query Rewriter]
    E --> F["Planner / Optimizer"]
    F --> G[Executor]
    G --> H[Buffer Manager]
    H --> I[Shared Buffers]
    I -->|Cache Miss| J[(Storage Manager)]
    G --> K[WAL]
    K --> L[(WAL Archive)]

本章重點

1. Process-per-Connection 是理解「為什麼不能無限開連線」「為什麼需要連線池」的根本原因。
2. Shared Buffers、WAL、Checkpoint、Autovacuum 是四個決定效能與穩定性的關鍵背景機制，後續章節會逐一深入。
3. DBA 調校前務必先理解這張架構圖——多數效能問題最終都能對應回其中某一個元件。

↑ 回目錄

第5章 PostgreSQL Internal Architecture：一條 SQL 的旅程

5.1 Query Processing Pipeline

以下面這條查詢為例：

SELECT customer_id, customer_name
FROM customer
WHERE customer_id = 100;

它會依序經過：

flowchart TB
    SQL["SQL 字串"] --> PARSER["Parser<br/>語法解析，產生 Parse Tree"]
    PARSER --> ANALYZER["Analyzer<br/>語意分析：確認資料表/欄位存在、型別檢查"]
    ANALYZER --> REWRITER["Rewriter<br/>套用 View、Rule 展開"]
    REWRITER --> PLANNER["Planner / Optimizer<br/>依統計資訊產生成本最低的執行計畫"]
    PLANNER --> EXECUTOR["Executor<br/>依 Plan Tree 逐步執行"]
    EXECUTOR --> STORAGE["Storage Manager<br/>透過 Buffer Manager 存取資料頁"]

5.2 MVCC 與 Transaction Manager 的角色

Executor 存取資料時，並非直接讀「最新資料」，而是透過 Snapshot 判斷哪些 Row Version 對目前交易可見——這就是 MVCC（見第17章）的核心機制，也是 PostgreSQL「讀不阻塞寫、寫不阻塞讀」的原因。

5.3 Lock Manager 與 Catalog

• Lock Manager：管理資料表、資料列等各層級的鎖，避免多個交易同時修改造成資料不一致（見第17章 Deadlock）。
• System Catalog（pg_catalog schema）：PostgreSQL 把「資料表結構本身」也存成資料表（如 pg_class、pg_attribute），這也是為什麼 PostgreSQL 可以用 SQL 查詢自己的 Metadata——這個特性在第32章「Reverse Engineering」會大量運用。

-- 查詢目前資料庫有哪些資料表（示範 Catalog 可被 SQL 查詢）
SELECT relname, relkind
FROM pg_class
WHERE relnamespace = 'public'::regnamespace
  AND relkind = 'r';

本章重點

1. 一條 SQL 從字串到結果，會依序經過 Parser → Analyzer → Rewriter → Planner → Executor，理解這個流程是診斷「查詢為什麼跑不動」的基礎。
2. 統計資訊（pg_statistic）過期會讓 Planner 做出錯誤決策——這是實務上最常見卻最容易被忽略的效能問題根因。
3. System Catalog 本身也是資料表，AI Agent／逆向工程場景可以直接用 SQL 查詢 Schema Metadata（第32、49章延伸）。

↑ 回目錄

第6章 安裝與部署：Windows

6.1 安裝步驟（PostgreSQL 18 Production／19 Beta 評估環境皆適用）

1. 至官方下載頁取得 Windows Installer（EDB 提供的官方打包版）
2. 執行安裝程式，選擇安裝路徑（預設 C:\Program Files\PostgreSQL\18）
3. 設定 Data Directory（預設 C:\Program Files\PostgreSQL\18\data）——生產環境建議指向獨立磁碟，而非系統碟
4. 設定 Port（預設 5432）
5. 設定 Superuser（postgres）密碼——企業環境務必使用強密碼並存入密鑰管理系統，不寫在安裝腳本中
6. 選擇 Locale（建議 en_US.UTF-8 或依企業標準統一，避免跨系統排序行為不一致）
7. 安裝完成後會自動註冊為 Windows Service（postgresql-x64-18）
8. 確認安裝目錄下的 bin 已加入 PATH 環境變數（或每次以完整路徑呼叫）

6.2 驗證安裝

psql --version

# 使用 postgres 超級使用者連線（會提示輸入密碼）
psql -U postgres -h localhost -p 5432

6.3 服務管理

# 查詢服務狀態
Get-Service -Name "postgresql-x64-18"

# 啟動 / 停止 / 重啟服務
Start-Service -Name "postgresql-x64-18"
Stop-Service -Name "postgresql-x64-18"
Restart-Service -Name "postgresql-x64-18"

6.4 pgAdmin

pgAdmin 是官方推薦的圖形化管理工具，安裝程式通常會一併安裝。首次啟動需連線至剛安裝的伺服器（Host: localhost、Port: 5432、User: postgres）。

Production 注意事項：pgAdmin 的連線密碼預設會被瀏覽器/應用程式加密儲存於本機，共用工作站環境務必評估是否採用個別帳號登入，而非共用 postgres 超級使用者。

本章重點

1. Windows 安裝的關鍵決策點是 Data Directory 位置、Port、Superuser 密碼與 Locale——四者事後修改成本都不低，安裝前先確認企業規範。
2. Service 化後可用標準 PowerShell 服務指令管理，方便納入既有 Windows 維運流程。

↑ 回目錄

第7章 安裝與部署：Linux

7.1 Ubuntu / Debian

sudo apt update
sudo apt install -y postgresql postgresql-contrib

# 確認版本（發行版套件庫版本可能落後官方最新版，企業環境建議改用 PGDG 官方套件庫）
psql --version

企業正式環境建議加入 PGDG（PostgreSQL Global Development Group）官方套件庫，才能取得最新且與官方文件一致的版本：

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg
sudo apt update
sudo apt install -y postgresql-18

7.2 RHEL / Rocky Linux / AlmaLinux

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql18-server postgresql18-contrib
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18

7.3 服務、使用者、目錄

# systemd 服務管理
sudo systemctl status postgresql-18
sudo systemctl restart postgresql-18

# 切換至 postgres 系統帳號操作
sudo -iu postgres psql

7.4 Port 與防火牆

# 確認監聽 Port（預設 5432）
sudo ss -tlnp | grep 5432

# firewalld 環境開放 Port（僅在真正需要外部連線時開放，內網服務建議維持預設關閉）
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

Security 提醒：預設安裝的 listen_addresses 通常僅監聽 localhost。除非有明確需求（例如獨立資料庫伺服器），否則不建議直接開放 0.0.0.0 對外監聽——應用程式連線建議透過內網、VPN 或連線池服務轉接（第10、25章延伸）。

本章重點

1. 生產環境建議使用 PGDG 官方套件庫而非發行版內建套件庫，確保版本與官方文件、Release Notes 一致。
2. 所有 PostgreSQL 程序以 postgres 系統帳號執行，維運指令與檔案權限管理需圍繞這個帳號設計。
3. 防火牆與 listen_addresses 是兩道獨立防線，兩者都需要「預設拒絕、按需開放」。

↑ 回目錄

第8章 安裝與部署：Docker／Podman／Kubernetes

8.1 核心觀念：Container 可被刪除，Data 不行

flowchart LR
    APP[Application] --> CONTAINER["PostgreSQL Container<br/>（可隨時重建）"]
    CONTAINER --> VOLUME[("Persistent Volume<br/>（資料實際所在）")]

Container 被刪除 ≠ Database Data 必須消失。 這是 Container 化 PostgreSQL 最核心也最容易被新手忽略的觀念：資料必須掛載在 Persistent Volume，Container 本身應視為無狀態、可隨時重建的執行單元。

8.2 Docker Compose 範例

services:
  postgres:
    image: postgres:18
    container_name: pg18
    restart: unless-stopped
    environment:
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
      POSTGRES_DB: appdb
    ports:
      - "5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
    secrets:
      - pg_password
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app_user -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5

secrets:
  pg_password:
    file: ./secrets/pg_password.txt

volumes:
  pg_data:

安全提醒：範例使用 POSTGRES_PASSWORD_FILE 搭配 Docker Secret，而非直接把密碼寫在 environment 或 .env 提交進 Git——這是本手冊第38章 DevSecOps 會反覆強調的原則。

8.3 Podman（Rootless 部署）

podman run -d \
  --name pg18 \
  -e POSTGRES_USER=app_user \
  -e POSTGRES_PASSWORD_FILE=/run/secrets/pg_password \
  -e POSTGRES_DB=appdb \
  -v pg_data:/var/lib/postgresql/data:Z \
  -p 5432:5432 \
  --secret pg_password \
  docker.io/library/postgres:18

Podman 預設支援 Rootless 執行，容器內程序不具備主機 root 權限，是企業安全基準常見要求。:Z 標記用於 SELinux 環境正確設定 Volume 標籤。

8.4 備份 / 還原 / 升級（Container 場景）

# 備份（透過容器內的 pg_dump）
docker exec -t pg18 pg_dump -U app_user -d appdb -F c -f /tmp/appdb.dump
docker cp pg18:/tmp/appdb.dump ./backup/appdb.dump

# 還原至新容器
docker cp ./backup/appdb.dump pg18_new:/tmp/appdb.dump
docker exec -t pg18_new pg_restore -U app_user -d appdb -c /tmp/appdb.dump

Container 場景的大版本升級（例如 18 → 19）不能只是換 image tag 重啟——PostgreSQL 的 Data Directory 版本與伺服器版本綁定，直接換版本會導致啟動失敗。正確做法是：pg_dump/pg_restore 或掛載新版容器執行 pg_upgrade（詳見第52、53章）。

8.5 Kubernetes：StatefulSet 與 Operator

裸 StatefulSet 部署 PostgreSQL 需要自行處理 Failover、備份排程、憑證輪替，企業場景更常見的做法是採用 PostgreSQL Operator：

# CloudNativePG 最小化 Cluster 範例（概念示意，實際欄位請以官方 CRD 文件為準）
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-cluster
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:18
  storage:
    size: 100Gi
  bootstrap:
    initdb:
      database: appdb
      owner: app_user

🔍 待官方確認：CloudNativePG CRD 欄位會隨版本演進，正式部署前請以當時官方文件為準，不要直接複製本手冊範例上線。

本章重點

1. Container 化 PostgreSQL 的第一原則：資料在 Volume，不在 Container；Container 應視為可拋棄的執行單元。
2. 密碼／憑證一律透過 Secret 機制注入，不寫進 image 或 Compose 檔案明文提交。
3. Kubernetes 上的 PostgreSQL 生產部署建議採用成熟 Operator（如 CloudNativePG），而非自行維護裸 StatefulSet 的 Failover 邏輯。

↑ 回目錄

第9章 PostgreSQL 設定

9.1 三個設定檔的分工

9.2 修改設定的三種方式與差異

-- 方式一：ALTER SYSTEM（寫入 postgresql.auto.conf，需 reload 或 restart 生效，依參數而定）
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

-- 方式二：SET（僅影響目前 Session，斷線即失效）
SET work_mem = '64MB';

-- 方式三：查詢目前生效值與是否需要重啟
SHOW work_mem;
SELECT name, setting, unit, context
FROM pg_settings
WHERE name = 'work_mem';

pg_settings.context 欄位標示該參數的生效方式：internal（不可改）、postmaster（需重啟）、sighup（reload 即可）、user（Session 層級可改）等——修改參數前先查這個欄位，避免以為 reload 生效實際上需要重啟。

9.3 重要參數（分類整理）

不要提供「一套參數適用所有 Production」的錯誤設定。 上述所有效能相關參數，必須依 CPU、RAM、Storage 類型（HDD/SSD/NVMe）、Workload 特性（OLTP/OLAP/混合）、Connection 數量與實際 Query Pattern 調校，並透過第24章的 Measure → Explain → Optimize → Benchmark 流程驗證，而不是抄一份網路上的「建議值」直接套用。

9.4 常見設定錯誤

本章重點

1. postgresql.conf 管行為、pg_hba.conf 管連線認證，兩者職責不同、不要混淆。
2. 每個參數修改前，先用 pg_settings.context 確認生效方式，避免「改了沒生效」的誤判。
3. 效能參數沒有放諸四海皆準的預設值，一切依實際 Workload 與監控數據調校。

↑ 回目錄

第10章 Authentication 與 Security

10.1 pg_hba.conf 的判斷邏輯

pg_hba.conf 由上而下逐行比對，第一個符合的規則生效，格式為：

# TYPE  DATABASE  USER  ADDRESS  METHOD
host    appdb     app_user  10.0.0.0/24   scram-sha-256
host    all       postgres  127.0.0.1/32  scram-sha-256
local   all       all                     peer

PostgreSQL 19 移除了 RADIUS 認證（UDP-based，官方認定不安全），若既有系統仍使用 RADIUS，升級前必須改為 LDAP、Kerberos 或憑證認證。

10.2 企業安全模型

flowchart TB
    A[Application Role<br/>應用程式專用帳號] --> B[Database Role<br/>對應到 Database 層級權限]
    B --> C[Schema Privilege<br/>USAGE / CREATE]
    C --> D[Table Privilege<br/>SELECT / INSERT / UPDATE / DELETE]
    D --> E[Column / Row Level Security<br/>細粒度控管]

-- 最小權限原則範例：應用程式帳號只給實際需要的權限
CREATE ROLE app_service LOGIN PASSWORD 'use-a-secret-manager-generated-value';
GRANT CONNECT ON DATABASE appdb TO app_service;
GRANT USAGE ON SCHEMA app TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_service;

-- Row Level Security：限制每個租戶只能看到自己的資料
ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON app.orders
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

10.3 Least Privilege 與 Separation of Duties

10.4 SQL Injection 與 Parameterized Query

// ❌ 錯誤：字串拼接，SQL Injection 風險
String sql = "SELECT * FROM customer WHERE name = '" + userInput + "'";

// ✅ 正確：Parameterized Query，交給 Driver / JPA 處理跳脫
String sql = "SELECT * FROM customer WHERE name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, userInput);

PostgreSQL 本身不會替你避免 SQL Injection——這是應用層的責任。任何直接把使用者輸入拼接進 SQL 字串的程式碼，無論是傳統後端或 AI Agent 產生的程式碼，都必須視為高風險並在 Code Review 中攔截（延伸見第46章 AI Agent SQL 安全分級）。

本章重點

1. pg_hba.conf 由上而下比對，規則順序本身就是安全設計的一部分。
2. 企業安全模型是一條鏈：Role → Database → Schema → Table → Row/Column，每一層都要遵守最小權限。
3. SQL Injection 防護是應用層責任，Parameterized Query 是唯一可靠的做法，不論程式碼由人或 AI Agent 撰寫。

↑ 回目錄

第11章 Database／Schema／Role 設計

11.1 三層次概念釐清

11.2 Enterprise Role Model 範例

BEGIN;

-- 建立資料庫與 Schema
CREATE DATABASE appdb;
\c appdb
CREATE SCHEMA app;
CREATE SCHEMA audit;

-- 建立「權限群組」角色（不可登入，僅作為權限容器）
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;

GRANT USAGE ON SCHEMA app TO app_readonly, app_readwrite;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;

-- 未來新建的資料表自動套用權限（避免每次手動 GRANT）
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- 建立實際登入帳號，繼承群組權限
CREATE ROLE svc_reporting LOGIN PASSWORD 'from-secret-manager' IN ROLE app_readonly;
CREATE ROLE svc_backend LOGIN PASSWORD 'from-secret-manager' IN ROLE app_readwrite;

COMMIT;

11.3 常見反模式

本章重點

1. Database 是硬隔離邊界，Schema 是邏輯分組，Role 兼具使用者與群組兩種型態。
2. 建議用 NOLOGIN 角色作為權限模板，登入帳號透過 IN ROLE 繼承，權限異動只需改模板角色。
3. ALTER DEFAULT PRIVILEGES 是常被忽略但非常關鍵的設定，避免「新資料表沒權限」的低級問題。

↑ 回目錄

第12章 SQL 基礎語法

12.1 基本 CRUD

-- SELECT
SELECT customer_id, customer_name, created_at
FROM app.customer
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- INSERT
INSERT INTO app.customer (customer_name, status)
VALUES ('Acme Corp', 'active')
RETURNING customer_id;

-- UPDATE（Production 建議先 SELECT 確認範圍再 UPDATE）
BEGIN;
SELECT count(*) FROM app.customer WHERE status = 'pending' AND created_at < now() - interval '30 days';
UPDATE app.customer
SET status = 'expired'
WHERE status = 'pending' AND created_at < now() - interval '30 days';
COMMIT;

-- DELETE（同樣先確認範圍）
BEGIN;
SELECT count(*) FROM app.customer WHERE status = 'expired' AND created_at < now() - interval '1 year';
DELETE FROM app.customer
WHERE status = 'expired' AND created_at < now() - interval '1 year';
COMMIT;

Production destructive operation 必須經過審核、Backup / Recovery Strategy 與 Change Management。 本章所有 UPDATE/DELETE 範例皆先用 SELECT 驗證影響範圍，這是實務上避免「誤刪整張表」最基本也最有效的習慣。

12.2 MERGE（PostgreSQL 15+）

MERGE INTO app.inventory AS target
USING app.inventory_staging AS source
ON target.sku = source.sku
WHEN MATCHED THEN
  UPDATE SET quantity = source.quantity, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (sku, quantity, updated_at)
  VALUES (source.sku, source.quantity, now());

12.3 JOIN / GROUP BY / HAVING

SELECT c.customer_name, count(o.order_id) AS order_count, sum(o.total_amount) AS total_spent
FROM app.customer c
LEFT JOIN app.orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_name
HAVING count(o.order_id) > 5
ORDER BY total_spent DESC;

12.4 CTE 與 Recursive CTE

-- 一般 CTE：拆解複雜查詢，提升可讀性
WITH active_customers AS (
  SELECT customer_id, customer_name FROM app.customer WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, order_id, total_amount
  FROM app.orders
  WHERE order_date >= now() - interval '90 days'
)
SELECT ac.customer_name, ro.order_id, ro.total_amount
FROM active_customers ac
JOIN recent_orders ro ON ro.customer_id = ac.customer_id;

-- Recursive CTE：組織架構、分類樹等階層資料的標準做法
WITH RECURSIVE org_tree AS (
  SELECT employee_id, manager_id, employee_name, 1 AS level
  FROM app.employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.employee_name, ot.level + 1
  FROM app.employee e
  JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY level, employee_id;

12.5 Window Function

SELECT
  order_id,
  customer_id,
  total_amount,
  row_number() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn,
  sum(total_amount) OVER (PARTITION BY customer_id) AS customer_total
FROM app.orders;

12.6 集合運算與子查詢

-- UNION / INTERSECT / EXCEPT
SELECT customer_id FROM app.vip_customer
EXCEPT
SELECT customer_id FROM app.churned_customer;

-- EXISTS 通常比 IN 對大表更有效率（Planner 可及早短路）
SELECT c.customer_id, c.customer_name
FROM app.customer c
WHERE EXISTS (
  SELECT 1 FROM app.orders o WHERE o.customer_id = c.customer_id
);

本章重點

1. UPDATE/DELETE 前先用對應條件 SELECT 驗證影響範圍，是最基本也最重要的 Production 習慣。
2. Recursive CTE 是處理階層資料（組織架構、分類樹、依賴關係）的標準工具，不需要在應用層手刻遞迴查詢。
3. Window Function 能在同一次查詢內同時取得明細與彙總，比額外的子查詢／應用層計算更有效率。

↑ 回目錄

第13章 PostgreSQL Data Types

13.1 數值型別

13.2 numeric vs floating point：金融系統的第一課

-- ❌ 錯誤：浮點數的精度誤差在金額計算中會累積成真實的金錢損失
SELECT 0.1::double precision + 0.2::double precision; -- 結果不精確等於 0.3

-- ✅ 正確：金額欄位一律使用 numeric，並明確指定精度與小數位數
CREATE TABLE app.ledger (
  entry_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount      numeric(18, 2) NOT NULL,
  currency    char(3) NOT NULL
);

企業金融系統原則：任何代表金錢的欄位（金額、匯率、利率）一律使用 numeric，絕不使用 real/double precision。numeric 的效能成本相對浮點數略高，但這是正確性換取效能的必要取捨。

13.3 timestamp vs timestamptz

CREATE TABLE app.audit_log (
  event_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),  -- ✅ 建議：儲存時區資訊
  legacy_time timestamp                             -- ⚠️ 無時區資訊，容易在跨時區系統產生錯誤
);

Best Practice：除非有明確理由（例如儲存「使用者本地時鐘上顯示的時間，不隨時區轉換」的特殊需求），一律使用 timestamptz，並讓應用層負責顯示時的時區轉換。

13.4 json vs jsonb

CREATE TABLE app.event_log (
  event_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_event_log_payload ON app.event_log USING gin (payload);

-- JSONB 查詢範例
SELECT * FROM app.event_log WHERE payload @> '{"event_type": "order_created"}';
SELECT payload ->> 'order_id' AS order_id FROM app.event_log WHERE payload ->> 'event_type' = 'order_created';

13.5 其他重要型別

本章重點

1. 金額一律用 numeric，時間一律優先用 timestamptz——這兩條是企業系統資料型別選型最容易出錯也最不能出錯的地方。
2. jsonb 是絕大多數半結構化資料的預設選擇，json 僅在需要保留原始格式時使用。
3. enum 提供緊湊儲存但犧牲彈性，選項會頻繁變動的欄位建議改用 varchar + 對照表或 CHECK 約束。

↑ 回目錄

第14章 Table Design 與正規化

14.1 完整性約束

CREATE TABLE app.orders (
  order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES app.customer(customer_id),
  order_no     varchar(32) NOT NULL UNIQUE,
  total_amount numeric(18,2) NOT NULL CHECK (total_amount >= 0),
  status       varchar(20) NOT NULL DEFAULT 'pending',
  order_date   timestamptz NOT NULL DEFAULT now(),
  tax_amount   numeric(18,2) GENERATED ALWAYS AS (total_amount * 0.05) STORED
);

14.2 正規化層級快速回顧

14.3 何時該刻意反正規化（Denormalization）

-- 反正規化範例：報表用的彙總欄位，犧牲一致性風險換取查詢效能
ALTER TABLE app.customer ADD COLUMN total_order_amount numeric(18,2) DEFAULT 0;
-- 需搭配 Trigger 或排程 Job 維護一致性，並在文件中明確記載此欄位為「衍生資料」

反正規化不是原罪，但必須是刻意的決策並有配套機制（Trigger／排程／CDC 保持同步），而不是因為「懶得 Join」而複製資料。

本章重點

1. 資料庫層的約束（FK/CHECK/UNIQUE）是資料完整性的最後一道防線，不能只靠應用層驗證。
2. 正規化到 3NF 是多數 OLTP 系統的合理預設，反正規化必須是有配套維護機制的刻意決策。
3. GENERATED ALWAYS AS IDENTITY 是目前建議的自增主鍵寫法，新專案不建議再用舊式 SERIAL。

↑ 回目錄

第15章 Index 設計與應用

15.1 索引類型總覽

15.2 進階索引技巧

-- Composite Index：多欄位查詢，欄位順序影響能否被使用
CREATE INDEX idx_orders_customer_date ON app.orders (customer_id, order_date DESC);

-- Partial Index：只索引常查詢的子集合，縮小索引體積、加快維護速度
CREATE INDEX idx_orders_pending ON app.orders (order_date) WHERE status = 'pending';

-- Expression Index：索引運算後的值
CREATE INDEX idx_customer_email_lower ON app.customer (lower(email));

-- Covering Index（INCLUDE）：讓查詢可以只靠索引回傳，不需回表（Index Only Scan）
CREATE INDEX idx_orders_covering ON app.orders (customer_id) INCLUDE (order_date, total_amount);

15.3 用 EXPLAIN ANALYZE 驗證索引是否被使用

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, order_date, total_amount
FROM app.orders
WHERE customer_id = 12345
ORDER BY order_date DESC
LIMIT 10;

Limit  (cost=0.42..8.86 rows=10 width=24) (actual time=0.03..0.05 rows=10 loops=1)
  ->  Index Scan using idx_orders_customer_date on orders
        (cost=0.42..120.30 rows=142 width=24) (actual time=0.03..0.04 rows=10 loops=1)
        Index Cond: (customer_id = 12345)
Planning Time: 0.15 ms
Execution Time: 0.08 ms

• Index Scan 表示索引被使用；若看到 Seq Scan 掃描整張大表卻預期應該走索引，通常代表：索引不存在、統計資訊過期、或 Planner 判斷 Seq Scan 成本更低（例如查詢會回傳表中大比例的資料列）。
• Index Only Scan 代表完全不需回表，是效能最佳的情境，通常需要 Covering Index（INCLUDE）配合。

15.4 常見錯誤

-- 找出從未被使用的索引（定期檢視候選刪除清單）
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

本章重點

1. B-tree 是預設且涵蓋面最廣的索引類型，GIN／BRIN／GiST 是針對特定資料型態的專門優化。
2. 索引不是越多越好，每個索引都是「查詢加速」與「寫入/儲存成本」的取捨，需定期用 pg_stat_user_indexes 檢視使用率。
3. EXPLAIN (ANALYZE, BUFFERS) 是驗證索引是否生效的標準工具，永遠不要憑感覺猜測執行計畫。

↑ 回目錄

第16章 Query Planner／Optimizer 與 EXPLAIN

16.1 Cost-Based Optimizer 的運作原理

PostgreSQL 的 Planner 是 Cost-Based：它依據 pg_statistic（由 ANALYZE 收集）中的資料分布統計，估算每種可能執行路徑（Seq Scan、Index Scan、Bitmap Scan、各種 Join 演算法）的成本，選出估計成本最低的方案——不是最快，而是「估計」最快，這就是為何統計資訊準確度直接影響查詢效能。

16.2 常見 Join 策略

16.3 掃描策略

Seq Scan      —— 全表掃描，資料量小或篩選比例低時反而是最優解
Index Scan    —— 依索引逐筆定位並回表取得完整資料列
Bitmap Scan   —— 先收集符合條件的頁面位置成 Bitmap，再批次讀取，適合中等選擇性的查詢

16.4 閱讀 EXPLAIN ANALYZE 的實務技巧

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.customer_name, sum(o.total_amount)
FROM app.customer c
JOIN app.orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= now() - interval '30 days'
GROUP BY c.customer_name;

閱讀順序建議：

1. 由內而外、由下而上看 Plan Tree，最底層節點先執行
2. 比對 estimated rows（Planner 預估）與 actual rows（實際回傳），差距過大（例如預估 10 筆、實際 10 萬筆）通常代表統計資訊過期，需要 ANALYZE
3. 注意 Execution Time 與各節點的 actual time，找出耗時最高的節點
4. BUFFERS 選項可看出 Cache Hit（shared hit）與實際磁碟讀取（shared read）的比例

16.5 Parallel Query、Memoize、Incremental Sort

• Parallel Query：大型 Seq Scan／Aggregate 可由多個 Worker 平行處理，受 max_parallel_workers_per_gather 等參數控制。
• Memoize：對重複出現的 Join Key 快取結果，避免 Nested Loop 對相同的內側查詢重複執行。
• Incremental Sort：當資料已依部分排序鍵排序時，只需針對剩餘鍵做局部排序，減少整體排序成本。

本章重點

1. Planner 的所有決策都建立在統計資訊之上，estimated rows 與 actual rows 差距過大是最重要的排查訊號。
2. 沒有「最好的 Join 策略」，只有「適合該資料分布與資料量的策略」——這也是為何同一條 SQL 在不同資料量下可能走完全不同的執行計畫。
3. 效能調校永遠從 EXPLAIN ANALYZE 的實際數據出發，禁止憑經驗法則臆測（詳見第24章方法論）。

↑ 回目錄

第17章 Transaction／MVCC／Concurrency

17.1 ACID 與 MVCC 的關係

MVCC（Multi-Version Concurrency Control，多版本併發控制）讓 PostgreSQL 得以實現「讀不阻塞寫、寫不阻塞讀」：每次 UPDATE 並非覆寫原資料列，而是產生新版本，舊版本依交易可見性規則保留一段時間（直到 VACUUM 回收，見第18章）。每個交易看到的是一份基於其 Snapshot 的一致資料視圖。

17.2 四種隔離等級

BEGIN ISOLATION LEVEL READ COMMITTED;   -- PostgreSQL 預設
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

PostgreSQL 沒有 Dirty Read（讀到未提交資料）這個問題，即使在最低的 Read Committed 等級也不會發生——這是與部分其他資料庫預設行為的重要差異。

17.3 Deadlock 實務案例

Transaction A:                    Transaction B:
UPDATE accounts                   UPDATE accounts
  SET balance = balance - 100       SET balance = balance - 50
  WHERE account_id = 1;             WHERE account_id = 2;
-- 持有 account_id=1 的鎖            -- 持有 account_id=2 的鎖

UPDATE accounts                   UPDATE accounts
  SET balance = balance + 100       SET balance = balance + 50
  WHERE account_id = 2;             WHERE account_id = 1;
-- 等待 A 釋放 account_id=2         -- 等待 B 釋放 account_id=1
-- ⇒ Deadlock，PostgreSQL 會自動偵測並終止其中一個交易

預防方式：確保所有交易以一致的順序存取資源（例如永遠先鎖 account_id 較小的那筆）。

-- 診斷目前的鎖等待狀況
SELECT
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

17.4 Blocking vs Deadlock

• Blocking：交易 B 等待交易 A 釋放鎖，屬正常現象，只要 A 最終會提交/回滾即可解決；長時間 Blocking 通常是「交易開太久沒提交」造成。
• Deadlock：兩個以上交易互相等待對方釋放鎖，形成循環，PostgreSQL 會自動偵測並終止其中一方（回傳 deadlock detected 錯誤），應用層需要有重試邏輯。

本章重點

1. MVCC 是 PostgreSQL 高併發能力的根本機制，理解「讀寫互不阻塞」背後是「多版本」而非「鎖免除」。
2. Read Committed 是預設且多數應用場景足夠，Serializable 提供最強保證但需要應用層處理重試。
3. Deadlock 無法完全避免，但可透過「一致的資源存取順序」大幅降低發生機率；應用層必須對 deadlock detected 有重試機制。

↑ 回目錄

第18章 VACUUM／Autovacuum／REPACK

18.1 為什麼需要 VACUUM

MVCC 的代價是：UPDATE／DELETE 產生的舊版本資料列（Dead Tuple）不會立刻消失，需要 VACUUM 回收。長期不清理會導致：

• Table／Index Bloat（膨脹）：實際占用空間遠大於有效資料量，拖慢掃描與 I/O
• Transaction ID Wraparound：PostgreSQL 的交易 ID 是有限範圍的循環計數器，若長期不 VACUUM FREEZE，理論上可能面臨 ID 回繞風險（現代版本有多重保護機制會提前警告甚至強制唯讀，但這是必須主動避免、不是「順其自然」的風險）

flowchart LR
    A[UPDATE / DELETE] --> B[產生 Dead Tuple]
    B --> C{Autovacuum 是否<br/>即時清理?}
    C -->|是| D[空間回收，統計更新]
    C -->|否，累積過多| E[Table/Index Bloat]
    E --> F[查詢變慢、儲存成本上升]

18.2 VACUUM 的幾種型態

VACUUM app.orders;                 -- 標準 VACUUM：回收空間供重複使用，不釋放給作業系統，不阻塞讀寫
VACUUM (VERBOSE, ANALYZE) app.orders;  -- 同時更新統計資訊，並輸出詳細進度
VACUUM FULL app.orders;            -- 重建整張表，實際釋放磁碟空間，但需要 ACCESS EXCLUSIVE 鎖，會阻塞讀寫
ANALYZE app.orders;                -- 僅更新統計資訊，不回收空間

PostgreSQL 19（Beta）新增 REPACK：REPACK app.orders; 可達到接近 VACUUM FULL 的空間回收效果，搭配 REPACK (CONCURRENTLY) app.orders; 可在不長期鎖表的情況下完成，是取代第三方 pg_repack 擴充套件的原生方案。GA 前語法仍可能微調，正式導入前請確認當時官方文件。

18.3 Autovacuum 調校

-- 全域參數（postgresql.conf）
-- autovacuum = on
-- autovacuum_vacuum_scale_factor = 0.2   -- 預設：Dead Tuple 超過 20% 才觸發，大表可能過於保守
-- autovacuum_vacuum_cost_delay = 2ms

-- 針對特定大表（高頻寫入）調整更積極的觸發門檻
ALTER TABLE app.orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 0
);

PostgreSQL 19（Beta）引入 Parallel Autovacuum（伺服器層級 autovacuum_max_parallel_workers、資料表層級 autovacuum_parallel_workers，允許索引清理平行化）與 Scoring 系統（由 autovacuum_vacuum_score_weight、autovacuum_vacuum_insert_score_weight、autovacuum_analyze_score_weight、autovacuum_freeze_score_weight、autovacuum_multixact_freeze_score_weight 五個權重參數共同決定優先處理哪些資料表，決策依據可透過新視圖 pg_stat_autovacuum_scores 查詢），目標是解決「大量資料表競爭同一批 Autovacuum Worker」的排隊問題。GA 前建議先在測試環境驗證對 CPU 使用率的影響（詳見第2章 2.2.4）。

18.4 DBA 維護策略

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

本章重點

1. VACUUM 不是「錦上添花」的維護，而是 MVCC 機制正常運作的必要條件，長期忽視會導致 Bloat 甚至 Wraparound 風險。
2. VACUUM FULL 會鎖表，Production 環境優先評估 PostgreSQL 19 的 REPACK CONCURRENTLY 或維護窗口執行。
3. 大表建議個別調整 Autovacuum 觸發門檻，不要只依賴全域預設值。

↑ 回目錄

第19章 WAL（Write-Ahead Logging）

19.1 核心概念

Write-Ahead Logging（預寫日誌）：任何資料變更必須先寫入 WAL 並確保落盤，才能回報交易提交成功——即使資料檔案本身的變更尚未寫回磁碟，也能透過 WAL 在當機後重建。這是 PostgreSQL 達成 Durability（持久性） 的核心機制。

flowchart TB
    T[Transaction 提交] --> W[WAL Buffer]
    W -->|fsync 落盤| WF[(WAL Segment File)]
    WF -->|回報提交成功| T
    W -.異步.-> DF[(Data File，由 Background Writer 稍後寫入)]
    WF --> ARCHIVE[(WAL Archive，供 PITR / 複寫使用)]

19.2 Checkpoint 的角色

Checkpoint 會將目前 Shared Buffers 中所有 Dirty Page 強制寫入資料檔案，並記錄一個「此時間點之前的資料都已落盤」的標記——Crash Recovery 只需要重播 Checkpoint 之後的 WAL，而不必從資料庫建立之初開始重播。checkpoint_timeout 與 max_wal_size 共同控制 Checkpoint 頻率：頻率太低會拉長 Recovery 時間，頻率太高會增加 I/O 負擔（Full Page Write 的成本）。

19.3 Point-in-Time Recovery（PITR）概念

Base Backup（某時間點的完整快照）
        |
        v
   WAL Archive（持續累積的異動紀錄）
        |
        v
還原至 Base Backup ──重播 WAL 至指定時間點── 任意精確到秒的還原點

PITR 讓企業可以還原到「誤刪資料發生前一秒」，而不只是還原到最近一次的每日備份——這是第20章 Backup／Restore 與第55章 Disaster Recovery 的技術基礎。

本章重點

1. WAL 是 PostgreSQL Durability 的根本機制：先寫日誌、後寫資料，當機也不遺失已提交的交易。
2. Checkpoint 頻率是「Recovery 時間」與「日常 I/O 負擔」的取捨，需依 RTO 要求與硬體條件調校。
3. WAL Archive 是 PITR 與複寫（Streaming／Logical Replication）共同的基礎設施，備份策略設計必須將其納入考量。

↑ 回目錄

第20章 Backup／Restore

20.1 邏輯備份 vs 實體備份

# 邏輯備份：單一資料庫，自訂格式（可搭配 pg_restore 的平行還原）
pg_dump -U postgres -d appdb -F c -f appdb.dump

# 邏輯備份：整個叢集（含所有 Database、Role）
pg_dumpall -U postgres -f cluster_backup.sql

# 還原
pg_restore -U postgres -d appdb_restored -j 4 appdb.dump

Production destructive operation 必須經過審核、Backup / Recovery Strategy 與 Change Management。 任何 Restore 到既有 Database 前，務必先確認目標是全新／測試環境，避免覆蓋 Production 資料。

20.2 pgBackRest 與 WAL-G

企業級實體備份很少直接手刻 pg_basebackup + Archive 腳本，通常採用成熟工具：

20.3 備份方式比較

20.4 Backup 反模式

Backup 必須定期 Restore Test。 「有備份」不等於「備份可用」——沒有經過還原驗證的備份，只是「看起來安心」。企業實務應定期（例如每季）於隔離環境執行完整 Restore 演練，並記錄實際還原所需時間，作為 RTO 評估依據（第54、55章延伸）。

本章重點

1. 邏輯備份跨版本相容性佳但還原慢，實體備份還原快且可搭配 PITR，但受限同版本；企業通常兩者並行。
2. 大型系統建議採用 pgBackRest 或 WAL-G 等成熟工具，而非自行維護 Archive 腳本。
3. 備份必須定期做 Restore Test，這是本手冊反覆強調的 Golden Rule 之一（見第69章）。

↑ 回目錄

第21章 High Availability

21.1 Streaming Replication 基礎

flowchart LR
    PRIMARY[(Primary)] -->|WAL Stream| REPLICA1[(Replica 1<br/>Synchronous)]
    PRIMARY -->|WAL Stream| REPLICA2[(Replica 2<br/>Asynchronous)]
    APP[Application] -->|寫入| PRIMARY
    APP -->|唯讀查詢/報表| REPLICA2

21.2 Failover、Switchover、Split Brain

21.3 完整 HA 架構

flowchart TB
    APP[Application] --> HAPROXY[HAProxy / PgBouncer<br/>依角色路由讀寫]
    HAPROXY --> PRIMARY[(Primary)]
    HAPROXY -.唯讀路由.-> REPLICA[(Replica)]
    PATRONI1[Patroni Agent] --- PRIMARY
    PATRONI2[Patroni Agent] --- REPLICA
    PATRONI1 <-->|Leader Election| ETCD[(etcd Cluster<br/>Quorum 仲裁)]
    PATRONI2 <-->|監控/回報| ETCD

21.4 Replication Slot：避免 Replica 落後導致 WAL 被提前清除

-- 建立 Physical Replication Slot，確保 Primary 不會清除 Replica 尚未取走的 WAL
SELECT pg_create_physical_replication_slot('replica_1_slot');

注意事項：Replication Slot 會讓 Primary 保留 WAL 直到對應 Replica 取走，若某個 Slot 對應的 Replica 長期離線，WAL 會持續累積直到磁碟被塞滿——這是實務上常見的 Production 事故，需搭配監控 pg_replication_slots 的 Slot 是否過期未使用。

本章重點

1. HA 不是「裝了 Streaming Replication 就叫高可用」，還需要 Leader Election、Quorum、自動 Failover 三者齊備才是完整方案。
2. Split Brain 是 HA 設計中最危險的失效模式，必須透過 Quorum 機制杜絕。
3. Replication Slot 提供了複寫的可靠性保障，但若疏於監控，反而可能因 WAL 累積拖垮 Primary 磁碟。

↑ 回目錄

第22章 Logical Replication

22.1 Publication / Subscription 模型

flowchart LR
    subgraph A["PostgreSQL A（來源）"]
        PUB[Publication]
    end
    subgraph B["PostgreSQL B（目標）"]
        SUB[Subscription]
    end
    PUB -->|Logical Decoding<br/>解析 WAL 成邏輯變更| SUB

-- 來源端：建立 Publication
CREATE PUBLICATION app_orders_pub FOR TABLE app.orders, app.customer;

-- 目標端：建立 Subscription 開始同步
CREATE SUBSCRIPTION app_orders_sub
  CONNECTION 'host=source-db.internal dbname=appdb user=repl_user password=xxx'
  PUBLICATION app_orders_pub;

22.2 與 Physical Replication 的差異

22.3 PostgreSQL 19（Beta）的複寫強化

• Sequence 複寫：過去邏輯複寫不包含 Sequence 數值，Failover/切換後常需手動校正；19 起 CREATE/ALTER PUBLICATION ... ALL SEQUENCES 與 ALTER SUBSCRIPTION ... REFRESH SEQUENCES 可原生同步。
• WAIT FOR：讓應用程式可以在 Standby／Subscriber 端等待特定 LSN 已被寫入/回放，實作「寫入後立即在複本讀到最新資料」（Read-Your-Writes）的模式，緩解過去邏輯複寫的最終一致性痛點。
• Publication EXCEPT 子句：可用排除法定義 Publication（「除了這幾張表以外全部複寫」），大量資料表的場景不需逐一列舉。
• retain_dead_tuples / max_retention_duration：控制邏輯複寫衝突解決時死元組的保留時間上限，降低訂閱端長時間離線造成來源端 Bloat 的風險。
• 上述功能皆為 Beta 階段，GA 前建議先在測試環境驗證行為（詳見第2章 2.2.3）。

22.4 零／低停機遷移應用

flowchart LR
    OLD[(舊系統 PostgreSQL 14)] -->|Logical Replication| NEW[(新系統 PostgreSQL 18)]
    NEW -->|資料追上後切換流量| APP[Application]

利用 Logical Replication 讓新舊版本並行一段時間、資料持續同步，待確認資料一致後再切換應用程式連線——這是大版本升級（第52、53章）與資料庫遷移最常見的低停機做法，也是 Change Data Capture（CDC）架構的基礎。

本章重點

1. Logical Replication 以資料表為單位、可跨版本，是升級與資料整合的關鍵工具，用途與 Physical Replication（HA 導向）不同。
2. PostgreSQL 19 補齊 Sequence 複寫的長年缺口，對零停機升級場景是實質改善，但仍屬 Beta 功能。
3. Replication Slot（邏輯複寫也使用）同樣有「Subscriber 離線導致來源端 WAL 累積」的風險，維運監控原則與實體複寫一致。

↑ 回目錄

第23章 Monitoring 與 Observability

23.1 核心系統視圖

-- 目前所有連線與執行中查詢
SELECT pid, usename, application_name, state, query, query_start
FROM pg_stat_activity
WHERE state != 'idle';

-- 資料庫層級統計（Cache Hit Ratio 是最重要的健康指標之一）
SELECT datname,
       round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio_pct
FROM pg_stat_database
WHERE datname = 'appdb';

-- 複寫延遲
SELECT application_name, client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- 目前的鎖等待
SELECT * FROM pg_locks WHERE NOT granted;

-- 慢查詢分析（需先啟用 pg_stat_statements 擴充套件）
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

23.2 Prometheus + Grafana 架構

flowchart LR
    PG[(PostgreSQL)] --> EXPORTER[postgres_exporter]
    EXPORTER --> PROM[Prometheus]
    PROM --> GRAF[Grafana Dashboard]
    PG -.日誌.-> OTEL[OpenTelemetry Collector]
    OTEL --> GRAF
    PROM --> ALERT[Alertmanager]
    ALERT --> ONCALL[On-Call 通知]

企業級監控除了指標（Metrics）還需搭配日誌（Logs）與追蹤（Traces），三者合稱 Observability 的三大支柱，OpenTelemetry 是目前將三者標準化整合的主流方案（詳見專案內另一份《OpenTelemetry教學手冊》與《Prometheus與Grafana教學手冊》）。

23.3 關鍵監控指標清單

本章重點

1. pg_stat_* 系統視圖是排查任何 PostgreSQL 問題的第一手資料來源，優先於猜測或翻找應用程式日誌。
2. Cache Hit Ratio、複寫延遲、鎖等待是三個最該放進儀表板首頁的指標。
3. Metrics（Prometheus/Grafana）+ Logs + Traces（OpenTelemetry）三者合一，才是完整的資料庫 Observability。

↑ 回目錄

第24章 Performance Tuning 方法論

24.1 完整方法論

flowchart LR
    A[Measure<br/>量測現況] --> B[Identify<br/>找出瓶頸]
    B --> C[Explain<br/>分析執行計畫]
    C --> D[Optimize<br/>提出修正]
    D --> E[Benchmark<br/>量化驗證]
    E --> F[Verify<br/>確認無副作用]
    F --> G[Monitor<br/>持續觀察]
    G -.回饋.-> A

禁止只說：「增加 shared_buffers 就會變快。」

效能調校必須由實際 Workload、Metrics、Execution Plan、I/O、CPU、Memory、Locking 與 Connection Behavior 驗證——任何脫離實測數據的「建議參數值」都只是猜測。

24.2 排查優先順序（實務經驗）

1. 先看是不是少了索引或統計資訊過期（第15、16章）——多數「突然變慢」的問題根因在此
2. 再看是不是 Lock 等待（第17章）——查詢本身很快，但卡在等鎖
3. 再看是不是 Autovacuum 跟不上（第18章）——Bloat 導致同樣查詢要掃更多實體頁面
4. 最後才調整記憶體/並行相關參數（第9章）——這類調整影響全域，變更前後都需要有基準數據比對

24.3 常見效能問題對照

本章重點

1. 效能調校是一個閉環方法論，不是單次「調參數」的動作。
2. 排查應由「查詢層」到「維運層」再到「全域參數」依序排除，避免一開始就跳去調整高風險的全域參數。
3. 任何效能改動都必須有 Before/After 的量化數據支撐，才能判斷是否真的有效、有無副作用。

↑ 回目錄

第25章 Connection Management／Pooling

25.1 為什麼需要連線池

第4章提到 PostgreSQL 是 Process-per-Connection 架構，每個連線都有記憶體與 Fork 開銷。應用程式（尤其是微服務、Serverless）常見的「每個請求開一條新連線」模式，在高併發下會快速耗盡 max_connections。連線池的角色是在應用程式與資料庫之間，重複利用少量實體連線。

25.2 Session Pooling vs Transaction Pooling（以 PgBouncer 為例）

; pgbouncer.ini 範例
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

25.3 應用程式框架的連線池建議

Best Practice：應用層連線池 + PgBouncer（或雲端代管服務內建的連線池）常見於高併發架構，兩層池化需要協調總連線數，避免「應用層以為池夠用，實際上疊加多個服務實例後仍打爆資料庫」。

本章重點

1. Process-per-Connection 架構讓連線池不是「錦上添花」而是高併發系統的必要元件。
2. Transaction Pooling 效率最高但犧牲 Session 狀態相容性，選型前需確認應用程式是否依賴 Session 層級功能。
3. 多服務實例場景要盤點「總連線數」而非只看單一服務的池大小設定。

↑ 回目錄

第26章 PostgreSQL Extensions

26.1 Extension 機制

-- 查詢可用的 Extension
SELECT name, default_version, comment FROM pg_available_extensions ORDER BY name;

-- 安裝 Extension（需要對應的資料庫具備權限，通常由 DBA 執行）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

26.2 常用企業 Extension

26.3 Extension 管理策略

本章重點

1. Extension 是 PostgreSQL「核心精簡、按需擴充」哲學的具體實現，但每個 Extension 都是額外的維運與升級相容性負擔。
2. pg_stat_statements 幾乎是所有 Production 資料庫都該安裝的基礎設施，而非選配。
3. Extension 安裝應納入版本控制與 Migration 流程，避免變成「口耳相傳」的隱性設定。

↑ 回目錄

第27章 PostgreSQL＋JSON／JSONB

27.1 JSON 運算子與路徑查詢

-- -> 取得 JSON 物件（保留 JSON 型別）；->> 取得文字值
SELECT payload -> 'customer' AS customer_json,
       payload ->> 'order_id' AS order_id_text
FROM app.event_log;

-- @> 包含運算子（可搭配 GIN 索引高效查詢）
SELECT * FROM app.event_log WHERE payload @> '{"status": "failed"}';

-- jsonpath（SQL/JSON Path Language）
SELECT jsonb_path_query(payload, '$.items[*].sku') FROM app.event_log;

27.2 Hybrid Relational + JSON 架構

flowchart LR
    subgraph Table["app.orders"]
        A["order_id (PK)<br/>customer_id (FK)<br/>total_amount<br/>order_date"]
        B["metadata jsonb<br/>（彈性擴充欄位）"]
    end
    A -.穩定核心欄位，正規化.-> Table
    B -.易變/來源多樣的附加資訊.-> Table

實務常見模式：核心業務欄位維持正規化的關聯式結構（保有 FK、CHECK、索引效益），易變或來源多樣的附加資訊（如第三方 API 回傳的原始 Payload、使用者自訂欄位）存放於 JSONB 欄位。

27.3 What 情況適合 JSONB？What 情況該用一般 Relational Table？

常見誤用：把所有資料都塞進單一 JSONB 欄位（「Schema-less 化整張資料庫」），放棄關聯式資料庫的完整性保證與 Join 效能——這是第58章 Anti-Patterns 明確列出的反模式之一。

本章重點

1. JSONB 是「關聯式資料庫的半結構化延伸」，不是要取代正規化設計。
2. GIN 索引讓 JSONB 的包含查詢效能可接受，但高頻精確查詢的欄位仍建議正規化拉出獨立欄位。
3. 混合架構（核心正規化 + 附加 JSONB）是多數企業系統的務實選擇。

↑ 回目錄

第28章 PostgreSQL＋Vector（pgvector）與 AI 應用

28.1 What：PostgreSQL 不只是傳統 RDBMS

透過 pgvector 擴充套件，PostgreSQL 可以額外扮演：

flowchart TB
    APP[AI Application] --> SVC[Application Service]
    SVC --> PG[(PostgreSQL)]
    PG --> ROLE1[System of Record<br/>業務交易資料]
    PG --> ROLE2[Metadata Store<br/>文件/來源/權限中繼資料]
    PG --> ROLE3[Vector Store<br/>Embedding 相似度搜尋]
    PG --> ROLE4[RAG Database<br/>檢索增強生成的知識來源]
    PG --> ROLE5[Agent Memory Store<br/>對話/任務狀態]
    PG --> ROLE6[Audit Store<br/>操作稽核紀錄]

重要客觀性提醒：以上是 pgvector 擴充套件賦予 PostgreSQL 的能力，不是 PostgreSQL 核心原生具備——選型評估時要清楚區分「PostgreSQL 本身」與「安裝 Extension 之後」的能力邊界。

28.2 Why：為什麼用 PostgreSQL 做向量搜尋

• 向量資料與業務資料同一個交易、同一套備份/複寫/權限機制，不需要額外維護一套獨立系統的一致性
• Metadata Filtering 是天生強項：WHERE tenant_id = ... AND created_at > ... 直接用 SQL 表達，比多數專用 Vector DB 的 Filter 語法更直覺
• 團隊已有的 PostgreSQL 維運能力（備份、監控、HA）可直接沿用

28.3 Architecture：安裝與資料表設計

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE app.document_chunk (
  chunk_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id  bigint NOT NULL REFERENCES app.document(document_id),
  tenant_id    uuid NOT NULL,
  content      text NOT NULL,
  embedding    vector(1536),   -- 維度需與 Embedding 模型輸出一致，例如常見模型為 1536 維
  metadata     jsonb NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now()
);

28.4 How：相似度搜尋與索引

-- 相似度搜尋：找出與查詢向量最相近的前 5 筆，並套用租戶隔離（Metadata Filtering）
SELECT chunk_id, content, embedding <=> :query_embedding AS distance
FROM app.document_chunk
WHERE tenant_id = :current_tenant_id
ORDER BY embedding <=> :query_embedding
LIMIT 5;

-- HNSW 索引：查詢速度快、召回率高，是目前建議的預設索引類型
CREATE INDEX idx_chunk_embedding_hnsw ON app.document_chunk
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- IVFFlat 索引：建索引速度較快、記憶體開銷較低，但查詢前需要合理的 lists 參數與資料分布
CREATE INDEX idx_chunk_embedding_ivfflat ON app.document_chunk
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

28.5 Hybrid Search：向量 + 全文檢索 + Metadata

-- 混合搜尋：向量相似度 + 全文檢索關鍵字比對 + 結構化條件過濾
WITH vector_candidates AS (
  SELECT chunk_id, content, embedding <=> :query_embedding AS vscore
  FROM app.document_chunk
  WHERE tenant_id = :current_tenant_id
  ORDER BY embedding <=> :query_embedding
  LIMIT 50
),
keyword_candidates AS (
  SELECT chunk_id, content, ts_rank(to_tsvector('english', content), plainto_tsquery('english', :keyword)) AS kscore
  FROM app.document_chunk
  WHERE tenant_id = :current_tenant_id
    AND to_tsvector('english', content) @@ plainto_tsquery('english', :keyword)
)
SELECT coalesce(v.chunk_id, k.chunk_id) AS chunk_id,
       coalesce(v.content, k.content) AS content,
       coalesce(1 - v.vscore, 0) * 0.7 + coalesce(k.kscore, 0) * 0.3 AS combined_score
FROM vector_candidates v
FULL OUTER JOIN keyword_candidates k USING (chunk_id)
ORDER BY combined_score DESC
LIMIT 10;

28.6 Production 注意事項

• 維度必須與 Embedding 模型輸出一致，模型更換（例如從 1536 維換成 3072 維）需要重建整張表或並存多個欄位，不能就地變更維度。
• HNSW／IVFFlat 都是**近似最近鄰（Approximate Nearest Neighbor）**演算法，召回率與查詢速度是取捨關係，需依業務對準確度的容忍度調整參數。
• 向量欄位體積大，VACUUM／備份時間會隨向量資料量顯著增加，容量規劃需納入考量（第54章）。

28.7 Security：Metadata Filtering 是租戶隔離的第一道防線

任何多租戶 RAG 系統，向量查詢必須搭配 WHERE tenant_id = ... 等過濾條件，否則會發生「跨租戶檢索到不該看到的內容」——這是 AI 應用中容易被忽略但後果嚴重的資安問題，建議透過 Row Level Security（第10章）在資料庫層強制，而非僅依賴應用層邏輯。

28.8 PostgreSQL + pgvector vs 專用 Vector Database 客觀比較

本章重點

1. pgvector 讓 PostgreSQL 成為 AI 應用可行的 Vector Store 選項，優勢在於與業務資料的一致性與 Metadata Filtering，而非取代所有專用向量資料庫的場景。
2. HNSW 是目前查詢效能優先場景的建議索引，IVFFlat 適合建索引成本敏感的情境，兩者都是近似演算法。
3. 多租戶場景的 Metadata Filtering／Row Level Security 是資安必要條件，不是效能優化選項。

↑ 回目錄

第29章 PostgreSQL＋RAG 架構

29.1 完整 RAG（Retrieval-Augmented Generation）Pipeline

flowchart TB
    DOC[Documents] --> PARSER[Document Parser]
    PARSER --> CHUNK[Chunking<br/>切分為適當長度片段]
    CHUNK --> EMBED[Embedding Model]
    EMBED --> PG[(PostgreSQL + pgvector)]
    QUERY[使用者提問] --> QEMBED[Query Embedding]
    QEMBED --> SEARCH[Similarity Search]
    PG --> SEARCH
    SEARCH --> RERANK[Reranking<br/>可選，提升精確度]
    RERANK --> LLM[LLM 生成回答]
    LLM --> ANSWER[回答 + 引用來源]

29.2 Chunk 與 Metadata 資料表設計

CREATE TABLE app.rag_document (
  document_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     uuid NOT NULL,
  source        text NOT NULL,          -- 文件來源，如檔名/URL/系統名稱
  version       int NOT NULL DEFAULT 1,
  permission_tag text NOT NULL DEFAULT 'internal',  -- 供權限過濾使用
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE app.rag_chunk (
  chunk_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id  bigint NOT NULL REFERENCES app.rag_document(document_id),
  tenant_id    uuid NOT NULL,
  chunk_index  int NOT NULL,
  content      text NOT NULL,
  embedding    vector(1536),
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_rag_chunk_embedding ON app.rag_chunk USING hnsw (embedding vector_cosine_ops);

29.3 企業 RAG Security 原則

1. 檢索前過濾，而非生成後過濾：權限判斷必須在 SQL 的 WHERE 條件中完成，不能只靠「請 LLM 不要回答敏感內容」的 Prompt 約束——LLM 的指令遵循不是安全邊界。
2. 回答附上來源引用：source + document_id + chunk_id 一併回傳，讓使用者可驗證答案依據，也利於事後稽核。
3. 版本一致性：文件更新後若未重新 Embedding，檢索到的內容可能與最新文件不一致，需要有 Embedding 更新流程與版本追蹤。

本章重點

1. RAG 的資料庫設計必須把「租戶隔離」「權限標籤」「來源可追溯」當作一開始就要考慮的欄位，而非事後補強。
2. 權限控制必須在檢索的 SQL 層完成，Prompt 層的「請不要洩漏」不能作為安全邊界。
3. Chunk 與原始文件的版本關聯，是企業 RAG 系統長期可維護、可稽核的關鍵。

↑ 回目錄

第30章 PostgreSQL＋AI Agent

30.1 AI Agent 需要的資料模型

flowchart TB
    AGENT[AI Agent] --> CONV[Conversation<br/>對話紀錄]
    AGENT --> TASK[Task<br/>任務狀態機]
    AGENT --> TOOL[Tool Call<br/>工具呼叫紀錄]
    AGENT --> MEM[Memory<br/>長短期記憶]
    AGENT --> KNOW[Knowledge<br/>知識庫/RAG]
    AGENT --> AUDIT[Audit<br/>操作稽核]
    CONV & TASK & TOOL & MEM & KNOW & AUDIT --> PG[(PostgreSQL)]

30.2 Schema 範例

CREATE TABLE agent.session (
  session_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      bigint NOT NULL,
  started_at   timestamptz NOT NULL DEFAULT now(),
  status       varchar(20) NOT NULL DEFAULT 'active'
);

CREATE TABLE agent.message (
  message_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  session_id   uuid NOT NULL REFERENCES agent.session(session_id),
  role         varchar(20) NOT NULL CHECK (role IN ('user', 'assistant', 'tool', 'system')),
  content      text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE agent.task (
  task_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  session_id   uuid NOT NULL REFERENCES agent.session(session_id),
  goal         text NOT NULL,
  status       varchar(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','in_progress','completed','failed','cancelled')),
  created_at   timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);

CREATE TABLE agent.tool_call (
  tool_call_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id       bigint NOT NULL REFERENCES agent.task(task_id),
  tool_name     varchar(100) NOT NULL,
  request_args  jsonb NOT NULL,
  response_body jsonb,
  risk_level    varchar(20) NOT NULL DEFAULT 'safe' CHECK (risk_level IN ('safe','requires_approval','denied')),
  approved_by   bigint,                -- 需人工核准時記錄核准者
  executed_at   timestamptz,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE agent.long_term_memory (
  memory_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id      bigint NOT NULL,
  memory_text  text NOT NULL,
  embedding    vector(1536),
  importance   smallint NOT NULL DEFAULT 5,
  created_at   timestamptz NOT NULL DEFAULT now(),
  last_used_at timestamptz
);

CREATE TABLE agent.audit_log (
  audit_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  session_id   uuid NOT NULL,
  actor        varchar(50) NOT NULL,   -- 'agent' 或實際操作的人員帳號
  action       text NOT NULL,
  target       text,
  result       varchar(20) NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

30.3 AI Agent 資料庫存取分層（安全核心觀念）

AI Agent 不應直接擁有 PostgreSQL Superuser 權限。

flowchart TB
    AGENT[AI Agent] --> TOOLLAYER[Tool Layer<br/>定義好的、有限的操作介面]
    TOOLLAYER --> POLICY[Policy Layer<br/>風險分級與規則檢查]
    POLICY --> ROROLE["Read-only DB Role<br/>（預設）"]
    ROROLE --> PG[(PostgreSQL)]

    AGENT2[AI Agent 需要寫入時] --> VALIDATED[Validated Tool<br/>參數化、範圍受限的寫入工具]
    VALIDATED --> AUTH[Authorization<br/>權限與風險檢查]
    AUTH --> TX[Transaction<br/>可控交易範圍]
    TX --> PG

本章重點

1. Agent 的 Conversation、Task、Tool Call、Memory、Audit 都應該有明確的資料表與生命週期狀態，而不是全部塞進一個 jsonb 欄位。
2. Tool Call 紀錄必須包含 risk_level 與 approved_by，讓「哪些操作需要人工核准」在資料層就有稽核軌跡。
3. 不同用途的 Agent（Read-only／Read-write／DBA／Migration／Monitoring）應對應不同的資料庫 Role，權限邊界在資料庫層強制，而非僅依賴 Agent 程式邏輯自律。

↑ 回目錄

第31章 PostgreSQL＋AI Coding Agent

31.1 定位：程式碼知識的資料底層

Claude Code、GitHub Copilot、Codex 等 AI Coding Agent 在處理大型既有專案時，最大的挑戰是「上下文視窗裝不下整個 Codebase」。PostgreSQL 可作為這些 Agent 的外部長期記憶與結構化知識庫，讓 Agent 用 SQL／向量查詢取得所需片段，而不是每次都重新掃描整個檔案系統。

flowchart TB
    REPO[Git Repository] --> PARSER[Code Parser<br/>AST/靜態分析]
    PARSER --> SYMBOL[Symbol 資料]
    PARSER --> DEP[Dependency 資料]
    PARSER --> AST[AST Metadata]
    PARSER --> EMBED[Code Embedding]
    SYMBOL & DEP & AST & EMBED --> PG[(PostgreSQL)]
    PG --> SQLSEARCH[SQL 結構化查詢]
    PG --> VECSEARCH[向量語意查詢]
    PG --> GRAPHREL[Graph-like 關聯查詢]
    SQLSEARCH & VECSEARCH & GRAPHREL --> AGENT[AI Coding Agent]

31.2 Schema 範例：可儲存的 12 類知識

CREATE TABLE code.repository (
  repository_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repo_url      text NOT NULL,
  default_branch varchar(100) NOT NULL DEFAULT 'main'
);

CREATE TABLE code.symbol (
  symbol_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repository_id bigint NOT NULL REFERENCES code.repository(repository_id),
  file_path     text NOT NULL,
  symbol_type   varchar(30) NOT NULL,  -- class / method / function / field
  symbol_name   text NOT NULL,
  signature     text,
  embedding     vector(1536),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE code.dependency (
  dependency_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_symbol_id bigint NOT NULL REFERENCES code.symbol(symbol_id),
  to_symbol_id   bigint NOT NULL REFERENCES code.symbol(symbol_id),
  dependency_type varchar(30) NOT NULL  -- calls / extends / implements / imports / reads_table / writes_table
);

CREATE TABLE code.requirement (
  requirement_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repository_id  bigint NOT NULL REFERENCES code.repository(repository_id),
  title          text NOT NULL,
  description    text NOT NULL,
  status         varchar(20) NOT NULL DEFAULT 'open'
);

CREATE TABLE code.architecture_decision (
  adr_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repository_id bigint NOT NULL REFERENCES code.repository(repository_id),
  title         text NOT NULL,
  context       text NOT NULL,
  decision      text NOT NULL,
  consequences  text,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE code.agent_task (
  task_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repository_id bigint NOT NULL REFERENCES code.repository(repository_id),
  description   text NOT NULL,
  status        varchar(20) NOT NULL DEFAULT 'pending',
  result_summary text
);

CREATE TABLE code.test_result (
  test_result_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id        bigint REFERENCES code.agent_task(task_id),
  test_name      text NOT NULL,
  passed         boolean NOT NULL,
  executed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE code.review_result (
  review_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id        bigint REFERENCES code.agent_task(task_id),
  reviewer       varchar(50) NOT NULL,  -- 'ai_agent' 或人員帳號
  finding        text NOT NULL,
  severity       varchar(20) NOT NULL
);

上表涵蓋 Codebase／Repository Metadata、Symbol、Dependency、AST Metadata（可存於 symbol／獨立表的 jsonb 欄位）、Code Embedding、Issue（可對應 requirement 或獨立 issue 表）、Requirement、Architecture Decision、Agent Task、Test Result、Code Review Result——即 Master Prompt 要求的核心知識類別，實際專案可依需求增減。

31.3 混合查詢範例

-- SQL 結構化查詢：找出所有依賴某個即將棄用的類別的程式碼
SELECT s2.file_path, s2.symbol_name
FROM code.symbol s1
JOIN code.dependency d ON d.to_symbol_id = s1.symbol_id
JOIN code.symbol s2 ON s2.symbol_id = d.from_symbol_id
WHERE s1.symbol_name = 'LegacyPaymentService';

-- 向量語意查詢：找出與「重試機制」語意相近的既有實作，避免重複造輪子
SELECT file_path, symbol_name, embedding <=> :query_embedding AS distance
FROM code.symbol
WHERE repository_id = :repo_id
ORDER BY embedding <=> :query_embedding
LIMIT 10;

本章重點

1. PostgreSQL 讓 AI Coding Agent 得以用「查詢」取代「每次重新掃描整個 Repository」，是突破上下文視窗限制的務實做法。
2. Symbol、Dependency 資料表本質是一個可用 SQL 查詢的程式碼關聯圖，能回答「誰依賴誰」這類結構化問題。
3. SQL 結構化查詢與向量語意查詢互補：前者回答「精確關聯」，後者回答「概念相似」。

↑ 回目錄

第32章 PostgreSQL＋Reverse Engineering

32.1 既有系統逆向工程方法論

flowchart TB
    IN["輸入：既有 Database / 既有 Application /<br/>既有 SQL / 既有 Java / 既有 API / 既有 Batch / 既有 Logs"] --> D[Discovery]
    D --> SA[Schema Analysis]
    SA --> RA[Relationship Analysis]
    RA --> SQLA[SQL Analysis]
    SQLA --> CA[Code Analysis]
    CA --> DA[Dependency Analysis]
    DA --> AIR[AI Reasoning]
    AIR --> ARR[Architecture Reconstruction]

32.2 用 System Catalog 做 Schema Discovery

第5章提到 PostgreSQL 的 Schema 本身也是資料表，這讓自動化逆向工程成為可能：

-- 列出所有資料表、欄位、型別、是否可為空
SELECT c.relname AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       a.attnotnull AS not_null
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind = 'r' AND a.attnum > 0 AND NOT a.attisdropped
  AND c.relnamespace = 'app'::regnamespace
ORDER BY c.relname, a.attnum;

-- 列出所有外鍵關係（重建 ER 圖的基礎資料）
SELECT
  tc.table_name AS from_table, kcu.column_name AS from_column,
  ccu.table_name AS to_table, ccu.column_name AS to_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

-- 列出所有函式/預存程序定義（理解既有商業邏輯散落在資料庫層的部分）
SELECT proname, prosrc FROM pg_proc WHERE pronamespace = 'app'::regnamespace;

32.3 儲存逆向工程結果供 AI Agent 查詢

延續第32章前段與第31章的 Schema 設計，逆向工程結果應存放於：

本章重點

1. PostgreSQL 的 System Catalog（pg_catalog / information_schema）本身就是最可靠的 Schema Discovery 資料來源，優先於人工閱讀文件或猜測。
2. 逆向工程的價值在於把「隱性知識」（散落在 SQL、程式碼、資料庫函式中的商業邏輯）轉為「可查詢的顯性資料」。
3. 逆向工程結果應該存回資料庫，讓後續的 AI Agent 或人類工程師可以持續查詢與累積，而不是每次重新分析。

↑ 回目錄

第33章 PostgreSQL＋Software Framework Upgrade 知識庫

33.1 升級知識庫架構

flowchart TB
    LEGACY[Legacy System] --> ANALYSIS[Analysis<br/>現況分析]
    ANALYSIS --> KB[(PostgreSQL Knowledge Base)]
    KB --> AGENT[AI Agent]
    AGENT --> PLAN[Migration Plan]
    PLAN --> CHANGE[Code Change]
    CHANGE --> TEST[Test]
    TEST --> RESULT[Result]
    RESULT -.回饋更新.-> KB

33.2 Schema 範例

CREATE TABLE upgrade.dependency_version (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repository_id  bigint NOT NULL,
  dependency_name text NOT NULL,   -- 如 'spring-boot', 'jakarta.servlet-api'
  current_version text NOT NULL,
  target_version  text NOT NULL,
  UNIQUE (repository_id, dependency_name)
);

CREATE TABLE upgrade.breaking_change (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  dependency_name text NOT NULL,
  from_version    text NOT NULL,
  to_version      text NOT NULL,
  description     text NOT NULL,
  affected_symbol_pattern text,   -- 如套件/類別名稱樣式，供比對受影響程式碼
  risk_level      varchar(20) NOT NULL DEFAULT 'medium'
);

CREATE TABLE upgrade.migration_task (
  task_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  repository_id  bigint NOT NULL,
  breaking_change_id bigint REFERENCES upgrade.breaking_change(id),
  file_path      text NOT NULL,
  status         varchar(20) NOT NULL DEFAULT 'pending',
  compatibility_result varchar(20),
  test_result    varchar(20)
);

33.3 應用情境

適用於 Java / Spring Boot / Spring Framework / Jakarta EE / Maven / Node.js / Vue / Angular 等框架升級評估：

1. 掃描專案依賴，寫入 upgrade.dependency_version
2. 比對官方 Migration Guide 中的已知 Breaking Change，寫入 upgrade.breaking_change（此步驟建議人工審核來源，避免 AI 幻覺產生不存在的變更項目）
3. 依 affected_symbol_pattern 掃描程式碼庫，找出受影響檔案，寫入 upgrade.migration_task
4. AI Agent 依任務逐一提出程式碼變更建議，經人工 Review 後套用
5. 測試結果回寫 test_result，作為升級風險評估的依據

本章重點

1. 框架升級的核心挑戰是「Breaking Change 散落在多份 Release Notes / Migration Guide 中」，用資料庫集中管理可讓查詢與比對系統化。
2. AI Agent 產出的 Breaking Change 清單必須有人工審核來源的機制，避免將幻覺內容當作既定事實寫入知識庫。
3. 升級知識庫的價值會隨專案持續累積——每次升級的實際踩坑經驗，都應該回寫成下一次升級的參考資料。

↑ 回目錄

第34章 PostgreSQL 與 Spring Boot／Java 25

34.1 標準連線堆疊

flowchart TB
    JAVA[Java 25] --> SB[Spring Boot 4.x]
    SB --> SD[Spring Data JPA]
    SD --> HIB[Hibernate]
    HIB --> JDBC[JDBC Driver]
    JDBC --> HIKARI[HikariCP 連線池]
    HIKARI --> PG[(PostgreSQL)]
    SB --> FLYWAY[Flyway Migration]
    FLYWAY --> PG

34.2 設定範例

spring.datasource.url=jdbc:postgresql://pg-host:5432/appdb
spring.datasource.username=svc_backend
# Production 密碼不應直接寫在 application.properties，見下方說明
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=3000

spring.jpa.hibernate.ddl-auto=validate
spring.flyway.locations=classpath:db/migration

Production Secret 不應直接寫在 application.properties。 建議透過環境變數注入（SPRING_DATASOURCE_PASSWORD）、Kubernetes Secret、或 Vault 等密鑰管理服務動態取得，並確保 application.properties 本身不含明文密碼被提交進 Git。

34.3 ddl-auto 的企業級選擇

34.4 Java 25／Spring Boot 4.x 整合注意事項

• Driver 版本：確認 PostgreSQL JDBC Driver 版本支援目標 Java 版本與 PostgreSQL Server 版本（含第2章列出的 19 Beta 相容性仍需自行驗證）。
• Connection Pool：HikariCP 的 maximum-pool-size 需與 PostgreSQL 端 max_connections 及其他服務實例數量整體規劃（第25章）。
• Transaction 邊界：@Transactional 的傳播行為（Propagation）需與第17章的隔離等級知識搭配理解，避免長交易占用連線與鎖。
• Observability：建議搭配 Micrometer + OpenTelemetry 匯出資料庫呼叫的追蹤資訊，與第23、51章的監控體系串接。

本章重點

1. Production 環境的 ddl-auto 應設為 validate，Schema 變更一律透過 Flyway/Liquibase 走版本控制（第36章）。
2. 資料庫密碼絕不寫死在設定檔提交進版本控制，改用環境變數或密鑰管理服務注入。
3. HikariCP 連線池大小需要與資料庫端 max_connections 及服務實例數整體規劃，不能只看單一服務的預設值。

↑ 回目錄

第35章 PostgreSQL 與前端框架的安全邊界

35.1 正確的架構邊界

flowchart LR
    FE[Vue / React / Angular] --> API[REST / GraphQL API]
    API --> BE[Backend<br/>驗證、授權、業務邏輯]
    BE --> PG[(PostgreSQL)]

前端不應直接連 PostgreSQL。 這不只是「工程慣例」，而是安全邊界的必要條件：

所有資料存取都必須經過後端 API：由後端負責身份驗證（Authentication）、授權（Authorization）、輸入驗證、Parameterized Query（第10章），前端只透過定義好的 API 合約互動。

本章重點

1. 前端與資料庫之間必須有後端服務作為安全邊界，這是不可妥協的架構原則。
2. 資料庫連線憑證絕不可能出現在任何會被瀏覽器載入的程式碼或設定中。
3. 業務規則與授權邏輯集中在後端，前端僅負責呈現與呼叫已授權的 API。

↑ 回目錄

第36章 Database Migration（Flyway／Liquibase）

36.1 版本化 Migration 的必要性

flowchart LR
    V1[V1__init_schema.sql] --> V2[V2__add_orders_table.sql]
    V2 --> V3[V3__add_index_orders_customer.sql]
    V3 --> V4[V4__add_tenant_id_column.sql]

每個 Migration 檔案代表 Schema 的一次確定性變更，工具（Flyway／Liquibase）會在資料庫中記錄已套用的版本，確保任何環境套用同一組 Migration，最終 Schema 狀態完全一致。

36.2 Flyway 範例

-- V4__add_tenant_id_column.sql
BEGIN;

ALTER TABLE app.orders ADD COLUMN tenant_id uuid;

-- 分批回填既有資料（大表建議分批執行避免長時間鎖表，見下方 Expand/Contract 說明）
UPDATE app.orders SET tenant_id = '00000000-0000-0000-0000-000000000001'
WHERE tenant_id IS NULL;

ALTER TABLE app.orders ALTER COLUMN tenant_id SET NOT NULL;

COMMIT;

36.3 Expand / Contract 模式（Zero-Downtime Migration）

這個模式讓「資料庫變更」與「應用程式部署」可以解耦，避免「Migration 一執行，舊版本程式碼立刻壞掉」的停機風險。

36.4 Rollback 策略

PostgreSQL 的 DDL 大多可在交易內執行（BEGIN...COMMIT），但 Migration 工具的「Rollback」通常代表執行另一個反向 Migration，而非資料庫原生撤銷——尤其牽涉資料搬移或欄位刪除的 Migration，必須額外撰寫並測試對應的回滾腳本，不能假設「反向操作」是顯而易見的。

本章重點

1. Migration 檔案必須版本化、確定性、可在任何環境重複套用出相同結果。
2. Expand/Contract 模式是避免 Schema 變更造成停機的標準做法，尤其適用於高可用系統。
3. Rollback 不是「自動反向」，牽涉資料的 Migration 必須額外設計並測試回滾腳本。

↑ 回目錄

第37章 PostgreSQL DevOps

37.1 CI/CD Pipeline 中的資料庫關卡

flowchart LR
    DEV[Developer] --> GIT[Git]
    GIT --> CI[CI]
    CI --> MIGTEST[Migration Test<br/>套用所有 Migration 至乾淨資料庫]
    MIGTEST --> INTTEST[Integration Test<br/>含資料庫的整合測試]
    INTTEST --> SECTEST[Security Test]
    SECTEST --> PERFTEST[Performance Test]
    PERFTEST --> DEPLOY[Deploy]

37.2 Testcontainers：CI 中的一次性 PostgreSQL

@Testcontainers
class OrderRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:18")
            .withDatabaseName("appdb_test")
            .withUsername("test_user")
            .withPassword("test_password");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Test
    void shouldPersistOrder() {
        // 測試針對真實 PostgreSQL 執行，而非 Mock，避免第10章提到的
        // 「Mock 測試通過但正式環境行為不同」的落差
    }
}

37.3 Infrastructure as Code

本章重點

1. Migration Test（在乾淨資料庫套用所有 Migration）應該是 CI 的必要關卡，避免「Migration 在 Production 才發現套用失敗」。
2. Testcontainers 讓整合測試針對真實 PostgreSQL 執行，比純 Mock 更能反映實際行為。
3. 資料庫基礎設施（不只是 Schema）也應納入 Infrastructure as Code 管理，而非人工手動設定。

↑ 回目錄

第38章 PostgreSQL DevSecOps

38.1 安全管線

flowchart LR
    CODE[Code] --> SQLSCAN[SQL Scan<br/>靜態分析潛在注入風險]
    SQLSCAN --> DEPSCAN[Dependency Scan]
    DEPSCAN --> SECRETSCAN[Secret Scan]
    SECRETSCAN --> MIGTEST[Migration Test]
    MIGTEST --> SECTEST[Security Test]
    SECTEST --> INTTEST[Integration Test]
    INTTEST --> DEPLOY[Deploy]

38.2 風險清單與對應措施

本章重點

1. DevSecOps 管線應把 SQL 掃描、依賴掃描、Secret 掃描都視為與功能測試同等重要的關卡，而非事後補救。
2. 備份加密與 Audit 紀錄常是企業安全稽核中最容易被忽略、卻最常被稽核單位要求提供證據的兩項。
3. 權限最小化不是一次性設定，而是需要定期稽核與清理的持續性工作。

↑ 回目錄

第39章 Multi-Tenant Architecture

39.1 三種主要架構比較

39.2 Row Level Security 實作 Shared Table 模式

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON app.orders
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_modify ON app.orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- 應用程式於連線建立後、處理該租戶請求前設定
SET app.current_tenant_id = '11111111-1111-1111-1111-111111111111';

常見錯誤：只在應用層 WHERE tenant_id = ? 做隔離，卻沒有搭配 RLS——只要有一處查詢忘記加條件（例如新加的報表功能），就會發生跨租戶資料外洩。RLS 讓隔離規則在資料庫層強制執行，是應用層邏輯的最後一道防線，而非取代應用層檢查。

本章重點

1. 多租戶架構選型的核心變數是「租戶數量」與「隔離性要求」，沒有放諸四海皆準的答案。
2. Shared Table 模式若無 Row Level Security 把關，任何一處遺漏 WHERE tenant_id 的查詢都是資安事故。
3. 大型 SaaS 常見 Hybrid 模式：核心大客戶獨立 Schema／Database，長尾客戶共用 Table + RLS，兼顧成本與關鍵客戶的隔離需求。

↑ 回目錄

第40章 Partitioning

40.1 三種分區策略

-- Range Partitioning：依時間範圍分區，金融交易/日誌類資料的常見選擇
CREATE TABLE app.transaction (
  transaction_id bigint GENERATED ALWAYS AS IDENTITY,
  transaction_date date NOT NULL,
  amount numeric(18,2) NOT NULL,
  PRIMARY KEY (transaction_id, transaction_date)
) PARTITION BY RANGE (transaction_date);

CREATE TABLE app.transaction_2024 PARTITION OF app.transaction
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE app.transaction_2025 PARTITION OF app.transaction
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE app.transaction_2026 PARTITION OF app.transaction
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

flowchart TB
    T["app.transaction<br/>Partition by transaction_date"]
    T --> P2024[transaction_2024]
    T --> P2025[transaction_2025]
    T --> P2026[transaction_2026]

40.2 為什麼要分區：金融/交易/Log/Audit 大型資料案例

• 查詢效能：查詢通常帶有時間範圍條件時，Planner 可透過 Partition Pruning 只掃描相關分區，避免掃描全表
• 維護效率：VACUUM／REINDEX 可針對單一分區進行，不需鎖定整張邏輯表
• 生命週期管理：過期資料（如 7 年前的交易紀錄，依法規僅需保留特定年限）可用 DETACH PARTITION 快速移除，遠比 DELETE 大量資料列有效率且不會產生大量 Dead Tuple

-- 移除過期分區（先 DETACH 保留資料以防萬一，確認無誤後再 DROP）
ALTER TABLE app.transaction DETACH PARTITION app.transaction_2019;
-- 確認保留策略符合法規要求後才執行：
-- DROP TABLE app.transaction_2019;

本章重點

1. Range Partitioning 是金融交易、日誌、Audit 等隨時間成長資料的標準做法，核心價值是 Partition Pruning 與生命週期管理。
2. 分區鍵必須反映實際查詢模式（多數查詢是否帶有該欄位的範圍條件），否則分區反而增加複雜度卻無效能收益。
3. 過期資料用 DETACH PARTITION 而非大量 DELETE，避免產生大量 Dead Tuple 拖累 Autovacuum。

↑ 回目錄

第41章 Enterprise Reference Architecture

41.1 完整企業架構圖

flowchart TB
    LB[Load Balancer] --> APP[Application / Spring Boot Services]
    APP --> POOL[Connection Pool<br/>PgBouncer]
    POOL --> PRIMARY[(PostgreSQL Primary)]
    PRIMARY --> REPLICA[(Read Replica)]
    PRIMARY --> BACKUP[Backup<br/>pgBackRest/WAL-G]
    REPLICA --> REPORT[Reporting / Analytics]
    PRIMARY --> WAL[WAL]
    WAL --> ARCHIVE[(Object Storage Archive)]
    ARCHIVE --> DR[(DR Site)]
    PATRONI[Patroni + etcd] -.管理.-> PRIMARY
    PATRONI -.管理.-> REPLICA
    PROM[Prometheus] --> PRIMARY
    PROM --> REPLICA
    PROM --> GRAF[Grafana]
    AGENT[AI Agent] -.唯讀監控/受控維運.-> PROM
    AGENT -.RAG/向量查詢.-> PRIMARY

41.2 各層責任

本章重點

1. 企業級 PostgreSQL 架構是多個子系統（HA、備份、監控、安全、AI）的組合，任一環節缺失都會成為整體可靠性的短板。
2. AI Agent 在此架構中的定位是「唯讀監控與受控輔助」，不是取代 DBA 的自動化維運黑盒。
3. 這張參考架構是設計起點，實際導入仍需依第54章 Capacity Planning 與第55章 Disaster Recovery 的方法調整規模與拓撲。

↑ 回目錄

第42章 Production Checklist

42.1 上線前完整檢查清單

• OS：作業系統版本受支援、已套用安全更新、時間同步（NTP）已設定
• Storage：使用 SSD/NVMe（生產環境不建議 HDD）、磁碟有足夠餘裕（含 WAL 成長空間）
• CPU／Memory：依 Workload 完成第54章 Capacity Planning 估算，非拍腦袋決定
• Network：資料庫僅對必要來源開放連線，跨可用區流量已評估延遲影響
• Security：pg_hba.conf 遵循最小必要原則，無 trust 規則暴露於非本機
• Authentication：採用 scram-sha-256 或更高強度的認證方式
• TLS：ssl = on，跨網段連線強制 hostssl
• Roles：應用程式帳號非 Superuser，遵循最小權限（第11章）
• Database／Schema：命名規範一致（第59章 Coding Standards）
• Index：關鍵查詢已透過 EXPLAIN ANALYZE 驗證使用索引
• Query：已知的高頻查詢完成效能驗證
• Connection Pool：已部署並設定合理的 Pool Size（第25章）
• Vacuum：Autovacuum 已針對大表個別調校（第18章）
• Backup：備份排程已設定，涵蓋邏輯與/或實體備份
• Restore Test：至少完成一次完整還原演練並記錄所需時間
• WAL：wal_level 符合複寫需求，WAL 保留策略已確認不會塞爆磁碟
• Replication：Streaming／Logical Replication（如適用）已驗證延遲在可接受範圍
• Monitoring：Prometheus/Grafana 或等效方案已上線，關鍵指標已設定告警
• Alerting：告警有明確 On-Call 對應窗口，而非發出後無人處理
• Logging：log_min_duration_statement 等關鍵日誌參數已設定
• Disaster Recovery：RPO/RTO 已定義並驗證可達成（第55章）
• Upgrade Plan：已知未來的升級路徑與時程（第52、53章）

↑ 回目錄

第43章 DBA 日常／週期性維運

43.1 Daily（每日）

• 確認 Primary／Replica 皆健康、複寫延遲在正常範圍
• 檢查是否有異常長時間執行的查詢（pg_stat_activity）
• 檢查昨夜是否有未預期的鎖等待或 Deadlock 記錄
• 檢查錯誤日誌是否有新的異常模式
• 確認磁碟使用率（資料目錄、WAL 目錄）在安全範圍

43.2 Weekly（每週）

• 檢視 Autovacuum 執行紀錄，確認大表未持續累積 Dead Tuple
• 檢視 Table／Index Bloat 排行，評估是否需要 REPACK
• 抽樣驗證備份可正確還原（非每週完整演練，但定期抽樣）
• 檢視 pg_stat_statements 慢查詢排行，是否有新出現的效能異常

43.3 Monthly（每月）

• 容量趨勢檢視，評估是否需提前擴充（第54章）
• 權限稽核：檢視 Role／GRANT 是否有不再需要的授權
• 檢查是否有可套用的安全性 Patch（Minor Version Upgrade，第52章）
• 檢視升級路徑規劃：現行版本距 EOL 尚有多久
• 完整 DR 演練（依企業政策頻率，至少每季一次，部分企業要求每月）

本章重點

1. 日常維運分層檢查（每日健康、每週趨勢、每月策略），避免所有事情都堆到「出事才處理」。
2. 備份 Restore Test 與 DR 演練必須排入固定週期，而非依賴「有空再做」。
3. 容量與升級規劃是「月」等級的節奏，避免臨到 EOL 或容量瓶頸才倉促應對。

↑ 回目錄

第44章 Troubleshooting

44.1 Connection 相關

44.2 Performance 相關

44.3 Storage 相關

44.4 Replication 相關

44.5 Application 相關

本章重點

1. Troubleshooting 應遵循 Symptom → Possible Cause → Diagnosis → Solution → Prevention 的固定流程，避免頭痛醫頭。
2. 多數 Production 事故的根因可回溯到本手冊前面章節（索引、Vacuum、連線池、鎖）——Troubleshooting 能力建立在對這些機制的理解上，而非死記案例。
3. Prevention 欄位往往比 Solution 更重要：解決一次事故不如建立監控告警，讓下次同類問題提早被發現。

↑ 回目錄

第45章 常用指令 Cheat Sheet

45.1 Shell 指令

psql -U postgres -h localhost -d appdb   # 連線
pg_dump -U postgres -d appdb -F c -f appdb.dump   # 邏輯備份
pg_restore -U postgres -d appdb_new -j 4 appdb.dump  # 平行還原
pg_dumpall -U postgres -f cluster.sql    # 整個叢集備份（含 Role）
createdb -U postgres appdb               # 建立資料庫
dropdb -U postgres appdb                 # 刪除資料庫（高風險，需再次確認）
createuser -U postgres --interactive     # 互動式建立角色
dropuser -U postgres app_user            # 刪除角色
pg_isready -h localhost -p 5432          # 健康檢查
pg_ctl status -D /path/to/data           # 查詢伺服器狀態

45.2 psql Meta 指令

\l          列出所有資料庫
\c dbname   切換資料庫
\dt         列出目前 Schema 的資料表
\d table    顯示資料表結構
\du         列出所有角色
\dn         列出所有 Schema
\dx         列出已安裝的 Extension
\df         列出函式
\di         列出索引
\x          切換擴展顯示模式（欄位多時較易讀）
\timing     顯示每次查詢耗時
\q          離開 psql

45.3 常用診斷 SQL

-- 目前連線與查詢
SELECT pid, usename, state, query FROM pg_stat_activity WHERE state != 'idle';

-- 資料庫大小
SELECT pg_size_pretty(pg_database_size('appdb'));

-- 資料表大小排行（含索引）
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

-- 終止異常查詢（審慎使用，執行前先確認 pid 對應的查詢內容）
SELECT pg_terminate_backend(12345);

↑ 回目錄

第46章 AI Agent Database Operations 與 SQL 安全分級

46.1 核心原則

AI Agent 不應直接擁有 PostgreSQL Superuser 權限。 這是本手冊在第30章已建立的原則，本章進一步定義「哪些 SQL 可以讓 Agent 直接執行、哪些需要人工核准、哪些永遠不允許」。

flowchart TB
    REQ[AI Request] --> PARSER[SQL Parser<br/>解析出操作類型與影響範圍]
    PARSER --> POLICY[Policy Engine]
    POLICY --> RISK[Risk Classification]
    RISK -->|Safe| EXEC[Execute]
    RISK -->|Requires Approval| APPROVAL[Human Approval]
    APPROVAL -->|核准| EXEC
    APPROVAL -->|駁回| DENY[Denied + Audit Log]
    RISK -->|Never Allowed| DENY

46.2 SQL 風險分級表

46.3 即使是 Requires Approval 的操作，也要求可控寫法

-- ✅ AI Agent 產生的變更提案應優先採用可驗證、可回滾的寫法
BEGIN;

-- 驗證影響範圍
SELECT count(*) FROM app.orders WHERE status = 'pending' AND order_date < now() - interval '90 days';

-- 若確認範圍符合預期才執行
UPDATE app.orders
SET status = 'expired'
WHERE status = 'pending' AND order_date < now() - interval '90 days';

COMMIT;

46.4 Read-only Query 的額外防護

-- 即使是 Safe 等級的 SELECT，也建議透過連線層級設定強制 Timeout 與唯讀
SET statement_timeout = '5s';
SET default_transaction_read_only = on;

本章重點

1. SQL 安全分級（Safe／Requires Approval／Never Allowed）讓「AI Agent 能做什麼」變成明確規則，而非依賴 Prompt 中的口頭約束。
2. 即使是需要核准的變更，也應該產生「先驗證範圍、再執行、可回滾」的提案，降低核准者的審查負擔與風險。
3. Read-only 查詢也要有 Timeout 與 Row Limit 等連線層防護，避免「安全等級最低的操作」反而造成可用性事故。

↑ 回目錄

第47章 AI Agent＋PostgreSQL MCP

47.1 MCP Server 應提供的能力

Model Context Protocol（MCP）讓 AI Agent 得以用標準化介面存取外部工具與資料源。PostgreSQL MCP Server 至少應提供：

47.2 MCP Server 必須具備的防護機制

flowchart TB
    AGENT[AI Agent] --> MCP[PostgreSQL MCP Server]
    MCP --> AUTHN[Authentication]
    AUTHN --> AUTHZ[Authorization]
    AUTHZ --> ALLOWLIST[SQL Allowlist<br/>僅允許特定操作類型]
    ALLOWLIST --> TIMEOUT[Query Timeout]
    TIMEOUT --> ROWLIMIT[Row Limit]
    ROWLIMIT --> AUDIT[Audit Log]
    AUDIT --> APPROVAL[Approval Workflow<br/>（寫入類操作）]
    APPROVAL --> PG[(PostgreSQL<br/>Read-only Role 預設)]

本章重點

1. MCP Server 是「AI Agent 存取 PostgreSQL 的閘道」，其安全設計等同於對外 API 的安全設計，不能因為呼叫端是 AI 就降低標準。
2. SQL Allowlist + Read-only Default 是最基本的縱深防禦：即使上層授權判斷有漏洞，底層連線角色仍限制了可能造成的傷害範圍。
3. Audit Log 必須記錄「誰（哪個 Agent/使用者）在何時執行了什麼查詢」，這是事後追查與合規稽核的必要條件。

↑ 回目錄

第48章 AI Agent Database Governance

48.1 治理框架

flowchart TB
    AGENT[AI Agent] --> IDENTITY[Identity<br/>是哪個 Agent/代表哪位使用者]
    IDENTITY --> POLICY[Policy<br/>適用哪些規則]
    POLICY --> PERMISSION[Permission<br/>實際可執行的操作範圍]
    PERMISSION --> VALIDATION[SQL Validation<br/>語句是否符合分級規則]
    VALIDATION --> AUDIT[Audit<br/>完整記錄]
    AUDIT --> PG[(PostgreSQL)]

48.2 治理必須回答的問題

48.3 治理落地：延伸第30章 Schema

第30章的 agent.tool_call 與 agent.audit_log 資料表，正是本章治理框架的具體實作——risk_level 對應 Policy 判斷結果，approved_by 對應 Approval 記錄，request_args／response_body 對應 What／Result。企業導入 AI Agent Database Governance 時，建議直接以這組 Schema 為起點，依組織治理需求擴充欄位（如加入 compliance_tag、data_classification）。

本章重點

1. AI Agent Database Governance 本質是把「Who/What/When/Why/Which/Result/Approval」七個問題，變成資料庫中可查詢、可稽核的紀錄。
2. 治理框架不是額外的官僚流程，而是讓「AI Agent 做了什麼」從不透明的黑盒，變成可事後重建的軌跡。
3. 第30章的 Schema 設計已內建治理所需的欄位，治理落地應優先重用既有結構，而非另起爐灶。

↑ 回目錄

第49章 AI Agent 逆向工程 Knowledge Graph

49.1 Knowledge Graph 的節點類型

延伸第31、32章的 Schema，逆向工程累積的知識可視為一張以 PostgreSQL 儲存的關聯圖：

flowchart TB
    REPO[Repository] --> PROJ[Project]
    PROJ --> MOD[Module]
    MOD --> PKG[Package]
    PKG --> CLS[Class]
    CLS --> METH[Method]
    METH -->|calls| API[API]
    METH -->|reads/writes| SQL[SQL]
    SQL -->|reads| TABLE[Table]
    TABLE --> COL[Column]
    REQ[Requirement] -.對應.-> CLS
    RULE[BusinessRule] -.來源於.-> METH
    TEST[Test] -.驗證.-> METH
    ISSUE[Issue] -.關聯.-> CLS

49.2 用 SQL 查詢架構關係範例

-- 找出某個 API 端點最終會讀寫哪些資料表（追蹤完整呼叫鏈）
WITH RECURSIVE call_chain AS (
  SELECT symbol_id, symbol_name, 1 AS depth
  FROM code.symbol
  WHERE symbol_name = 'OrderController.createOrder'
  UNION ALL
  SELECT s.symbol_id, s.symbol_name, cc.depth + 1
  FROM code.dependency d
  JOIN code.symbol s ON s.symbol_id = d.to_symbol_id
  JOIN call_chain cc ON cc.symbol_id = d.from_symbol_id
  WHERE cc.depth < 10  -- 避免遞迴過深或循環依賴造成無限迴圈
)
SELECT DISTINCT symbol_name, depth FROM call_chain ORDER BY depth;

這類查詢讓 AI Agent（或人類架構師）可以直接問「這個 API 到底動到哪些資料表」，而不需要人工逐層追蹤程式碼——這正是第5章提到「System Catalog 本身可被 SQL 查詢」精神的延伸應用：架構知識本身也被建模成可查詢的資料。

本章重點

1. Knowledge Graph 不需要額外導入圖資料庫——PostgreSQL 的關聯式結構搭配 Recursive CTE，足以表達與查詢多數程式碼架構關係。
2. 呼叫鏈追蹤（Class → Method → SQL → Table）讓「這段程式碼影響範圍多大」這類問題可以被系統化回答，而非依賴資深工程師的經驗記憶。
3. Knowledge Graph 的價值來自持續維護——建議搭配 CI 流程，在程式碼變更時增量更新，而非一次性建置後放著不管。

↑ 回目錄

第50章 RAG＋Knowledge Graph 混合架構

50.1 三種查詢模式並存

flowchart TB
    AGENT[AI Agent]
    AGENT --> SQLSEARCH[SQL Search<br/>結構化、精確關聯查詢]
    AGENT --> VECSEARCH[Vector Search<br/>語意相似度查詢]
    SQLSEARCH --> PG[(PostgreSQL)]
    VECSEARCH --> PG
    PG --> REL[Relational<br/>結構化業務資料]
    PG --> JSONB[JSONB<br/>半結構化資料]
    PG --> VECTOR[pgvector<br/>Embedding]

50.2 何時使用哪種查詢模式

50.3 混合查詢範例：RAG 檢索時同時套用結構化過濾

-- 向量相似度 + 結構化條件（文件狀態、權限標籤）+ 全文檢索關鍵字加權
SELECT chunk_id, content,
       (embedding <=> :query_embedding) AS vector_distance
FROM app.rag_chunk c
JOIN app.rag_document d ON d.document_id = c.document_id
WHERE d.tenant_id = :tenant_id
  AND d.permission_tag = ANY(:user_allowed_tags)
ORDER BY vector_distance
LIMIT 10;

本章重點

1. 沒有一種查詢模式能解決所有問題，成熟的 AI 應用架構會依問題性質選擇 SQL／JSONB／全文檢索／向量搜尋/Graph 查詢的組合。
2. PostgreSQL 的優勢正是能在同一個系統、同一次交易內混合使用這些模式，不需要跨系統整合的額外複雜度。
3. RAG 檢索永遠要疊加結構化的權限與租戶過濾條件，這是第29章已強調、在此進一步落實的安全原則。

↑ 回目錄

第51章 Observability＋AI（Root Cause Analysis）

51.1 架構

flowchart TB
    PG[(PostgreSQL)] --> METRICS[Metrics]
    PG --> LOGS[Logs]
    PG --> TRACES[Traces]
    METRICS --> PROM[Prometheus]
    LOGS --> OTEL[OpenTelemetry Collector]
    TRACES --> OTEL
    PROM --> GRAF[Grafana]
    OTEL --> GRAF
    GRAF --> AGENT[AI Agent]
    AGENT --> RCA[Root Cause Analysis<br/>建議報告]
    RCA --> HUMAN[人工審閱與決策]

51.2 AI Agent 可協助分析的項目

51.3 不可跨越的邊界

AI 建議 ≠ 自動執行。 高風險 DBA 操作（重啟服務、調整全域參數、終止交易、執行 REPACK/VACUUM FULL 等鎖表操作）必須經過人工審閱與核准（呼應第46章的風險分級），AI Agent 在 Observability 場景的角色定位是分析與建議，而非自主修復（Auto-Remediation）——除非企業已針對特定、範圍明確、風險極低的操作（如「清除已知安全的過期快取」）建立過完整測試與核准的自動化白名單。

本章重點

1. AI Agent 在 Observability 領域最有價值的貢獻是「跨指標關聯分析」與「產生結構化 Root Cause 假設」，加速人工診斷，而非取代判斷。
2. 任何會改變資料庫狀態或影響可用性的建議，執行前都必須回到第46章的 SQL 安全分級與人工核准流程。
3. Observability + AI 的成熟度應該漸進：先做「輔助診斷」，累積足夠信任與案例後，才考慮對極低風險操作開放範圍受限的自動化。

↑ 回目錄

第52章 PostgreSQL Upgrade（Minor／Major）

52.1 Minor Upgrade vs Major Upgrade

原則：Minor Upgrade 應盡快、定期套用（尤其是安全性修補），Major Upgrade 需要完整的評估流程，不應倉促執行。

52.2 Major Upgrade 三種手段比較

52.3 標準升級流程

flowchart LR
    A[Assessment] --> B[Compatibility Test]
    B --> C[Backup]
    C --> D[Upgrade]
    D --> E[Validation]
    E --> F[Performance Test]
    F --> G[Cutover]
    G --> H[Rollback Plan<br/>已準備但期望不需使用]

本章重點

1. Minor Upgrade 是低風險的例行維護，應保持定期套用的節奏，尤其是安全性修補。
2. Major Upgrade 的手段選擇（pg_upgrade / dump-restore / Logical Replication）取決於資料量與可接受停機時間的權衡。
3. 升級流程必須包含明確的 Rollback Plan，即使期望用不到，也要確保「真的用得到時來得及」。

↑ 回目錄

第53章 PostgreSQL 18 → 19 Migration Runbook

53.1 相容性檢查重點

依第2章整理的 PostgreSQL 19（Beta）變更，18 → 19 升級評估必須特別檢查：

53.1a pg_upgrade --check 會直接攔截的項目（必須升級前排除）

以下四項是 19 Release Notes 明確列為會讓 pg_upgrade --check 直接失敗的不相容變更，優先度高於其他一般性檢查——建議作為 Runbook 第一階段（Pre-Check）的強制檢查項，而非「建議檢查」：

實務建議：升級 Runbook 的 Pre-Check 階段，第一步就先跑一次 pg_upgrade --check（不執行實際升級，僅檢查），把上述四項的檢查結果當作「能否進入下一階段」的關卡（Gate），而非等到正式升級當下才發現卡關。

53.2 Runbook

flowchart TB
    P1["1. Pre-Check<br/>盤點自訂參數、Extension、Driver"] --> P2["2. Backup<br/>完整備份現有 18 環境"]
    P2 --> P3["3. Compatibility Test<br/>測試環境還原並升級"]
    P3 --> P4["4. Dependency 驗證<br/>應用程式回歸測試"]
    P4 --> P5["5. Extension 驗證"]
    P5 --> P6["6. Test<br/>功能/效能/安全測試"]
    P6 --> P7["7. Upgrade<br/>正式環境執行"]
    P7 --> P8["8. Validation<br/>資料與功能驗證"]
    P8 --> P9["9. Monitoring<br/>密切觀察 48-72 小時"]
    P9 --> P10["10. Rollback（如需要）"]
    P10 --> P11["11. Post Upgrade Review"]

🔍 待官方確認：本 Runbook 基於撰稿當下（Beta 3）已知資訊整理。PostgreSQL 19 GA 前，本章內容僅供評估規劃參考，正式升級 Production 前必須以當時的官方 Release Notes 與官方升級文件為準，並重新確認是否有新增的 Breaking Change。

本章重點

1. 18 → 19 升級評估的重點不是「有什麼新功能」，而是「哪些既有行為會改變」——max_locks_per_transaction、JIT 預設值、RADIUS 移除是三個最需要事先確認的項目。
2. Runbook 的核心價值在於「可重複執行、可稽核」，每個階段都應留下紀錄，而非依賴執行者的臨場經驗。
3. GA 前的所有評估都應標註為「基於 Beta 版本的初步結論」，正式升級前必須以 GA 版本的官方文件重新驗證。

↑ 回目錄

第54章 Capacity Planning

54.1 需要估算的維度

54.2 簡化估算範例（僅供示意，實際仍需依真實 Workload 調整）

預估尖峰併發連線數：200
每連線平均 work_mem 峰值：32MB
work_mem 總需求（保守估計，非所有連線同時達峰值）：約 3-4GB

shared_buffers 建議起點：實體記憶體的 25%
作業系統檔案快取保留：不應被 shared_buffers 過度排擠

⇒ 需要透過實測（第24章方法論）驗證，而非直接套用此範例數字

本章重點

1. Capacity Planning 涵蓋 CPU/Memory/Storage/IOPS/Network/Connection 六大維度，任何單一維度的估算不足都可能成為未來的瓶頸。
2. 容量估算是「起點」而非「終點」，必須搭配第24章的方法論持續用實際監控數據校正。
3. Backup Window 與 WAL Rate 是容易被忽略、卻直接影響 RPO/RTO 可達成性的兩個維度。

↑ 回目錄

第55章 Disaster Recovery

55.1 RPO 與 RTO

55.2 DR 架構

flowchart TB
    PRIMARY[(Primary<br/>主要區域)] -->|Streaming Replication| DRSITE[(DR PostgreSQL<br/>異地備援區域)]
    PRIMARY --> BACKUP[Backup]
    BACKUP --> OBJSTORE[(Object Storage<br/>跨區域複製)]
    OBJSTORE -.可還原至.-> DRSITE

55.3 DR 演練的重要性

RPO/RTO 只是紙上目標，唯有實際演練過的數字才可信。 企業應定期（依政策至少每季）執行完整的 DR 演練：模擬 Primary 完全失效，實際執行 Failover 或從備份還原至 DR 站點，記錄實際花費時間與過程中發現的問題，並據此更新 Runbook。

本章重點

1. RPO 與 RTO 是業務需求（能接受遺失多少資料、能接受停機多久）反推出的技術指標，不是先射箭再畫靶。
2. DR 架構的選擇（純備份／異步 Standby／同步 Standby）本質是成本與 RPO/RTO 目標之間的權衡。
3. 未經演練驗證的 DR 計畫只是理論——實際執行過的 Failover/還原時間，才是可信的 RTO 依據。

↑ 回目錄

第56章 金融／銀行企業應用案例

56.1 典型業務資料模型

-- 帳戶（Account）
CREATE TABLE fin.account (
  account_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL,
  account_no   varchar(34) NOT NULL UNIQUE,   -- 預留 IBAN 長度
  currency     char(3) NOT NULL,
  balance      numeric(18,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  status       varchar(20) NOT NULL DEFAULT 'active'
);

-- 分類帳（Ledger）：每筆異動都留下不可竄改的紀錄，餘額透過加總計算或搭配 Trigger 維護
CREATE TABLE fin.ledger_entry (
  entry_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id   bigint NOT NULL REFERENCES fin.account(account_id),
  entry_type   varchar(10) NOT NULL CHECK (entry_type IN ('debit', 'credit')),
  amount       numeric(18,2) NOT NULL CHECK (amount > 0),
  reference_id bigint NOT NULL,   -- 關聯至 payment/transaction
  created_at   timestamptz NOT NULL DEFAULT now()
);

56.2 金融系統的特別要求

-- 悲觀鎖範例：轉帳時鎖定兩個帳戶列，避免併發扣款造成餘額錯誤
BEGIN;
SELECT balance FROM fin.account WHERE account_id = 1001 FOR UPDATE;
SELECT balance FROM fin.account WHERE account_id = 1002 FOR UPDATE;
-- 確認餘額充足後才執行扣款與入帳
UPDATE fin.account SET balance = balance - 100 WHERE account_id = 1001;
UPDATE fin.account SET balance = balance + 100 WHERE account_id = 1002;
COMMIT;

本節僅示範資料庫設計層面的通用原則，不涉及、也不得捏造任何特定銀行的內部機密資訊或實際業務規則。

本章重點

1. 金融系統的資料模型設計核心是「精確金額 + 不可竄改的異動紀錄」，這兩者是資料庫層必須強制的完整性保證。
2. 涉及餘額扣款的併發操作，需要明確的鎖策略（悲觀鎖或 Serializable 隔離），避免競態條件造成資金誤差。
3. 日終批次與報表應與線上交易資源隔離（走 Replica），避免報表查詢拖垮交易系統的回應時間。

↑ 回目錄

第57章 大型企業微服務資料庫模式

57.1 資料庫拓撲模式

flowchart TB
    GW[API Gateway] --> MS1[Order Service]
    GW --> MS2[Inventory Service]
    GW --> MS3[Payment Service]
    GW --> MS4[Notification Service]
    MS1 --> DB1[(Order DB)]
    MS2 --> DB2[(Inventory DB)]
    MS3 --> DB3[(Payment DB)]
    MS4 --> DB4[(Notification DB)]

企業實務上，Database per Service 是較被推薦的預設模式，但需要配套解決跨服務資料一致性的問題。

57.2 跨服務一致性：Saga、Event Driven、Outbox Pattern

flowchart LR
    ORDER[Order Service] -->|1.建立訂單+寫入 Outbox| ORDERDB[(Order DB)]
    ORDERDB -->|2.CDC 讀取 Outbox| RELAY[Outbox Relay / CDC]
    RELAY -->|3.發布事件| BUS[Message Bus]
    BUS -->|4.消費事件| PAYMENT[Payment Service]
    PAYMENT -->|5.處理付款| PAYMENTDB[(Payment DB)]

-- Outbox 資料表範例：與業務異動同一交易寫入，保證「異動成功」與「事件已記錄」的一致性
CREATE TABLE app.outbox_event (
  event_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  aggregate_type varchar(50) NOT NULL,
  aggregate_id   bigint NOT NULL,
  event_type     varchar(50) NOT NULL,
  payload        jsonb NOT NULL,
  published      boolean NOT NULL DEFAULT false,
  created_at     timestamptz NOT NULL DEFAULT now()
);

BEGIN;
INSERT INTO app.orders (customer_id, total_amount) VALUES (123, 999.00) RETURNING order_id;
INSERT INTO app.outbox_event (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', :order_id, 'order_created', '{"order_id": ' || :order_id || '}');
COMMIT;

本章重點

1. Database per Service 是微服務架構下較推薦的預設拓撲，但必須搭配 Saga／Event Driven／Outbox 解決跨服務一致性問題。
2. Outbox Pattern 利用「業務資料與事件記錄同一資料庫交易」的特性，解決了「資料寫入成功但事件發布失敗」的經典分散式系統難題。
3. Logical Replication／Logical Decoding（第22章）可作為實作 CDC 與 Outbox Relay 的原生技術基礎，不一定需要額外引入 Debezium 等第三方工具（但大型專案也常見兩者搭配使用）。

↑ 回目錄

第58章 Anti-Patterns

以下反模式在企業 PostgreSQL 專案中反覆出現，逐一列出原因與正確做法：

本章重點

1. 這些反模式的共通根源多半是「圖一時方便，忽略長期維運與安全代價」。
2. 多數反模式都有本手冊對應章節的正確做法，出現反模式時應回頭檢視對應章節的原則，而非只做局部修補。
3. Code Review／架構審查應將本清單作為檢查項目之一，提前攔截而非等事故發生才處理。

↑ 回目錄

第59章 Coding Standards

59.1 命名規範

59.2 SQL 格式化與交易慣例

-- 關鍵字大寫、每個子句換行、適度縮排，提升團隊可讀性一致性
SELECT
    c.customer_id,
    c.customer_name,
    count(o.order_id) AS order_count
FROM app.customer AS c
LEFT JOIN app.orders AS o
    ON o.customer_id = c.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.customer_name
ORDER BY order_count DESC;

• 多步驟寫入務必包在明確的 BEGIN...COMMIT 交易範圍內
• 錯誤處理：應用層對交易失敗（含 Deadlock、Serialization Failure）需有一致的重試/回報機制，不可靜默吞掉例外
• 註解：僅在「為什麼這樣寫」不直觀時才加註解（例如刻意反正規化的欄位、繞過某個已知限制的 Workaround），避免贅述「這行在做什麼」

本章重點

1. 命名規範的價值在於「一致性」本身，團隊選定規範後應透過 Code Review 或 Linter 持續落實，而非各自為政。
2. SQL 格式化一致性能大幅降低 Code Review 的認知負擔，建議搭配團隊共用的格式化工具設定。
3. 交易邊界與錯誤處理是比格式化更關鍵的規範，多步驟寫入未包交易是實務上常見卻後果嚴重的疏漏。

↑ 回目錄

第60章 AI Development Coding Standards

本章制定 AI Agent（或使用 AI 輔助撰寫 SQL 的工程師）產生 SQL 與 Schema 變更時應遵守的規範，呼應第46～48章的治理原則：

1. 不得猜 Schema。 Agent 產生 SQL 前，必須先透過 Catalog 查詢（第5、32章）確認實際的資料表與欄位存在，不可依賴訓練資料中的記憶或猜測欄位名稱。
2. 先 Inspect Schema，再撰寫查詢。 透過 MCP（第47章）或直接查詢 information_schema 取得最新結構。
3. 先 EXPLAIN，再修改。 對於效能敏感的查詢，先取得執行計畫評估影響，而非直接上線後才發現效能問題。
4. Production 寫操作必須 Approval。 依第46章的 SQL 安全分級，UPDATE／DELETE／DDL 一律進入核准流程。
5. 所有 Migration 必須 Version Control。 不允許 Agent 直接對 Production 執行未經版本控制的 ALTER TABLE 等變更（第36章）。
6. 所有 Destructive SQL 必須明確標示。 Agent 產生的變更提案中，任何 DROP／DELETE／TRUNCATE 必須在說明中清楚標註風險與影響範圍，不可隱藏在大量變更中不易被發現。
7. 所有 Query 必須有 Timeout。 避免 Agent 產生的查詢因執行計畫不佳而長時間佔用資源（第46章）。
8. 大型資料操作必須評估 Lock 影響。 大表的 DDL／批次更新，Agent 應主動評估是否需要分批執行或使用 CONCURRENTLY 選項，而非產生會長時間鎖表的一次性操作。
9. 必須保留 Audit。 所有 Agent 執行或建議的操作，需記錄於第48章的治理框架中。
10. 不得將 Secret 放入 Prompt 或程式碼。 資料庫密碼、API Key 等機密資訊不應出現在 Agent 的輸入、輸出或產生的程式碼中，一律透過密鑰管理服務參照。

本章重點

1. AI Coding Standards 本質是把「資深 DBA 的直覺」轉譯成 Agent 可依循的明確規則，因為 Agent 沒有「過去踩過的坑」帶來的直覺。
2. 「先查證、後動作」（Inspect Schema → Explain → 提案 → 核准 → 執行）是貫穿本章所有規則的核心思維模式。
3. 這十條規則應內建於 Agent 的 System Prompt 或工具層限制中，而非僅是文件上的期望，才能真正產生約束力。

↑ 回目錄

第61章 實戰 Lab

以下 20 個 Lab 依難度遞增排列，建議依序完成，每個 Lab 都基於前面章節的實際指令。

本章重點

1. 20 個 Lab 覆蓋從安裝到 AI Agent 整合的完整技能樹，建議搭配對應章節內容邊做邊讀。
2. Lab 8、9（Transaction／Deadlock）與 Lab 12（Replication）建議在容器或虛擬機環境操作，避免影響其他開發用途的資料庫。
3. 完成全部 Lab 後，應能獨立完成「建置一個具備基本 HA、監控、AI 整合能力的 PostgreSQL 應用系統」。

↑ 回目錄

第62章 完整企業專案實例

62.1 專案架構：AI Agent + Spring Boot + PostgreSQL + pgvector + Vue 3

flowchart TB
    VUE[Vue 3 前端] --> API[REST API]
    API --> SB[Spring Boot 4.x]
    SB --> SVC[Service Layer]
    SVC --> REPO[Repository Layer<br/>Spring Data JPA]
    REPO --> PG[(PostgreSQL 18)]
    PG --> REL[Relational 業務資料]
    PG --> JSONB[JSONB 彈性欄位]
    PG --> FTS[Full Text Search]
    PG --> VEC[pgvector 向量搜尋]
    SVC --> AGENTSVC[AI Agent Service]
    AGENTSVC --> LLM[LLM API]
    AGENTSVC --> PG

62.2 技術堆疊清單

62.3 核心模組示意

// 示意：AI Agent 服務層呼叫受控的資料存取工具，而非直接組裝任意 SQL
@Service
public class AgentQueryService {

    private final JdbcTemplate readOnlyJdbcTemplate; // 綁定第46章定義的 Read-only Role 連線

    public List<DocumentChunk> searchSimilarChunks(UUID tenantId, float[] queryEmbedding, int limit) {
        String sql = """
            SELECT chunk_id, content, embedding <=> ? AS distance
            FROM app.rag_chunk
            WHERE tenant_id = ?
            ORDER BY embedding <=> ?
            LIMIT ?
            """;
        return readOnlyJdbcTemplate.query(sql,
            (rs, rowNum) -> mapToChunk(rs),
            queryEmbedding, tenantId, queryEmbedding, limit);
    }
}

本章重點

1. 本專案實例串連了本手冊多數重點：正規化業務資料、JSONB 彈性欄位、pgvector 向量搜尋、AI Agent 受控存取、完整測試與監控。
2. AI Agent 相關的資料存取，即使在應用程式內部，也建議透過獨立、綁定唯讀角色的 Service/Repository 呼叫，維持第46章的安全分級精神。
3. 這是一個「架構骨架」示範，實際專案仍需依第42章 Production Checklist 補齊安全、備份、HA 等生產化細節。

↑ 回目錄

第63章 Enterprise AI Coding Platform 實例

63.1 平台架構

flowchart TB
    DEV[Developer] --> AGENT[AI Coding Agent]
    AGENT --> ORCH[Agent Orchestrator]
    ORCH --> REPO[Code Repository]
    ORCH --> PG[(PostgreSQL)]
    PG --> META[Metadata<br/>Symbol/Dependency/AST]
    PG --> RAG[RAG<br/>文件/需求/ADR]
    PG --> VEC[Vector<br/>Code Embedding]
    META & RAG & VEC --> AGENT2[AI Agent 查詢與推理]

63.2 平台定位：AI Software Engineering Knowledge Platform

這個架構把第31章（AI Coding Agent）、第32章（Reverse Engineering）、第33章（Framework Upgrade 知識庫）、第49章（Knowledge Graph）的資料模型整合成單一平台：

63.3 客觀邊界提醒

PostgreSQL 在此平台中的角色是「知識底層」，不是「智慧來源」。 實際的程式碼理解、生成、推理能力來自 AI Agent／LLM 本身；PostgreSQL 提供的是讓這些能力可以查詢到正確、最新、結構化資訊的基礎設施。清楚區分這個責任邊界，才能正確評估平台各部分的維護重點：資料庫負責「資料新鮮度與查詢效能」，Agent／LLM 負責「推理品質」，兩者是互補而非替代關係。

本章重點

1. Enterprise AI Coding Platform 的核心價值在於把分散的程式碼知識（Metadata、逆向工程結果、升級知識、Knowledge Graph、RAG 文件）整合進同一個可查詢的資料底層。
2. 平台的資料新鮮度直接影響 Agent 建議的品質——過期的 Metadata 比沒有 Metadata 更危險（Agent 可能基於錯誤的既有結構做出錯誤建議）。
3. PostgreSQL 是知識基礎設施，不是推理引擎，責任邊界需要在架構文件中明確寫清楚，避免高估資料庫在整個 AI 平台中的角色。

↑ 回目錄

第64章 常見面試題

Beginner

Q1. PostgreSQL 是什麼類型的資料庫？與 MySQL 最大的差異是什麼？ A：物件關聯式資料庫（ORDBMS），與 MySQL 相比在 SQL 標準相容性（Window Function、CTE、Recursive Query）、資料型別豐富度（JSONB、Array、Range）與 Extension 生態上更完整（第1、3章）。

Q2. timestamp 與 timestamptz 的差異？ A：timestamp 不含時區資訊；timestamptz 內部以 UTC 儲存，依連線 TimeZone 設定顯示，企業系統建議預設使用 timestamptz（第13章）。

Q3. 為什麼金額欄位不能用 double precision？ A：浮點數有精度誤差，累積計算會產生真實的金額誤差；numeric 提供精確的十進位運算（第13章）。

Intermediate

Q4. 什麼是 MVCC？它如何讓 PostgreSQL 達成「讀不阻塞寫」？ A：MVCC 讓每次 UPDATE 產生新版本資料列而非覆寫，交易依 Snapshot 判斷可見版本，讀取交易不需要等待寫入交易釋放鎖（第17章）。

Q5. VACUUM 與 VACUUM FULL 的差異？ A：VACUUM 回收空間供內部重複使用，不阻塞讀寫；VACUUM FULL 重建整張表釋放磁碟空間給作業系統，但需要 Exclusive Lock，會阻塞讀寫（第18章）。

Q6. 什麼情況下 Planner 會選擇 Seq Scan 而非 Index Scan，即使有索引存在？ A：當查詢預期回傳的資料列比例夠高，Seq Scan 的成本反而低於「大量隨機 I/O 的 Index Scan + 回表」，這是 Cost-Based Optimizer 的正常行為（第16章）。

Advanced

Q7. Read Committed 與 Serializable 隔離等級的具體差異，並各自可能發生的異常？ A：Read Committed 每個陳述式看當下已提交的最新資料，可能有 Non-repeatable Read；Serializable 提供最嚴格保證，PostgreSQL 以 SSI 實作，異常改以 Serialization Failure 呈現，需應用層重試（第17章）。

Q8. Logical Replication 與 Physical Replication 的核心差異及各自適用場景？ A：Physical 是位元組級 WAL 複寫，整個叢集、需相同版本，適合 HA；Logical 是資料表層級、可跨版本，適合升級與資料整合（第22章）。

DBA

Q9. Replication Slot 為什麼可能導致 Primary 磁碟塞滿？如何預防？ A：Slot 會讓 Primary 保留 WAL 直到對應 Replica/Subscriber 取走，若該端長期離線，WAL 持續累積；需監控 pg_replication_slots 並對失效 Slot 有清理機制（第21、44章）。

Q10. 如何診斷並解決 Deadlock？ A：透過 pg_locks 搭配 pg_stat_activity 重建鎖等待鏈，找出涉及的交易；長期解法是統一資源存取順序、應用層加入重試邏輯（第17、44章）。

Architect

Q11. 設計一個多租戶 SaaS 系統的資料庫架構，你會如何選擇 Database per Tenant / Schema per Tenant / Shared Table？ A：依租戶數量與隔離性要求權衡（第39章）：租戶少且隔離要求極高選 Database per Tenant；租戶數中等選 Schema per Tenant；租戶數龐大選 Shared Table + Row Level Security，大型 SaaS 常見依客戶規模採 Hybrid 模式。

Q12. 如何設計一個零停機的 PostgreSQL 大版本升級方案？ A：透過 Logical Replication 讓新舊版本並行同步，資料追上後切換應用程式連線（第22、52章），搭配 Expand/Contract 模式處理應用程式相容性（第36章）。

AI Engineer

Q13. pgvector 的 HNSW 與 IVFFlat 索引該如何選擇？ A：HNSW 建索引較慢但查詢延遲低、召回率穩定，適合查詢頻繁的 Production；IVFFlat 建索引快但需要足夠資料量與合理的 lists 參數，查詢前需設定 probes 平衡召回率與速度（第28章）。

Q14. 企業 RAG 系統中，如何防止跨租戶資料外洩？ A：檢索的 SQL 必須在 WHERE 條件中強制過濾 tenant_id，並建議搭配 Row Level Security 在資料庫層強制執行，而非僅依賴應用層邏輯或 Prompt 約束（第28、29章）。

Q15. 為什麼 AI Agent 不應該擁有 PostgreSQL Superuser？應該如何設計權限模型？ A：Superuser 一旦被 Prompt Injection 或邏輯錯誤誘導，可能造成無法挽回的損害；應依 Agent 類型（Read-only／Read-write／DBA／Migration／Monitoring）分別授予最小必要權限，並搭配 SQL 安全分級與人工核准流程（第30、46章）。

本章重點

1. 面試題設計涵蓋 Beginner 到 AI Engineer 六個層級，可依受訪者職級與方向調整比重。
2. 多數進階題目的正確答案都需要引用具體機制（MVCC、Cost-Based Optimizer、Replication Slot），而非僅停留在名詞定義，這也是評估候選人實務深度的關鍵。
3. AI Engineer 相關題目特別聚焦「安全邊界」而非「功能會不會用」，反映企業導入 AI Agent 時最容易出問題的環節。

↑ 回目錄

第65章 FAQ

Q：PostgreSQL vs MySQL，該怎麼選？ A：需要複雜查詢、高標準相容性、豐富資料型別（JSONB/Array/Range）時選 PostgreSQL；追求極致簡單、讀多寫少且已有大量 MySQL 維運經驗的團隊，MySQL 也是合理選擇。兩者都是成熟的開源選項，差異在於工作負載特性與團隊既有經驗（第3章）。

Q：PostgreSQL vs Oracle，企業為什麼要遷移？ A：授權成本是最直接的驅動因素；此外 PostgreSQL 的開放治理模式避免被單一廠商鎖定。遷移前需仔細評估既有 PL/SQL 商業邏輯的移植成本（第1、3章）。

Q：JSONB 是否能取代 MongoDB？ A：能覆蓋多數「以為需要文件資料庫」的場景（Schema 大致穩定但有彈性欄位），但無法取代 MongoDB 在真正 Schema-less、需要極致水平擴展場景的優勢（第3、27章）。

Q：PostgreSQL 能不能做 Vector DB？ A：透過 pgvector 可以，且在需要與業務資料一致性、Metadata Filtering 的場景有優勢；十億級向量的極致擴展場景則專用 Vector DB 更合適（第28章）。

Q：PostgreSQL 能不能做 RAG？ A：可以，pgvector + 關聯式 Metadata + 全文檢索的組合，適合中小至中大規模、需要與業務資料一致的 RAG 系統（第29章）。

Q：PostgreSQL 能不能做 AI Agent Memory？ A：可以，透過第30章的 Schema 設計（Session/Message/Task/Memory/Audit），PostgreSQL 可作為 Agent 的短期與長期記憶儲存，且天生具備交易一致性與稽核能力。

Q：PostgreSQL 是否適合金融系統？ A：適合，numeric 精確運算、完整 ACID、成熟的 HA/DR 生態，是金融系統常見選擇；實際導入仍需依監理要求完成合規驗證（第56章）。

Q：PostgreSQL 是否適合 Microservices？ A：適合作為 Database per Service 模式下各服務的資料庫；跨服務一致性需搭配 Saga/Event Driven/Outbox Pattern（第57章）。

Q：pgvector 是否適合大型 AI 應用？ A：中大規模、需與業務資料整合一致性的場景適合；超大規模（十億級以上向量、向量搜尋為唯一核心工作負載）建議評估專用 Vector DB（第28章）。

Q：PostgreSQL 如何做 HA？ A：Streaming Replication + Patroni（Leader Election）+ etcd（Quorum）+ HAProxy/PgBouncer（流量路由）是常見的完整方案（第21章）。

Q：PostgreSQL 如何 Backup？ A：邏輯備份（pg_dump）跨版本相容但還原慢；實體備份（pg_basebackup/pgBackRest/WAL-G）還原快且可搭配 PITR。企業通常兩者並行，且必須定期做 Restore Test（第20章）。

Q：PostgreSQL 如何 Upgrade？ A：Minor Upgrade 定期套用風險低；Major Upgrade 依資料量與可接受停機時間，選擇 pg_upgrade、dump/restore 或 Logical Replication（第52、53章）。

↑ 回目錄

第66章 技術選型決策表

本章重點

1. 技術選型沒有「萬用解」，PostgreSQL 的強項是「一個系統涵蓋多種工作負載」，而非在每個單點需求上都是最強解。
2. 當某個非關聯式需求（快取、搜尋、圖、分析）已成為系統的核心工作負載時，應評估搭配對應的專用系統，而非勉強用 PostgreSQL 承擔。
3. Extension 生態（PostGIS、pgvector）讓「PostgreSQL + Extension」在多個評估項目中都具備競爭力，選型時應把這個組合視為候選方案。

↑ 回目錄

第67章 Learning Roadmap

flowchart TB
    L1[Level 1<br/>SQL Beginner] --> L2[Level 2<br/>Application Developer]
    L2 --> L3[Level 3<br/>Database Developer]
    L3 --> L4[Level 4<br/>Performance Engineer]
    L4 --> L5[Level 5<br/>DBA]
    L5 --> L6[Level 6<br/>Architect]
    L6 --> L7[Level 7<br/>AI Database Engineer]
    L7 --> L8[Level 8<br/>AI Agent Database Architect]

本章重點

1. Roadmap 是漸進式的，Level 5（DBA）與 Level 7（AI Database Engineer）並非互斥分支，而是同一位資深工程師可以同時具備的複合能力。
2. 多數團隊不需要每個人都走到 Level 8，但至少應有 1-2 位角色具備 Level 6～8 的能力，作為架構決策與 AI 導入的把關者。
3. 這個 Roadmap 也可作為團隊培訓計畫或職涯發展對談的參考架構。

↑ 回目錄

第68章 Enterprise Adoption Recommendation

68.1 公司為什麼應該使用 PostgreSQL

寬鬆授權、去中心化治理、SQL 標準相容性、Extension 生態帶來的擴充彈性，讓 PostgreSQL 在長期技術風險（不被單一廠商鎖定）與短期開發效率（豐富資料型別、成熟工具鏈）上都具備優勢（第1章）。

68.2 哪些系統適合

企業核心業務系統（訂單、客戶、金流）、需要交易一致性的服務、AI/RAG 應用的 Metadata + Vector 儲存、需要地理空間能力的系統。

68.3 哪些系統不適合（或不應「只用」PostgreSQL）

純 Key-Value 微秒級快取場景、PB 級 OLAP 分析、以圖演算法為核心的應用、全文檢索為主要功能且規模龐大的搜尋系統——這些場景 PostgreSQL 可以「輔助」（如儲存 Metadata），但不應是唯一系統（第66章）。

68.4 AI Agent 如何導入

遵循漸進式導入：先從 Read-only Agent（查詢、報表輔助）開始累積信任，再導入 Read/Write Agent（受控的業務操作），DBA/Migration/Monitoring Agent 應在明確的核准流程與 Audit 機制到位後才逐步開放（第30、46～48章）。

68.5 DBA 如何轉型

傳統 DBA 技能（效能調校、HA、備份復原）仍是基礎，但需要進一步具備：理解向量索引與 RAG 架構的基本原理、能與 AI Engineer 協作設計 Agent 的資料庫權限模型、能審查 AI Agent 產生的 Migration 提案（第28～30、46章）。

68.6 Developer 如何使用

從第12～17章的 SQL 與 Transaction 基礎開始，逐步理解索引與執行計畫（第15、16章），並學習如何與 AI Coding Agent 協作（先讓 Agent Inspect Schema、產生提案，自己負責審查與核准，第31、60章）。

68.7 Architect 如何設計

以第41章 Enterprise Reference Architecture 為起點，依組織實際的租戶模型（第39章）、資料規模（第54章）、RPO/RTO 要求（第55章）調整拓撲，並在架構文件中明確定義 AI Agent 的權限邊界（第46～48章）。

68.8 DevOps 如何維護

將 Migration Test、Security Test 納入 CI 必要關卡（第37、38章），資料庫基礎設施納入 IaC 管理，並確保監控告警有明確的 On-Call 對應（第23章）。

68.9 如何建立 PostgreSQL Center of Excellence

建議由資深 DBA + Architect + 一位熟悉 AI Agent 整合的工程師組成核心小組，負責：制定第59章 Coding Standards、審核重大 Schema 變更、維護升級路線圖（第52、53章）、對內提供諮詢與教育訓練（本手冊即為此類教材的範例）。

68.10 如何建立企業 PostgreSQL Standard

以本手冊第42章 Production Checklist、第59章 Coding Standards、第69章 Golden Rules 為基礎，依組織實際情況調整後正式化為內部規範文件，並納入新專案啟動時的必要審查項目。

68.11 如何建立 AI Agent Database Governance

以第48章的治理框架（Who/What/When/Why/Which/Result/Approval）為基礎，落實到實際的 Schema 設計（第30章）與 MCP Server 實作（第47章），並定期稽核 Audit Log 確保治理機制真正運作，而非形同虛設。

68.12 如何建立 PostgreSQL Security Standard

整合第10章（Authentication）、第38章（DevSecOps）、第46章（AI Agent SQL 安全分級）的原則，制定涵蓋「人類操作」與「AI Agent 操作」兩種主體的統一安全規範，避免出現「人類走嚴謹流程、AI Agent 走捷徑」的雙重標準。

68.13 如何建立 PostgreSQL Upgrade Policy

明確定義 Minor Upgrade 的例行套用頻率（如每季）、Major Upgrade 的評估週期（如每個大版本 GA 後 6-12 個月內完成評估），並將第53章的 Runbook 模式複製到未來每次大版本升級。

本章重點

1. 企業導入 PostgreSQL（含 AI Agent 整合）應該是漸進式、有明確治理框架的過程，而非一次到位的技術決策。
2. Center of Excellence 與 Governance 機制的價值在於「持續性」——技術標準需要有人維護與更新，而非寫完文件就束之高閣。
3. AI Agent 的安全標準應與人類操作的安全標準一致甚至更嚴格，不應存在「因為是 AI 所以走捷徑」的例外。

↑ 回目錄

第69章 PostgreSQL Enterprise Golden Rules

1. Application 不使用 Superuser，一律透過最小權限 Role 連線（第11章）。
2. Production Database 必須有備份，「沒有備份」不是選項（第20章）。
3. Backup 必須定期執行 Restore Test，未驗證的備份等同沒有備份（第20、43章）。
4. Production 必須有主動監控與告警，而非「出事才知道」（第23章）。
5. 所有 Schema 變更必須透過版本化 Migration 工具，禁止手動改 Production Schema（第36章）。
6. AI Agent 預設使用 Read-only 角色，寫入權限需明確申請與核准（第30、46章）。
7. Destructive SQL（DROP／無條件 DELETE／TRUNCATE）必須經過人工核准（第46章）。
8. 所有重要資料庫操作必須留下 Audit 紀錄，涵蓋人類與 AI Agent 兩種操作者（第48章）。
9. 不要盲目增加 Index，先用 pg_stat_user_indexes 確認實際使用率（第15章）。
10. 不要盲目修改 PostgreSQL 全域參數，任何調整都要有 Before/After 的量化數據支撐（第9、24章）。
11. 所有效能問題必須透過 Measurement（EXPLAIN ANALYZE、pg_stat_*）驗證，禁止憑經驗法則臆測（第16、24章）。
12. 所有大版本升級必須先完成 Compatibility Test，不可直接在 Production 執行（第52、53章）。
13. PostgreSQL 19 GA 前不應被視為 Production Stable Version（第2章）。
14. Secrets（密碼、API Key、連線字串）絕不寫入 Git，一律透過密鑰管理服務注入（第10、38章）。
15. Frontend 不直接連 Database，一律透過後端 API 存取（第35章）。
16. 金額欄位一律使用 numeric，禁止使用 real/double precision（第13章）。
17. 時間欄位優先使用 timestamptz，除非有明確理由使用 timestamp（第13章）。
18. SELECT * 應避免用於 Production 程式碼，明確指定所需欄位（第58章）。
19. 多步驟寫入操作必須包在明確的交易（BEGIN...COMMIT）範圍內（第17、59章）。
20. 連線數必須透過連線池管理，禁止無限制建立連線（第25章）。
21. 多租戶 Shared Table 架構必須搭配 Row Level Security，不能只依賴應用層過濾（第39章）。
22. RAG／向量檢索必須在 SQL 層強制租戶與權限過濾，不可僅依賴 Prompt 約束（第28、29章）。
23. VACUUM FULL 需評估其鎖表影響，Production 優先考慮 REPACK CONCURRENTLY 或維護窗口（第18章）。
24. Replication Slot 必須定期稽核，避免失效 Slot 導致 WAL 累積塞爆磁碟（第21、44章）。
25. 大表的 DDL／批次更新，執行前必須評估鎖表影響與分批策略（第40、44章）。
26. 所有查詢（尤其 AI Agent 產生的查詢）應設定合理的 statement_timeout（第46章）。
27. Extension 安裝與升級必須納入版本控制，不可僅憑口耳相傳的維運知識（第26章）。
28. 生產環境的 ddl-auto（或等效的 ORM 自動建表設定）必須為 validate，Schema 變更一律走 Migration（第34章）。
29. 資料庫容量規劃必須涵蓋 CPU/Memory/Storage/IOPS/Network/Connection 六大維度，不可只看單一指標（第54章）。
30. DR 演練必須實際執行並記錄真實耗時，未經演練驗證的 RPO/RTO 只是紙上目標（第55章）。
31. AI Agent 產生的 Breaking Change／升級知識必須經人工審核來源，避免將幻覺內容當作既定事實（第33章）。
32. 任何跨越 Beta／RC／GA 狀態的功能引用，必須清楚標示版本狀態，不可將未 GA 功能當作既定穩定行為描述（第2章）。

↑ 回目錄

References

↑ 回目錄

附錄：新進成員快速上手 Checklist

🔧 開發前準備

• 已安裝 PostgreSQL 18（Production 基準版本），並確認 psql --version（第6～8章）
• 已理解 Process-per-Connection 架構與 Shared Buffers/WAL 基本概念（第4、5章）
• 已閱讀第10、11章，理解企業安全模型與 Role 設計原則
• 已知道公司 Coding Standard 的命名規範（第59章）

📝 日常開發規範

• 所有 UPDATE/DELETE 先用對應條件 SELECT 驗證影響範圍（第12章）
• 金額欄位使用 numeric、時間欄位優先使用 timestamptz（第13章）
• Schema 變更一律透過 Flyway/Liquibase Migration，不手動改 Production（第36章）
• 資料庫密碼／連線字串不寫入版本控制（第10、38章）
• 撰寫查詢前先確認是否有可用索引，必要時用 EXPLAIN ANALYZE 驗證（第15、16章）

🔍 提交前自我檢查

• 多步驟寫入是否已包在明確交易範圍內（第17章）
• 是否有 SELECT *，能否改為明確欄位（第58章）
• 是否有遺漏的 FK／CHECK 約束（第14章）
• 大表操作是否評估過鎖表影響（第40、44章）

🤖 涉及 AI Agent 的開發

• Agent 使用的資料庫連線是否為 Read-only（除非明確需要寫入）（第46章）
• 寫入類操作是否已納入核准流程（第46、48章）
• 查詢是否設定 statement_timeout 與合理 Row Limit（第46、47章）
• RAG／向量檢索是否已套用租戶與權限過濾（第28、29章）
• 是否已記錄 Audit（誰、何時、做了什麼）（第48章）

🛡️ 上線前確認

• 已對照第42章 Production Checklist 逐項確認
• 已完成至少一次備份 Restore Test（第20章）
• 監控告警已設定且有明確 On-Call 對應（第23章）
• 已知悉本手冊第69章 Golden Rules，並確認變更不違反任一條

本手冊為企業內部教育訓練教材，內容基於 PostgreSQL 官方文件與公開技術資料整理而成，撰寫過程中已標明版本狀態（Stable／Beta）與資訊查證基準日。PostgreSQL 19 於本手冊撰寫時尚未 GA，任何 Production 導入決策請以當時最新的官方 Release Notes 為準。

↑ 回目錄