JSONB 字元串數組（帶 GIN 索引）與拆分行（B 樹索引）

我有一個數據庫，它儲存receiver以指示數據與哪個帳戶相關。這導致了大量的數據重複，因為一組數據可能會創建 3 個單獨的行，其中所有列數據都相同，但receiver列除外。在重新設計數據庫時，我考慮過使用帶有 GIN 索引的數組，而不是接收器上的目前 B-Tree 索引。

目前表定義：

CREATE TABLE public.actions (
   global_sequence bigint NOT NULL DEFAULT nextval('actions_global_sequence_seq'::regclass),
   time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
   receiver text NOT NULL,
   tx_id text NOT NULL,
   block_num integer NOT NULL,
   contract text NOT NULL,
   action text NOT NULL,
   data jsonb NOT NULL
);

索引：

• “actions_pkey” PRIMARY KEY, btree (global_sequence, time)
• “actions_time_idx” btree（時間 DESC）
• “receiver_idx” btree（接收者）

欄位詳情：

• 全域序列是一個連續遞增的 ID

• 區塊號和時間不是唯一的，也是遞增的

• 全域序列和時間是主鍵，因為數據在內部是按時間分區的

  • 有一些接收器有超過 10 億個關關聯作（每個都有一個唯一的 global_sequence）。

• 平均文本長度：

  • 接收器：12
  • tx_id: 52
  • 契約：12
  • 行動：6
  • 數據：帶有動作元數據的中小型 JSONB

3 個模式選項的基數：

• **目前：**此表中的行數為 42 億行

• **接收器作為數組：**大約有 18 億行

• **標準化：**將有 3 個表：

  • 操作：18 億行
  • Actions_Accounts：42 億行
  • 帳戶：500 000 行

常見查詢：

• SELECT * FROM actions WHERE receiver = 'Alpha' ORDER BY time DESC LIMIT 100

查詢中需要所有列。看不到 NULL 值。我相信規範化模式中的連接會減慢&查詢速度是#1優先級）

最佳 DB 設計始終取決於整體情況。

通常，對於您的簡單查詢，幾乎沒有什麼比普通 btree 索引更快的了。結合 GIN 索引引入json甚至是普通數組類型很可能會使其變慢。jsonb

對於您的原始表，這個具有正確排序順序的多列索引應該是您常見查詢的遊戲規則改變者：

CREATE INDEX game_changer ON actions (receiver, time DESC);

這樣，Postgres 可以直接從索引中挑選前 100 行。超級快。

有關的：

• 優化對一系列時間戳（兩列）的查詢

您目前的索引receiver_idx可能actions_time_idx會失去其用途。

除了完美的索引，儲存大小是大表的一個重要因素，因此避免重複可能是正確的想法。但這可以通過多種方式實現。您是否考慮過良好的舊規範化？

CREATE TABLE receiver (
  receiver_id serial PRIMARY KEY
, receiver    text NOT NULL -- UNIQUE?
);

CREATE TABLE action (  -- I shortened the name to "action"
  action_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- global_sequence bigint NOT NULL DEFAULT nextval('actions_global_sequence_seq'::regclass),  -- ??
  time       timestamptz NOT NULL DEFAULT now(),
  block_num  int NOT NULL,
  tx_id      text NOT NULL,
  contract   text NOT NULL,
  action     text NOT NULL,
  data       jsonb NOT NULL
)

CREATE TABLE receiver_action (
  receiver_id int    REFERENCES receiver
, action_id   bigint REFERENCES action
, PRIMARY KEY (receiver_id, action_id)
);

另請注意 table 中列的更改順序，action每行節省幾個字節，這為數十億行節省了幾個 GB。

看：

• 在 PostgreSQL 中計算和節省空間
• 如何在 PostgreSQL 中實現多對多關係？
• 為讀取性能配置 PostgreSQL

您的常見查詢略微更改為：

SELECT a.*
FROM   receiver_action ra
JOIN   action a USING (action_id)
WHERE  ra. receiver_id = (SELECT receiver_id FROM receiver WHERE receiver = 'Alpha')
ORDER  BY a.time DESC
LIMIT  100;

***缺點：***現在讓你的常見查詢變得更快更難了。有關的：

• 空間索引能否幫助“範圍-按-限制”查詢

快速（而且有點臟）修復：time在表中冗餘地包含列receiver_action（或將其移到那裡）。

CREATE TABLE receiver_action (
  receiver_id int    REFERENCES receiver
, action_id   bigint REFERENCES action
, time        timestamptz NOT NULL DEFAULT now()  -- !
, PRIMARY KEY (receiver_id, action_id)
);

創建索引：

CREATE INDEX game_changer ON receiver_action (receiver_id, time DESC) INCLUDE (action_id);

INCLUDE需要 Postgres 11 或更高版本。看：

• 具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快？

並使用此查詢：

SELECT a.*
FROM  (
  SELECT action_id
  FROM   receiver_action
  WHERE  receiver_id = (SELECT receiver_id FROM receiver WHERE receiver = 'Alpha')
  ORDER  BY time DESC
  LIMIT  100
  )
JOIN   action a USING (action_id);

---

根據背後的確切故事，one set of data may create 3 separate rows更多可能是可能的 - 甚至表操作中的 3 個單獨的列而不是 n:m 實現和表達式 GIN 索引……但這太深入了。我在這裡敲門。

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