恭迎萬億級行銷(圈人)瀟灑的邁入毫秒年齡 - 萬億user_tags級即時推薦系統資料庫設計

來源:互聯網
上載者:User

摘要: 標籤PostgreSQL , 標籤, 推薦系統, 即時圈人, 陣列, gin , gist , 索引, rum , tsvector , tsquery , 萬億, user , tag , 淘寶幕後推薦系統是廣告行銷平臺的奶牛,其核心是精准、即時、高效。

幕後

我們僅用了PostgreSQL的兩個小屬性,卻解決了商務困擾已久的大問題。

推薦系統是廣告行銷平臺的奶牛,其核心是精准、即時、高效。

這麼多廣告平臺,到底誰家強?誰的核心牛逼?

1. 精准,指對用戶的標題精准,通常需要基於大量的使用者行為資料,經歷深度學習後形成的用戶畫像,或稱之為標籤系統。 標籤的準確性關係到推薦的精准度,比如你可能不會對一個正常的年輕人推薦老花眼鏡(當然如果有其他購買意向的標籤來指出他有購買老花眼鏡的欲望除外)。

2. 即時,指標籤的更新即時性,很多標籤是具有非常強的時效性的,比如一次行銷的目標人群,又或者用戶最近瀏覽的一些商品可能是有潛在購買欲望的商品,都具備時效性。如果你的標籤生成是隔天,或者個很多天的,那麼可能已經錯過了推薦時機。因此即時性在推薦系統中是非常重要的。

3. 高效,指基於標籤圈人的動作的效率與並行能力,作為購買廣告的金主,當然是期望他們拿到資料的速度越快越好。並且會有很多人向你的平臺購買廣告,這考驗的是並行能力。

做到以上三點,這樣的廣告平臺才具備一定的競爭力。

除此之外還需要追隨的是平臺的成本,包括硬體的成本,開發成本,維修成本等。

下面將以電商的推薦系統為例,介紹推薦系統的資料庫設計與優化技巧。

以及如何讓行銷瀟灑 (低成本,高並行,高效率)的邁入毫秒年齡

電商推薦系統 部份需求介紹

比如一家店鋪,如何找到它的目標消費群體?

要回覆這個問題,首先我們需要收集一些資料,比如:

1. 這家店鋪以及其他的同類店鋪的瀏覽、購買群體。

我們在逛電商時,會產生一些行為的記錄,比如在什麼時間,逛了哪些店鋪,看了哪些商品,最後在哪家店鋪購買了什麼商品。

然後,對於單個商店來說,有哪些用戶逛過他們的商店,購買過哪些商品,可以抽取出一部分人群。

2. 得到這些用戶群體後,篩選出有同類消費欲望、或者具備相同屬性的群體。

對這部份人群的屬性進行剖析,可以獲得一個更大範圍的群體,從而可以對這部份群體進行行銷。

以上是對電商推薦系統的兩個簡單的推理。

量級

電商的用戶量級,放眼全球,可能會達到幾十億的等級。

店鋪數量,放眼全球,可能會達到千萬等級。

商品數量(細分種類,比如條碼),放眼全球,可能會達到億級。

店鋪標籤數量,針對單個用戶而言,逛了哪些店,多少次,看了哪些商品,多少次,買了哪些商品等。通常一個人,在一定的時間範圍內,會產生上千的這樣的標籤。

用戶標籤,畫像,10萬等級,這個量級可以標題清晰人的屬性。

根據以上的估算,user_tags可能達到萬億(user幾十億, 店鋪商品瀏覽相關的標籤數千等級)的等級。

我們首先清理一下關鍵因素

用戶ID、瀏覽過的店鋪 以及瀏覽次數、瀏覽過的商品 以及瀏覽次數、購買的商品 以及購買數量。(次數數量 可以根據區間,設定為列舉類型,比如0表示100次以下,1表示100到500次,。。。)

這幾個要素很容易從用戶的行為資料中生成,從而當某家店鋪需要做推廣,或者針對某個產品做推廣時,可以結合這些因素,產生一批目標人群。

比如1周內瀏覽護手霜相關商品超過10次的人群。

表結構設計

1. 店鋪、商品編成ID

2. 瀏覽過多少次、購買了多少某個商品

由於每個用戶在某個時間段內,都可能瀏覽或者購買多個店鋪或商品。如果每個商店,每個商品都使用一條記錄來隱藏,會產生很多很多的記錄。浪費空間,並且影響查詢效率。

