🐘 PostgreSQL Protocol 完整指南

⏱️ 閱讀時間： 12 分鐘 🎯 難度： ⭐⭐ (中等)

🎯 本篇重點

理解 PostgreSQL 前端/後端協定的原理、Simple Query vs Extended Query 的差異、COPY Protocol 批次匯入，以及與 MySQL Protocol 的對比。

🤔 什麼是 PostgreSQL Protocol？

PostgreSQL Protocol = PostgreSQL 前端（客戶端）與後端（伺服器）之間的通訊協定

一句話解釋： PostgreSQL Protocol 定義了客戶端如何與 PostgreSQL Server 建立連線、執行查詢、處理結果的規則，相比 MySQL 提供更細粒度的控制。

比喻：精品餐廳 vs 速食店

MySQL Protocol = 速食店
- 簡單快速
- 套餐模式（Text/Binary Protocol 二選一）

PostgreSQL Protocol = 精品餐廳
- 更多選擇
- 可以單點（Parse、Bind、Execute 分開控制）
- 更靈活的客製化

🏗️ PostgreSQL Protocol 在網路模型中的位置

OSI 7 層模型

┌──────────────────────────────┬──────────────────────┐
│ 7. Application Layer (應用層) │  PostgreSQL Protocol │ ← PostgreSQL 在這裡
├──────────────────────────────┼──────────────────────┤
│ 6. Presentation Layer (表示層)│  加密、壓縮           │
├──────────────────────────────┼──────────────────────┤
│ 5. Session Layer (會話層)     │  建立、維護會話       │
├──────────────────────────────┼──────────────────────┤
│ 4. Transport Layer (傳輸層)   │  TCP                 │
├──────────────────────────────┼──────────────────────┤
│ 3. Network Layer (網路層)     │  IP                  │
├──────────────────────────────┼──────────────────────┤
│ 2. Data Link Layer (資料鏈結層)│  Ethernet            │
├──────────────────────────────┼──────────────────────┤
│ 1. Physical Layer (實體層)    │  網路線、光纖         │
└──────────────────────────────┴──────────────────────┘

PostgreSQL Protocol 位於第 7 層（應用層）

• PostgreSQL Protocol 是應用層協定
• 提供資料庫查詢、資料傳輸服務
• Frontend/Backend 架構的通訊協定

TCP/IP 4 層模型

┌─────────────────────────────┬──────────────────────┐
│ 4. Application Layer (應用層) │  PostgreSQL Protocol │ ← PostgreSQL 在這裡
├─────────────────────────────┼──────────────────────┤
│ 3. Transport Layer (傳輸層)  │  TCP                 │
├─────────────────────────────┼──────────────────────┤
│ 2. Internet Layer (網際網路層)│  IP                  │
├─────────────────────────────┼──────────────────────┤
│ 1. Network Access (網路存取層)│  Ethernet            │
└─────────────────────────────┴──────────────────────┘

PostgreSQL Protocol 位於第 4 層（應用層）

• 在 TCP/IP 模型中，PostgreSQL Protocol 是應用層協定
• 使用 TCP 作為傳輸層協定（Port 5432）
• TCP 提供可靠的連線導向傳輸

對比表：

重點：

• PostgreSQL Protocol 是應用層協定（兩種模型都是）
• 使用 TCP 作為傳輸層（Port 5432）
• TCP 提供可靠傳輸，PostgreSQL Protocol 提供資料庫通訊
• Frontend/Backend 架構（Client/Server）

🏗️ PostgreSQL Protocol 特性

與 MySQL Protocol 對比

PostgreSQL 的優勢：

1. ✅ Extended Query Protocol：更靈活的查詢執行
2. ✅ COPY Protocol：超快速批次匯入
3. ✅ LISTEN/NOTIFY：內建發布訂閱
4. ✅ 豐富的資料類型：陣列、JSON、範圍類型等
5. ✅ 更好的錯誤處理：NOTICE、WARNING、ERROR 分層

📡 PostgreSQL 通訊流程

