PostgreSQL數(shù)據(jù)類型怎么選？
在數(shù)據(jù)庫設(shè)計時，表字段的類型選擇至關(guān)重要。它不僅影響數(shù)據(jù)庫的存儲效率和性能優(yōu)化，還影響數(shù)據(jù)的完整性和擴(kuò)展性。
本文分析了不同應(yīng)用場景下可以使用的 PostgreSQL 數(shù)據(jù)類型，以及它們的存儲需求和優(yōu)缺點(diǎn)。
布爾值
數(shù)據(jù)類型 | BOOLEAN（推薦） | SMALLINT | BIT（不推薦） | CHAR(1) |
優(yōu)點(diǎn) | 存儲需求最?。? 字節(jié)）；語義清晰；支持輸入：true, yes, on, 1, false, no, off, 0。 | 支持算術(shù)運(yùn)算；可以存儲真或假之外的數(shù)值。 | 無 | 可以存儲任何字符，輸入靈活。 |
缺點(diǎn) | 只能存儲真或者假。 | 語義不夠清晰；需要通過約束實(shí)現(xiàn)布爾邏輯；需要更多存儲（2 字節(jié)）。 | 需要更多存儲（6 字節(jié)）；只能存儲 1/0；語義不夠清晰；不易查詢。 | 需要通過約束實(shí)現(xiàn)布爾邏輯，需要更多存儲（2 字節(jié)）。 |
百萬行存儲空間 | 1 MB | 2 MB | 6 MB | 2 MB |
UUID
數(shù)據(jù)類型 | UUID（推薦） | TEXT | CHAR(36) |
優(yōu)點(diǎn) | 存儲優(yōu)化（16 字節(jié)）；校驗(yàn) UUID 格式；索引優(yōu)化。 | 實(shí)現(xiàn)簡單，格式靈活。 | 固定長度。 |
缺點(diǎn) | 無 | 更多存儲（36 字節(jié) + 1 字節(jié)）；索引低效；需要通過約束驗(yàn)證格式。 | 更多存儲（36 字節(jié) + 1 字節(jié)）；索引低效；需要通過約束驗(yàn)證格式。 |
百萬行存儲空間 | 16 MB | 37 MB | 37 MB |
備注：如果需要使用 UUID 作為主鍵，推薦使用 UUIDv7 這種基于時間排序的算法。隨機(jī)生成的 UUID 可能導(dǎo)致 B-樹分裂問題。
MD5
數(shù)據(jù)類型 | UUID（推薦） | BYTEA | TEXT | CHAR(32) |
優(yōu)點(diǎn) | 存儲優(yōu)化（16 字節(jié)）；索引優(yōu)化。 | 存儲優(yōu)化（16 字節(jié) + 4 字節(jié)）；索引優(yōu)化。 | 實(shí)現(xiàn)簡單，格式靈活。 | 固定長度。 |
缺點(diǎn) | MD5 顯示成 UUID 格式。 | 二進(jìn)制數(shù)據(jù)不方便處理。 | 更多存儲（32 字節(jié) + 4 字節(jié)）；索引低效。 | 更多存儲（32 字節(jié) + 4 字節(jié)）；索引低效。 |
百萬行存儲空間 | 16 MB | 20 MB | 36 MB | 36 MB |
MD5 轉(zhuǎn)換成二進(jìn)制類型的方法如下：
SELECT DECODE(MD5('abc'), 'hex');日期時間
數(shù)據(jù)類型 | DATE（推薦） | TIME | TIMESTAMP | TIMESTAMPTZ（推薦） |
優(yōu)點(diǎn) | 高效存儲日期；語義清晰 | 存儲于日前無關(guān)的時間信息 | 同時存儲日期和時間 | 存儲包含時區(qū)的日期和時間，支持全球化；兼容夏令時 |
缺點(diǎn) | 不包含時間信息 | 不包含日期信息 | 沒有時區(qū)信息 | 無 |
百萬行存儲空間 | 4 MB | 8 MB | 8 MB | 8 MB |
二進(jìn)制數(shù)據(jù)
數(shù)據(jù)類型 | BYTEA（推薦） | TEXT |
優(yōu)點(diǎn) | 高效的二進(jìn)制存儲和處理 | 應(yīng)用程序處理簡單 |
缺點(diǎn) | 應(yīng)用程序需要編碼/解碼 | 存儲效率低 |
百萬行存儲空間 | 2 MB - 2.1 PB | 1 MB - 1.1 PB |
備注：對于文檔、圖像、音頻、視頻等二進(jìn)制數(shù)據(jù)，不建議使用數(shù)據(jù)庫存儲，可以使用文件系統(tǒng)存儲這些內(nèi)容，并且在數(shù)據(jù)庫中記錄文件的訪問地址。
貨幣數(shù)字
數(shù)據(jù)類型 | MONEY（不推薦） | NUMERIC(15,2)（推薦） | BIGINT（推薦） | FLOAT（不推薦） |
優(yōu)點(diǎn) | 內(nèi)置貨幣符號；方便存儲財務(wù)數(shù)據(jù)；高效的存儲和處理 | 高精度存儲；可以靈活設(shè)置存儲精度 | 可以將小數(shù)存儲為整數(shù)；計算性能好 | 計算性能最好 |
缺點(diǎn) | 只能支持一種貨幣符號；只能支持小數(shù)點(diǎn)后兩位 | 占用更多存儲；計算性能不如浮點(diǎn)數(shù) | 應(yīng)用程序需要進(jìn)行小數(shù)轉(zhuǎn)換，例如乘以 100 將小數(shù)點(diǎn)后的分轉(zhuǎn)換為整數(shù) | 非精確數(shù)字；計算時存在精度損失 |
百萬行存儲空間 | 8 MB | 11 MB | 8 MB | 4 MB/8 MB |
枚舉值
數(shù)據(jù)類型 | ENUM | TEXT | SMALLINT（推薦） | SMALLINT查找表（推薦） |
優(yōu)點(diǎn) | 提供數(shù)據(jù)庫級別校驗(yàn)；存儲高效；可讀性高 | 使用靈活，方便增加新的枚舉值 | 存儲高效；方便增加新的枚舉值 | 存儲高效；方便增加新的枚舉值；通過查找表可以獲取更多信息 |
缺點(diǎn) | 刪除某個枚舉時比較復(fù)雜；對于動態(tài)枚舉值不夠靈活 | 沒有數(shù)據(jù)校驗(yàn)，可能導(dǎo)致數(shù)據(jù)不一致；可能占用更多存儲 | 沒有數(shù)據(jù)校驗(yàn)；含義不明確，應(yīng)用程序需要解釋數(shù)據(jù)含義 | 需要關(guān)聯(lián)查詢獲取枚舉值含義，增加了復(fù)雜度 |
百萬行存儲空間 | 4 MB | >=2 MB | 2 MB | 2 MB |
文本
數(shù)據(jù)類型 | TEXT（推薦） | VARCHAR(N) | CHAR(N) （不推薦） |
優(yōu)點(diǎn) | 幾乎沒有長度限制；靈活易用 | 限制了最大長度 | 固定長度，占用固定大小 |
缺點(diǎn) | 需要通過檢查約束限制長度 | 需要提前定義最大長度；超長時出現(xiàn)錯誤 | 使用空格填充，可能浪費(fèi)空間；不適用于變長字符串 |
百萬行存儲空間 | 2 MB - 1.1 PB | 2 MB - 1.1 PB | 2 MB - 1.1 PB |
PostgreSQL 數(shù)據(jù)庫中這三種字符串類型沒有明顯的性能差異，只是 CHAR 類型可能浪費(fèi)一些存儲空間，而指定字段的最大長度則需要消耗一些 CPU 執(zhí)行長度校驗(yàn)。
數(shù)字
數(shù)據(jù)類型 | NUMERIC | FLOAT |
優(yōu)點(diǎn) | 超大范圍精確數(shù)字；適合金融財務(wù)數(shù)據(jù) | 占用更少存儲；計算速度更快，適合科學(xué)計算 |
缺點(diǎn) | 占用更多存儲；計算更慢 | 不夠精確，可能存在舍入誤差 |
百萬行存儲空間 | 5 MB - 1 TB | 4 MB/8 MB |
整數(shù)
數(shù)據(jù)類型 | SMALLINT | INTEGER | BIGINT |
優(yōu)點(diǎn) | 占用空間最少 | 占用空間較少，支持較大范圍數(shù)字 | 支持超大范圍數(shù)字 |
缺點(diǎn) | 支持的數(shù)字范圍小 | 無 | 占用空間最大 |
百萬行存儲空間 | 2 MB | 4 MB | 8 MB |
SAMLLINT 支持的數(shù)字范圍從 -32768 到 32767，INTEGER 支持的數(shù)字范圍從 -2147483648 到 2147483647，BIGINT 支持的數(shù)字范圍從 -9223372036854775808 到 9223372036854775807。
JSON
數(shù)據(jù)類型 | JSON | JSONB（推薦） |
優(yōu)點(diǎn) | 存儲原始文本，保留空白符、順序、重復(fù)鍵 | 二進(jìn)制存儲，優(yōu)化了查詢性能；支持 GIN 索引 |
缺點(diǎn) | 讀取速度更慢；不支持高效索引 | 寫入時需要更多解析操作；可能需要更多元數(shù)據(jù)存儲 |
百萬行存儲空間 | 2 MB - 1.1 PB | 2 MB - 1.1 PB |
數(shù)組
數(shù)據(jù)類型 | ARRAY | JSONB ARRAY |
優(yōu)點(diǎn) | 單個字段存儲多個值，優(yōu)化特定應(yīng)用 | 靈活存儲多個值；支持索引 |
缺點(diǎn) | 查詢和索引復(fù)雜；可能占用更多存儲 | 需要額外處理 JSON；可能占用更多存儲 |
百萬行存儲空間 | 12 MB - 1.1 PB | 8 MB - 1.1 PB |
