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

標籤：

Lateral是一家內容建議服務供應商，其類比程式使用PostgreSQL儲存文檔。每個文檔包含一個 text列和一個儲存標題、日期和URL等中繼資料的JSON列。他們希望為類比程式建立快速搜尋功能，搜尋文檔全文和標題，產生推薦內容。近 日，Lateral首席技術官Max撰文介紹了他們的做法。

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

• 不需要額外安裝軟體或庫
• 可以重用他們在應用程式中使用的資料庫介面
• 不需要配置額外的伺服器
• 不增加成本
• 資料可以儲存在可控的地方
• 不需要在不同的資料來源之間同步資料

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

建立一個列tsv，儲存tsvector值； 在建立的列上建立索引，並用下面的語句填充列： UPDATE data_rows SET tsv=setweight(to_tsvector(coalesce(meta->>‘title‘,‘‘)), ‘A‘) ||setweight(to_tsvector(coalesce(text,‘‘)), ‘D‘); 此處需要注意，JSON列的權重為A，text列的權重為D； 建立tsv列更新函數；在表上建立觸發器，當更新和新增行時，執行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毫秒。

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