利用PostgreSQL實現毫秒級全文檢索

原文  http://www.infoq.com/cn/news/2015/05/PostgreSQL-Lateral-Max

Lateral 是一家內容推薦服務提供商，其 模擬程序 使用 PostgreSQL 存儲文檔。每個文檔包含一個text列和一個存儲標題、日期和URL等元數據的JSON列。他們希望為模擬程序創建快速搜索功能，搜索文檔全文和標題，生成推薦內容。近日，Lateral首席技術官Max 撰文 介紹了他們的做法。

為了實現這一目標，可以選擇開源解決方案 Apache Solr 或 Elasticsearch ，也可以選擇托管解決方案 Elastic 或 Algolia ，但出于以下考慮，他們選擇了 PostgreSQL的全文搜索功能 ：

• 不需要額外安裝軟件或庫
• 可以重用他們在應用程序中使用的數據庫接口
• 不需要配置額外的服務器
• 不增加成本
• 數據可以存儲在可控的地方
• 不需要在不同的數據源之間同步數據

雖然PostgreSQL搜索的精度和 大規模查詢速度存在缺陷 ，但Max認為，它可以滿足他們的應用場景。以下是他們的做法：

1. 創建一個列tsv，存儲tsvector值；

2. 在新建的列上創建索引，并用下面的語句填充列：

   UPDATE data_rows SET tsv
   =setweight(to_tsvector(coalesce(meta->>'title','')), 'A') 
   ||setweight(to_tsvector(coalesce(text,'')), 'D');

   
   

   此處需要注意，JSON列的權重為A，text列的權重為D；

3. 創建tsv列更新函數；
4. 在表上創建觸發器，當更新和新增行時，執行tsv列更新函數。

當一切就緒后，替換下面代碼中的“你的查詢”并執行：

SELECT id, meta->>'title' as title, meta FROM (
  SELECT id, meta, tsv
  FROM data_rows, plainto_tsquery('你的查詢') AS q
  WHERE (tsv @@ q)
) AS t1 ORDER BY ts_rank_cd(t1.tsv, plainto_tsquery('你的查詢')) DESC LIMIT 5;

經測試，該查詢大約50毫秒即可完成。如果返回文檔全文，則會增加大約350毫秒，這更多的可能是受網絡負載影響。如果只返回文檔中的200個字符，則僅僅增加大約100毫秒。