{"id":"postgres-hybrid-text-search","name":"postgres-hybrid-text-search","summary":"このスキルを活用して、BM25キーワード検索とReciprocal Rank Fusion(RRF)を用いたセマンティックベクトル検索を組み合わせたハイブリッド検索を実装します。","body":"# Hybrid Text Search\n\nHybrid search combines keyword search (BM25) with semantic search (vector embeddings) to get the best of both: exact keyword matching and meaning-based retrieval. Use Reciprocal Rank Fusion (RRF) to merge results from both methods into a single ranked list.\n\nThis guide covers combining [pg_textsearch](https://github.com/timescale/pg_textsearch) (BM25) with [pgvector](https://github.com/pgvector/pgvector). Requires both extensions. For high-volume setups, filtering, or advanced pgvector tuning (binary quantization, HNSW parameters), see the **pgvector-semantic-search** skill.\n\npg_textsearch is a new BM25 text search extension for PostgreSQL, fully open-source and available hosted on Tiger Cloud as well as for self-managed deployments. It provides true BM25 ranking, which often improves relevance compared to PostgreSQL's built-in ts_rank and can offer better performance at scale. Note: pg_textsearch is currently in prerelease and not yet recommended for production use. pg_textsearch currently supports PostgreSQL 17 and 18.\n\n## When to Use Hybrid Search\n\n- **Use hybrid** when queries mix specific terms (product names, codes, proper nouns) with conceptual intent\n- **Use semantic only** when meaning matters more than exact wording (e.g., \"how to fix slow queries\" should match \"query optimization\")\n- **Use keyword only** when exact matches are critical (e.g., error codes, SKUs, legal citations)\n\nHybrid search typically improves recall over either method alone, at the cost of slightly more complexity.\n\n## Data Preparation\n\nChunk your documents into smaller pieces (typically 500–1000 tokens) and store each chunk with its embedding. Both BM25 and semantic search operate on the same chunks—this keeps fusion simple since you're comparing like with like.\n\n## Golden Path (Default Setup)\n\n```sql\n-- Enable extensions\nCREATE EXTENSION IF NOT EXISTS vector;\nCREATE EXTENSION IF NOT EXISTS pg_textsearch;\n\n-- Table with both indexes\nCREATE TABLE documents (\n  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  content TEXT NOT NULL,\n  embedding halfvec(1536) NOT NULL\n);\n\n-- BM25 index for keyword search\nCREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');\n\n-- HNSW index for semantic search\nCREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops);\n```\n\n### BM25 Notes\n\n- **Negative scores**: The `<@>` operator returns negative values where lower = better match. RRF uses rank position, so this doesn't affect fusion.\n- **Language config**: Change `text_config` to match your content language (e.g., `'french'`, `'german'`). See [PostgreSQL text search configurations](https://www.postgresql.org/docs/current/textsearch-configuration.html).\n- **Tuning**: BM25 has `k1` (term frequency saturation, default 1.2) and `b` (length normalization, default 0.75) parameters. Defaults work well; only tune if relevance is poor.\n  ```sql\n  CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english', k1 = 1.5, b = 0.8);\n  ```\n- **Partitioned tables**: Each partition maintains local statistics. Scores are not directly comparable across partitions—query individual partitions when score comparability matters.\n\n## RRF Query Pattern\n\nReciprocal Rank Fusion combines rankings from multiple searches. Each result's score is `1 / (k + rank)` where `k` is a constant (typically 60). Results are summed across searches and re-sorted.\n\n**Run both queries in parallel from your client** for lower latency, then fuse results client-side:\n\n```sql\n-- Query 1: Keyword search (BM25)\n-- $1: search text\nSELECT id, content FROM documents ORDER BY content <@> $1 LIMIT 50;\n```\n\n```sql\n-- Query 2: Semantic search (separate query, run in parallel)\n-- $1: embedding of your search text as halfvec(1536)\nSELECT id, content FROM documents ORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;\n```\n\n```python\n# Client-side RRF fusion (Python)\ndef rrf_fusion(keyword_results, semantic_results, k=60, limit=10):\n    scores = {}\n    content_map = {}\n\n    for rank, row in enumerate(keyword_results, start=1):\n        scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank)\n        content_map[row['id']] = row['content']\n\n    for rank, row in enumerate(semantic_results, start=1):\n        scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank)\n        content_map[row['id']] = row['content']\n\n    sorted_ids = sorted(scores, key=scores.get, reverse=True)[:limit]\n    return [{'id': id, 'content': content_map[id], 'score': scores[id]} for id in sorted_ids]\n```\n\n```typescript\n// Client-side RRF fusion (TypeScript)\ntype Row = { id: number; content: string };\ntype Result = Row & { score: number };\n\nfunction rrfFusion(keywordResults: Row[], semanticResults: Row[], k = 60, limit = 10): Result[] {\n  const scores = new Map<number, number>();\n  const contentMap = new Map<number, string>();\n\n  keywordResults.forEach((row, i) => {\n    scores.set(row.id, (scores.get(row.id) ?? 0) + 1 / (k + i + 1));\n    contentMap.set(row.id, row.content);\n  });\n\n  semanticResults.forEach((row, i) => {\n    scores.set(row.id, (scores.get(row.id) ?? 0) + 1 / (k + i + 1));\n    contentMap.set(row.id, row.content);\n  });\n\n  return [...scores.entries()]\n    .sort((a, b) => b[1] - a[1])\n    .slice(0, limit)\n    .map(([id, score]) => ({ id, content: contentMap.get(id)!, score }));\n}\n```\n\n### RRF Parameters\n\n| Parameter | Default | Description |\n|-----------|---------|-------------|\n| `k` | 60 | Smoothing constant. Higher values reduce rank differences; 60 is standard |\n| Candidates per search | 50 | Higher = better recall, more work |\n| Final limit | 10 | Results returned after fusion |\n\nIncrease candidates if relevant results are being missed. The k=60 constant rarely needs tuning.\n\n## Weighting Keyword vs Semantic\n\nTo favor one method over another, multiply its RRF contribution:\n\n```python\n# Weight semantic search 2x higher than keyword\nkeyword_weight = 1.0\nsemantic_weight = 2.0\n\nfor rank, row in enumerate(keyword_results, start=1):\n    scores[row['id']] = scores.get(row['id'], 0) + keyword_weight / (k + rank)\n\nfor rank, row in enumerate(semantic_results, start=1):\n    scores[row['id']] = scores.get(row['id'], 0) + semantic_weight / (k + rank)\n```\n\n```typescript\n// Weight semantic search 2x higher than keyword\nconst keywordWeight = 1.0;\nconst semanticWeight = 2.0;\n\nkeywordResults.forEach((row, i) => {\n  scores.set(row.id, (scores.get(row.id) ?? 0) + keywordWeight / (k + i + 1));\n});\n\nsemanticResults.forEach((row, i) => {\n  scores.set(row.id, (scores.get(row.id) ?? 0) + semanticWeight / (k + i + 1));\n});\n```\n\nStart with equal weights (1.0 each) and adjust based on measured relevance.\n\n## Reranking with ML Models\n\nFor highest quality, add a reranking step using a cross-encoder model. Cross-encoders (e.g., `cross-encoder/ms-marco-MiniLM-L-6-v2`) are more accurate than bi-encoders but too slow for initial retrieval—use them only on the candidate set.\n\nRun the same parallel queries as above with a higher LIMIT (e.g., 100), then:\n\n```python\n# 1. Fuse results with RRF (more candidates for reranking)\ncandidates = rrf_fusion(keyword_results, semantic_results, limit=100)\n\n# 2. Rerank with cross-encoder\nfrom sentence_transformers import CrossEncoder\nreranker = CrossEncoder('cross-encoder/ms-marco-MiniLM-L-6-v2')\n\npairs = [(query_text, doc['content']) for doc in candidates]\nscores = reranker.predict(pairs)\n\n# 3. Return top 10 by reranker score\nreranked = sorted(zip(candidates, scores), key=lambda x: x[1], reverse=True)[:10]\n```\n\n```typescript\nimport { CohereClientV2 } from 'cohere-ai';\n\n// 1. Fuse results with RRF (more candidates for reranking)\nconst candidates = rrfFusion(keywordResults, semanticResults, 60, 100);\n\n// 2. Rerank via API (example uses Cohere SDK; Jina, Voyage, and others work similarly)\nconst cohere = new CohereClientV2({ token: COHERE_API_KEY });\n\nconst reranked = await cohere.rerank({\n  model: 'rerank-v3.5',\n  query: queryText,\n  documents: candidates.map(c => c.content),\n  topN: 10\n});\n\n// 3. Map back to original documents\nconst results = reranked.results.map(r => candidates[r.index]);\n```\n\nReranking is optional—hybrid RRF alone significantly improves over single-method search.\n\n## Performance Considerations\n\n- **Index both columns**: BM25 index on text, HNSW index on embedding\n- **Limit candidate pools**: 50–100 candidates per method is usually sufficient\n- **Run queries in parallel**: Client-side parallelism reduces latency vs sequential execution\n- **Monitor latency**: Hybrid adds overhead; ensure both indexes fit in memory\n\n## Scaling with pgvectorscale\n\nFor large datasets (10M+ vectors) or workloads with selective metadata filters, consider [pgvectorscale](https://github.com/timescale/pgvectorscale)'s StreamingDiskANN index instead of HNSW for the semantic search component.\n\n**When to use StreamingDiskANN:**\n- Large datasets where HNSW doesn't fit in memory\n- Queries that filter by labels (e.g., tenant_id, category, tags)\n- When you need high-performance filtered vector search\n\n**Label-based filtering:** StreamingDiskANN supports filtered indexes on `smallint[]` label columns. Labels are indexed alongside vectors, enabling efficient filtered search without post-filtering accuracy loss.\n\n```sql\n-- Enable pgvectorscale (in addition to pgvector)\nCREATE EXTENSION IF NOT EXISTS vectorscale;\n\n-- Table with label column for filtering\nCREATE TABLE documents (\n  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  content TEXT NOT NULL,\n  embedding halfvec(1536) NOT NULL,\n  labels smallint[] NOT NULL  -- e.g., category IDs, tenant IDs\n);\n\n-- StreamingDiskANN index with label filtering\nCREATE INDEX ON documents USING diskann (embedding vector_cosine_ops, labels);\n\n-- BM25 index for keyword search\nCREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');\n\n-- Filtered semantic search using && (array overlap)\nSELECT id, content FROM documents\nWHERE labels && ARRAY[1, 3]::smallint[]\nORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;\n```\n\nSee the [pgvectorscale documentation](https://github.com/timescale/pgvectorscale) for more details on filtered indexes and tuning parameters.\n\n## Monitoring & Debugging\n\n```sql\n-- Force index usage for verification (planner may prefer seqscan on small tables)\nSET enable_seqscan = off;\n\n-- Verify BM25 index is used\nEXPLAIN SELECT id, content FROM documents ORDER BY content <@> 'search text' LIMIT 10;\n-- Look for: Index Scan using ... (bm25)\n\n-- Verify HNSW index is used\nEXPLAIN SELECT id, content FROM documents ORDER BY embedding <=> '[0.1, 0.2, ...]'::halfvec(1536) LIMIT 10;\n-- Look for: Index Scan using ... (hnsw)\n\nSET enable_seqscan = on;  -- Re-enable for normal operation\n\n-- Check index sizes\nSELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size\nFROM pg_indexes WHERE tablename = 'documents';\n```\n\nIf EXPLAIN still shows sequential scans with `enable_seqscan = off`, verify indexes exist and queries use correct operators (`<@>` for BM25, `<=>` for cosine). For more pgvector debugging guidance, see the **pgvector-semantic-search** skill.\n\n## Common Issues\n\n| Symptom | Likely Cause | Fix |\n|---------|--------------|-----|\n| Missing exact matches | Keyword search not returning them | Check BM25 index exists; verify text_config matches content language |\n| Poor semantic results | Embedding model mismatch | Ensure query embedding uses same model as stored embeddings |\n| Slow queries | Large candidate pools or missing indexes | Reduce inner LIMIT; verify both indexes exist and are used (EXPLAIN) |\n| Skewed results | One method dominating | Adjust RRF weights; verify both searches return reasonable candidates |","author":"@timescale","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/postgres-hybrid-text-search","license":"Apache-2.0","category":"coding","lang":"en","tokens":2882,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":["www.postgresql.org"]}}