PostgreSQL流式處理應用實踐-二手商品實時歸類(非同步訊息notify/listen、閱後即焚)

德哥發表於2018-10-05

背景

因為二手商品沒有太多的活動、硬性分類，廣告等活動，所以購買或者銷售速度沒有新商品那麼快。為了提高二手商品的銷售效率，需要提供一套歸類策略。

當商品新增或商品內容發生變化時，需要根據商品屬性，以及定義的規則，實時進行商品歸類（魚塘，圈子等）方便使用者查詢。

結構設計

1、商品ID，屬性

create table a (      
  id int8 primary key,   -- 商品ID      
  att jsonb   -- 商品屬性      
);

屬性設計為JSON，JSON裡面是K-V的屬性對，V裡面是陣列，包含K的值以及這對屬性的最後更新時間。

更新時間用於merge insert，當屬性發生變化時才更新，沒有發生變化時，不更新。

所以json需要遍歷，並做合併處理。

合併JSON屬性的UDF

create or replace function merge_json(jsonb, jsonb) returns jsonb as $$    
  select jsonb_object_agg(key,value) from (    
  select     
    coalesce(a.key, b.key) as key,     
    case     
    when     
    coalesce(jsonb_array_element(a.value,1)::text::timestamp, `1970-01-01`::timestamp)     
    >     
    coalesce(jsonb_array_element(b.value,1)::text::timestamp, `1970-01-01`::timestamp)     
    then a.value    
    else b.value    
    end    
  from jsonb_each($1) a full outer join jsonb_each($2) b using (key)    
  ) t;      
$$ language sql strict ;    
    
    
postgres=# select merge_json(`{"price":[10000, "2018-01-01 10:10:11"], "newatt":[120, "2017-01-01 12:22:00"]}`,  `{"price":[8880, "2018-01-04 10:10:12"], "count":[100, "2017-01-01 10:10:00"]}`);    
                                                       merge_json                                                            
-------------------------------------------------------------------------------------------------------------------------    
 {"count": [100, "2017-01-01 10:10:00"], "price": [8880, "2018-01-04 10:10:12"], "newatt": [120, "2017-01-01 12:22:00"]}    
(1 row)

觸發器設計

觸發器裡面定義分類規則，例如這裡對價格大於100的商品，吐出訊息.

CREATE OR REPLACE FUNCTION notify1() returns trigger      
AS $function$      
declare      
begin      
  if jsonb_array_element(NEW.att->`price`, 0)::text::float8 > 100 then   -- 規則1， 價格大於100，推送非同步訊息      
     perform pg_notify(      
       `a`,    -- 非同步訊息通道名字      
       format(`CLASS:high price, ID:%s, ATT:%s`, NEW.id, NEW.att)   -- 訊息內容      
     );      
  -- elsif ... then  其他規則      
  -- else  其他規則      
  end if;      
return null;      
end;      
$function$ language plpgsql strict;

建立after insert or update觸發器

create trigger tg1 after insert or update on a for each row execute procedure notify1();

其他觸發器（規則設計方法）

本文未使用

CREATE OR REPLACE FUNCTION notify1() returns trigger      
AS $function$      
declare      
begin      
  for key,value in select key, jsonb_array_element(value, 0)::text from jsonb_each(NEW.att)  -- 解析一次JSONB    
  loop    
    -- 規則處理    
    -- if key=`price` then ...; end if;    
    -- if key=`count` then ...; end if;    
  end loop;    
return null;    
end;    
$function$ language plpgsql strict;

-- 動態規則表    
    
create table tbl_rule (    
  key text,  -- key值    
  exp text,  -- value 代入的表示式    
  class text,  -- 滿足exp時，指向這個歸類    
)    
    
CREATE OR REPLACE FUNCTION notify1() returns trigger      
AS $function$      
declare      
begin      
  for key,value in select key, jsonb_array_element(value, 0)::text from jsonb_each(NEW.att)  -- 解析一次JSONB    
  loop    
    -- 使用tbl_rule生成規則處理邏輯，動態    
  end loop;    