PostgreSQL支援數群組類型,可以很好的完成這樣的任務,減少隱藏,同時支援陣列索引,提高查詢效率。

3. 表結構如下

3.1 範圍表,約幾十或上百條記錄

欄位和標題

class int,--維度,對應用戶標籤表的s1,s2,s3,s4
id int,--位移量(或者叫列舉值)
description--標題(例如 1-10000,10001-100000,。。。。。)

3.2 用戶標籤表

uid int primary key,--用戶ID
s1 int[],--瀏覽過的店鋪以及次數範圍(店鋪ID雜湊 +範圍表id)
s2 int[],--瀏覽過的商品以及次數範圍(商品ID雜湊 +範圍表id)
s3 int[],--購買的商品以及數量範圍(商品ID雜湊 +範圍表id)
s4 int[],-- ....其他維度以此類推
時間區間1,--比如按天,每天統計一次,寫入該表

3.3 次數階梯化

瀏覽次數,購買個數都是連續值,為了更好的進行挖掘,建議將其階梯化。

對應3.1的設計,例如1-10000一個階級,10001-100000又一個階級。

例子

曲目 s1對應的階梯
1 -> 0
2 -> 1-10
3 -> 11-100
4 -> 101-500
5 -> 501-
...
9 -> 10000+

3.4 將(商品、店鋪ID)與階梯群組成一個新的值 - 方法1

使用text[]例如 店鋪ID:OFFSET表示。text[]陣列效率可能不如整型陣列INT[],空間也比INT[]要大一點。

如果商務方可以容忍,使用text[]開發投入量小點。

例子

userid|s1|s2|s3
1|{'1:2', '109:9'}|{'2:2','88:19'}|{'2:1', '88:2'}

含義解釋:

·用戶ID:1,

·瀏覽了店鋪1(次數階梯=2)、店鋪109(次數階梯9),

·瀏覽了商品2(次數階梯=2)、商品88(次數階梯19),

·購買了商品2(次數階梯=1)、商品88(次數階梯2)。

3.5 將(商品、店鋪ID)與階梯群組成一個新的值 - 方法2

方法1用了text陣列,方法2將使用int/int8陣列,效率更高。

要使用一個int/int8新值表達兩層含義(原始店鋪、商品ID,以及階梯),需要方程式支援。

方程式設計如下,(方程式、常量)。

以流量店鋪次數(s1)欄位為例:

新值起始ID = new_start_val = 1--常量,用戶可以自由指定,但是固定下來了就不要變它
對應維度步長(比如流量店鋪的階梯數) = step = 9--常量,用戶可以自由指定(每個維度階數可以不一樣),但是固定下來了就不要變它
店鋪ID = dp_id--指原來的店鋪ID
int/int8新值 = new_val--生成的,帶有兩層含義(店鋪ID,階數)的新值
已知店鋪ID求new_val(寫入、查詢程序):
$new_val = new_start_val + (step+1)*(dp_id-1)
已知new_val求店鋪ID(翻譯程序):
$dp_id = 1 + (new_val-new_start_val)/(step+1)

例子(step=19階,new_start_val=1)

瀏覽店鋪ID=1,1階
瀏覽店鋪ID=192,15階
根據以上資訊、常量、方程式生成new_val陣列:
{1, 3821}
根據以上陣列、常量、方程式翻譯出店鋪ID:
{1, 192}

4. 分區

例如,建議每500萬或1000萬一個分區,查詢時,可以並行查詢,提高效率。

如果要快速圈得所有的用戶,建議使用並行查詢(plproxy,每個分區一個串連,並行查詢)。

如果要快速的得到用戶,流式返回,建議使用繼承(如果是多節點,可以使用postgres_fdw+pg_pathman,或者postgres_fdw+繼承),使用游標返回。

效能指標

幾十億用戶,每個用戶將時間區間的瀏覽過的店鋪、商品、購買過的商品以及數量級彙總成標籤化的陣列,產生萬億等級的user_tags群組。

根據tags從幾十億的用戶群體中圈選人群,能達到什麼樣的效能呢?

由於使用了索引,如果使用流式返回的話可以控制在10毫秒左右。

是不是頓時覺得剖析型的商務進入了毫秒年齡?

