Hybrid search in Postgres without pg_search

Supabase does not offer pg_search, the BM25 extension from ParadeDB. People have been asking for it since 2023 in a GitHub discussion. In the meantime you can build a solid hybrid search from the extensions Supabase already ships. Lovguiden has done that for about two years. This post walks through the current version, including the parts we got wrong the first time.

The setup

• About 1.45 million chunks of court decisions, 510,000 chunks of legislation and 150,000 case rows
• Hosted Supabase, with vector 0.8, rum 1.3, pg_trgm and unaccent
• 1024-dimensional embeddings from Isaacus kanon-2-embedder
• Each search is one database call, followed by one rerank call in the app

Each search function has three arms that run on the same query. A keyword arm finds documents that contain the words. A vector arm finds documents that mean the same thing. A trigram arm catches names, case numbers and misspellings. The three ranked lists are merged with Reciprocal Rank Fusion (RRF), and the top 30 go to a reranker.

Index what people search for, not the whole document

This change did more for relevance than any ranking function. A court decision can run to 30 pages, and most of it is procedure. Searching that text directly matches a lot of noise. So for each case, a language model writes a short list of phrases a user would actually type, based on the case summary and the holding rather than the full text. We call it the search intent.

The search intent gets the highest weight in a generated tsvector column. The title and the case number get lower weights. Because the column is generated, it can never drift from the text it is built from. The same phrases are also added to the text that is embedded, so the vector arm benefits too.

ALTER TABLE court_cases ADD COLUMN search_intent text;

ALTER TABLE court_cases ADD COLUMN search_intent_tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('danish', coalesce(search_intent, '')), 'A') ||
    setweight(to_tsvector('danish', coalesce(ai_title, '')),      'B') ||
    setweight(to_tsvector('danish', coalesce(original_id, '')),   'C')
  ) STORED;

CREATE INDEX ON court_cases USING gin (search_intent_tsv);

It also softens a known weakness of Postgres full-text search. Both plainto_tsquery and websearch_to_tsquery require every word in the query to match. A five-word question often matches nothing. When the paraphrases and synonyms are already in the indexed text, far more questions match. We keep websearch_to_tsquery because quoted phrases and excluded words are useful in legal search. When the keyword arm still returns nothing, the other two arms carry the result.

Keyword arm: RUM instead of GIN

The usual advice is a GIN index and ts_rank_cd. GIN finds the matches quickly, but ts_rank_cd has to read every matching row's tsvector from the table to score it. On a broad query that matched 267,000 chunks, finding them took 88 milliseconds. Scoring them hit our 44-second timeout. Moving the window function outside the LIMIT does not help here, because the score still has to be computed for every match before the top ones are known.

A RUM index stores word positions inside the index, so ranking happens while the index is read. Its <=> operator returns a distance, where lower is better.

CREATE EXTENSION IF NOT EXISTS rum;

CREATE INDEX idx_vdv_rum_chunk_text_tsv
  ON verdict_doc_vec USING rum (chunk_text_tsv rum_tsvector_ops);

-- <=> is a distance (lower is better) and is served from the index
SELECT id, row_number() OVER (ORDER BY chunk_text_tsv <=> ts_query) AS rank
FROM verdict_doc_vec
WHERE chunk_text_tsv @@ ts_query
ORDER BY chunk_text_tsv <=> ts_query
LIMIT candidate_pool;

The ranking is still not true BM25, but it is now cheap enough for a million rows. RUM indexes are larger than GIN and slower to build. Ours took 10 to 20 minutes on 1.1 million rows and blocked writes in the meantime. On the legislation table we use a partial RUM index that only covers current versions, which keeps it smaller.

Vector arm: HNSW on halfvec

The embeddings are stored at full precision, but the HNSW indexes are built on a half-precision cast. That saved 15 to 20 GB of index storage, with no recall loss we could measure at hnsw.ef_search = 100.

-- the column stays vector(1024); only the index is half precision
CREATE INDEX idx_vdv_hnsw_cosine_half
  ON verdict_doc_vec USING hnsw ((embedding::halfvec(1024)) halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 200);

-- the query must use the exact same expression
SELECT id FROM verdict_doc_vec
ORDER BY embedding::halfvec(1024) <=> $1::halfvec(1024)
LIMIT candidate_pool;

The query has to use exactly the same cast as the index. If it does not, Postgres silently ignores the index and scans the whole table. There is no error, just a slow query.

Filtered vector search needs a literal

Users often search one court or tribunal at a time. The largest sources have their own partial HNSW indexes. Inside a PL/pgSQL function, a condition like type = p_source_filter can be planned without knowing the value, and then Postgres cannot use a partial index whose predicate is type = 'landsskatteretten'. It falls back to the global index and filters afterwards. Because HNSW returns a fixed number of nearest neighbours before the filter is applied, that can leave very few results.

