postgresql

何時避免在 PostgreSQL Schema 中使用 JSONB

JSONB好用，但在PostgreSQL Schema中並非永遠適用。

何時避免在 PostgreSQL Schema 中使用 JSONB
Photo by Cameron Cress / Unsplash
原文: When To Avoid JSONB In A PostgreSQL Schema
作者: Dan Robinson

PostgreSQL 9.4 版推出 JSONB 資料型別時，備受矚目（對關聯式資料庫的新資料型別來說，算是相當高的關注度）。這項功能很棒：它讓您能以現代網路服務常用的 JSON 格式儲存資料，不必每次存取欄位時都重新解析，而且可以針對像是包含其他 JSON 區塊等複雜條件建立索引。

它大幅強化 PostgreSQL 的功能，讓它成為許多文件儲存工作流程的理想選擇。這也符合新創公司的工程思維：只要在資料表新增一個 properties 欄位，用來儲存未來可能需要的額外屬性，您的 Schema 就正式「面向未來 TM」了。

Heap 大量使用 JSONB，因為我們的 API 允許客戶將任意屬性附加到我們收集的事件中，這是很自然的選擇。最近，我收到一些關於使用 JSONB 儲存整個資料表的優缺點的疑問：為什麼不只用 id 和 data 欄位呢？

許多人喜歡不必明確管理 Schema 的想法，所以 JSONB 會被這樣使用並不令人意外。但這樣做會付出相當大的效能成本，其中有些並不明顯。雖然有很多資料可以幫助您決定 JSON、JSONB 或 hstore 哪個最適合您的專案，但正確答案通常是「以上皆非」。*以下列出幾個原因。

隱藏成本一：缺乏統計資訊導致查詢緩慢

對於傳統資料型別，PostgreSQL 會儲存每個資料表每個欄位的值分布統計資訊，例如：

  • 相異值的數量
  • 最常見的值
  • NULL 值的比例
  • 對於有序型別，欄位值分布的直方圖

對於指定的查詢，查詢規劃器會使用這些統計資訊來估算哪個執行計畫最快。我們建立一個包含一百萬筆「測量值」的資料表，每筆包含三個值，每個值都從 {0, 1} 中隨機均勻選取。每次測量都由一萬名科學家其中一位執行，每位科學家都來自三個實驗室的其中一個：

CREATE TABLE measurements (
  tick BIGSERIAL PRIMARY KEY,
  value_1 INTEGER,
  value_2 INTEGER,
  value_3 INTEGER,
  scientist_id BIGINT
);
INSERT INTO measurements (value_1, value_2, value_3, scientist_id)
  SELECT
    trunc(2 * random()),
    trunc(2 * random()),
    trunc(2 * random()),
    trunc(10000 * random() + 1)
  FROM generate_series(0, 999999);

CREATE TABLE scientist_labs (scientist_id BIGSERIAL PRIMARY KEY, lab_name TEXT);
INSERT INTO scientist_labs (lab_name)
  SELECT (
    '{"California Scientific", "Industrial Labs", "Western Science People"}'::TEXT[]
  )[i % 3 + 1]
  FROM generate_series(1, 10000) i;

ANALYZE;

假設我們要找出三個值都是 0 的 tick 值（約佔 1/8），並查看這些測量中每個實驗室出現的次數。我們的查詢如下：

SELECT lab_name, COUNT(*)
FROM (
  SELECT scientist_id
  FROM measurements
  WHERE
    value_1 = 0 AND
    value_2 = 0 AND
    value_3 = 0
) m
JOIN scientist_labs AS s
  ON (m.scientist_id = s.scientist_id)
GROUP BY lab_name

我們的查詢計畫如下：https://explain.depesz.com/s/H4oY

這正是我們期望的：查詢規劃器從資料表統計資訊得知，約 1/8 的 measurements 資料列中，value_1、value_2 和 value_3 等於 0，所以約 125,000 筆資料需要與科學家的實驗室資訊連接，資料庫透過雜湊連接完成。也就是說，將 scientist_labs 的內容載入到以 scientist_id 為鍵的雜湊表中，掃描 measurements 中符合條件的資料列，並透過 scientist_id 值在雜湊表中查找。在我的電腦上執行速度很快，約 300 毫秒。

如果我們改用 JSONB 儲存測量值，如下：

CREATE TABLE measurements (tick BIGSERIAL PRIMARY KEY, record JSONB);
INSERT INTO measurements (record)
  SELECT (
    '{ "value_1":' || trunc(2 * random()) ||
    ', "value_2":' || trunc(2 * random()) ||
    ', "value_3":' || trunc(2 * random()) ||
    ', "scientist_id":' || trunc(10000 * random() + 1) || ' }')::JSONB
  FROM generate_series(0, 999999) i

