PostgreSQL 版本 Schema 效能比較
需求
- 開發者持續發布新軟體，版本格式是 {major}.{minor}.{micro}.{build}
- 當裝置詢問升級資訊時，我們需要找出最新的軟體版本
- 但不只是最新的版本，我們可能還需要知道以下版本資訊，才能提供適合的建議
- 是否有 3 個更新的軟體版本？
- 有多少更新的軟體版本？
挑戰
- 資料庫用「自然排序」來排文字
- 這兩個版本，如果用自然排序的話，舊版本會被當成新版本
- 1.2.3.100 => 會被當成比較舊的，因為 2 > 1
- 1.2.3.20 => 所以版本 20 反而會被當成比較新的
想法
- 把 major、minor、micro、build 分別存在不同的欄位
- 把版本號計算成一個數字，這樣就能正確排序
- 把版本號合併成一個文字，而且要能正確排序（需要 padLeft 0 讓所有版本變成相同長度）
- 把 major、minor、micro、build 存在 byte array (BLOB) 裡，假設自然排序會從陣列的第一個元素開始比較 BLOB。
測試步驟
- 準備 TestContainer 做 PostgreSQL 測試
- 產生 10000 筆資料
- major 0
- minor 0-9
- micro 0-9
- build 0-99
- 跟指定版本比較：0.6.7.58
- 比較查詢計劃和查詢結果（查詢最新的 3 筆資料）
測試程式碼
資料表
Version1
DDL
CREATE TABLE version1 (
id uuid PRIMARY KEY,
major int,
minor int,
micro int,
build int
);
CREATE INDEX version1_major_idx ON version1 (major DESC);
CREATE INDEX version1_major_minor_idx ON version1 (major DESC, minor DESC);
CREATE INDEX version1_major_minor_micro_idx ON version1 (major DESC, minor DESC, micro DESC);
CREATE INDEX version1_major_minor_micro_build_idx ON version1 (major DESC, minor DESC, micro DESC, build DESC);
SQL
String sql = "SELECT * FROM Version1 " +
"WHERE (major > 0) " +
"OR (major = 0 AND minor > 6) " +
"OR (major = 0 AND minor = 6 AND micro > 7) " +
"OR (major = 0 AND minor = 6 AND micro = 7 AND build > 58) " +
"ORDER BY major DESC, minor DESC, micro DESC, build DESC " +
"LIMIT 3";
查詢計劃
QUERY PLAN: Limit (cost=0.29..1.16 rows=3 width=32) (actual time=0.065..0.193 rows=3 loops=1)
QUERY PLAN: -> Index Scan using version1_major_minor_micro_build_idx on version1 (cost=0.29..982.84 rows=3366 width=32) (actual time=0.051..0.073 rows=3 loops=1)
QUERY PLAN: Filter: ((major > 0) OR ((major = 0) AND (minor > 6)) OR ((major = 0) AND (minor = 6) AND (micro > 7)) OR ((major = 0) AND (minor = 6) AND (micro = 7) AND (build > 58)))
QUERY PLAN: Planning Time: 0.920 ms
QUERY PLAN: Execution Time: 0.307 ms
QUERY RESULT: {major=0, minor=9, micro=9, build=99, id=7e5878ff-9d25-46dc-a0c0-79196fd8c5d3}
QUERY RESULT: {major=0, minor=9, micro=9, build=98, id=ee2097ef-fe86-491e-869b-afda5976a354}
QUERY RESULT: {major=0, minor=9, micro=9, build=97, id=ada64c48-72e2-4a39-a6b0-5eabda135d0d}
Version2
DDL
CREATE TABLE version2 (
id uuid PRIMARY KEY,
build int
);
CREATE INDEX version2_order_idx ON version2 (build DESC);
SQL
String sql = "SELECT * FROM Version2 " +
"WHERE build > " + getVersion2Number(0,6,7,58) +
" ORDER BY build DESC" +
" LIMIT 3";
查詢計劃
QUERY PLAN: Limit (cost=0.29..0.61 rows=3 width=20) (actual time=0.053..0.124 rows=3 loops=1)
QUERY PLAN: -> Index Scan using version2_order_idx on version2 (cost=0.29..391.76 rows=3627 width=20) (actual time=0.037..0.060 rows=3 loops=1)
QUERY PLAN: Index Cond: (build > 6758)
QUERY PLAN: Planning Time: 0.388 ms
QUERY PLAN: Execution Time: 0.195 ms
QUERY RESULT: {build=9999, id=e32b8067-9cae-4c49-b776-372e8a2137e4}
QUERY RESULT: {build=9998, id=c2902963-6579-48d3-bb91-deac6a8528bd}
QUERY RESULT: {build=9997, id=de28843c-b245-4b46-8e2d-7e549f0b862e}
Version3
DDL
CREATE TABLE version3 (
id uuid PRIMARY KEY,
build bytea
);
CREATE INDEX version3_order_idx ON version3 (build DESC);
SQL
String sql = "SELECT * FROM Version3 " +
"WHERE build > ?" +
" ORDER BY build DESC" +
" LIMIT 3";
查詢計劃
QUERY PLAN: Limit (cost=0.28..0.77 rows=3 width=48) (actual time=0.063..0.130 rows=3 loops=1)
QUERY PLAN: -> Index Scan using version3_order_idx on version3 (cost=0.28..368.24 rows=2283 width=48) (actual time=0.047..0.068 rows=3 loops=1)
QUERY PLAN: Index Cond: (build > '\x000506073a'::bytea)
QUERY PLAN: Planning Time: 0.329 ms
QUERY PLAN: Execution Time: 0.214 ms
[0, 9, 9, 99]
[0, 9, 9, 98]
[0, 9, 9, 97]
Version4
DDL
CREATE TABLE version4 (
id uuid PRIMARY KEY,
build VARCHAR
);
CREATE INDEX version4_build_idx ON version4 (build DESC);
SQL
String sql = "SELECT * FROM Version4 " +
"WHERE build > '" + getVersion4Text(0, 6, 7, 58) + "'" +
" ORDER BY build DESC" +
" LIMIT 3";
查詢計劃
QUERY PLAN: Limit (cost=0.29..0.80 rows=3 width=48) (actual time=0.074..0.137 rows=3 loops=1)
QUERY PLAN: -> Index Scan using version4_build_idx on version4 (cost=0.29..512.72 rows=2996 width=48) (actual time=0.060..0.079 rows=3 loops=1)
QUERY PLAN: Index Cond: ((build)::text > '0000000600070058'::text)
QUERY PLAN: Planning Time: 0.313 ms
QUERY PLAN: Execution Time: 0.256 ms
QUERY RESULT: {build=0000000900090099, id=29f9f1a0-40eb-4a31-873f-ff04868fa3d1}
QUERY RESULT: {build=0000000900090098, id=1ead5f4d-73d9-4cda-b899-f745b90f8598}
QUERY RESULT: {build=0000000900090097, id=bc9a4dfe-12c9-4c34-8925-836e8c4a7ad3}
比較
| 方案 | 成本 | 優缺點 |
| Version1（分開欄位） | (cost=0.29..982.84 rows=3366 width=32) | 優點：彈性高，可以輕鬆調整 SQL 缺點：慢 |
Version2 （計算成數字） | (cost=0.29..391.76 rows=3627 width=20) | 優點：快 缺點：
|
Version3 （計算成字串） | (cost=0.28..368.24 rows=2283 width=48) | 優點：快，而且沒有最大值的問題 缺點：存入前需要先計算，如果比較邏輯改變可能需要做資料遷移 |
Version4 （計算成 byte array） | (cost=0.29..512.72 rows=2996 width=48) | 優點：不需要額外計算 缺點：
|