客戶端                          PostgreSQL Server
  │                                 │
  ├──────── 1. 建立 TCP 連線 ──────>│
  │                                 │
  ├──── 2. Startup Message (啟動) ─>│
  │         (Protocol 3.0, User, Database)
  │                                 │
  │<─── 3. Authentication Request ──┤
  │         (密碼挑戰)
  │                                 │
  ├──── 4. Password Response ──────>│
  │                                 │
  │<──── 5. Authentication OK ───────┤
  │                                 │
  │<──── 6. Backend Parameters ──────┤
  │         (server_version, encoding, etc.)
  │                                 │
  │<─── 7. Ready for Query ('I') ────┤
  │                                 │
  ├───── 8. Query / Extended Query ─>│
  │                                 │
  │<────── 9. Result / Error ────────┤
  │                                 │
  ├──────── 10. Terminate ──────────>│
  │                                 │
  └──────── 11. 關閉 TCP 連線 ───────┘

重點差異（vs MySQL）：

• PostgreSQL：客戶端主動發 Startup Message
• MySQL：伺服器主動發 Server Greeting

🚀 階段 1：Startup（啟動）

Startup Message

客戶端首先發送：

Startup Message 包含：

1. Protocol Version
   - 目前是 3.0
   - 格式：196608 (0x00030000)

2. Parameters（參數）
   - user：使用者名稱（如："postgres"）
   - database：資料庫名稱（如："mydb"）
   - application_name：應用程式名稱
   - client_encoding：客戶端編碼（如："UTF8"）
   - 其他可選參數...

範例：

Protocol: 3.0 (196608)
user: postgres
database: testdb
application_name: psql
client_encoding: UTF8

對比 MySQL：

MySQL：Server 先發 Greeting
PostgreSQL：Client 先發 Startup

🔐 階段 2：Authentication（驗證）

驗證方法

PostgreSQL 支援多種驗證方法：

SCRAM-SHA-256 驗證流程

PostgreSQL 推薦使用 SCRAM（最安全）：

1. Server → Client: AuthenticationSASL
   支援的機制：SCRAM-SHA-256

2. Client → Server: SASLInitialResponse
   client-first-message: "n,,n=user,r=client_nonce"

3. Server → Client: AuthenticationSASLContinue
   server-first-message: "r=client_nonce+server_nonce,s=salt,i=4096"

4. Client 計算：
   SaltedPassword = PBKDF2(password, salt, 4096)
   ClientKey = HMAC(SaltedPassword, "Client Key")
   StoredKey = SHA256(ClientKey)
   ClientSignature = HMAC(StoredKey, AuthMessage)
   ClientProof = ClientKey XOR ClientSignature

5. Client → Server: SASLResponse
   client-final-message: "c=biws,r=nonce,p=ClientProof"

6. Server 驗證 ClientProof

7. Server → Client: AuthenticationSASLFinal
   server-final-message: "v=ServerSignature"

8. Client 驗證 ServerSignature

9. Server → Client: AuthenticationOk

優點：

• ✅ 密碼不會明文傳輸
• ✅ 防止重放攻擊（每次 nonce 不同）
• ✅ 雙向驗證（Client 也驗證 Server）
• ✅ 強加密（SHA-256 + PBKDF2）

對比 MySQL：

MySQL md5：
hash = MD5(password)
challenge = SHA1(seed + hash) XOR hash

PostgreSQL SCRAM-SHA-256：
使用 PBKDF2 + SHA-256 + HMAC
更安全、更複雜

💬 階段 3：Query Execution（查詢執行）

Simple Query Protocol

最簡單的查詢方式（類似 MySQL Text Protocol）：

客戶端發送：
Q: Query Message
"SELECT * FROM users WHERE id = 1"

伺服器回應：
T: RowDescription (欄位定義)
D: DataRow (資料列) × N
C: CommandComplete ("SELECT 1")
Z: ReadyForQuery ('I' = Idle)

範例流程：

Client → Server:
  Q "SELECT id, name FROM users LIMIT 2"

Server → Client:
  T [RowDescription]
    Field 1: "id", type=INT4
    Field 2: "name", type=VARCHAR

  D [DataRow 1]
    Column 1: "1"
    Column 2: "Alice"

  D [DataRow 2]
    Column 1: "2"
    Column 2: "Bob"

  C [CommandComplete]
    "SELECT 2"

  Z [ReadyForQuery]
    'I' (Idle, no transaction)

特性：

• ✅ 簡單
• ✅ 一個訊息完成
• ❌ 所有資料都是文字格式
• ❌ 無法重複使用（無 prepared statement）
• ❌ 有 SQL Injection 風險