return null;    
end;    
$function$ language plpgsql strict;

規則描述

json屬性對中，value的型別可能很多，對應不同的規則語義。

1、文字 LIKE

2、陣列 IN

3、等值

4、數值範圍

5、時間範圍

等等，在trigger的UDF中寫規則即可。

資料合併寫入測試

insert into a values       
  (1, `{"price":[10000, "2018-01-01 10:10:11"]}`)       
  on conflict (id)       
  do update set       
  att = merge_json(a.att, excluded.att)  -- 合併新屬性，保留老屬性，需要使用一個UDF來合併      
  where       
  a.att <> merge_json(a.att, excluded.att);  -- 如果相等的概率很低，則可以去掉這個判斷, 降低CPU開銷   
    
    
postgres=# insert into a values    
  (1, `{"price":[1000, "2019-01-01 10:10:12"], "newatt": ["hello", "2018-01-01"]}`)    
  on conflict (id)    
  do update set    
  att = merge_json(a.att, excluded.att)  -- 合併新屬性，保留老屬性，需要使用一個UDF來合併    
  where    
  a.att <> merge_json(a.att, excluded.att);   -- 如果相等的概率很低，則可以去掉這個判斷, 降低CPU開銷  
INSERT 0 1    
    
    
postgres=# select * from a;    
 id |                                     att                                         
----+-----------------------------------------------------------------------------    
  1 | {"price": [1000, "2019-01-01 10:10:12"], "newatt": ["hello", "2018-01-01"]}    
(1 row)

監聽訊息

postgres=# listen a;      
LISTEN      
Asynchronous notification "a" with payload "ID:1, ATT:{"price": [10000, "2018-01-01 10:10:19"]}" received from server process with PID 51380.

https://jdbc.postgresql.org/documentation/head/listennotify.html

其他

刪除商品，可以使用DELETE觸發器，告訴下游，比如商品已成交，刪除。

CREATE OR REPLACE FUNCTION notify2() returns trigger      
AS $function$      
declare      
begin      
     perform pg_notify(      
       `a`,                                                     -- 非同步訊息通道名字      
       format(`CLASS:delete, ID:%s, ATT:%s`, OLD.id, OLD.att)   -- 訊息內容      
     );      
return null;      
end;      
$function$ language plpgsql strict;      
    
create trigger tg2 after delete on a for each row execute procedure notify2();

方案二 – 流式批量消費

使用非同步訊息的方式，當連線中斷時，重新連線後需要重新監聽，並且在中斷連線期間的訊息會被丟棄掉。所以可靠性不佳。

另外，非同步訊息無法控制一次消費多少條，也不是特別友好。

所以我們實際上還有其他方法，持久化表，並且使用非同步批量消費的方式進行消費。

效能指標：

CASE	資料量	併發	TPS	平均響應時間
流式處理 – 閱後即焚 – 消費	10億，消費 395.2 萬行/s	56	3952	14毫秒

結構沿用前面的例子，

1、新增一張結果表（也可以新增多張表，看業務量，通常一張夠用了），

2、同時修改一下觸發器內容，把notify改成寫表，

3、修改客戶端把監聽通道改成非同步消費SQL

DEMO

1、新增結果表

create table t_result(id serial8 primary key, class text, content text);

2、觸發器裡面定義分類規則，例如這裡對價格大於100的商品，吐出資訊到結果表.

CREATE OR REPLACE FUNCTION notify1() returns trigger      
AS $function$      
declare      
begin      
  if jsonb_array_element(NEW.att->`price`, 0)::text::float8 > 100 then   -- 規則1， 價格大於100，寫入結果表      
     insert into t_result(class,content) values (    
       `a`,    -- 歸類    
       format(`CLASS:high price, ID:%s, ATT:%s`, NEW.id, NEW.att)   -- 訊息內容      
     );    
  -- elsif ... then  其他規則      
  -- else  其他規則      
  end if;      
