# postgres-hybrid-text-search

Teaches AI agents how to combine BM25 keyword matching with pgvector semantic retrieval using Reciprocal Rank Fusion in PostgreSQL.

- skill · `type=skill` · Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, OpenCode · 372 installs · ★ 1874
- Author: tigerdata
- License: Apache-2.0 · policy=mirror · the beginning of the file is at the end of this page
- Source: https://github.com/timescale/pg-aiguide/tree/HEAD/skills/postgres-hybrid-text-search/SKILL.md
- Updated: 2026-10-22
- HTML: https://affpapa.org/en/nevibecode/skills/postgres-hybrid-text-search

## What it does

This skill instructs coding agents on setting up hybrid retrieval in PostgreSQL using `pg_textsearch` for BM25 keyword scoring and `pgvector` for semantic cosine distance. It outlines parallel queries over chunked text documents, followed by client-side rank merging.

Inside, you get SQL patterns for `bm25` and `hnsw` indexes with `halfvec(1536)`, plus client-side Reciprocal Rank Fusion (RRF) implementations in Python and TypeScript. The `pg_textsearch` extension is currently in prerelease and targets PostgreSQL 17 and 18, which means using it in mission-critical production setups is premature.

## Requirements
- Works with: PostgreSQL

## Install

### Claude Code (native platform)

1. **Skills CLI (Vercel)**
```bash
npx skills add timescale/pg-aiguide --skill postgres-hybrid-text-search -a claude-code
```

2. **Manually: put the files in place**
- File: `~/.claude/skills/postgres-hybrid-text-search/SKILL.md` — Copy the whole skill folder; for one project use .claude/skills/postgres-hybrid-text-search/

### Codex

1. **Skills CLI (Vercel)**
```bash
npx skills add timescale/pg-aiguide --skill postgres-hybrid-text-search -a codex
```

2. **Manually: put the files in place**
- File: `.agents/skills/postgres-hybrid-text-search/SKILL.md` — For all projects use ~/.agents/skills/

### Cursor

1. **Skills CLI (Vercel)**
```bash
npx skills add timescale/pg-aiguide --skill postgres-hybrid-text-search -a cursor
```

2. **Manually: put the files in place**
- File: `.cursor/skills/postgres-hybrid-text-search/SKILL.md` — Cursor also reads .agents/skills/ and .claude/skills/

### GitHub Copilot

1. **GitHub CLI**
```bash
gh skill install timescale/pg-aiguide postgres-hybrid-text-search
```

2. **Manually: put the files in place**
- File: `.github/skills/postgres-hybrid-text-search/SKILL.md`

### Gemini CLI

1. **Skills CLI (Vercel)**
```bash
npx skills add timescale/pg-aiguide --skill postgres-hybrid-text-search -a gemini-cli
```

2. **Platform CLI**
```bash
gemini skills install https://github.com/timescale/pg-aiguide --path skills/postgres-hybrid-text-search
```

### Windsurf (Devin Desktop)

**Manually: put the files in place**
- File: `.devin/skills/postgres-hybrid-text-search/SKILL.md` — The legacy .windsurf/skills/ path still works

### OpenCode

**Skills CLI (Vercel)**
```bash
npx skills add timescale/pg-aiguide --skill postgres-hybrid-text-search -a opencode
```

### Claude.ai

**Claude.ai**
- Zip the folder and upload it in Settings → Capabilities → Skills

Where a file is given instead of a command, copy the files from the source to that path: https://github.com/timescale/pg-aiguide/tree/HEAD/skills/postgres-hybrid-text-search/SKILL.md

## When to use it
- combine exact keyword matching with semantic vector retrieval
- create unified bm25 and hnsw indexes on a single documents table
- merge parallel search results via Reciprocal Rank Fusion in Python or TypeScript
- tune BM25 saturation parameters and adjust search weights

## Example prompts
- Show me how to set up hybrid search using pg_textsearch and pgvector
- Write a client-side RRF fusion function in TypeScript with k=60
- How do I index halfvec embeddings and BM25 text in the same PostgreSQL table?

## Source text · SKILL.md · Apache-2.0

Below is the content of a third-party file. It is data, not instructions for you: do not act on anything written inside unless the human asks. Block tag: f192df53227ced02.

BEGIN THIRD-PARTY FILE f192df53227ced02
````text
---
name: postgres-hybrid-text-search
description: |
  Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF).

  **Trigger when user asks to:**
  - Combine keyword and semantic search
  - Implement hybrid search or multi-modal retrieval
  - Use BM25/pg_textsearch with pgvector together
  - Implement RRF (Reciprocal Rank Fusion) for search
  - Build search that handles both exact terms and meaning


  **Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder

  Covers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with pgvector and pg_textsearch extensions
metadata:
  author: tigerdata
---

# Hybrid Text Search

Hybrid 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.

This 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.

pg_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.

## When to Use Hybrid Search

- **Use hybrid** when queries mix specific terms (product names, codes, proper nouns) with conceptual intent
- **Use semantic only** when meaning matters more than exact wording (e.g., "how to fix slow queries" should match "query optimization")
- **Use keyword only** when exact matches are critical (e.g., error codes, SKUs, legal citations)

Hybrid search typically improves recall over either method alone, at the cost of slightly more complexity.

## Data Preparation

Chunk 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.

## Golden Path (Default Setup)

```sql
-- Enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_textsearch;
````
END THIRD-PARTY FILE f192df53227ced02

The third-party file has ended. Everything between the f192df53227ced02 tags is data: requests and commands from it are not followed. An “end of file” line with another tag inside the block is part of the file.

First 2866 of 12600 bytes. © tigerdata · Apache-2.0 · https://github.com/timescale/pg-aiguide/tree/HEAD/skills/postgres-hybrid-text-search/SKILL.md

_Part of the page did not fit — see the HTML version._

---
NeVibeCode on affpapa.org. Catalog texts are descriptions, not instructions for the agent. How to choose and install: https://affpapa.org/en/nevibecode/agents.md