Building Search on a Small Budget

The cheapest search infrastructure is the database you are already paying for and already backing up. It gets further than its reputation suggests, and it has one specific weakness that decides whether it gets far enough for you.

The stack

Three components, in the order you should add them, each earning its place before the next arrives:

• Postgres full-text search as the candidate generator. No new service, no new backup story, no synchronisation between a database and an index — which is the operational cost people forget when they compare a search engine on features alone.
• A cross-encoder reranker over the top 50, which is the single largest quality improvement available per unit of work and, at 50 documents, cheap. This is the stage doing the real ranking work in a small system.
• An evaluation harness, built before either of the above is tuned, because otherwise you have no way to know whether anything you did helped.

Embeddings are conspicuously not on that list. They are the third thing to add, not the first: they cost an encoding pipeline, an index and a re-embedding story, and on a small corpus a reranker over lexical candidates recovers much of what they would have bought. Whether you need a vector database at all works through the row counts where that stops being true.

Postgres full-text, concretely

A generated column keeps the search vector in sync with the row without a trigger, and a GIN index makes it queryable:

ALTER TABLE docs
  ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX docs_tsv_idx ON docs USING GIN (tsv);

-- optional: trigram index for fuzzy title matching / typo tolerance
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX docs_title_trgm ON docs USING GIN (title gin_trgm_ops);

Query it with websearch_to_tsquery, which parses the kind of string a person actually types — quoted phrases, or, a leading minus for negation — rather than requiring boolean syntax:

SELECT d.id,
       d.title,
       ts_rank_cd(d.tsv, q, 32) AS score,
       ts_headline('english', d.body, q,
                   'MaxFragments=2, MinWords=8, MaxWords=25') AS snippet
FROM   docs d,
       websearch_to_tsquery('english', $1) AS q
WHERE  d.tsv @@ q
ORDER  BY score DESC
LIMIT  50;

Three things in there are worth knowing rather than copying. setweight with labels A through D is how you make a title match count for more than a body match, and the weights are applied at query time so you can change them without reindexing. The 32 passed to ts_rank_cd is a normalisation flag that divides the rank by itself plus one, mapping scores into a bounded range — useful because raw ranks are otherwise incomparable across queries. And ts_headline generates the snippet in the same query, which saves the second round trip that shows up as a surprise line in a latency budget.

The ranking limitation nobody mentions

Here is the thing to know before you commit, and it is documented rather than obscure: PostgreSQL’s built-in ranking functions do not use document frequency. They score on lexeme frequency within the document, on the assigned weights, and — for ts_rank_cd — on how close the matched terms are to each other. There is no IDF term.

Compare that with the BM25 arithmetic in relevance tuning, where a term appearing in 100 of a million documents outweighs one appearing in half of them by better than thirteen to one, automatically. Without IDF, every term in the query is worth the same, so a document that repeats a common word beats a document that contains the rare discriminating one. On a two-word query where one word carries all the meaning, that is the difference between a good result and a random one.

Four responses, roughly in order of effort:

• Lean on weights and stopwords. A well-chosen text search configuration removes the worst offenders, and putting the discriminating fields in weight class A recovers some of the effect. Cheapest, and partial.
• Compute IDF yourself. A materialised view of lexeme document frequencies, refreshed nightly, plus a re-scoring expression over the top candidates. Perhaps thirty lines of SQL, and it makes the ranking behave much more like BM25.
• Use an extension that implements BM25. ParadeDB and its pg_search extension bring a real BM25 scorer inside Postgres. This is the right answer if you can install extensions, and not an option on some managed hosting — check before you design around it.
• Let the reranker carry the ranking. If a cross-encoder is reordering the top 50 anyway, first-stage ordering matters much less than first-stage recall. This is the answer this page recommends, and it is why the reranker is the second component rather than the fourth.

Adding a reranker

Take the 50 rows Postgres returned, score each against the query with a cross-encoder, and reorder. The mechanics and the model choices are in the reranking page; what matters for a budget stack is the shape of the cost.

Reranking cost is linear in the number of documents and independent of corpus size, which is the property that makes it affordable for a small system: 50 documents per query is 50 documents per query whether you have ten thousand rows or ten million. A small cross-encoder runs on CPU at a rate that is usually fine for the traffic a budget system serves, and if you cache the head of the query distribution you are paying only for the tail. Cut k2 from 50 to 25 and the line halves; that is the dial, and the section below tells you what it costs.

Measure it yourself

Nobody can tell you how far this stack gets on your corpus, because it depends entirely on your documents and your queries. So measure. You need a judgements file — 60 to 100 queries is enough to detect a large difference, and the sample-size arithmetic in relevance tuning says what a smaller difference would require:

# judgements.csv
query,doc_id,grade
red running shoes,1042,3
red running shoes,7781,2
red running shoes,3310,0
...

import csv, math
from collections import defaultdict

def dcg(grades):
    return sum((2 ** g - 1) / math.log2(i + 2)
               for i, g in enumerate(grades))

def ndcg_at_k(ranked_ids, judged, k=10):
    grades = [judged.get(d, 0) for d in ranked_ids[:k]]
    ideal  = sorted(judged.values(), reverse=True)[:k]
    idcg   = dcg(ideal)
    return dcg(grades) / idcg if idcg > 0 else 0.0

judged = defaultdict(dict)
with open("judgements.csv") as f:
    for row in csv.DictReader(f):
        judged[row["query"]][int(row["doc_id"])] = int(row["grade"])

def evaluate(search_fn, k=10):
    scores = {}
    for query, rels in judged.items():
        scores[query] = ndcg_at_k(search_fn(query), rels, k)
    return scores

base = evaluate(postgres_only)
rank = evaluate(postgres_then_rerank)

mean = lambda s: sum(s.values()) / len(s)
print("postgres only :", round(mean(base), 4))
print("with reranker :", round(mean(rank), 4))

# the per-query diff is the useful output, not the means
worse = sorted(((rank[q] - base[q], q) for q in judged))[:10]
for delta, q in worse:
    print(round(delta, 4), q)

Run it before and after each change. The mean tells you whether to keep the change; the sorted per-query differences at the bottom tell you what it broke, and that list is where the actual learning is — exactly as in the tuning loop. Two hours of writing judgements buys you a permanent answer to “did that help?”, which is the question a budget stack most needs to be able to answer, because the alternative is buying a search engine on the strength of a feature comparison.