return null;      
end;      
$function$ language plpgsql strict;

3、建立after insert or update觸發器

create trigger tg1 after insert or update on a for each row execute procedure notify1();

4、資料合併寫入測試

insert into a values       
  (1, `{"price":[10000, "2018-01-01 10:10:11"]}`)       
  on conflict (id)       
  do update set       
  att = merge_json(a.att, excluded.att)  -- 合併新屬性，保留老屬性，需要使用一個UDF來合併      
  where       
  a.att <> merge_json(a.att, excluded.att);   -- 如果相等的概率很低，則可以去掉這個判斷, 降低CPU開銷  
    
    
postgres=# insert into a values    
  (1, `{"price":[1000, "2019-01-01 10:10:12"], "newatt": ["hello", "2018-01-01"]}`)    
  on conflict (id)    
  do update set    
  att = merge_json(a.att, excluded.att)  -- 合併新屬性，保留老屬性，需要使用一個UDF來合併    
  where    
  a.att <> merge_json(a.att, excluded.att); -- 如果相等的概率很低，則可以去掉這個判斷, 降低CPU開銷   
    
INSERT 0 1    
    
postgres=# select * from a;    
 id |                                     att                                         
----+-----------------------------------------------------------------------------    
  1 | {"price": [1000, "2019-01-01 10:10:12"], "newatt": ["hello", "2018-01-01"]}    
(1 row)

5、非同步批量消費結果表的內容(閱後即焚)

with a as (delete from t_result where ctid= any(array(     
  select ctid from t_result order by id limit 10 for update skip locked  -- 可以併發消費，不會相互堵塞，消費順與寫入順序一致    
)) returning *)    
select * from a;

 id | class |                                                 content                                                     
----+-------+---------------------------------------------------------------------------------------------------------    
  1 | a     | CLASS:high price, ID:1, ATT:{"price": [10000, "2018-01-01 10:10:11"]}    
  2 | a     | CLASS:high price, ID:1, ATT:{"price": [1000, "2019-01-01 10:10:12"], "newatt": ["hello", "2018-01-01"]}    
(2 rows)    
    
    
原子操作，閱後即焚，再次查詢已消費完畢    
    
postgres=# select * from t_result;    
 id | class | content     
----+-------+---------    
(0 rows)

方案二續 – 使用statement級觸發器代替row級觸發器

為什麼建議使用statement級觸發器代替row級觸發器，參考：

《PostgreSQL 批量、單步寫入 – row, statement 觸發器(中間表)、CTE 幾種用法效能對比》

觸發器函式修改如下

CREATE OR REPLACE FUNCTION notify1() returns trigger        
AS $function$        
declare        
begin        
  -- 規則1  
  insert into t_result(class,content) select   
    `a`,    -- 歸類    
    format(`CLASS:high price, ID:%s, ATT:%s`, id, att)   -- 訊息內容     
  from new_table   
  where jsonb_array_element(att->`price`, 0)::text::float8 > 100;    -- 規則1， 價格大於100，寫入結果表    
    
  -- 其他規則  
  -- insert into t_result(class,content) select   
  -- ......  
  --   from new_table   
  -- where ...  -- 規則n  
    
  return null;        
end;        
$function$ language plpgsql strict;

觸發器修改如下

create trigger tg1 after insert on a REFERENCING NEW TABLE AS new_table for each STATEMENT execute procedure notify1();      
create trigger tg2 after update on a REFERENCING NEW TABLE AS new_table for each STATEMENT execute procedure notify1();

postgres=# d a  
                 Table "public.a"  
 Column |  Type   | Collation | Nullable | Default   
--------+---------+-----------+----------+---------  
 id     | integer |           | not null |   
 att    | jsonb   |           |          |   
Indexes:  
    "pk" PRIMARY KEY, btree (id)  
Triggers:  
    tg1 AFTER INSERT ON a REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE PROCEDURE notify1()  
    tg2 AFTER UPDATE ON a REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE PROCEDURE notify1()