JSONB 與 BRIN 索引

要求是這樣的：

• 每個使用者都有一個錢包，可以包含數十到一百種左右不同的貨幣，每種貨幣都有一個金額
• 貨幣數量可以增長> 1600（讓我擔心列限制）
• 數以百萬計的使用者群
• 對整個錢包進行大量讀取，對貨幣金額進行少量寫入
• 每種貨幣的所有使用者每晚的總金額

到目前為止，我正在考慮 2 個選項：

• 每個使用者都有一個{"currency":amount}JSONB 欄位，或者
• 一個 500M+ 行的表，其中user_id, currency_id, amount, 聚集並使用 BRIN 索引user_id

我應該走哪條路？感謝您的意見。

假設一個幾乎不可變的集合 ~ 總共 100 種貨幣（您還不清楚），以及您給定的要求，請考慮簡單的方法：1 個表，每個使用者 1 行，每個貨幣 1 列。像：

CREATE TABLE wallet (
  user_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY 
, currency1 integer -- or numeric, depends on missing info
, currency2 integer
, ...
, currency100 integer
);

到目前為止，與您的兩個選項中的任何一個相比，它的磁碟佔用空間要小得多*。*

每種貨幣使用 4 個字節（帶integer），加上 16 個字節用於 NULL 點陣圖。NULL 儲存非常便宜。看：

• 可以為空的列在 PostgreSQL 中是否會佔用額外的空間？
• 為讀取性能配置 PostgreSQL

數據類型integer或numeric？

• PostgreSQL：貨幣應該使用哪種數據類型？

您的選項 1 ( jsonb) 通過為每個金額儲存一個鍵名，至少使使用的每種貨幣的大小翻倍。在儲存方面，每位使用者只需很少的貨幣即可獲勝。求和、計算、索引更慢更複雜。數據完整性很難執行。

您的選項 2 每個貨幣佔用約 44 個字節（單獨的行）。非常乾淨的數據模型，可以靈活地動態添加/刪除貨幣，但會浪費大量空間，這會使一切變得緩慢。

---

• 整個錢包的很多讀取都非常簡單：

SELECT * FROM wallet WHERE user_id = 123;

user_id您只需要PK 提供的索引。

• 從所有使用者那裡獲取每種貨幣的總金額的每晚總和是盡可能簡單和快速的：

SELECT sum(currency1), sum(currency2), ... FROM wallet;

沒有索引。

---

如果你有幾十種貨幣覆蓋了所有條目的大部分，你可以嘗試一種組合策略：固定列用於常客，而 jsonb 列用於其餘部分。這結合了最小儲存大小和絕對靈活性——代價是更複雜的查詢和計算，因為您現在必須將兩者結合起來。並且執行完整性的手段要弱得多。

CREATE TABLE wallet (
  user_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY 
, currency1 integer -- or numeric, depends on missing info
, currency2 integer
, ...
, currency70 integer
, chickenfeed jsonb
);

在為 NULL 點陣圖分配另外 8 個字節之前，我選擇了 70 個貨幣列以保持低於 72 個列的局部最優值。一個次要的考慮。選擇一個適合您的數據分佈的數字。

維護所有允許貨幣的表格 - 您不想搜尋數百萬行以獲得完整列表。並在列中使用最小長度的鍵名jsonb，例如'{"A1":123}'（2 個字節的鍵），這樣就不會浪費 GB 的儲存空間來一遍又一遍地重複冗長的名稱。

引用自：https://dba.stackexchange.com/questions/229333