Extended Query Protocol ⭐⭐⭐

PostgreSQL 的精華！比 MySQL 更細粒度：

流程分為 3 步驟：

1. Parse（解析）
   - 解析 SQL 語法
   - 建立 prepared statement
   - 可重複使用

2. Bind（綁定）
   - 綁定參數值
   - 指定結果格式（text/binary）
   - 建立 portal（執行入口）

3. Execute（執行）
   - 執行 portal
   - 可以限制回傳筆數
   - 可以多次執行同一個 portal

完整流程：

Client → Server:

1. P [Parse]
   statement_name: "stmt1"
   query: "SELECT * FROM users WHERE id = $1 AND age > $2"
   parameter_types: [INT4, INT4]

2. B [Bind]
   portal_name: "portal1"
   statement_name: "stmt1"
   parameter_values: [1, 18]
   result_formats: [binary, binary]

3. D [Describe]
   portal_name: "portal1"

4. E [Execute]
   portal_name: "portal1"
   max_rows: 0 (all rows)

5. S [Sync]

Server → Client:

1. [ParseComplete]
2. [BindComplete]
3. T [RowDescription]
   Field 1: "id", type=INT4, format=binary
   Field 2: "name", type=VARCHAR, format=binary
   ...
4. D [DataRow] × N (binary format)
5. C [CommandComplete]
6. Z [ReadyForQuery]

Extended Query 的優勢

1. 細粒度控制

Parse 一次，Bind 多次：

# Parse once
Parse: "SELECT * FROM users WHERE id = $1"

# Bind and Execute multiple times
Bind: $1 = 1 → Execute
Bind: $1 = 2 → Execute
Bind: $1 = 3 → Execute

省去重複 Parse 的開銷！

2. Binary Format

Simple Query：
所有資料都是 text

Extended Query：
可以選擇 binary format
→ 整數、浮點數直接用二進位傳輸
→ 省去字串轉換，效能更好

3. 分批取得結果

Execute with max_rows：

Execute portal1, max_rows=100  → 取得 100 筆
Execute portal1, max_rows=100  → 再取 100 筆
Execute portal1, max_rows=100  → 再取 100 筆

適合處理大量資料，避免記憶體爆滿！

4. Transaction Control

Parse, Bind 不會開始事務
只有 Execute 才會

優點：
可以在事務外準備 statement
減少事務持有時間

Simple Query vs Extended Query

建議：

• 臨時查詢、測試 → Simple Query
• 應用程式、重複查詢 → Extended Query
• 處理用戶輸入 → 必須用 Extended Query

📦 COPY Protocol（批次匯入）

什麼是 COPY？

💡 PostgreSQL 的超級武器

COPY 可以快速匯入/匯出大量資料
比 INSERT 快 10-100 倍！

COPY 流程

匯入資料：

Client → Server:

1. Q [Query]
   "COPY users (id, name, email) FROM STDIN"

Server → Client:

2. G [CopyInResponse]
   format: text
   columns: 3

Client → Server:

3. d [CopyData] × N
   "1\tAlice\talice@example.com\n"
   "2\tBob\tbob@example.com\n"
   "3\tCharlie\tcharlie@example.com\n"

4. c [CopyDone]

Server → Client:

5. C [CommandComplete]
   "COPY 3"

6. Z [ReadyForQuery]

匯出資料：

Client → Server:

1. Q [Query]
   "COPY users TO STDOUT"

Server → Client:

2. H [CopyOutResponse]
   format: text
   columns: 3

3. d [CopyData] × N
   "1\tAlice\talice@example.com\n"
   "2\tBob\tbob@example.com\n"
   ...

4. c [CopyDone]

5. C [CommandComplete]

6. Z [ReadyForQuery]

COPY 效能對比

匯入 100 萬筆資料：

❌ 逐筆 INSERT：
for i in range(1000000):
    cursor.execute("INSERT INTO users VALUES (...)")
→ 耗時：10-30 分鐘

⭐ Batch INSERT（1000 筆一批）：
cursor.executemany("INSERT ...", batch)
→ 耗時：1-3 分鐘

✅ COPY：
cursor.copy_from(file, 'users')
→ 耗時：10-30 秒

COPY 快 10-100 倍！

Python 範例：

import psycopg2
from io import StringIO

