Postgres BM25 Search: Replace Elasticsearch With PG Text Search

Introduction

Anyone who’s tried to do real search with Postgres’s built-in full-text features has hit the wall. TSVector and TSQuery are fine for finding documents that contain keywords, but they don’t rank well, they treat common and rare terms the same, and they collapse completely when one query word is missing from an otherwise perfect document. That’s why Elasticsearch became the default external search layer for serious applications.

PG Text Search, open-sourced by Tiger Data, brings true BM25 relevance-ranked keyword search directly into Postgres. Pair it with pgvector for hybrid keyword + semantic search and the entire search stack collapses into one database. This guide walks through why this matters in the AI era, how BM25 fixes Postgres’s ranking gap, and a hands-on demo with the Tiger Data MCP server in Cursor.

📚 Table of contents

🕰️ The three eras of search

🚧 Why Postgres native full-text breaks for AI

Postgres ranks with TSRANK, which is keyword-presence with rough scoring. For RAG, the failure modes are loud:

• No IDF. The word “database” counts the same as “pooling” even though one is everywhere and the other is rare and meaningful.
• No term-frequency saturation. Repeating a keyword 50 times keeps boosting your score, so spam-formatted long docs win.
• No length normalisation. Long documents unfairly outrank shorter focused ones.
• Boolean strictness. If one query term is missing, the doc is excluded before ranking even runs — brittle for natural-language queries.

For human-facing search this is annoying. For an LLM pipeline it’s damaging: retrieved garbage in, generated garbage out, regardless of how good your model is.

🎯 The BM25 fix

BM25 is the standard ranking algorithm behind every modern search engine — Google, YouTube, Elasticsearch, all of them. It addresses every failure mode above:

• Inverse Document Frequency (IDF) weights rare terms higher than common ones.
• Term-frequency saturation means repeating a word helps to a point, then plateaus — no more keyword stuffing.
• Length normalisation ensures short, focused documents compete fairly with long ones.

BM25 isn’t experimental — it’s production-grade and ubiquitous. PG Text Search brings that exact ranking into Postgres without dragging in a full external platform.

📦 What PG Text Search ships

• A pg_textsearch extension you enable with one statement.
• BM25 indexes that transactionally sync with your data — no reindex jobs, no consistency lag.
• A score operator (@@@) that returns relevance for ordering and filtering.
• Compatibility with pgvector for hybrid queries — keyword over semantic over the same dataset.

One database. One index lifecycle. No Elasticsearch cluster, no Algolia bill, no separate sync pipeline keeping a search service in step with your source of truth.

⚙️ Setting it up on Tiger Data

Tiger Data runs a free Postgres tier with PG Text Search available. No credit card. The flow:

1. Create a free Tiger Cloud account, spin up a free service. It auto-pauses after a few hours idle.
2. Install the Tiger CLI: go install github.com/tiger-data/tiger-cli/... (Homebrew on macOS).
3. tiger auth login → authorise in browser.
4. Save the service password (on Windows PowerShell, use $env:TIGER_DB_PASSWORD = "..."; then tiger db save-password <service>).
5. tiger mcp install → pick Cursor (or whichever editor) to register the MCP server.

Once the MCP server is registered in Cursor, you can ask it in plain English: “List my Tiger Cloud services. Run this SQL on the first one.” No copy-pasting into a SQL editor.

🧪 A worked example

First, enable the extension — this is the step that’s easy to miss:

CREATE EXTENSION IF NOT EXISTS pg_textsearch;

Create a products table and load some sample data (the MCP server can do this in two prompts — “create a products table with 100 sample rows”).

CREATE TABLE products (
  id          serial primary key,
  title       text,
  description text
);
-- ...100 INSERTs...

Build the BM25 index:

CREATE INDEX products_bm25_idx
ON products
USING bm25 (title, description);

Search with the BM25 operator and order by relevance score:

SELECT id, title, description,
       (description @@@ 'wireless charging') AS score
FROM products
ORDER BY score DESC
LIMIT 5;

Results in the demo: wireless charging stand, wireless charger stand, wireless mouse pad, wireless charging map, wireless charger — ranked by genuine relevance, not just keyword presence. Compared with Postgres native full-text, the quality jump is immediate.

🧬 Hybrid search — BM25 + pgvector

Modern AI retrieval is hybrid: vector search gives you semantic recall (“cordless power accessory” can match “wireless charger”), keyword search gives you precision on specific terms. Combined, they out-perform either alone.

With both extensions enabled in the same database, a typical pattern: do a wide vector search first, then re-rank with BM25 over the candidate set. The whole pipeline is one SQL query, hitting one database, with one consistency model.

✨ Best practices & common mistakes

Explore More on DevShelf

• →
  Traditional RAG vs Vectorless RAG

  The retrieval layer that sits above BM25 — when vector similarity and keyword ranking should combine vs. compete.

• →
  Production RAG with Redis and BetterDB

  The production deployment pattern for the hybrid search stack described here — Redis caching, BetterDB, and MCP wiring.