如果你對PostgreSQL接觸不多,可能會感到很驚奇,接觸多了就習慣了,PostgreSQL有很多功能會幫你解決很多問題,有時候甚至給你大開腦洞的。

即時設計

前面講了如何強烈影響獲得用戶,接下來我們要看看如何即時的更新TAG了。

流處理

目的是即時的更新使用者的TAG,比如一個用戶,一天可能產生幾萬比瀏覽的追蹤記錄,這些記錄要合併到他的標籤中。

如果活躍用戶達到億等級,那麼一天產生的更新流水就達到了萬億等級,這個怎麼能即時的在資料庫中處理呢?估計很多使用者會使用T+1的方式,放棄即時性。

但是實際上,並不是做不到的,比如我們可以使用PostgreSQL資料庫的流處理功能來實現這種超高流水的更新。

你可能要疑問了,資料庫能處理流嗎?資料如何在資料庫中完成即時的更新呢?

PostgreSQL社群的一個開源產品pipelinedb,(基於postgresql,與postgresql全相容),就是用來幹這個的,它會幫你即時的進行合併,(用戶可以設定合併的時間間隔,或者累計的ROWS變更數)達到閾值後,進行持久化的動作,否則會先持續的在記憶體中進行更新。

有兩篇本文可以參考

《"物聯網"串流套用 - 用PostgreSQL即時處理(萬億每天)》

《流計算風雲再起 -PostgreSQL攜PipelineDB力挺IoT》

當然如果用戶沒有即時的要求,T+1 就能滿足需求的話,你大可不必使用pipelinedb.

為什麼要在資料庫中完成串流

我們知道,標籤資料最後都要進到資料庫後,才能施展資料庫的圈人功能,完成圈人的查詢,如果不在資料庫中實現流計算,而是使用類似JSTROM的架構的話,實際上是使用JSTROM擋了一層,比如將1000億次的更新轉化成了1億的更新。

但是使用外部的流處理會引入一些問題

1. 額外增加了JSTROM所需的計算資源,並行效率實際上還不如pipelinedb

2. 用戶查資料的時效性不如直接放在資料庫中的流計算

3. 增加了開發成本

壓測

進入壓測環節,我選擇了一台32CORE,2塊SSD卡,512GB的記憶體的機器進行壓測。

存放3.2億用戶,每個用戶4個陣列欄位,每個欄位包括1000個元素,即4000*3.2億 = 1.28萬億user_tags。

用例1

10張表,每張表格儲存體1000萬用戶,4個標籤欄位,使用tsvector隱藏標籤。

使用rum索引。

postgres=# create tablespace tbs1 location '/u01/digoal/tbs1';
CREATE TABLESPACE

postgres=# create tablespace tbs2 location '/u02/digoal/tbs2';
CREATE TABLESPACE

do language plpgsql $$
declare
i int;
suffix text;
tbs text;
begin
for i in 0..10 loop
if i=0 then
suffix := '';
tbs := 'tbs1';
elsif i >=1 and i<=5 then
suffix := i::text;
tbs := 'tbs1';
else
suffix := i::text;
tbs := 'tbs2';
end if;
if i=0 then
execute 'create unlogged table test'||suffix||'(uid int primary key USING INDEX TABLESPACE '||tbs||', s1 tsvector, s2 tsvector, s3 tsvector, s4 tsvector) with (autovacuum_enabled=off, toast.autovacuum_enabled=off) tablespace '||tbs;
else
execute 'create unlogged table test'||suffix||'(uid int primary key USING INDEX TABLESPACE '||tbs||', s1 tsvector, s2 tsvector, s3 tsvector, s4 tsvector) inherits(test) with (autovacuum_enabled=off, toast.autovacuum_enabled=off) tablespace '||tbs;
end if;
execute 'create index idx_test'||suffix||'_s1 on test'||suffix||' using rum(s1 rum_tsvector_ops) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s2 on test'||suffix||' using rum(s2 rum_tsvector_ops) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s3 on test'||suffix||' using rum(s3 rum_tsvector_ops) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s4 on test'||suffix||' using rum(s4 rum_tsvector_ops) tablespace '||tbs;
end loop;
end;
$$;

select relname,reltablespace from pg_classwhere relname ~ 'test' order by 2,1;

產生測試資料的腳本

vi test.sql

