基于 阿裡雲 RDS PostgreSQL 打造實時使用者畫像推薦系統

postgresql , 實時推薦系統 , 使用者畫像 , 标簽 , tag , 比特位

使用者畫像在市場營銷的應用重建中非常常見，已經不是什麼新鮮的東西，比較流行的解決方案是給使用者貼标簽，根據标簽的組合，圈出需要的使用者。

通常畫像系統會用到寬表，以及分布式的系統。

寬表的作用是存儲标簽，例如每列代表一個标簽。

但實際上這種設計不一定是最優或唯一的設計，本文将以postgresql資料庫為基礎，給大家講解一下更加另類的設計思路，并且看看效率如何。

假設有一個2b的實時使用者推薦系統，每個appid代表一個b。

業務資料包括appid，userids，tags。（2b的使用者id，最終使用者id，标簽）

業務沒有跨appid的資料交換操作需求，也就是說僅限于appid内的使用者推薦。

查詢局限在某個標明的appid，以及tag組合，搜尋符合條件的userid，并将userid推送給使用者。

資料總量約10億，單個appid的使用者數最大約1億。

tag總數設計容量為1萬個。

查詢需求: 包含，不包含，或，與。

并發幾百，rt 毫秒級。

接下來我會列舉4個方案，并分析每種方案的優缺點。

通常表的寬度是有限制的，以postgresql為例，一條記錄是無法跨page的（變長字段存儲到toast存儲，以存儲超過1頁大小的列，頁内隻存儲指針），這就使得表的寬度受到了限制。

例如8kb的資料塊，可能能存下接近2000個列。

如果要為每個tag設計一個列，則需要1萬個列的寬表。

相信其它資料庫也有類似的限制，1萬個列的寬表，除非改造資料庫核心，否則無法滿足需求。

那麼可以使用appid+userid作為pk，存儲為多個表來實作無限個tag的需求。 以單表1000個列為例，10個表就能滿足1萬個tag的需求。

為了提升效率，要為每個tag字段建立索引，也就是說需要1萬個索引。

如果tag的組合跨表了，還有join操作。

1. 優點

沒有用什麼特殊的優化，幾乎所有的資料庫都支援。

2. 缺點

性能不一定好，特别是查詢組合條件多的話，性能會下降比較明顯，例如(tag1 and tag2 and (tag4 or tag5) or not tag6) 。

使用數組代替tag列，要求資料庫有數組類型，同時有數組的高效檢索能力，這一點postgresql可以很好的滿足需求。

1. 資料結構

單個數組最大長度1gb(約支援2.6億個tag)

2. 按appid分區，随機分片

3. query文法

3.1 包含array2指定的所有tag

數組1包含數組2的所有元素

支援索引檢索

3.2 包含array2指定的tag之一

數組1與數組2有重疊元素

3.3 不包含array2指定的所有tag

數組1與數組2沒有重疊元素

不支援索引檢索

4. 例子

5. 優點

可以存儲很多tag，幾億個足夠用啦(行業内有1萬個tag的已經是非常多的啦)。

支援數組的索引查詢，但是not不支援索引。

6. 缺點

資料量還是有點大，一條記錄1萬個tag，約80kb。

1億記錄約8tb，索引還需要約8tb。

不是所有的資料庫都支援數組類型。

使用bit存儲tag，0和1表示有或者沒有這個tag。

單個bit字段最大支援1gb長度bit流（支援85億個tag）

每個bit代表一個tag

3.1 包含bit2指定的所有tag(需要包含的tag對應的bit設定為1，其他為0)

3.2 包含bit2指定的tag之一(需要包含的tag對應的bit設定為1，其他為0)

3.3 不包含bit2指定的所有tag (需要包含的tag對應的bit設定為1，其他為0)

可以存儲很多tag，85億個tag足夠用啦吧(行業内有1萬個tag的已經是非常多的啦)。

1萬個tag，占用1萬個bit，約1.25kb。

1億記錄約120gb，無索引。

沒有索引方法，查詢是隻能通過并行計算提升性能。

postgresql 9.6 支援cpu并行計算，1億使用者時，可以滿足20秒内傳回，但是會消耗很多的cpu資源，是以查詢的并行度不能做到很高。

有沒有又高效，又節省資源的方法呢？

答案是有的。

因為查詢通常是以tag為組合條件，取出複合條件的userid的查詢。

是以反過來設計，查詢效果就會很好，以tag為次元，userid為比特位的設計。

我們需要維護的是每個tag下有哪些使用者，是以這塊的資料更新量會很大，需要考慮增量合并與讀時合并的設計。

資料流如下，資料可以快速的寫入

讀取時，使用兩部分資料進行合并，一部分是tag的計算結果，另一部分是未合并的明細表的結果，兩者merge。

當然，如果可以做到分鐘内的合并延遲，業務也能夠忍受分鐘的延遲的話，那麼查詢是就沒有merge的必要了，直接查結果，那會非常非常快。

1. query

1.1 包含這些tags的使用者

結果為bit位為1的使用者

1.2 不包含這些tags的使用者

結果為bit位為0的使用者

