PostgreSQL 使用 Extended Query Protocol 避免頻寬與效能浪費

你的 lib 能啟用 Binary Representation 傳遞參數與結果嗎？

此文章也有於 Golang Taipei Gathering #50 x Dcard 分享的版本，其中差異在於改用 wireshark 展示並補充 Connection Pool 細節：
https://speakerdeck.com/rueian/golang-postgresql-libraries-comparison-with-wireshark

序

SQL 提供的抽象化讓我們可以很輕鬆的撰寫聲明式的語法，讓資料庫用它認為比較好的方式去幫我們撈資料，這也是 SQL 資料庫熱門的原因之一。

這層抽象偶爾也會帶來一些隱藏的小坑，多半與當下 Query Planner 的實現有關係，例如我前兩篇文章：

PostgreSQL 如何估算 HashAggregate 的 Return Rows ，以及低估的後果

PostgreSQL 如何估算 LIKE 的 return rows 數量

除此之外，你有沒有注意到某些情景之下 SQL 本身的 Text Representation 也會造成一些無法忽視的頻寬與效能的浪費呢？

案例

若我們用 PostgreSQL 做一個通用的 kv 儲存…

至於為什麼選用 PostgreSQL，只是因為 kv 儲存比較容易拿來說明。而在 Production 上選用 PostgreSQL 做 kv 也可以有很多理由，例如：

1. 有 Atomic 跟處理 Race Condition 需求，用 PostgreSQL 比較容易
2. 有 Range Query 需求，用 PostgreSQL 比較容易
3. 搭配其他既有的基礎建設，比方說定期快照、Change Data Capture…

要做一個 kv 儲存，我們可以開一張這樣的表來實作：

CREATE TABLE kv (
    key   VARCHAR PRIMARY KEY COLLATE "C",
    value BYTEA,
    updated_at TIMESTAMP WITH TIME ZONE
);
-- 選用 COLLATE "C" 可以支援 Prefix Range Query
-- 選用 BYTEA 儲存 value 可以讓使用者自行決定資料格式，甚至不同的壓縮方法

若我們要往裡面插入一筆 key=foo value=bar 資料，可以寫這樣的 SQL：

INSERT INTO kv (key, value, updated_at) VALUES 
('foo', '\x626172', NOW()) RETURNING *;

在這個例子中就可以看到為了在 SQL 上面寫入 bytea，我們把資料先轉成 printable 的格式，例如轉成 Hex String 長度就變成兩倍。

事實上除了 text 型別之外的資料型態用 text 來表示都有可能產生類似的情況，例如常用的 uuid 、 timestamptz。

那這問題我們能怎麼辦？當然不是沒辦法，或許其實大家也都已經不知不覺的在用了： Extended Query Protocol。

PostgreSQL Extended Query Protocol

PostgreSQL 在文件 Message Flow 中提及提供兩種 Query 模式，分別是 Simple Query 以及 Extended Query。

PostgreSQL: Documentation: 12: 52.2. Message Flow

前者是 client 端經由 tcp stream 發送一個 Query 訊息給 PostgreSQL，其中包含一個或多個 SQL 語句，然後 PostgreSQL 會解析每一句 SQL 來執行並用 Text Representation 返回結果集；

後者是則是把 Simple Query 拆解成三個步驟：

1. client 端對 PostgreSQL 發送一個 Parse 訊息，其中包含一個 SQL 語句而其中參數的部分可以使用 $1, $2, ...替代
2. client 端對 PostgreSQL 發送一個 Bind 訊息，將參數綁定到先前透過 Parse 訊息註冊的 SQL 語句上面
3. client 端對 PostgreSQL 發送一個 Exec 訊息，讓 PostgreSQL 去執行先前已經綁定好參數的 SQL 語句

Extended Query Protocol 這個流程也就是大家熟知的 Prepared Statement，在各個資料庫中都有類似的實作，雖然它把原本的流程拆解成多個步驟增加了一個 Parse 訊息的 RTT (一般的實作中傳送完 Bind 訊息會立刻再寫 Exec 訊息，因此雖分成了三個訊息但通常只增加一個 RTT)，但帶來的好處包含：

1. 可以在同一個 DB Session 之中重複使用解析好的 SQL 語句，不用每次都重複解析。也有機會可以重複使用先前的 Query Plan。也就是說同一個 Prepared Statement 的 Parse RTT 在整個 DB Session 只需要一次。
2. 拆解 SQL 語句與參數，避免 SQL Injection。若使用 Simple Query 的話則完全仰賴於 User Code 是否做 Bug Free 的參數 Escaping，否則就有安全疑慮。
3. 參數與回傳結果集可以不必受限於 Text Representation。

