PostgreSQL流式處理應用實踐-二手商品實時歸類(非同步訊息notify/listen、閱後即焚)
標籤
PostgreSQL , rule , trigger , 歸類 , json , udf , 非同步訊息 , listen , notify
背景
因為二手商品沒有太多的活動、硬性分類,廣告等活動,所以購買或者銷售速度沒有新商品那麼快。為了提高二手商品的銷售效率,需要提供一套歸類策略。
當商品新增或商品內容發生變化時,需要根據商品屬性,以及定義的規則,實時進行商品歸類(魚塘,圈子等)方便使用者查詢。
結構設計
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()
小結
使用非同步訊息,UDF,規則或觸發器,非常輕量化的解決了實時計算的問題。
但是,非同步訊息是可能丟訊息的,例如監聽連線中斷後,重連時,需要重新發起監聽,並且中斷連線時未消費的訊息,不會再被消費,所以相當於丟訊息了。
改進方法:
1、如果要保證不丟訊息,可以將notify改成INSERT,把結果寫入預先定義好的某個結果表,使用邏輯DECODE的方式,解析這個結果表相關的logical decode資訊,從而獲取變化量,參考如下。
《PostgreSQL pg_recvlogical 與 test_decoding 自定義,支援source table filter, 對接kafka,es等》
2、使用閱後即焚的方法,類似本方案2.
《阿里雲RDS PostgreSQL varbitx實踐 – 流式標籤 (閱後即焚流式批量計算) – 萬億級,任意標籤圈人,毫秒響應》
《HTAP資料庫 PostgreSQL 場景與效能測試之 32 – (OLTP) 高吞吐資料進出(堆存、行掃、無需索引) – 閱後即焚(JSON + 函式流式計算)》
《HTAP資料庫 PostgreSQL 場景與效能測試之 31 – (OLTP) 高吞吐資料進出(堆存、行掃、無需索引) – 閱後即焚(讀寫大吞吐並測)》
《HTAP資料庫 PostgreSQL 場景與效能測試之 27 – (OLTP) 物聯網 – FEED日誌, 流式處理 與 閱後即焚 (CTE)》
《在PostgreSQL中實現update | delete limit – CTID掃描實踐 (高效閱後即焚)》
參考
https://www.postgresql.org/docs/11/static/functions-json.html
https://www.postgresql.org/docs/11/static/datatype-json.html
https://jdbc.postgresql.org/documentation/head/listennotify.html
https://www.postgresql.org/docs/11/static/sql-notify.html
https://www.postgresql.org/docs/11/static/sql-listen.html
https://www.postgresql.org/docs/11/static/sql-unlisten.html
https://www.postgresql.org/docs/11/static/libpq-notify.html
https://www.postgresql.org/docs/11/static/sql-notify.html#id-1.9.3.157.7.5
https://www.postgresql.org/docs/11/static/functions-info.html
https://www.postgresql.org/docs/11/static/plpgsql-trigger.html
https://github.com/impossibl/pgjdbc-ng
https://www.openmakesoftware.com/postgresql-listen-notify-events-example/
相關文章
- PostgreSQL:LISTEN | NOTIFYSQL
- PostgreSQL百萬級每秒的流式實時統計應用SQL
- 流式處理新秀Flink原理與實踐
- (流式、lambda、觸發器)實時處理大比拼-物聯網(IoT)金融,時序處理最佳實踐觸發器
- Java如何使用實時流式計算處理?Java
- WEB 即時通訊最佳實踐Web
- 處理XML資料應用實踐XML
- 手淘千牛IM即時通訊-星巴克訊息開放實踐
- WhatsApp進軍閱後即焚 將威脅SnapchatAPP
- 電商非同步訊息系統的實踐非同步
- iOS流式即時通訊教程iOS
- 使用PostgreSQL_Notify實現多例項快取同步SQL快取
- ASP.NET Web API實踐系列05,訊息處理管道ASP.NETWebAPI
- 即時通訊中音影片同步的實現
- 構建一個即時訊息應用(二):OAuthOAuth
- 音訊 3A 處理實踐,讓你的應用更「動聽」音訊
- Android應用程式訊息處理機制Android
- Windows應用程式的訊息處理機制Windows
- 【實戰教程】微信卡券訊息處理
- 微信後臺非同步訊息佇列的優化升級實踐分享非同步佇列優化
- 「實戰」搭建完整的IM(即時通訊)應用(2)
- 「實戰」搭建完整的IM(即時通訊)應用(1)
- 應用實踐——新東方實時數倉實踐
- MFC應用程式中處理訊息的順序
- PostgreSQL10流式物理、邏輯主從最佳實踐SQL
- Web 應用程式中提交表單時處理 GIF 動畫的最佳實踐Web動畫
- Laravel 實現 Kafka 訊息推送與接收處理LaravelKafka
- 未讀訊息(小紅點),前端與 RabbitMQ實時訊息推送實踐,賊簡單~前端MQ
- Service初探與非同步訊息處理機制非同步
- 訊息的即時推送——net實現、websocket實現以及socket.io實現Web
- Data Guard備庫日誌的實時應用與非實時應用
- 即時訊息 - 時信魔方教程
- 短視訊影象處理 OpenGL ES 實踐
- Redis 應用-非同步訊息佇列與延時佇列Redis非同步佇列
- 實現類似QQ的即時通訊程式(十一)
- 用 Laravel 自帶訊息模組搭建小程式實時推送訊息Laravel
- Asynq 實現 Go 後臺作業非同步定時任務處理Go非同步
- Mysql資料實時同步實踐MySql