1.3 包含這些tags之一的使用者

2. 優點

因為資料存儲的次元發生了變化，采用以查詢為目标的設計，資料的查詢效率非常高。

3. 缺點

由于使用了比特位表示userid，是以必須有位置與userid的映射關系。

需要維護使用者id字典表，需要使用增量合并的手段減少資料的更新頻率。

會有一定的延遲，通常可以控制在分鐘内，如果業務允許這樣的延遲，則非常棒。

通常業務的userid會周期性的失效（例如僵屍userid，随着時間可以逐漸失效），那麼需要周期性的維護使用者id字典，同時也要更新userid比特資訊。

架構如圖

本文會用到幾個新增的function，這幾個function很有用，同時會加入阿裡雲的rds postgresql中。

資料庫内置的bit操作函數請參考源碼

src/backend/utils/adt/varbit.c

使用bit存儲使用者

userid int8表示，可以超過40億。

rowid int表示，也就是說單個appid不能允許超過20億的使用者，從0開始自增，配合bit下标的表示。

appid int表示，不會超過40億個。

1. 字典表, rowid決定map順序，使用視窗查詢傳回順序。

插入使用者字典表的函數，可以産生無縫的連續rowid。

如果以上調用傳回null，說明插入失敗，可能違反了唯一限制，應用端重試即可。

壓測以上函數是否能無縫插入，壓測時raise notice可以去掉。

驗證

插入速度，無縫需求，完全符合要求。

生成1億測試使用者,appid=1, 用于後面的測試

2. 實時變更表

為了提高寫入性能，資料将實時的寫入這張表，背景增量的将這個表的資料合并到tag表。

生成1.5千萬測試資料(appid=1 , userid 總量20億，随機産生, 新增tagid 範圍1-10000, 删除tagid 範圍1-1000)

3. tag + userids bitmap 表，這個是最關鍵的表，查詢量很大，從t_user_tags增量合并進這個表。

生成1萬個tag的測試資料，每個tag包含1億個使用者的bit。友善下面的測試

這個名額顯示了使用者勾選一些tag組合後，圈定并傳回使用者群體的性能。

測試方法很簡單： 包含所有，不包含，包含任意。

1. 包含以下tag的使用者id

測試sql如下

性能資料

2. 不包含以下tag的使用者

3. 包含以下任意tag

1. 結合bit_posite，可以實作正向取若幹使用者，反向取若幹使用者(例如有100萬個結果，本次推廣隻要1萬個使用者，而且要最近新增的1萬個使用者，則反向取1萬個使用者即可)。

2. 結合get_bit則可以實作截取某一段bit，再取得結果，很好用哦。

新增資料即往t_user_tags表插入資料的性能。

測試如下

性能資料(單步操作的qps約12.2萬，包括新增，删除tag)

更新的動作需要拆成兩個部分，新增和删除，不要合并到一條記錄中。

資料的合并包括3個部分，

1. 更新使用者字典表t_userid_dic，

2. 批量擷取并删除t_user_tags的記錄，

3. 合并标簽資料到t_tags。

以上三個動作應該在一個事務中完成。

考慮到t_tags表userids字段1億bit約12.5mb，更新單條記錄耗時會比較長，是以建議采用并行的模式，每個tag都可以并行。

從t_user_tags表取出資料并更新資料字典，被取出的資料标記為允許合并。

此操作沒有必要并行，串行即可，搞個背景程序無限循環。

由于批量操作，可能申請大量的ad lock, 是以需要增加max_locks_per_transaction, 資料庫參數調整

雖然沒有必要并行，但是這個函數需要保護其并行的安全性，是以接下來驗證并行安全性

驗證并行結果的安全性，結果可靠

前面已經處理了字典的更新，接下來就可以将t_user_tags.dic=true的資料，合并到t_tags中。

考慮更新t_tags可能較慢，盡量提高并行度，不同tag并行。

-- 不要對同一個appid并行使用appid與appid+tag的模式.

速度測試

驗證方法，merge的結果資料與被merge的資料一緻即可。

符合要求

找到userid對應的rowid, 根據userids rowid位置的bit，判斷是否有該tag.

查詢單個使用者有哪些tag是一個比較重的操作，如果碰到有很多tag并且使用者數非常龐大時，建議使用并行。

大appid，按user号段切分

appid+号段分片

如果一個appid 1萬個tag，1億使用者，隻占用120gb。

通常是出現傾斜時才需要重分布。

postgresql使用postgres_fdw可以原生支援資料分片。

參考我之前寫的文檔

<a href="https://github.com/digoal/blog/blob/master/201610/20161004_01.md">《postgresql 9.6 單元化,sharding (based on postgres_fdw) - 核心層支援前傳》</a>

<a href="https://github.com/digoal/blog/blob/master/201610/20161005_01.md">《postgresql 9.6 sharding + 單元化 (based on postgres_fdw) 最佳實踐 - 通用水準分庫場景設計與實踐》</a>

同一個appid的一批tag必須在一個節點

機器學習，生成tag，本文不涉及。

參考 madlib、r ，聚類分析 postgresql都可以非常好的支援。