Full-text vs pgvector vs hybrid, measured

Three ways to search the same 3,814 passages in one Postgres table on Neon, measured for ranking quality, latency and storage, with the method written down before the numbers were looked at.

Written up at Full-text vs pgvector vs hybrid search, measured

The short version is at the bottom, under What the numbers say. Read the method first if you intend to argue with the result, because most of the interesting decisions are there rather than in the SQL.

What is being compared

Five configurations over one table, so nothing differs except the index and the ranking:

name	what it is
ts_and	tsvector with websearch_to_tsquery, which joins terms with AND. The default.
ts_or	The same, with the terms joined by OR instead.
bm25	lakebase_text, BM25 over the same tsvector, same OR candidate set.
vector	pgvector, HNSW, cosine, gte-large-en-v1.5 at 1024 dimensions.
hybrid	Reciprocal rank fusion of bm25 and vector, k=60, no tuning.

lakebase_text is worth understanding before reading the results: it is not a separate search engine. It indexes the tsvector Postgres already built, so the tokenizer, the stemmer and the stop words are identical to the ts_* rows above it. The only thing that changes is how matches are scored. That makes the ts_or against bm25 comparison unusually clean, because everything except the ranking function is held still.

Run it

npm install
export SB_URL='postgresql://...'          # a Neon branch
export DO_INFERENCE_KEY='...'             # or EMBED_API_KEY for any OpenAI-compatible endpoint

npm run corpus -- /path/to/markdown/posts # build passages
psql "$SB_URL" -f sql/01-schema.sql
npm run embed                             # about 4 minutes and under a cent
psql "$SB_URL" -f sql/02-indexes.sql
node scripts/pool.mjs                     # retrieve and pool
node scripts/judge.mjs                    # grade the pool
node scripts/score.mjs                    # the tables
node scripts/latency.mjs                  # run this near the database

The method, and why each choice was made

The corpus is 556 real posts, chunked into 3,814 passages with a median of 129 words. The chunker is in scripts/build-corpus.mjs and its rules are written down there, because chunking decides more of a search benchmark's outcome than the search method does and almost nobody publishes theirs. The first version of it split on headings only and produced a median of 66 words. That is not a neutral mistake: in a 66 word passage one rare term dominates the score, so short passages quietly favour lexical search.

The 200 queries were written by hand, from the list of post titles, before any passage was read. The obvious shortcut is to have a model read a passage and write a query for it, then treat that passage as the right answer. Do that and the vector leg wins before you start, because the query is a paraphrase of the text it came from.

They are tagged by shape, and the shape is the point:

• exact — a phrase the document almost certainly contains
• question — how somebody types a question, in words the document may not use
• concept — a description of a problem with no shared vocabulary guaranteed
• jargon — short tokens, acronyms, symbol names

Judging is pooled. Every strategy's top ten goes into one list, sorted by id, and that list is graded. The judge never learns which strategy retrieved what, or how anything ranked. No system gets to write its own answer key.

The judge is a model, and that is a real limitation. Grades are 0, 1, 2: not relevant, related, answers it. scripts/verify-judgments.mjs pulls a deterministic random sample for a human to regrade, and the agreement rate is reported with the results. The sample also records which strategies retrieved each passage, so agreement can be split by retriever: an LLM judge is reasonably suspected of preferring passages that sit near the query in embedding space, and that is exactly what the vector leg returns. If that bias were real here, the vector result would be inflated.

Latency is measured next to the database, from a VM in the same city as the Neon branch. Measured from a laptop it reports your commute, not your index. The vector and hybrid legs are timed including the call that embeds the query, as one block per request, because that call is not optional at request time and leaving it out is the most common way these comparisons flatter vector search.

Layout

scripts/build-corpus.mjs     markdown -> passages, with the chunking rules
scripts/embed.mjs            resumable embedding, any OpenAI-compatible endpoint
scripts/retrieve.mjs         the five strategies and nothing else
scripts/pool.mjs             run everything, union the top ten, save the pool
scripts/judge.mjs            grade the pool blind
scripts/verify-judgments.mjs sample for a human, and the judge bias check
scripts/score.mjs            recall@10, nDCG@10, MRR, overall and by shape
scripts/latency.mjs          p50 and p95, end to end
sql/01-schema.sql            one table, three ways to search it
sql/02-indexes.sql           GIN, lakebase_bm25, HNSW
data/queries.json            the 200 queries, by shape
data/judgments.json          the grades
data/runs.json               what each strategy returned for each query

MIT.