CREATE INDEX idx_vdv_hnsw_landsskat_half
  ON verdict_doc_vec USING hnsw ((embedding::halfvec(1024)) halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 200)
  WHERE type = 'landsskatteretten';

-- a) inline the value so the planner can match the index predicate
RETURN QUERY EXECUTE format($q$
  SELECT ... FROM verdict_doc_vec vdv
  WHERE vdv.type = %L
  ORDER BY vdv.embedding::halfvec(1024) <=> $1
  LIMIT $2
$q$, p_source_filter) USING v_halfvec, candidate_pool;

-- b) or plan every call with its real values
CREATE FUNCTION ... SET plan_cache_mode TO 'force_custom_plan' AS $$ ... $$;

When filters are active we also raise hnsw.ef_search to 200, so the graph search looks further before filtering. pgvector 0.8 adds iterative index scans, which solve the same problem in another way.

Trigram arm: names, typos and compound words

Danish compound words such as parkeringsbøde or hundeloven, people's names and case numbers are exactly where both stemming and embeddings fail. A trigram index on an unaccented, lowercased copy of the title and search intent handles them. The unaccent function is not marked immutable, so it needs a small wrapper before it can be used in a generated column.

CREATE FUNCTION immutable_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
  AS $$ SELECT extensions.unaccent('extensions.unaccent'::regdictionary, $1) $$;

ALTER TABLE court_cases ADD COLUMN fuzzy_text text
  GENERATED ALWAYS AS (
    immutable_unaccent(lower(coalesce(ai_title, '') || ' ' || coalesce(search_intent, '')))
  ) STORED;

CREATE INDEX ON court_cases USING gist (fuzzy_text gist_trgm_ops (siglen = 256));

-- nearest neighbours by word-similarity distance, then a similarity floor
SELECT id, word_similarity(q, fuzzy_text) AS sim
FROM court_cases
ORDER BY q <<-> fuzzy_text
LIMIT candidate_pool;   -- keep sim >= 0.5, rank by sim

We only run this arm for queries of up to three words. For longer questions the nearest-neighbour search gets slow and adds almost nothing.

Fusion: weighted RRF

RRF gives each document 1 / (rank + k) for every list it appears in, and adds them up. It only uses ranks, so it does not matter that a vector distance and a text rank are on completely different scales.

CREATE FUNCTION rrf_score(rank bigint, rrf_k integer DEFAULT 50)
RETURNS numeric LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$ SELECT COALESCE(1.0 / ($1 + $2), 0.0) $$;

-- inside the search function
combined AS (
  SELECT *, 0.5 AS w FROM vec_arm
  UNION ALL SELECT *, 0.5 AS w FROM lex_arm
  UNION ALL SELECT *, 0.4 AS w FROM fuzzy_arm
)
SELECT id, sum(w * rrf_score(rank, 50)) AS score
FROM combined
GROUP BY id
ORDER BY score DESC
LIMIT p_limit;

A few details matter more than they look:

• Each arm returns at least 60 candidates, or twice the final limit, not just the final limit. Otherwise a document ranked 31st in all three arms never gets the chance to win the fusion.
• The arms have weights. On case law, vector and keyword count equally and trigram slightly less. On legislation, keyword counts a little more, because people search for exact terms.
• Domain adjustments come after fusion. Statutes rank above commentary, recent decisions get a small boost capped at 18 percent, and a query containing § 12 pins rows for section 12 to the top of the keyword arm.
• Ties need a tiebreaker. ts_rank_cd produces many equal scores, so ordering by score alone can return different rows for the same query. Add the row id as the last sort key.

Rerank in the application

The database returns about 30 candidates, and a cross-encoder reranker (Isaacus kanon-2-reranker) puts them in their final order. The split works well: RRF is good at getting the right documents into the top 30, and the reranker is good at ordering them. Reranking 30 documents is fast. Reranking 3,000 would not be.

Pitfalls

• SECURITY DEFINER bypasses row-level security. Our search functions use it because the data is public. For user data, keep the default SECURITY INVOKER, and RLS applies to the search exactly as it does everywhere else.
• SET LOCAL work_mem and SET LOCAL hnsw.ef_search inside the function only apply to that call, which is exactly what you want behind PostgREST.
• Generated columns that call a SQL wrapper such as immutable_unaccent broke our Supabase major-version upgrade. We had to drop them before the upgrade and add them again afterwards.
• Building HNSW and RUM indexes without CONCURRENTLY blocks writes. Pause the ingestion pipeline, or build concurrently outside a transaction.
• Check how often each arm actually returns rows. A keyword arm that is silently empty looks fine in the results, because the vector arm still returns something.

When pg_search arrives

Real BM25 would still be better than RUM ranking, and the day Supabase ships pg_search we will swap out the keyword arm. Everything else stays the same: the search intent text, the vector and trigram arms, the fusion and the reranker. Until then, this runs in the database we already have, with row-level security intact and no second search service to keep in sync.