《PostgreSQL在阿里的應用》要點：
本文介紹了PostgreSQL在阿里的應用，希望對您有用。如果有疑問，可以聯系我們。

相關主題：PostgreSQL教程

文章整理自周正中在 GOPS2017 · 深圳站的演講.高效運維社區致力于陪伴您的職業生涯,與您一起愉快的成長.

作者簡介

周正中(digoal)
PostgreSQL中國社區發起人之一、杭州分會會長,PostgreSQL中國社區大學發起人之一,10余項數據庫相關專利.
就職于阿里云,ApsaraDB內核組.

前言

ostgreSQL這幾年的發展非常迅猛,在國內掀起了一波PostgreSQL的熱潮,但運維人才還是比較緊缺,所以在一些公司沒有大面積鋪開,不過不用擔心,很多云廠商都提供了PostgreSQL的數據庫服務.

阿里云的RDSPostgreSQL除了提供公有云服務,同時也對阿里巴巴集團提供內部的服務.接下來我會分享幾個在阿里巴巴內部使用PostgreSQL的一些場景.大家可以想想思考一下,如果用其他數據庫和技術手段怎么解決這些問題.

• 海量導購文實時去重
• 精準廣告投放
• TOB 實時畫像
• 任意字段組合
• 任意字段模糊匹配

1. 海量導購文實時去重

1.1 導購業務介紹

首先是海量導購文實時去重.在日常生活中特別是妹子很喜歡看導購的推送消息(比如每日白菜價)；特別是家庭主婦,在家里沒事就瀏覽白菜價,如果家里有小孩的,小孩才一兩個月,已經買到十幾歲的衣服了,這和導購推送有密切關系.

這么多的導購文章,每一篇文章都會推很多的商品,如果你是一個用戶每天翻看這些文章都是一樣的商品,是很令人討厭的.

所以在導購平臺就有一個很重要的工作,得過濾這些別人已經推薦過的商品,別人推薦過的商品你就不要再推薦了.

比如說每日白菜精選的文章里可能會涉及到幾十個商品,整個導購平臺可能會沉積上億的文章,如果平均五十個商品的話,就會有五十億個商品.當然里面有重疊,一篇文章里跟另外一篇文章可能有一兩個重疊,這是沒有關系的,但是你不能80%以上都重疊,這個重疊比例是需要可以設定的.

1.2 導購文審核發展歷程

因為店家會去做推廣,就會有傭金,網絡寫手會為了傭金去寫導購文章.但是為了防止出現盜文現象,就需要審核導購文章,最原始的做法是什么樣的?比如我剛新建了這樣一個導購平臺,用戶數也不是特別多,這時候請一些較為廉價的勞動力來幫你解決審核的問題,最早期的為勞動力密集型.

發展到第二代,用計算機幫你做這個事情,新提交一個文章的時候,要在上億的文章里去辨別跟我剛剛提交的商品的重復率是什么樣的,涉及的運算量非常大,因此并不能做到實時的審核,通常是隔天的.

對于導購文章的編輯來說,這個效率是低下的,網絡寫手提交文章后,第二天才能告知有沒有通過,才能發到網站上面,這樣可能就錯過了商家的營銷時機.

1.2.1 數據結構問題

回到原始的數據去看,每一篇導購文章里面涉及到50個商品,按平均數來,商品使用一個數組來存儲,這里面的每個數值對應的都是商品的 ID,每個記錄會涉及到大概 50 個這樣的值.

當用戶提交一個新的導購文章來的時候,我們看到又有一堆的值進來,怎么做呢?

我們需要去比對庫里的每一條記錄,看他們重疊的元素有多少個,比如說新上的文章推薦了十個商品,與歷史導購文章中某記錄重疊的有八個商品 ID,意味著你新上傳的文章有 80% 跟其中的某一篇文章的商品是重疊的,審核結果是拒絕.

1.2.2 PostgreSQL GIN索引應用

在 PostgreSQL 里面有一個什么技術能夠幫你高效實現應用場景呢?我們用了 GIN 索引,它就是一個倒排索引,在你的一條記錄里,可以對數組去建索引,一個數組有50個商品,第一列是一個行號,解開之后一個行號對應這么多值,倒轉一下會變成這個值對應的行號是什么,就是幫你做了翻轉.

有什么用呢?做完了翻轉,用戶上傳了一篇新的導購文章,里面涉及到假設商品的 ID,我們看最左邊的一列,1,3,101,198,上傳這些商品 ID 上來,在索引上怎么搜索,比如說對1這個 ID,因為它有行號,里面對應的是數據塊的 ID 加上這條記錄在數據塊的偏移,對應的號拿出來之后,在第一個數據塊出現過,通過索引搜索出來,在第101個數據塊里也出現過,數據庫會幫你把每個數據塊里的命中商品總數給記錄下來,就變成了最下面的一行記錄,213 422,什么意思呢?代表是在101這個數據塊里有命中四個商品ID.

