使用 JSONB 列或另一個表來保存關係

我試圖在這裡徹底搜尋，但沒有找到任何答案。

我有一個 PostgreSQL 數據庫，它有兩個主表：

• 文件
• 使用者

這兩個表有不同的關係。使用者可以：

• 像
• 稍後閱讀的書籤
• 節省

… 一份文件。

問題是我應該如何保存這些關係？

根據我使用 MySQL 的經驗，顯而易見的方法是為這些多對多關係創建表，其中包含user_id和document_id.

但是當我們使用 PostgreSQL 並且它具有驚人的 JSON 支持時，我們認為也許更好的方法是擁有一個包含的user_document表和一個包含所有關係的JSON 列。user_id``document_id

JSON 將是這樣的：

{
  'follow' : {'date' : 1523517140, 'doesFollow' : 't'}, 
  'bookmark' : {'date' : null, 'doesBookmark' : 'f'},
  ....
}

我對 PostgreSQL 的經驗幾乎為零，而且我不知道查詢 JSONB 列的性能。而且我不知道這種方法在 PostgreSQL 中是否有意義。但它似乎沒問題，如果它沒有任何問題，也許它比第一種正常方法更可取。

json像or jsonb(or xmlor ) 這樣的文件類型hstore便於儲存文件。非常適合在查詢中具有不同鍵且很少更新且不太複雜的過濾條件的數據。在數據庫中大量操作大文件將是一種反模式。

結構化數據通常以小增量寫入，並且可能搜尋了很多（就像在您的情況下最有可能）在標準化設計中效率更高 - 關於儲存、性能、並發寫入訪問和數據完整性。因此手冊中的建議：

當儲存在表中時，JSON 數據與任何其他數據類型一樣受到相同的並發控制注意事項。儘管儲存大文件是可行的，但請記住，任何更新都會獲取整行的行級鎖。考慮將 JSON 文件限制在可管理的大小，以減少更新事務之間的鎖爭用。理想情況下，每個 JSON 文件都應該代表一個原子數據，業務規則規定不能合理地進一步細分為可以獨立修改的較小數據。

**使用一個或多個聯結表實現您的 n:m 關係。**您可以使用約束（PK、FK、UNIQUE、CHECK ..）強制數據完整性，而對於文件類型而言，這些都不容易實現。除非您對“喜歡”、“書籤”等有不同的要求，否則我會傾向於一張桌子。

例子：

如果您不確定典型佈局，以下是基礎知識：

• 如何在 PostgreSQL 中實現多對多關係？

假設您的每個關係類型每個使用者/文件組合只能使用一次。對於一個充滿值的手（在您的情況下為 3），我將使用 1 字節"char"列作為查找表的 PK 來優化表和索引中的儲存和性能。

CREATE TABLE reltype (
  reltype "char" PRIMARY KEY
, relation_type text UNIQUE NOT NULL
);

INSERT INTO reltype(reltype, relation_type) VALUES
  ('l', 'like')
, ('b', 'bookmark')
, ('s', 'save');

CREATE TABLE user_doc (
  user_doc_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY
, user_id     int REFERENCES users     ON UPDATE CASCADE ON DELETE CASCADE
, document_id int REFERENCES documents ON UPDATE CASCADE ON DELETE CASCADE
, reltype     "char" REFERENCES reltype NOT NULL DEFAULT 'l'
, CONSTRAINT user_document_pkey UNIQUE(user_id, document_id, reltype)
);

您始終可以將聚合數據導出為 JSON 文件。甚至有一個VIEW或MATERIALIZED VIEW讀起來像一張桌子。但不要在單個大 JSON 文件中管理關係。

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