set uid1 random(1,10000000)
set uid2 random(10000001,20000000)
set uid3 random(20000001,30000000)
set uid4 random(30000001,40000000)
set uid5 random(40000001,50000000)
set uid6 random(50000001,60000000)
set uid7 random(60000001,70000000)
set uid8 random(70000001,80000000)
set uid9 random(80000001,90000000)
set uid10 random(90000001,100000000)
insert into test1 (uid,s1,s2,s3,s4) select :uid1+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test2 (uid,s1,s2,s3,s4) select :uid2+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test3 (uid,s1,s2,s3,s4) select :uid3+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test4 (uid,s1,s2,s3,s4) select :uid4+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test5 (uid,s1,s2,s3,s4) select :uid5+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test6 (uid,s1,s2,s3,s4) select :uid6+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test7 (uid,s1,s2,s3,s4) select :uid7+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test8 (uid,s1,s2,s3,s4) select :uid8+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test9 (uid,s1,s2,s3,s4) select :uid9+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;
insert into test10 (uid,s1,s2,s3,s4) select :uid10+id, (select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)),(select array_to_tsvector(array_agg(trunc(5000000*random())||'_'||trunc(20*random()))) from generate_series(1,1000)) from generate_series(1,1000) t(id) on conflict do nothing;


nohup pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 1000000 >/dev/null 2>&1 &

標籤由500萬個唯一ID+20個唯一ID的群組程序,每個tsvector中存放1000個這樣的群組。

用例2

10張表,每張表格儲存體1000萬用戶,4個標籤欄位,使用text[]隱藏標籤。

索引使用的是GIN索引,其他與用例1一致。

do language plpgsql $$
declare
i int;
suffix text;
tbs text;
begin
for i in 0..10 loop
if i=0 then
suffix := '';
tbs := 'tbs1';
elsif i >=1 and i<=5 then
suffix := i::text;
tbs := 'tbs1';
else
suffix := i::text;
tbs := 'tbs2';
end if;
if i=0 then
execute 'create unlogged table test'||suffix||'(uid int primary key USING INDEX TABLESPACE '||tbs||', s1 text[], s2 text[], s3 text[], s4 text[]) with (autovacuum_enabled=off, toast.autovacuum_enabled=off) tablespace '||tbs;
else
execute 'create unlogged table test'||suffix||'(uid int primary key USING INDEX TABLESPACE '||tbs||', s1 text[], s2 text[], s3 text[], s4 text[]) inherits(test) with (autovacuum_enabled=off, toast.autovacuum_enabled=off) tablespace '||tbs;
end if;
execute 'create index idx_test'||suffix||'_s1 on test'||suffix||' using gin(s1 ) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s2 on test'||suffix||' using gin(s2 ) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s3 on test'||suffix||' using gin(s3 ) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s4 on test'||suffix||' using gin(s4 ) tablespace '||tbs;
end loop;
end;
$$;

select relname,reltablespace from pg_classwhere relname ~ 'test' order by 2,1;

用例3以及壓測

64張磁碟分割表格,每個分區500萬記錄,使用int陣列隱藏標籤,標籤總量400萬,每個用戶4000個推測標籤,確保圈人時可以圈到足夠多的人群。

同樣使用GIN索引圈人。

alter role postgres set gin_pending_list_limit='128MB';

do language plpgsql $$
declare
i int;
suffix text;
tbs text;
begin
for i in 0..64 loop
if i=0 then
suffix := '';
tbs := 'tbs1';
elsif i >=1 and i<=32 then
suffix := i::text;
tbs := 'tbs1';
else
suffix := i::text;
tbs := 'tbs2';
end if;
if i=0 then
execute 'create unlogged table test'||suffix||'(uid int primary key USING INDEX TABLESPACE '||tbs||', s1 int[], s2 int[], s3 int[], s4 int[]) with (autovacuum_enabled=off, toast.autovacuum_enabled=off) tablespace '||tbs;
else
execute 'create unlogged table test'||suffix||'(uid int primary key USING INDEX TABLESPACE '||tbs||', s1 int[], s2 int[], s3 int[], s4 int[]) inherits(test) with (autovacuum_enabled=off, toast.autovacuum_enabled=off) tablespace '||tbs;
end if;
execute 'create index idx_test'||suffix||'_s1 on test'||suffix||' using gin(s1 ) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s2 on test'||suffix||' using gin(s2 ) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s3 on test'||suffix||' using gin(s3 ) tablespace '||tbs;
execute 'create index idx_test'||suffix||'_s4 on test'||suffix||' using gin(s4 ) tablespace '||tbs;
end loop;
end;
$$;