1.2.3 多重過濾提高檢索效率

比如設置了重復數>4,通過索引可以直接拒絕發布.如果設置為>=3那么只要提取出第49號數據塊和第101的數據塊的數據.進入第二道工序.

剛才講的是做的第一層過濾,第二層過濾是幫你定位到兩個數據塊了,然后你去每一條檢查,因為每個數據塊涉及的記錄也就是幾百條,整個下來效率就非常高.

1.2.4 仿真數據測試結果

根據真實數據的特征,構建一批仿真數據.

被推薦的商品數總量：超過1千萬,每一篇導購文章平均下來涉及的商品,從歷史數據來看是11到50個商品,一篇文章會推11到50個商品給你.

熱點商品：是說非常大的店鋪給的傭金非常高,很多人愿意去寫文章推薦這種商品,這是熱點商品.

在一千萬的商品里占到了2%的樣子,我們來看熱點商品被推薦的次數,比如說在整個6千萬的推薦文章里面,有一千萬的文章是推薦熱點商品,接下來看一下效果.

1.2.5 PostgreSQL優化時效測試

這些測試對應的就是實際的應用場景,一篇新的文章上來之后,多快的時間能夠告訴你有沒有人跟你重疊,如果你是普通商品的話,過濾39個跟你重復的,我就把文章替掉,不讓你發布了.

如果一篇文章里推的都是熱點商品,因為被推薦的次數多,記錄所涉及的數據塊更多,因此一級過濾出來的數據塊比較多,二級過濾做的就比較多,但是也能夠達到15個毫秒.

吞吐量達到1萬,相比以往隔天要告訴你能夠審核通過,現在可以做到實時響應.

這是 PostgreSQL 在阿里的導購平臺的應用,因為使用其他的技術根本沒法解決這個問題,因為我們在阿里有一個 ATA 的技術論壇發帖子,剛好他們看到這個技術,找到我們團隊,去給他們做了這個方案并上線.

2、精準廣告投放

第二個應用場景是精準的廣告投放,這個對應的是廣告營銷的產品,數量級也是龐大的.我們看這個場景的介紹,在你使用產品的時候一些瀏覽行為,比如你瀏覽了哪些店鋪、購買了哪些商品,這些在數據平臺里都是有跟蹤記錄的,比如說每個人瀏覽了哪些店鋪,瀏覽了多少次,瀏覽了哪些商品,這些數據就可以用來做精準的廣告投放.

2.1 業務介紹

什么意思呢?比如說最近經常瀏覽化妝品或者所關注的商品,你在購買前可能會一直瀏覽,根據這些行為,可以圈出一些最近都在關注化妝品的人群,賣化妝品的商家就可以定向推送運營的活動,把消息告訴這些人,因為這些人可能最近就要買了.

對于一個營銷系統來說,它的非常重要的指標,一個是精準性,另外是實時性,如果你今天瀏覽完之后可能下單了,第二天再告訴你沒有任何意義,就不會再給你重復推薦了.因此這兩個重要的因素一結合,我們看PostgreSQL在里面怎么幫助平臺達到效果.

首先看數據結構,一個是用戶ID,然后是店鋪軌跡,你瀏覽這個店鋪多少次,瀏覽這個商品多少次,最后你買了這個商品多少數量,會有一些這樣的軌跡,然后會有地理位置,基于地理位置的推薦,也會存位置信息.根據這些,我們可以根據時間區間、位置、瀏覽的店鋪、瀏覽的商品等條件,圈選人群.

再看數據量,整個量級是百億級別,商鋪是億級別,單個用戶軌跡平均瀏覽一千個用戶,還有瀏覽的商品量級以及購買的商品量級,基本上是在千這個級別,當然這個級別估得比較高,設計時需要考慮未來的體量.

2.2 階梯化舉例

業務需求剛才已經講過,根據某個商品,比如瀏覽某個商品超過多少次的人群,某個區域瀏覽某些商品超過多少次的人群,相當于是精準圈人的意思.我們怎么對這個場景做設計?

首先是數字,瀏覽了多少次商品,是個精準的數字,如果說這些完完全全精準的存在里面的話,不利于后期的優化.

所以我們首先會做一個階梯化,這是汽車的變速箱,比如說10AT的汽車,對于精準營銷來說可能十檔不夠,要做得精準,我就要投放超過瀏覽次數100次的.

