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 範圍表，約幾十或上百條記錄
字段和描述
3.2 使用者标簽表
3.3 次數階梯化
浏覽次數，購買個數都是連續值，為了更好的進行挖掘，建議将其階梯化。
對應3.1的設計，例如1-10000一個階級，10001-100000又一個階級。
例子
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)字段為例：
例子(step=19階, new_start_val=1)
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變更數）達到門檻值後，進行持久化的動作，否則會先持續的在記憶體中進行更新。
有兩篇文章可以參考
<a href="https://github.com/digoal/blog/blob/master/201512/20151215_01.md">《"物聯網"流式處理應用 - 用postgresql實時處理(萬億每天)》</a>
<a href="https://github.com/digoal/blog/blob/master/201612/20161220_01.md">《流計算風雲再起 - postgresql攜pipelinedb力挺iot》</a>
當然如果使用者沒有實時的要求，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。
10張表，每張表存儲1000萬使用者，4個标簽字段，使用tsvector存儲标簽。
使用rum索引。
産生測試資料的腳本
标簽由500萬個唯一id+20個唯一id的組合過程，每個tsvector中存放1000個這樣的組合。
10張表，每張表存儲1000萬使用者，4個标簽字段，使用text[]存儲标簽。
索引使用的是gin索引，其他與用例1一緻。
64張分區表，每個分區500萬記錄，使用int數組存儲标簽，标簽總量400萬，每個使用者4000個随機标簽，確定圈人時可以圈到足夠多的人群。
同樣使用gin索引圈人。
生成測試資料的腳本
開始生成測試資料
輸出插完後将pengding list 合并
執行vacuum analyze或gin_clean_pending_list即可，參考
<a href="https://www.postgresql.org/docs/9.6/static/functions-admin.html#functions-admin-index">https://www.postgresql.org/docs/9.6/static/functions-admin.html#functions-admin-index</a>
<a href="https://www.postgresql.org/docs/9.6/static/sql-vacuum.html">https://www.postgresql.org/docs/9.6/static/sql-vacuum.html</a>
<a href="https://www.postgresql.org/docs/9.6/static/gin-implementation.html#gin-fast-update">https://www.postgresql.org/docs/9.6/static/gin-implementation.html#gin-fast-update</a>
對用例3進行壓測
1. 圈人，10毫秒以内完成。
比如查找s1包含3, s2包含4的人群
這個人群太少，沒有代表性，我們找一個人群多一點的
2. 分頁，前面已經提到了，使用遊标。
3. 流式傳回，前面的例子已經提到了。
4. 并行批量傳回
并行批量傳回，可以使用plproxy插件，為每個分區指定一個并行，進而實作并行的批量傳回。效果能好到什麼程度呢？
比如串行查詢，所有的分片表是依次查詢的，是以累加的時間比較長，例如，圈出15221個人，耗時113毫秒。
而并行查詢的性能則相當于單個分區的耗時, 約0.幾毫秒。
使用并行，可以大幅提升整體的性能。
參考文檔
<a href="https://github.com/digoal/blog/blob/master/201005/20100511_01.md">《使用plproxy設計postgresql分布式資料庫》</a>
<a href="https://github.com/digoal/blog/blob/master/201110/20111025_01.md">《a smart postgresql extension plproxy 2.2 practices》</a>
<a href="https://github.com/digoal/blog/blob/master/201608/20160824_02.md">《postgresql 最佳實踐 - 水準分庫(基于plproxy)》</a>
而如果你需要的是流式傳回，則沒有必要使用并行。
當使用者數達到幾十億時，我們可以按使用者id進行分片，使用多台主機。
當然了，如果你的主機空間足夠大，cpu核心足夠多，可以滿足業務的需求的話，完全沒有必要使用多台主機。
如果要使用多台主機，有哪些方法呢? 可以參考如下文章，也很簡單，幾步完成
你就把postgres_fdw節點當成mysql的tddl或者drds就好了，支援跨節點join，條件，排序，聚合 的下推等，用起來和tddl drds一樣的爽。
postgres_fdw是無狀态的，僅僅存儲結構（分發規則），是以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>
<a href="https://github.com/digoal/blog/blob/master/201610/20161027_01.md">《postgresql 9.6 sharding based on fdw & pg_pathman》</a>
<a href="https://github.com/digoal/blog/blob/master/201610/20161024_01.md">《postgresql 9.5+ 高效分區表實作 - pg_pathman》</a>
這個需求直接落入postgresql的懷抱，其實就是基于位置的knn查詢，postgresql可以通過gist索引來支撐這個需求。
在資料分片後，postgresql通過歸并排序，依舊可以快速的得到結果。
例如，
<a href="https://yq.aliyun.com/articles/2999">《postgresql 百億地理位置資料 近鄰查詢性能》</a>
12core的機器，每次請求約0.8毫秒傳回，tps約8萬。
回到圈人系統的三個核心問題，
精準，本文未涉及，屬于資料挖掘系統的事情，我們可以在下一篇文章介紹（使用postgresql, greenplum 的 madlib機器學習庫）。
實時，實時的更新标簽，本文已給出了在資料庫中進行流式處理的解決方案，相比外部流處理的方案，節約資源，減少開發成本，提高開發效率，提高時效性。
高效，相比傳統的方案，使用postgresql以及數組的gin索引功能，實作了本文在萬億user_tags的情況下的毫秒級别的圈人功能。
<a href="https://github.com/digoal/blog/blob/master/201606/20160621_01.md">《為了部落 - 如何通過postgresql基因配對，産生優良下一代》</a>
<a href="https://github.com/digoal/blog/blob/master/201612/20161216_01.md">《分析加速引擎黑科技 - llvm、列存、多核并行、算子複用 大聯姻 - 一起來開啟postgresql的百寶箱》</a>
<a href="https://github.com/digoal/blog/blob/master/201612/20161213_01.md">《金融風控、公安刑偵、社會關系、人脈分析等需求分析與資料庫實作 - postgresql圖資料庫場景應用》</a>
<a href="https://github.com/digoal/blog/blob/master/201612/20161205_02.md">《實時資料交換平台 - bottledwater-pg with confluent》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161126_01.md">《postgresql 在視訊、圖檔去重，圖像搜尋業務中的應用》</a>
<a href="https://github.com/digoal/blog/blob/master/201610/20161021_01.md">《基于 阿裡雲 rds postgresql 打造實時使用者畫像推薦系統》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161124_02.md">《postgresql 與 12306 搶火車票的思考》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161124_01.md">《門禁廣告銷售系統需求剖析 與 postgresql資料庫實作》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161114_01.md">《聊一聊雙十一背後的技術 - 物流、動态路徑規劃》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161115_01.md">《聊一聊雙十一背後的技術 - 分詞和搜尋》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161117_01.md">《聊一聊雙十一背後的技術 - 不一樣的秒殺技術, 裸秒》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161118_01.md">《聊一聊雙十一背後的技術 - 毫秒分詞算啥, 試試正則和相似度》</a>
<a href="https://github.com/digoal/blog/blob/master/201610/20161001_01.md">《postgresql 9.6 引領開源資料庫攻克多核并行計算難題》</a>
<a href="https://github.com/digoal/blog/blob/master/201609/20160929_02.md">《postgresql 前世今生》</a>
<a href="https://github.com/digoal/blog/blob/master/201609/20160906_01.md">《如何建立gis測試環境 - 将openstreetmap的樣本資料導入postgresql postgis庫》</a>
<a href="https://github.com/digoal/blog/blob/master/201506/20150601_01.md">《postgresql 資料庫安全指南》</a>
<a href="https://github.com/digoal/blog/blob/master/201605/20160523_01.md">《postgresql 9.6 黑科技 bloom 算法索引，一個索引支撐任意列組合查詢》</a>
<a href="https://github.com/digoal/blog/blob/master/201607/20160725_01.md">《postgresql 使用遞歸sql 找出資料庫對象之間的依賴關系》</a>
<a href="https://github.com/digoal/blog/blob/master/201612/20161203_01.md">《用postgresql描繪人生的高潮、尿點、低谷 - 視窗/幀 or 斜率/導數/曲率/微積分?》</a>
<a href="https://github.com/digoal/blog/blob/master/201612/20161201_01.md">《用postgresql找回618秒逝去的青春 - 遞歸收斂優化》</a>
<a href="https://yq.aliyun.com/articles/50922">《postgis 在 o2o應用中的優勢》</a>