# mcp-pgvector-search

An [MCP](https://modelcontextprotocol.io) server that gives a language model retrieval over your own documents, using PostgreSQL and `pgvector` as the whole backend.

It runs **two searches for every query** — semantic and keyword — and fuses the rankings. No separate vector database, no extra service to operate.

## Why hybrid, and not just embeddings

Vector search and keyword search fail in opposite directions.

**Embeddings match meaning and miss exact tokens.** Ask for error code `E4021` and a semantic search returns passages about error handling. The one paragraph that names the code may not rank at all, because a rare alphanumeric string barely moves an embedding.

**Full-text search nails those and misses paraphrase entirely.** Ask "how do I cancel my plan" against a document that says "terminating a subscription" and you get nothing.

Running both recovers most of what either misses. The interesting part is how you combine them.

### Why Reciprocal Rank Fusion

The obvious approach is to normalize both scores and add them. It does not work well: a cosine similarity sits around 0.7–0.9, a PostgreSQL `ts_rank` is often below 0.1, and the mapping between them shifts as the corpus grows. Any weighting you tune today is wrong next month.

RRF ignores the scores and combines by **position**. Each ranking contributes `1 / (k + rank)`, summed across rankings. Nothing to tune, and it has a property worth having: **a passage both retrievers found ranks above one that topped only a single list.** Two independent methods agreeing is a stronger signal than either alone, and the fused order says so.

Every result reports which retrievers found it, so the model can weigh it:

```json
{
  "document": "docs/billing.md",
  "text": "Subscriptions can be terminated from…",
  "relevance": 0.03252,
  "matched_by": ["keyword", "semantic"],
  "high_confidence": true
}
```

## Setup

```bash
pip install mcp-pgvector-search
psql "$DATABASE_URL" -f schema.sql
```

```bash
export DATABASE_URL="postgresql://user:pass@host/db"
export EMBEDDING_API_KEY="sk-..."
```

Index a file or a directory:

```bash
mcp-pgvector-search index ./docs
```

Run the server:

```bash
mcp-pgvector-search
```

Or point an MCP client at it:

```json
{
  "mcpServers": {
    "pgvector-search": {
      "command": "mcp-pgvector-search",
      "env": {
        "DATABASE_URL": "postgresql://user:pass@host/db",
        "EMBEDDING_API_KEY": "sk-..."
      }
    }
  }
}
```

## Design decisions

**Chunking splits on the largest natural boundary that fits.** Paragraphs first, then sentences, and a hard character cut only when a single sentence exceeds the budget — which happens with tables, code blocks and minified text. A chunk that cuts mid-sentence embeds badly; a chunk spanning three unrelated topics embeds into an average of all of them and matches none well.

**Chunks overlap by default.** A fact stated across a boundary is otherwise retrievable from neither side. The cost is storing the overlapping text twice, which is cheap next to silently losing an answer.

**The embedding provider sits behind an interface.** The wire format is the OpenAI-compatible `/v1/embeddings` shape, which OpenAI, Azure, LiteLLM, Ollama and most local inference servers speak. Switching provider is two environment variables, not a rewrite. Dimension is verified at startup, because a mismatch found during a query is a confusing error at the worst possible moment.

**Indexing is checksum-guarded.** Re-running the indexer over unchanged files re-embeds nothing. Replacing a document happens in one transaction, so a failure halfway leaves the previous version intact rather than a half-indexed document that returns partial answers.

**The `tsvector` is a generated column.** It cannot drift out of sync with the content it indexes, because the application never writes it.

**HNSW rather than IVFFlat.** Slower to build, and worth it: no training pass, so accuracy holds as the corpus grows instead of degrading until someone remembers to reindex.

**Results are capped by character budget.** Retrieval quality is not the only constraint — everything returned lands in a context window someone pays for by the token. When results are dropped, the response says so, so the model narrows its query instead of assuming it saw everything.

**Empty results say so explicitly.** The response tells the model to report that nothing matched rather than answering from general knowledge. Silent empty retrieval is how a RAG system starts confidently making things up.

## Configuration

| Variable | Required | Default | Notes |
|---|---|---|---|
| `DATABASE_URL` | yes | — | PostgreSQL DSN with the `vector` extension |
| `EMBEDDING_API_KEY` | usually | — | Omit for local servers that need no auth |
| `EMBEDDING_BASE_URL` | no | `https://api.openai.com/v1` | Any OpenAI-compatible endpoint |
| `EMBEDDING_MODEL` | no | `text-embedding-3-small` | |
| `EMBEDDING_DIMENSION` | no | `1536` | Must match both the model and `schema.sql` |

Changing the embedding model means changing the dimension and re-embedding the corpus. That is a property of embeddings, not a limitation of this tool.

## Development

```bash
pip install -e ".[dev]"
pytest
ruff check .
```

Chunking and fusion have no database or network dependencies, so the retrieval logic is tested without a running PostgreSQL.

## License

MIT