對應的讀取查詢如下：

SELECT lab_name, COUNT(*)
FROM (
  SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
  FROM measurements
  WHERE
    (record ->> 'value_1')::INTEGER = 0 AND
    (record ->> 'value_2')::INTEGER = 0 AND
    (record ->> 'value_3')::INTEGER = 0
) m
JOIN scientist_labs AS s
  ON (m.scientist_id = s.scientist_id)
GROUP BY lab_name

效能卻大幅下降 — 在我的筆電上花了 584 秒，慢了約 2000 倍： https://explain.depesz.com/s/zJiT

根本原因是 PostgreSQL 不知道如何保存 JSONB 欄位值的統計資訊。它無法得知 record ->> 'value_2' = 0 成立的機率約為 50%，所以它使用預設的 0.1% 估計值。因此，它估計 measurements 資料表中只有 0.1% 的 0.1% 的 0.1% （約 1 列）符合條件。結果，它選擇巢狀迴圈連接：對於每個符合篩選條件的 measurements 資料列，透過主鍵在 scientist_labs 中查找對應的 lab_name。但實際上有大約 125,000 筆這樣的測量值，而不是 1 筆，導致查詢時間冗長。**

準確的統計資訊一直是資料庫效能的關鍵。缺乏這些資訊，查詢規劃器就像瞎子摸象。這是 JSONB 的隱藏成本之一：您的資料沒有統計資訊，查詢規劃器無法有效運作。

這不是紙上談兵。我們在實際產品中就遇到這個問題，唯一的解決方法是將 enable_nestloop 設為 off，完全停用巢狀迴圈連接。一般來說，您不應該這樣做。

在鍵值或文件儲存的工作負載中，您可能不會遇到這個問題，但如果您使用 JSONB 進行分析查詢，就很容易遇到。

隱藏成本二：更大的資料表佔用空間

PostgreSQL 的 JSON 資料型別將資料以字串形式儲存，只是它知道這些字串是有效的 JSON。JSONB 編碼的儲存空間開銷更大，好處是不需要解析 JSON 即可擷取特定欄位。無論如何，資料庫至少會在每一列儲存每個鍵和值。PostgreSQL 沒有任何機制可以刪除重複出現的鍵。

以上述的 measurements 資料表為例，非 JSONB 版本的資料表佔用 79MB 的磁碟空間，而 JSONB 版本佔用 164MB，超過兩倍。這是因為資料表內容大部分是重複出現的字串 value_1、value_2、value_3 和 scientist_id。在這種情況下，您需要付出兩倍的磁碟空間成本，更別提後續效應會讓各種操作變慢或成本更高。原始 schema 的快取效果更好，或者可能完全放在記憶體中。檔案較小也代表大型讀取或維護操作只需要一半的 I/O。

另一個實際案例是，我們將 45 個常用欄位從 JSONB 移到一般欄位，節省了約 30% 的磁碟空間。對於 PB 級的資料集來說，效益相當可觀。

經驗法則：每個欄位在資料表的每一列約有 1 bit 的開銷，無論欄位值是否為 NULL。*** 因此，如果一個非必要欄位在 JSONB 中有個十個字元的鍵，每列至少需要 80 bit 來儲存這個鍵，那麼如果這個欄位出現在至少 1/80 的列中，將它獨立出來會比較省空間。

對於有很多非必要值的資料集，將每個值都設為資料表欄位可能不切實際或不可能。在這種情況下，JSONB 不論在簡潔性或效能上都是不錯的選擇。但對於大多數列都出現的值，最好還是將它們獨立出來。實際上，還有其他因素會影響您組織資料的方式，例如管理明確 schema 所需的工程成本，或類型安全和 SQL 可讀性的優點。但如果不必使用 JSONB，卻使用了，通常會付出相當大的效能代價。

知道其他看似無害卻對效能影響重大的變更嗎？歡迎在 @danlovesproofs 與我聯繫。我們持續評估各種 schema 和索引策略，以處理數千億事件的臨時查詢。如果您對這類工作感興趣，我們正在招募人才！歡迎參考我們的工程團隊和職缺資訊。

*建議您從這篇文章開始閱讀：https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/

**順帶一提，explain.depesz 是個很棒的工具，可以找出查詢中的這類問題。在這個例子中，您可以看到查詢規劃器低估了這個子查詢返回的列數，誤差高達 124,616 倍。

***這並不完全正確。PostgreSQL 為前 8 個欄位配置每列一個位元組，之後每 64 個欄位配置 8 個位元組。例如，前 8 個欄位是免費的，第 9 個欄位在資料表的每一列佔用 8 個位元組，然後第 10 到 72 個是免費的，依此類推。（感謝 Michael Malis 的研究。）

Read more