conn = psycopg2.connect("dbname=test user=postgres")
cursor = conn.cursor()

# 方法 1：從檔案 COPY
with open('users.csv', 'r') as f:
    cursor.copy_from(f, 'users', sep=',', columns=('id', 'name', 'email'))

# 方法 2：從記憶體 COPY
data = StringIO()
data.write("1\tAlice\talice@example.com\n")
data.write("2\tBob\tbob@example.com\n")
data.seek(0)
cursor.copy_from(data, 'users', sep='\t')

conn.commit()

為什麼 COPY 這麼快？

1. ✅ 批次處理（減少網路往返）
2. ✅ 繞過 SQL Parser（直接寫入）
3. ✅ 最小化 WAL（Write-Ahead Log）
4. ✅ 減少索引更新（批次更新）

🔔 LISTEN/NOTIFY（發布訂閱）

PostgreSQL 的獨特功能

LISTEN/NOTIFY 提供輕量級的發布訂閱機制

範例：

-- Session 1: 訂閱者
LISTEN my_channel;

-- Session 2: 發布者
NOTIFY my_channel, 'Hello, World!';

-- Session 1 收到通知：
Asynchronous notification "my_channel" with payload "Hello, World!" received.

協定流程：

Client → Server:
  Q "LISTEN my_channel"

Server → Client:
  C [CommandComplete] "LISTEN"
  Z [ReadyForQuery]

（稍後，有人發 NOTIFY）

Server → Client:
  A [NotificationResponse]
    pid: 12345
    channel: "my_channel"
    payload: "Hello, World!"

應用場景：

• 📢 即時通知（如：新訂單、新訊息）
• 🔄 快取失效通知
• 📊 即時儀表板更新
• 🎮 多人遊戲狀態同步

Python 範例：

import select
import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()

# 訂閱頻道
cursor.execute("LISTEN my_channel;")

print("Waiting for notifications...")
while True:
    if select.select([conn], [], [], 5) == ([], [], []):
        print("Timeout, still waiting...")
    else:
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            print(f"Got NOTIFY: {notify.pid}, {notify.channel}, {notify.payload}")

🎓 面試常見問題

Q1：PostgreSQL Protocol 和 MySQL Protocol 有什麼差異？

A：都是二進位協定，但設計哲學不同

相同點：
✅ 都是二進位協定
✅ 都支援參數化查詢（防 SQL Injection）
✅ 都支援連線池

主要差異：

1. 啟動流程
   MySQL：Server 主動發 Greeting
   PostgreSQL：Client 主動發 Startup

2. 查詢方式
   MySQL：
   - Text Protocol（COM_QUERY）
   - Binary Protocol（Prepared Statements）

   PostgreSQL：
   - Simple Query（類似 MySQL Text）
   - Extended Query（Parse/Bind/Execute 分離）⭐

3. 細粒度控制
   MySQL Prepared Statements：
   PREPARE → EXECUTE（2 步驟）

   PostgreSQL Extended Query：
   Parse → Bind → Execute（3 步驟）
   可以 Parse 一次，Bind 多次，效能更好

4. 批次匯入
   MySQL：LOAD DATA INFILE
   PostgreSQL：COPY Protocol（快 10-100 倍）⭐

5. 發布訂閱
   MySQL：❌ 無內建支援
   PostgreSQL：LISTEN/NOTIFY ✅

6. 錯誤處理
   MySQL：ERROR or OK
   PostgreSQL：ERROR, NOTICE, WARNING（分層更細）

7. 資料類型
   MySQL：標準類型
   PostgreSQL：陣列、JSON、自訂類型、範圍類型等

總結：
PostgreSQL Protocol 更靈活、功能更豐富
MySQL Protocol 更簡單、易用

Q2：什麼是 Extended Query Protocol？為什麼它很重要？

A：PostgreSQL 的精華功能

Extended Query Protocol 將查詢執行分成 3 步驟：
1. Parse（解析 SQL）
2. Bind（綁定參數）
3. Execute（執行查詢）

優勢：

1️⃣ Parse 一次，Bind 多次
範例：
Parse: "SELECT * FROM users WHERE id = $1"

Bind: $1 = 1 → Execute
Bind: $1 = 2 → Execute
Bind: $1 = 3 → Execute

省去重複 Parse 的開銷！

