PostgreSQL 實踐 - 内容社群(如論壇)圖式搜尋應用

PostgreSQL , 圖資料庫 , 論壇搜尋 , 推薦 , 數組 , smlar相似搜尋

通常一個内容社群網站可能需要記錄這麼一些資料: 文章，使用者，标簽。

還有三者之間的關系，包括，文章的标簽，使用者閱讀了文章，使用者收藏了文章，使用者關注了某使用者，使用者是某篇文章的作者。

最終要實作毫無人道的查詢，例如：

閱讀了此篇文章的人還在閱讀什麼其他文章，和我愛好相近的人都有哪些等等等等。

其中文章數量幾千萬，使用者數量接近一千萬。

實際上PostgreSQL裡面的數組、smlar實作這個需求非常的友善。下面開始設計和壓測。

數組用于存儲正向和反向關系，标簽等。

smlar用于查詢相似的數組（找出愛好相似的人）。

1、使用者表

2、标簽表

3、文章表

1、正向關系

1.1、文章被誰看過

1.2、文章被誰收藏過

2、反向關系

2.1、使用者看過哪些文章，包含哪些标簽

2.2、使用者收藏了哪些文章，包含哪些标簽

1、閱讀了此篇文章的其他人還在閱讀什麼其他文章，(過濾目前文章、以及我閱讀過的文章)。

邏輯如下，寫成UDF即可：

UDF如下，都能使用索引，都是聚合後的點查，性能很贊：

2、與我（閱讀文章）愛好相近的人有哪些，走GIN索引，性能很贊。

3、與我（閱讀文章标簽）愛好相近的人有哪些。

與2類似，略。

4、與我（收藏文章）愛好相近的人有哪些。

5、與我（收藏文章标簽）愛好相近的人有哪些。

使用UDF，減少互動次數，完成以下幾類業務邏輯的操作。UDF可以使用plpgsql編寫，很簡單，本文略：

<a href="https://www.postgresql.org/docs/10/static/plpgsql.html">https://www.postgresql.org/docs/10/static/plpgsql.html</a>

1、建立文章的行為，自動産生标簽，并更新或追加标簽表。

2、閱讀行為，修改正向反向關系。

3、收藏行為，修改正向反向關系。

可選索引

1、生成1000萬使用者

2、生成10萬标簽

3、生成5000萬文章

4、生成正向關系，平均每篇文章被500人閱讀，被50人收藏。

5、生成反向關系（按理說，反向關系和正向關系應該一一對應，為了測試友善，我這裡就不對應了，測試效果是一樣的）

平均每人閱讀1000篇文章，涉及500個标簽。收藏100篇文章，涉及50個标簽。

其他人一共閱讀了約50萬其他文章，擷取加排序耗時：200毫秒。

2、與我（閱讀文章）愛好相近的人有哪些。

耗時：2.4毫秒。

前面的推薦文章、找相似的人。指的是實時查詢的性能，而實際這些操作都可以預計算的(因為文章增量不會太大、而且文章的閱讀人群變化不會太大)，例如一天重新整理一次，那麼像使用者推薦相似使用者，推薦相似文章時，有預計算則直接查詢結果，那性能會提升到0.0N毫秒級響應。沒有預計算的新文章，則實時查詢（并更新到預計算的表中），也能夠毫秒級響應。

預計算還可以做成另一種模式，當有人查詢這篇文章時，根據上次預計算的時間，決定是否需要重新查詢，并更新它。 （也就是說，實時計算 + 緩存 + 緩存逾時 的模式。）

邏輯如下

3分開發，7分營運。内容網站與社交軟體類似，營運是重頭戲。營運中關鍵的一環是圈子，圈子可以聚人氣，形成圈子往往靠的是推薦，推薦的源頭又是行為，推薦什麼樣的内容、人給目标，靠的是行為。所謂物以類聚，人以群居，就是這個理。

PostgreSQL 的數組、smlar實作高效的歸類查詢、推薦需求非常的友善。

1、數組用于存儲正向和反向關系，标簽等。

2、smlar用于查詢相似的數組（找出愛好相似的人）。

在社交營運、内容營運場景中，非常友善、高效。

熱點人、熱點文章也不在話下，在其他案例中已經測試過，可以參考本文末尾。

<a href="https://github.com/digoal/blog/blob/master/201701/20170112_02.md">《電商内容去重\内容篩選應用(實時識别轉載\盜圖\侵權?) - 文本、圖檔集、商品集、數組相似判定的優化和索引技術》</a>

<a href="https://www.postgresql.org/docs/10/static/intarray.html">https://www.postgresql.org/docs/10/static/intarray.html</a>

計數、實時需求也口可以使用流計算，案例參考：

<a href="https://github.com/digoal/blog/blob/master/201705/20170512_02.md">《三體高可用PCC大賽 - facebook\微網誌 like場景 - 資料庫設計與性能壓測》</a>

<a href="https://github.com/bitnine-oss/agensgraph">https://github.com/bitnine-oss/agensgraph</a>