這是檔位階梯化,對行為軌跡精準次數做范圍,根據不同范圍定階梯,定完階梯,我的每個用戶 ID 加上對應的軌跡,相當于是我給你撥一個檔位一樣,比如六檔,說明你這個用戶是落在六檔這個范圍,這樣做之后就可以把左上角的這個值,這是原來的值,代表這個用戶瀏覽了這個用戶98次,我把它階梯化之后就變成 711 767 740,表示它是1檔,拼成一個值,把這個東西加上去.

圖中所示公式為轉換公式,怎么把老的值轉成新的值,原來我用兩個新的元素表示的數字,變成一個數字來表示.然后我就可以對軌跡建倒排索引,這個索引建完之后,就可以實現定向圈人.比如說瀏覽的商品包含什么,比如包含某一個商品的 ID 加檔位數,這是對數組的操作.

瀏覽了十個店家任意一家超過多少次的,我就用 overlap 的做法做.

2.3 實現定向圈人

當用戶量是 3.2 億加上 64 個分區數,標簽總量是 400 萬,個人平均標簽數是4千個,在一毫秒就可以挖出一萬多的人群出來,實現了精準的實時營銷.從量級來算,如果不用數組類型的話要存多少條記錄?

每個人都有四千個標簽,如果不用數組的話,每一個就是一條記錄,這樣乘3.2億,相當于是1.2萬億的數字,而使用一臺主機就可以解決.使用 PostgreSQL 的數組和GIN索引,巧妙的解決了業務的問題.

3、TOB 實時畫像

我們再看 TOB 實時畫像,這是阿里云對外的一個項目,TOB 的實時畫像的業務.

3.1業務介紹

跟前面一個例子項目非常類似,差別就在于 TAG 數不一樣.前面講的例子TAG數有400萬個,在這個系統里面是1萬個 TAG 來描述 TOB 的用戶,就好像我給你看相一樣,貼一萬個 TAG,基本上把特征描述得清楚了.

當初設計1萬個 TAG,基本上一千多個列已經是極限了,因為一條記錄是不能跨數據塊的,所以對于這種超過兩千個字段的表,要么就是拆表,根據ID去聯合起來.

因此一張表肯定是搞不定的,搜索的條件是按照包含哪些 TAG 并且不包含哪些 TAG,這種比較類似于挖掘型的操作,原來使用了8臺物理機,1億個用戶,1萬臺 TAG 去解決這個問題.

3.2 圈人-業務指標

思考一下將來的設計,這個用戶如果只是換一個平臺可能沒什么興趣,他是說將來要用戶量級再漲一倍,同時希望壓縮成本,因為用了很多成本.

TAG 數有1萬,每個 TAG 一列,如果存一張表肯定是搞不定的,用戶規模加 TAG 是一萬億 user tags,貼標簽、刪除標簽、更新標簽都要求分鐘級的延遲.

由于是 TOB 的系統,查詢的并發要求200到300,用戶根據 TAG 字段查出包含、不包含、或者多個字段的組合.最后就是響應時間的要求,這種查詢響應時間要毫秒級.

3.3 方案介紹

我們來想怎么設計這個表結構,因為一張表是存不下一萬個字段的,如果每個 TAG 一個字段的話,你要寫很多的 and 跟 or,基本上做不到毫秒,80臺機器也做不到,這就是問題.是不是每個字段都要建索引,第二是超寬表怎么解決,這都是場景要面臨的問題.

我們來看解決方案的優缺點.首先是用數組存,一個數組最大長度是1個G,如果我用 int4 做標簽的話可以存 2.6 億個 TAG,然后還可以進行分區.

query 的寫法也簡單,原來要包含某一些 TAG 的數組出來,包含兩個的就可以.指定 TAG 之一的話就是看有沒有相交,最后是不包含,是沒有辦法支持索引的,這是方案一.

再看方案二,把 TAG 變成 BTI,但是有個問題是我不能對每個 BTI 建索引,比如說要求第10個 BTI 等于1的,把你這個用戶撈出來,最大的缺點就是沒法建索引,但是數據量下降很多,因為一個 BTI 和一個數組字節差了幾十倍.這個方案要么通過 CPU 多核并行測算,一個32核的機器在這11條記錄里搜一遍也要花十幾秒.

方案三使用了阿里云 RDS PostgreSQL 提供的 varbitx 擴展.針對方案2做一次翻轉,一個 TAG 一條記錄,每個用戶一個 BTI,它的空間跟第二方案是一樣的,比第一個縮小差不多80倍的樣子.

我們在使用這個方案之后要去撈一批用戶出來,是記錄與記錄之間的運算,因為我只有1萬條記錄,當然建索引是最好的,你只要在1萬條記錄上建索引.

最終我們的方案是選擇了方案三,這是我們給他們設計的數據合并過程,最終支持了他的應用場景,通過一臺機器就解決了原來八臺機器無法解決的問題.

