PostgreSQL 適用的 Cloud SQL 中的 pg_textsearch 擴充功能,使用業界標準的 BM25 (Best Matching 25) 評分演算法提供全文搜尋,可提供高度準確的關聯性評分。這項擴充功能是 GitHub 上的開放原始碼專案。
pg_textsearch 擴充功能需要 PostgreSQL 17 以上版本。
pg_textsearch 支援逆向文件頻率、詞頻飽和度和文件長度正規化,因此能產生比 PostgreSQL 內建 ts_rank 函式更相關的結果。此外,由於這項功能直接在標準 PostgreSQL 儲存體頁面上運作,因此您不必安裝 Elasticsearch 等外部引擎,也不必擔心雙叢集維護和資料同步問題。使用 pg_textsearch,您就能建構強大、可擴充且高度相關的搜尋體驗,不必離開 PostgreSQL 生態系統。
多種搜尋方式
在 PostgreSQL 適用的 Cloud SQL 中,有幾種文字搜尋選項:
完全比對:使用標準 SQL 搜尋時,您可以使用
LIKE和ILIKE陳述式掃描完全相符的字元序列。查詢範例如下:ILIKE '%smart fitness watches%',這會找出類似下列的例項:- 探索我們特價中的全新運動手錶。
- 「智慧運動手錶是送禮的好選擇。」
但無法找到:
- 「跑者需要智慧型防水運動手錶。」
- 「這款手錶在健身方面特別智慧。」
全文搜尋:全文搜尋會使用 BM25 演算法和
tsvector,將文字拆解為詞根、忽略篩選字詞,並評估關聯性。這項功能可理解語言規則 (包括複數和文法),並考量字詞頻率。對smart AND fitness AND watches進行全文搜尋,會找到完全比對查詢範例會遺漏的執行個體,以及更複雜的執行個體,例如:- 「智慧手錶是日常健身活動的完美搭檔。」
- 「不是每支手錶在健身方面都這麼智慧。」
但無法找到:
- 「智慧健康手錶可協助你執行運動計畫。」
- 「這款智慧運動追蹤手環物超所值。」
語意搜尋:語意搜尋會使用 AI 模型,將文字轉換為向量並測量相似度距離。能理解含意、意圖、上下文、同義詞和相關概念。語意搜尋
smart fitness watches會找到其他搜尋方法會遺漏的範例執行個體。它會瞭解「智慧健康感知手錶」與「運動手錶」相同,且「智慧運動追蹤手環」也可能相關。如果系統未充分優先考慮「手錶」,可能會誤將計步器手環視為相符項目。
安裝 pg_textsearch 擴充功能
請按照下列步驟安裝並啟用 pg_textsearch:
按照「設定資料庫旗標」一文所述,將
cloudsql.enable_pg_textsearch旗標設為on。這會將pg_textsearch新增至shared_preload_libraries。安裝
pg_textsearch擴充功能CREATE EXTENSION pg_textsearch;驗證安裝項目:
SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'
使用 pg_textsearch 搜尋
假設下列範例表格已填入資料:
CREATE TABLE documents (
doc_id TEXT PRIMARY KEY,
content TEXT,
text_embedding vector(3072)
GENERATED ALWAYS AS (embedding('gemini-embedding-001', content)) STORED
);
使用全文搜尋前,請先建立 BM25 索引:
CREATE INDEX idx_docs_bm25
ON documents
USING bm25 (content)
WITH (text_config = 'english');
接著,您可以像這樣執行全文搜尋查詢:
SELECT doc_id, content, content <@> 'database system'
AS score FROM documents
ORDER BY content <@> 'database system'
ASC LIMIT 5;
使用 pg_textsearch 和 vector 執行混合型搜尋
您可以將 pg_textsearch 與 vector 語意搜尋擴充功能搭配使用,執行混合搜尋。按照下列方式安裝 vector 語意搜尋擴充功能:
CREATE EXTENSION IF NOT EXISTS vector CASCADE;
提出混合語意和全文搜尋查詢,如下所示:
WITH
-- Semantic search results
vector_search AS (
SELECT doc_id,
RANK () OVER (ORDER BY text_embedding <=>
google_ml.embedding('gemini-embedding-001',
'database')::VECTOR ) AS rank
FROM documents
ORDER BY text_embedding <=> google_ml.embedding('gemini-embedding-001',
'database')::VECTOR
LIMIT 10
),
-- Full text search results
text_search AS (
SELECT doc_id,
RANK () OVER (ORDER BY content <@> 'database' ASC) AS rank
FROM documents
ORDER BY content <@> 'database' ASC
LIMIT 10
)
-- RRF combining both semantic and full text search results
SELECT
COALESCE(vector_search.doc_id, text_search.doc_id) AS id,
COALESCE(1.0 / (60 + vector_search.rank), 0.0) +
COALESCE(1.0 / (60 + text_search.rank), 0.0) AS rrf_score
FROM vector_search
FULL OUTER JOIN text_search ON vector_search.doc_id = text_search.doc_id
ORDER BY rrf_score DESC
LIMIT 5;
用於設定 pg_textsearch 擴充功能的旗標
使用下列旗標設定 pg_textsearch 全文搜尋:
pg_textsearch.bulk_load_threshold:自動溢出前,每筆交易的期數。設為 0 即可停用。預設值:100,000。pg_textsearch.compress_segments:壓縮新區段中的張貼區塊。預設值:on。pg_textsearch.default_limit:沒有LIMIT子句時,要評分的文件數量上限。預設值:1,000。pg_textsearch.memtable_pages_threshold:自動溢出前要鏈結的頁面數量。如要停用,請設為 0。預設值:64。pg_textsearch.segments_per_level:自動壓縮前,每個層級的區隔數量。範圍:2 至 64。預設為 8。