2️⃣ Binary Format
可以選擇 binary 格式傳輸資料
→ 整數、浮點數直接用二進位
→ 省去字串轉換

效能提升：
- 整數 1000：text=4 bytes, binary=4 bytes
- float 99.99：text=5 bytes, binary=4 bytes
- 大量資料時節省顯著

3️⃣ 分批取得結果
Execute with max_rows：
Execute portal1, max_rows=100  ← 取 100 筆
Execute portal1, max_rows=100  ← 再取 100 筆

避免一次載入百萬筆資料，記憶體爆滿！

4️⃣ 防止 SQL Injection
參數化查詢：
Parse: "SELECT * FROM users WHERE id = $1"
Bind: $1 = "1 OR 1=1"

Server 會把 "1 OR 1=1" 當成字串值
不會當成 SQL 執行 ✅

5️⃣ 更好的事務控制
Parse, Bind 不會開始事務
只有 Execute 才會
→ 減少事務持有時間

對比 MySQL Prepared Statements：
MySQL：PREPARE → EXECUTE（2 步驟）
PostgreSQL：Parse → Bind → Execute（3 步驟）

PostgreSQL 更靈活：
- 可以先 Parse 多個 statement
- 再依需要 Bind 和 Execute
- 可以重複使用 parsed statement

結論：
Extended Query Protocol 是 PostgreSQL 的核心優勢
提供更細粒度的控制和更好的效能

Q3：COPY Protocol 為什麼比 INSERT 快這麼多？

A：COPY 繞過多個開銷

效能對比（100 萬筆資料）：
❌ 逐筆 INSERT：10-30 分鐘
⭐ Batch INSERT：1-3 分鐘
✅ COPY：10-30 秒

COPY 快 10-100 倍的原因：

1️⃣ 批次處理
INSERT：
每筆資料一個 SQL → 100 萬次網路往返

COPY：
資料流式傳輸 → 只需幾次網路往返

2️⃣ 繞過 SQL Parser
INSERT：
每個 INSERT 都需要 parse SQL

COPY：
直接解析 CSV/TSV 格式
不需要 SQL parsing 開銷

3️⃣ 最小化 WAL
INSERT：
每筆 INSERT 都寫 WAL（Write-Ahead Log）

COPY：
批次寫 WAL，減少 I/O

4️⃣ 批次更新索引
INSERT：
每筆 INSERT 都更新索引

COPY：
先匯入資料，再批次更新索引
減少索引碎片，效率更高

5️⃣ 減少鎖競爭
INSERT：
每筆獲取鎖、釋放鎖

COPY：
獲取鎖一次，批次寫入

實際測試：
# 測試環境：PostgreSQL 14, 8 核 CPU
# 資料：100 萬筆 users (id, name, email)

方法 1：逐筆 INSERT
for i in range(1000000):
    cursor.execute("INSERT INTO users VALUES (%s, %s, %s)", (...))
→ 耗時：25 分鐘

方法 2：executemany（批次 1000）
cursor.executemany("INSERT INTO users VALUES (%s, %s, %s)", batch)
→ 耗時：2 分鐘

方法 3：COPY
cursor.copy_from(file, 'users')
→ 耗時：15 秒

COPY 快 100 倍！

最佳實踐：
✅ 大量資料匯入 → 必用 COPY
✅ 暫時停用索引 → 更快
✅ 增加 maintenance_work_mem → 索引建立更快
✅ 關閉 autovacuum → 匯入期間不要清理

範例：
-- 停用索引
ALTER TABLE users DISABLE TRIGGER ALL;
DROP INDEX users_email_idx;

-- COPY 匯入
COPY users FROM '/tmp/users.csv' CSV;

-- 重建索引
CREATE INDEX CONCURRENTLY users_email_idx ON users(email);
ALTER TABLE users ENABLE TRIGGER ALL;

-- 分析表格
ANALYZE users;

Q4：PostgreSQL 的 LISTEN/NOTIFY 如何實作？有什麼應用場景？

A：輕量級發布訂閱機制

原理：
1. Client 執行 LISTEN channel_name
2. Server 記錄該連線訂閱了 channel_name
3. 其他 Client 執行 NOTIFY channel_name, 'payload'
4. Server 找出所有訂閱 channel_name 的連線
5. 發送 NotificationResponse 給這些連線