select relname,reltablespace from pg_classwhere relname ~ 'test' order by 2,1;

生成測試資料的腳本

vi test1.sh

for ((i=1;i<=64;i++))
do
echo "set uid random($((($i-1)*5000000+1)),$(($i*5000000)))" > test$i.sql

echo "insert into test$i (uid,s1,s2,s3,s4) select :uid, (select array_agg(trunc(random()*4000000)) from generate_series(1,1000)) s1,(select array_agg(trunc(random()*4000000)) from generate_series(1,1000)) s2,(select array_agg(trunc(random()*4000000)) from generate_series(1,1000)) s3, (select array_agg(trunc(random()*4000000)) from generate_series(1,1000)) s4 on conflict do nothing;" >> test$i.sql

done

. ./test1.sh

開始生成測試資料

vi test2.sh

for ((i=1;i<=64;i++))
do
nohup pgbench -M prepared -n -r -P 1 -f ./test$i.sql -c 1 -j 1 -T 1000000 >/dev/null 2>&1 &
done

. ./test2.sh

輸出插完後將pengding list 合併

執行vacuum analyze或gin_clean_pending_list即可,參考

https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADMIN-INDEX

https://www.postgresql.org/docs/9.6/static/sql-vacuum.html

https://www.postgresql.org/docs/9.6/static/gin-implementation.html#GIN-FAST-UPDATE

圈人需求 - 效能測試

對用例3進行壓測

1. 圈人,10毫秒以內完成。

比如尋找s1包含3,s2包含4的人群

postgres=# begin;
BEGIN
Time: 0.030 ms
postgres=# declare a cursor for select uid from test where s1 @> array[1] and s2 @> array[4];
DECLARE CURSOR
Time: 6.679 ms
postgres=# fetch 100 in a;
uid
-----------
19246842
 118611240
 148504032
 185844649
(4 rows)
Time: 101.041 ms

這個人群太少,沒有代表性,我們找一個人群多一點的

postgres=# begin;
BEGIN
postgres=# declare a cursor for select uid from test where s1 @> array[1] or s2 @> array[4];
DECLARE CURSOR
Time: 3.484 ms
postgres=# fetch 100 in a;
uid
---------
 2911941
 2373506
 .....
29713
 3353782
 2836804
 1602067
(100 rows)
Time: 3.892 ms

postgres=# fetch 100 in a;
uid
---------
384170
 1332271
 4282941
 ......
 1190946
 4524861
 1110635
(100 rows)
Time: 4.005 ms

2. 分頁,前面已經提到了,使用游標。

3. 流式返回,前面的例子已經提到了。

4. 並行批量返回

並行批量返回,可以使用plproxy外掛程式,為每個分區指定一個並行,從而實現並行的批量返回。效果能好到什麼程度呢?

比如串列查詢,所有的分區表是依次查詢的,所以累加的時間比較長,例如,圈出15221個人,耗時113毫秒。

