owned this note   changed 4 years ago  
Linked with GitHub

想用就用 JSON in PostgreSQL ／ 古哥（古永忠）

歡迎來到 Modern Web 2020 共筆

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

共筆入口：https://hackmd.io/@ModernWeb/2020
手機版請點選上方 按鈕展開議程列表。

簡報下載

共筆從這開始

近年來 json 成為 api 資料交換主流格式，在 2013 年跟 xml 黃金交叉

JSON in RDB 優勢

正規化 反正規化
條件檢核 減少 JOIN
統計查詢 快速開發
資料效能 彈性資料源輸入
Ex. MongoDB Ex. JSON

重點在於資料庫設計透過這些功能，可以符合人性、善用資料，而不是選邊站。

JSON in PostgreSQL

需要使用到 JSON 建議使用 PostgreSQL 12 以上的版本，
JSONPATH要到Version 12才支援。

SELECT '("PostgreSQL":12}'::json;

  • A data type
  • Transaction SAFE
  • Index OK
  • Constraint OK
  • Procedure OK: plpython3u & jsonb_plpython3u
  • jsonpath Type (PG12)
  • Query size limitation: 1 GB

JSON想用就用，不想用就不用


JOIN
View 表格, 可以降低開發的門檻
REPORT 統計資訊部分
FDW

JSON and JSONB

  • JSONB = 處理過的 JSON
  • JSON (前期收集資料)
    • 快速輸入資料、RAW DATA、不會重寫內容
  • JSONB (進來資料庫後索引)
    • 可索引
    • 內容重新編譯
    • 會將資料轉換成符合 PostgreSQL 的型別規則
    • 數字表示可能會產生改變
  • 範例

Index

  • GIN index

  • jsonb_ops and json_path_ops

    • 主要常用的兩種
    • Operator支援的範圍不同
    • Size vs Performance
    • 層次多的 JSON 差異較大
      Name Data Type Operators
      jsonb_ops jsonb ? ?& ?
      jsonb_path_ops jsonb @> @? @@

  • Index on Expression

    • Unique Index 也可以！

Partition table

單機效能還可以

效能不會是決定性因素

JSONB: few more stories about the performance

不推薦的地方

  • When To Avoid JSONB In A PostgreSQL Schema
  • Hidden Cost #1: Slow Queries Due To Lack Of Statistics
    • 拿JSON 裡的內容出來JOINㆍ
  • Hidden Cost #2: Larger Table Footprint
    • 和INT、CHAR等基本欄位相比會估用更多的础碟空間。

確定很常使用的欄位
就提出來使用基本欄位型別處理

結論

  • pros
    • Schema Flexibility
    • Nested Object
    • Foreign Data
  • cons
    • Data Size (zfs, vdo)
    • Scale Out (postgres_fdw + partition table)
    • Transaction (unlogged table)

不應該期待因此而取代其他資料庫
而是你因此有了更多的選擇

tags: MW20 架構與設計 PostgreSQL
Select a repo