在 md5(JSONB_COLUMN::text) 上使用 UNIQUE 約束優化 INSERT 性能

我定義了下表和索引：

CREATE TABLE IF NOT EXISTS data (
   id serial,
   job_id bigint NOT NULL,
   payload jsonb NOT NULL,
   tags jsonb NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_data_content on data (md5(payload::text));
CREATE INDEX IF NOT EXISTS idx_data_tags ON data USING GIN (tags jsonb_path_ops);
CREATE INDEX IF NOT EXISTS idx_data_jid ON data USING btree (job_id);

在該表中，我儲存了許多 JSON blob，它們與儲存job_id在. 我想避免儲存重複的 JSON blob，所以我使用這種方法作為約束。然而，在數據庫中有幾十萬行之後，性能開始下降，沒有約束就不會發生這種情況。payload->>'jobId'``payload->>'__time'``UNIQUE``INSERT``UNIQUE

極不可能有兩個不同的 JSON blob 具有相同的 job_id 和 payload->>'__time'。有什麼辦法可以利用它，例如，通過定義一個UNIQUE約束，首先檢查job_id, 然後payload->>'__time'，並且只有當兩者都匹配時才真正檢查md5(payload::text)索引？

（請注意，我的 JSON blob 中的鍵對於相同的文件總是以相同的順序排列。）

如果唯一性約束是唯一的問題（我有興趣在上面的討論中了解更多關於為什麼），這裡有一個想法：

• 移除唯一性約束
• 當您讀取（選擇）時，order by id asc limit 1請忽略重複項
• 有某種並行過程定期遍歷表並刪除重複項

這裡有幾個想法。

一個在函式中進行約束檢查。

第二個修改表，創建一個觸發器以添加失去的數據，並在必須檢查的三個欄位上創建一個新索引

CREATE TRIGGER check_jsonb
   BEFORE INSERT  
   ON data
   FOR EACH ROW
   EXECUTE PROCEDURE 

CREATE FUNCTION public._check_jsonb()
   RETURNS trigger
   LANGUAGE 'plpgsql'
   COST 100
AS $BODY$
   declare

   _check_rec record;

   begin
       select job_id, payload from data where job_id = new.job_id 
       if found then
           if _check.payload.__time == new.jsonb.__time  
                 --do not know json command in postgresql 
                 -- that well to extract this value
               select true from data where new.payload == (md5(payload::text)) 
           end if;
       end if ;
       return new;
$BODY$

–第二個嘗試的想法

CREATE TABLE IF NOT EXISTS data (
   id serial,
   job_id bigint NOT NULL,
   payload jsonb NOT NULL,
   tags jsonb NULL,
   hash_check text not null, 
   time timestamp not null 
);
create unique index if not exists idx_jobid_time on data (id, time, hash_check);
CREATE INDEX IF NOT EXISTS idx_data_tags ON data USING GIN (tags jsonb_path_ops);
CREATE INDEX IF NOT EXISTS idx_data_jid ON data USING btree (job_id);

CREATE TRIGGER check_jsonb_idea2
   BEFORE INSERT  or Update
   ON data
   FOR EACH ROW
   EXECUTE PROCEDURE 

CREATE FUNCTION public._check_jsonb_idea2()
   RETURNS trigger
   LANGUAGE 'plpgsql'
   COST 100
AS $BODY$
   begin 
       new.time = new.jsonb.__time ;-- do not know json commands very while extract this 
       new.hash_check = (md5(payload::text)) 
       return new;
   end;
$BODY$

這也可能允許刪除其他索引

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