postgres=# explain (analyze,verbose,timing,costs,buffers) select uid from test where s1 @> array[1];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Append(cost=0.00..233541.24 rows=206876 width=4) (actual time=0.081..108.037 rows=15221 loops=1)
Buffers: shared hit=60641
->Seq Scan on public.test(cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
Output: test.uid
Filter: (test.s1 @> '{1}'::integer[])
->Bitmap Heap Scan on public.test1(cost=33.71..2901.56 rows=3188 width=4) (actual time=0.078..0.381 rows=242 loops=1)
Output: test1.uid
Recheck Cond: (test1.s1 @> '{1}'::integer[])
Heap Blocks: exact=238
Buffers: shared hit=243
->Bitmap Index Scan on idx_test1_s1(cost=0.00..32.91 rows=3188 width=0) (actual time=0.049..0.049 rows=242 loops=1)
Index Cond: (test1.s1 @> '{1}'::integer[])
Buffers: shared hit=5

...中間省略62個表

->Bitmap Heap Scan on public.test64(cost=34.00..2935.31 rows=3225 width=4) (actual time=0.068..0.327 rows=214 loops=1)
Output: test64.uid
Recheck Cond: (test64.s1 @> '{1}'::integer[])
Heap Blocks: exact=211
Buffers: shared hit=216
->Bitmap Index Scan on idx_test64_s1(cost=0.00..33.19 rows=3225 width=0) (actual time=0.041..0.041 rows=214 loops=1)
Index Cond: (test64.s1 @> '{1}'::integer[])
Buffers: shared hit=5
 Planning time: 2.016 ms
 Execution time: 109.400 ms
(519 rows)
Time: 113.216 ms

而並行查詢的效能則相當於單個分區的耗時, 約0.幾毫秒。

postgres=# explain (analyze,verbose,timing,costs,buffers) select uid from test1 where s1 @> array[1];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.test1(cost=33.71..2901.56 rows=3188 width=4) (actual time=0.085..0.383 rows=242 loops=1)
Output: uid
Recheck Cond: (test1.s1 @> '{1}'::integer[])
Heap Blocks: exact=238
Buffers: shared hit=243
->Bitmap Index Scan on idx_test1_s1(cost=0.00..32.91 rows=3188 width=0) (actual time=0.051..0.051 rows=242 loops=1)
Index Cond: (test1.s1 @> '{1}'::integer[])
Buffers: shared hit=5
 Planning time: 0.097 ms
 Execution time: 0.423 ms
(10 rows)
Time: 1.011 ms

使用並行,可以大幅升階整體的效能。

參考文件

《使用Plproxy設計PostgreSQL分散式資料庫》

《ASmart PostgreSQL extension plproxy 2.2 practices》

《PostgreSQL最佳實踐 - 水準分庫(基於plproxy)》

而如果你需要的是流式返回,則沒有必要使用並行。

sharding

當用戶數達到幾十億時,我們可以按用戶ID進行分區,使用多台主機。

當然了,如果你的主機空間足夠大,CPU核心足夠多,可以滿足商務的需求的話,完全沒有必要使用多台主機。

如果要使用多台主機,有哪些方法呢? 可以參考如下本文,也很簡單,幾步完成

你就把postgres_fdw節點當成MySQL的TDDL或者DRDS就好了,支援跨節點JOIN,條件,排序,彙總 的下推等,用起來和TDDL DRDS一樣的爽。

postgres_fdw是無狀態的,僅僅隱藏結構(分發規則),所以postgres_fdw節點本身也可以非常方便的橫向擴充。

《PostgreSQL9.6 單元化,sharding (based on postgres_fdw) - 核心層支援前傳》

《PostgreSQL9.6 sharding + 單元化 (based on postgres_fdw) 最佳實踐 - 通用水準分庫場景設計與實踐》

《PostgreSQL9.6 sharding based on FDW & pg_pathman》

《PostgreSQL9.5+ 高效磁碟分割表格實現 - pg_pathman》

基於置放圈人的需求與效能

這個需求直接落入PostgreSQL的懷抱,其實就是基於置放的KNN查詢,PostgreSQL可以通過GiST索引來支架這個需求。

在資料分區後,PostgreSQL通過歸併排序,依舊可以快速的得到結果。

例如,

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t order by id limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit(cost=0.72..1.13 rows=10 width=4) (actual time=0.158..0.165 rows=10 loops=1)
Output: t.id
Buffers: shared hit=3 read=4
->Merge Append(cost=0.72..819.74 rows=20001 width=4) (actual time=0.157..0.162 rows=10 loops=1)
Sort Key: t.id
Buffers: shared hit=3 read=4
->Index Only Scan using idx on public.t(cost=0.12..2.14 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
Output: t.id
Heap Fetches: 0
Buffers: shared hit=1
->Index Only Scan using idx1 on public.t1(cost=0.29..225.28 rows=10000 width=4) (actual time=0.107..0.107 rows=6 loops=1)
Output: t1.id
Heap Fetches: 6
Buffers: shared hit=1 read=2
->Index Only Scan using idx2 on public.t2(cost=0.29..225.28 rows=10000 width=4) (actual time=0.043..0.044 rows=5 loops=1)
Output: t2.id
Heap Fetches: 5
Buffers: shared hit=1 read=2
 Planning time: 0.181 ms
 Execution time: 0.219 ms
(20 rows)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t order by id ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Merge Append(cost=0.72..819.74 rows=20001 width=4) (actual time=0.043..10.324 rows=20000 loops=1)
Sort Key: t.id
Buffers: shared hit=149
->Index Only Scan using idx on public.t(cost=0.12..2.14 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)
Output: t.id
Heap Fetches: 0
Buffers: shared hit=1
->Index Only Scan using idx1 on public.t1(cost=0.29..225.28 rows=10000 width=4) (actual time=0.021..3.266 rows=10000 loops=1)
Output: t1.id
Heap Fetches: 10000
Buffers: shared hit=74
->Index Only Scan using idx2 on public.t2(cost=0.29..225.28 rows=10000 width=4) (actual time=0.017..3.309 rows=10000 loops=1)
Output: t2.id
Heap Fetches: 10000
Buffers: shared hit=74
 Planning time: 0.175 ms
 Execution time: 11.791 ms
(17 rows)

《PostgreSQL 百億地理位置資料 近鄰查詢效能》

12Core的機器,每次要求約0.8毫秒返回,TPS約8萬。

小結

回到圈人系統的三個核心問題,

精准,本文未涉及,屬於資料採礦系統的事情,我們可以在下一篇本文介紹(使用PostgreSQL, Greenplum 的MADlib機器學習服務庫)。

即時,即時的更新標籤,本文已給出了在資料庫中進行串流的解決方案,相比外部流處理的方案,節約資源,減少開發成本,提高開發效率,提高時效性。

高效,相比傳統的方案,使用PostgreSQL以及陣列的GIN索引功能,實現了本文在萬億USER_TAGS的情況下的毫秒等級的圈人功能。

其他相關本文

《"物聯網"串流套用 - 用PostgreSQL即時處理(萬億每天)》

《為了部落 - 如何通過PostgreSQL基因配對,產生優良下一代》

《流計算風雲再起 -PostgreSQL攜PipelineDB力挺IoT》

《剖析加速引擎黑科技 -LLVM、列存、多核並行、運算元複用 大聯姻 - 一起來開啟PostgreSQL的百寶箱》

《金融風控、公安刑偵、社會關係、人脈剖析等需求剖析與資料庫實現 - PostgreSQL圖資料庫場景套用》

《即時資料交換平臺 -BottledWater-pg with confluent》

《PostgreSQL在視訊、圖片去重,影像搜尋商務中的套用》

《基於 阿裡雲 RDSPostgreSQL 打造即時用戶畫像推薦系統》

《PostgreSQL與 12306 搶火車票的思考》

《門禁廣告銷售系統需求剖析 與PostgreSQL資料庫實現》

《聊一聊雙十一背後的技術 - 物流、動態路徑規劃》

《聊一聊雙十一背後的技術 - 分詞和搜尋》

《聊一聊雙十一背後的技術 - 不一樣的秒殺技術, 裸秒》

《聊一聊雙十一背後的技術 - 毫秒分詞算啥, 試試正則和相似性》

《PostgreSQL9.6 引領開來源資料庫攻克多核平行計算難題》

《PostgreSQL前世今生》

《如何建立GIS測試環境 - 將openstreetmap的樣本資料匯入PostgreSQLPostGIS庫》

《PostgreSQL9.6 單元化,sharding (based on postgres_fdw) - 核心層支援前傳》

《PostgreSQL9.6 sharding + 單元化 (based on postgres_fdw) 最佳實踐 - 通用水準分庫場景設計與實踐》

《PostgreSQL9.6 sharding based on FDW & pg_pathman》

《PostgreSQL9.5+ 高效磁碟分割表格實現 - pg_pathman》

《PostgreSQL資料庫安全指南》

《PostgreSQL9.6 黑科技 bloom 演算法索引,一個索引支架任意列群組查詢》

《PostgreSQL使用遞迴SQL 找出資料庫物件之間的相依性屬性》

《用PostgreSQL描繪人生的高潮、尿點、低谷 - 視窗/幀 or 斜率/導數/曲率/微積分?》

《用PostgreSQL找回618秒逝去的青春 - 遞迴收斂優化》

《PostGIS 在 O2O套用中的優勢》

《PostgreSQL 百億地理位置資料 近鄰查詢效能》

《使用Plproxy設計PostgreSQL分散式資料庫》

《ASmart PostgreSQL extension plproxy 2.2 practices》

《PostgreSQL最佳實踐 - 水準分庫(基於plproxy)》

相關產品:

1. 剖析型資料庫

2. 商務即時監控服務

3. 巨量資料計算服務(MaxCompute)

4. 雲端服務器ECS

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.