特性：
✅ 輕量級（記憶體中的佇列）
✅ 即時（幾毫秒延遲）
✅ 跨 Session（不同連線可以通訊）
❌ 不可靠（連線斷開會遺失通知）
❌ 無法追溯（只能收到連線後的通知）

協定流程：
# 訂閱者
Client → Server: Q "LISTEN orders"
Server → Client: C "LISTEN", Z 'I'

# 發布者
Client → Server: Q "NOTIFY orders, 'order_id=123'"
Server → Client: C "NOTIFY", Z 'I'

# 訂閱者收到
Server → Client: A [NotificationResponse]
  pid: 54321
  channel: "orders"
  payload: "order_id=123"

應用場景：

1️⃣ 即時通知
-- 新訂單通知
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('new_order',
    json_build_object('order_id', NEW.id, 'user_id', NEW.user_id)::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_notify_trigger
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();

2️⃣ 快取失效
-- 資料更新時，通知應用程式清除快取
CREATE TRIGGER user_cache_invalidate
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION pg_notify('cache_invalidate',
  'table=users,id=' || NEW.id
);

3️⃣ 即時儀表板
-- 訂單狀態變更，即時更新儀表板
NOTIFY dashboard_update, 'new_order_count=10';

4️⃣ 微服務通訊
-- 訂單服務通知庫存服務
NOTIFY inventory_service, 'action=reduce_stock,product_id=123,qty=5';

Python 範例：
import select
import psycopg2
import json

# 監聽連線
listener_conn = psycopg2.connect("dbname=test")
listener_conn.set_isolation_level(0)  # autocommit
listener_cursor = listener_conn.cursor()
listener_cursor.execute("LISTEN new_order;")

# 發布連線
publisher_conn = psycopg2.connect("dbname=test")
publisher_cursor = publisher_conn.cursor()

# 模擬新訂單
publisher_cursor.execute("""
  NOTIFY new_order, '{"order_id": 123, "user_id": 456}'
""")
publisher_conn.commit()

# 監聽通知
while True:
    if select.select([listener_conn], [], [], 5) != ([], [], []):
        listener_conn.poll()
        while listener_conn.notifies:
            notify = listener_conn.notifies.pop(0)
            payload = json.loads(notify.payload)
            print(f"New order: {payload}")
            # 處理訂單...

限制：
❌ 不持久（連線斷開會遺失）
❌ payload 最大 8000 bytes
❌ 無法保證順序（多個 NOTIFY）
❌ 無法追溯歷史通知

適合：
✅ 即時通知（可以容忍偶爾遺失）
✅ 快取失效通知
✅ 簡單的發布訂閱

不適合：
❌ 關鍵業務邏輯（需要可靠訊息佇列）
❌ 大量訊息（應該用 RabbitMQ、Kafka）
❌ 需要持久化（應該用 message queue）

Q5：Simple Query 和 Extended Query 該如何選擇？

A：根據場景選擇

Simple Query：
特性：
- 一個訊息完成
- 所有資料都是文字格式
- 無法重複使用
- 有 SQL Injection 風險

適用場景：
✅ 臨時查詢、測試
✅ psql 命令列工具
✅ 單次執行的簡單 SQL
✅ 不涉及用戶輸入

範例：
-- 管理任務
psql> SELECT version();
psql> SHOW all;

-- 一次性查詢
SELECT * FROM pg_stat_activity;

Extended Query：
特性：
- 3 個訊息（Parse/Bind/Execute）
- 可以選擇 binary 格式
- 可以重複使用
- 參數化，防 SQL Injection

適用場景：
✅ 應用程式開發（必用！）
✅ 處理用戶輸入（必用！）
✅ 重複執行的查詢
✅ 需要 binary 格式（效能）
✅ 大量資料（分批取得）

範例：
-- 用戶登入（防 SQL Injection）
Parse: "SELECT * FROM users WHERE username = $1 AND password = $2"
Bind: $1 = user_input1, $2 = user_input2
Execute

-- 重複查詢（Parse 一次）
Parse: "SELECT * FROM products WHERE category_id = $1"
Bind: $1 = 1 → Execute
Bind: $1 = 2 → Execute
Bind: $1 = 3 → Execute

-- 分批取得大量資料
Parse: "SELECT * FROM logs WHERE date > $1"
Bind: $1 = '2025-01-01'
Execute max_rows=1000  ← 取 1000 筆
Execute max_rows=1000  ← 再取 1000 筆
...

決策樹：

是否處理用戶輸入？
├─ 是 → Extended Query（必須！）
└─ 否
    └─ 是否重複執行？
        ├─ 是 → Extended Query（效能更好）
        └─ 否
            └─ 是否需要 binary 格式？
                ├─ 是 → Extended Query
                └─ 否 → Simple Query（簡單場景）

Python 範例對比：

# ❌ Simple Query（危險！）
username = request.form['username']  # 用戶輸入
sql = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(sql)
# SQL Injection 風險！

# ✅ Extended Query（安全）
username = request.form['username']
cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
# psycopg2 會自動使用 Extended Query
# 參數化，安全！

最佳實踐：
1. 應用程式 → 永遠使用 Extended Query
2. 用戶輸入 → 必須參數化
3. 重複查詢 → 使用 prepared statements
4. 臨時測試 → Simple Query 可以接受

💡 實戰建議

1. 使用 Wireshark 觀察 PostgreSQL Protocol

1. 啟動 Wireshark
2. 篩選：tcp.port == 5432
3. 執行 PostgreSQL 查詢
4. 查看封包內容

觀察重點：
- Startup Message
- Authentication (SCRAM-SHA-256)
- Extended Query (Parse/Bind/Execute)
- COPY Protocol

2. psycopg2 使用 Extended Query

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cursor = conn.cursor()

# psycopg2 自動使用 Extended Query
cursor.execute(
    "SELECT * FROM users WHERE id = %s AND age > %s",
    (1, 18)
)

# 查看實際發送的訊息（除錯模式）
import logging
logging.basicConfig(level=logging.DEBUG)
# psycopg2 會記錄 Parse, Bind, Execute

3. 批次匯入最佳實踐

import psycopg2
from io import StringIO
import csv

conn = psycopg2.connect("dbname=test")
cursor = conn.cursor()

# 方法 1：從 CSV 檔案
with open('users.csv', 'r') as f:
    cursor.copy_expert(
        "COPY users (id, name, email) FROM STDIN WITH CSV",
        f
    )

# 方法 2：從資料產生 CSV
data = StringIO()
writer = csv.writer(data, delimiter='\t')
for i in range(100000):
    writer.writerow([i, f'User{i}', f'user{i}@example.com'])
data.seek(0)
cursor.copy_from(data, 'users', sep='\t', columns=('id', 'name', 'email'))

conn.commit()

# 效能優化
# 1. 暫時停用索引
cursor.execute("DROP INDEX IF EXISTS users_email_idx")

# 2. 匯入資料
cursor.copy_from(data, 'users')

# 3. 重建索引
cursor.execute("CREATE INDEX CONCURRENTLY users_email_idx ON users(email)")

# 4. 分析表格
cursor.execute("ANALYZE users")

✅ 重點回顧

PostgreSQL Protocol 特性：

• 二進位協定（與 MySQL 相同）
• Client 主動發 Startup（與 MySQL 相反）
• SCRAM-SHA-256 驗證（比 MySQL md5 更安全）

兩種查詢方式：

• Simple Query：簡單、一個訊息、文字格式
• Extended Query：Parse/Bind/Execute 分離、binary 格式、可重複使用 ⭐

Extended Query 優勢：

1. Parse 一次，Bind 多次
2. Binary format（效能更好）
3. 分批取得結果（max_rows）
4. 防 SQL Injection
5. 更好的事務控制

COPY Protocol：

• 批次匯入/匯出
• 比 INSERT 快 10-100 倍
• 適合大量資料處理

LISTEN/NOTIFY：

• 輕量級發布訂閱
• 即時通知
• 適合簡單場景

面試重點：

• ✅ PostgreSQL vs MySQL Protocol 差異
• ✅ Extended Query Protocol 原理
• ✅ COPY 為什麼這麼快
• ✅ LISTEN/NOTIFY 應用場景
• ✅ Simple vs Extended Query 選擇

記憶口訣：

• 「解、綁、行」= Parse, Bind, Execute

上一篇： 05-1. MySQL Protocol 下一篇： 05-3. Redis Protocol (RESP)

相關文章：

• 05-3. Redis Protocol (RESP)
• 02-1. HTTP 基礎概念

最後更新：2025-01-15