這次分享的關鍵就在於 Bind 訊息，從文件中可以看到 client 端可以在 Bind 訊息中指定參數以及回傳欄位的 Format Code。

Note
The choice between text and binary output is determined by the format codes given in Bind, regardless of the SQL command involved.

ref: https://www.postgresql.org/docs/12/protocol-flow.html

而 Bind 訊息的詳細格式可以於 Message Formats 章節找到：

PostgreSQL: Documentation: 12: 52.7. Message Formats

各種資料型別的 Binary Representation 的說明在 Formats and Format Codes 可以找到，不過實際上文件只明定了整數是使用 big-endian，其他的要看原始碼。

那你用的 PostgreSQL Client Library 有支援 Extended Query Protocol 以及需要的 Binary Representation 嗎？

驗證是否使用預想中的 Binary Representation

這種 encoding 的選擇就看各別 Library 怎麼實作，所以在挑選工具的時候還是有必要去驗證一下的。

最通用的驗證方式就是 tcpdump 或 wireshark 看一下實際的封包，或是自己弄個 tcp proxy 攔截一下。如果你像我一樣用 golang，我這邊也有個簡單的 proxy 可以印出 PostgreSQL 的 Query, Parse, Bind, DataRow 訊息內容：

pgproxy.go

這邊就拿幾個 golang 熱門的 PostgreSQL Library 往這張 kv 表插入一行 key=foo, value=10個隨機 bytes 的 row 試試看，分別是：

1. github.com/lib/pq
2. github.com/go-pg/pg/v9
3. github.com/jinzhu/gorm
4. github.com/jackc/pgx/v4

為了簡短篇幅，範例 code 都忽略錯誤處理。

github.com/lib/pq

ligpq.go

透過 proxy 可以看到 lib/pq 在 bytea 的回傳可以使用 Binary Representation，但是在參數是用 Text Representation。而這個例子用 Text Representation 長度則從原本的 10 變成 31 bytes；timestamp 則是從 8 變成 32 bytes。

仔細看文件之後發現，其實是可以透過在 connection string 後面加上 binary_parameters=yes 來為 bytea 參數開啟 Binary Representation：

lib/pq

開啟之後再嘗試一次，可以看 bytea 參數的 len 已經變成 10，但是 timestamp 的參數與回傳依舊使用 Text Representation。

github.com/go-pg/pg

gopg.go

可以看到 github.com/go-pg/pg 無論是參數還是回傳的格式都沒有選擇 Binary Representation。透過追原始碼可以看到問題出在這邊：

go-pg/pg

它直接放棄指定參數與回傳的 Format Code 並且沒有選項可以做調整，只能使用 Text Representation。

github.com/jinzhu/gorm

gorm.go

Gorm 一開始會發一個 Ping Simple Query，其他基本上與 lib/pq 一樣，因為 Gorm 也是用 lib/pq：bytea 參數用 Text ，回傳用 Binary；而 timestamptz 一律用 Text。

但是 Gorm 在開啟 binary_parameters=yes 之後反而變成參數用 Binary，回傳用 Text。

Gorm 另外值得注意的是從上面的 Parse Query: name= 可以發現它是使用 Unnamed Prepared Statement。這種 Prepared Statement 只要在 DB Session 中再碰到下一個 Parse 訊息就會被覆蓋掉，無法重複使用也因此無法減少不必要的 Parse RTT。

github.com/jackc/pgx

pgx.go

從 Proxy 結果可以看到 pgx 使用 Named Prepared Statement，而且對於 bytea 以及 timestamptz 的參數與回傳皆使用 Binary Representation，節省不少體積。

結論

四個熱門的 Golang PostgreSQL Library 在 Extended Query Protocol 上的功能比較

以上四個是 golang 生態系中熱門的 PostgreSQL Library，可以發現這種 encoding 的實作是很容易存在差異，且支援程度相當有限，只有 https://github.com/jackc/pgtype 提供了 bytea 以外的 Binary Representation的支援。

對於 bytea 這種場景且對於頻寬跟效能特別要求的話，最好還是用個 tcpdump/wireshark 或是 proxy 仔細看看是否存在可以改進的空間。像是從上面測試中可以看到若用 lib/pq，一個不小心沒設定 binary_parameters=yes 就會讓傳遞 bytea 的體積變成兩倍或三倍，造成資源浪費。而常用的 timstamptz 用 text 傳遞也是約變成三到四倍體積。

最後用 lib/pq 寫了一個簡易的 benchmark 比較使用 Text Representation 跟 Binary Representation 的效能差異給大家參考。

同場加映 node-postgres

NodeJS 也是我平常有在用的，順便也附上熱門的 https://github.com/brianc/node-postgres 範例：