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