3.4 方案空間評估

這是方案的對比,首先是空間上的對比.如果使用方案1,需要8個T的空間,如果是方案2或者3只要100個G空間就可以.

除了對比空間還要對比查詢效率,第一個方案對空間要求很多,但是產生的效率也不低,包括插入更新、查詢響應都是滿足客戶需求的,只是占用空間比較多.

對于方案2,我得用 BIT 運行.

方案3,每個指標都是滿足需求,包括數據的合并寫入,查詢并發是超過了用戶最初預計的要求.

3.5 實施前后對比

優化前用了8臺物理機,用戶體量翻了一個量級之后,原來的更新是天級別,現在是分鐘級別.查詢并發也是翻了一倍,響應也從原來的分鐘級降到毫秒級.

未來在內核層的優化,如果只更新了一些用戶的 TAG,將來更新某一小塊數據的時候,不用更新現在整個大的一個 BIT,將來是可以做到快捷更新.

4、任意字段組合

再分享兩個場景,一個是任意字段的組合查詢,也是一個比較常見的場景.

4.1 業務介紹

在瀏覽一些頁面的時候有很多選項,你可以勾是不是贈送退貨運費險或者也可以勾選是否要二手或者天貓,對用戶是有很多選擇的,而對于設計人員來說就得考慮,你的每一個選擇對應數據庫里都是一個字段,是不是每個字段都要建索引,如果我給用戶60個選擇,是不是都要建索引.

每個字段都建索引會帶來一定的影響,在更新、插入的時候,索引的變更會引入 RT,比如每更新一次索引,增加 0.1 毫秒的 RT,建十個索引的時候就變成一個毫秒,這是非常嚴重的問題.但是業務又要求每個字段都要勾選,怎么快速響應給用戶,這個問題是很矛盾的.

解決方案：GIN組合索引-任意組合查詢

該在哪個字段建索引,還是用倒排索引,除了倒排作用,還可以為每個字段上面都建符合倒排索引,達到什么效果呢?比如說有6個列是給用戶可以選擇的,我原來可能要建6個字段,一個階層的任意組合的索引才能達到效果.

4.2 實施后的性能指標

但是現在建一個索引,看任意六個列,任意列做 OR 或者 AND 的查詢達到什么效果?可以看響應時間,任意 AND 都可以達到零點幾個毫秒,也就是在這樣的場景下,可以通過一個這樣的索引解決你原來根本就不知道建多少個索引才能解決的問題.如果大家想了解GIN索引的內部原理,可以參考我的 GITHUB (https://github.com/digoal/blog/blob/master/README.md)..

5、任意字段模糊匹配

5.1 業務介紹

再引入下面一個應用場景,是我們的客戶關系系統,我們在這個系統里是為用戶提供了類似于你覺得搜索引擎在能干的事情.

比如用戶提供一些關鍵字來搜索,而且是任意階段的,只要匹配這個關鍵字就反饋給你,原來我根本不知道怎么建索引.比如是 URL 地址,如果建全文檢索根本就沒用,因為全文檢索里面是得分詞的,得有字典,而URL是沒有什么意義的,可能取的名字根本沒法分詞分出來,所以檢索解決不了.

還有一些公司名稱,公司的名稱可能不是一個常見詞,往往全文檢索詞庫中沒有,沒法進行分詞.因此全文檢索無法解決模糊查詢的問題.搜索引擎能不能這個問題呢?

可以解決,但是它得跟搜索引擎同步數據,還有考慮數據庫和搜索引起的一致性問題.額外的產生的費用還有維護成本的問題,因為你還得維護一個搜索引擎,包括數據的同步,還有更新,包括數據的過期等等.

5.2 pg_trgm 解決方案

既然這兩個都解決不了,有什么方法能解決這個問題?在 PostgreSQL 里面有一個叫做 PGTRGM 的小組件,可以幫你的詞拆成連續一個一個的字段,去做匹配,可以達到非常高的查詢效率.最終達到的效果,數據量是億級別,用戶任意的模糊搜索是可以達到毫秒級別的響應.

總結

這是我的微信,我同時也是PostgreSQL 中國社區發起人之一,社區每年會在各個地方舉辦 PostgreSQL 的技術分享活動,大家可以關注PG的公眾號或者我的微信.同時每年會舉辦一屆全國性的用戶峰會,有各個行業的同仁包括金融,物聯網,互聯網,社交,證券,物流,政府,科研機構,高校等行業.PG 是一個跨行業非常多的開源數據庫產品, BSD 許可非常寬松,從使用到售賣都沒有任何法律風險,大家可以放心的使用.

文章來自微信公眾號： 高效運維

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/2218.html