Add new pg_bm25 full-text-search postgres extension

[olee]

A new, powerful full-text-search extension for postgres has been released recently called pg_bm25.
I think it would be a huge benefit to add support for this new interesting extension to the supabase environment (docker image & cloud).

Besides the basic pg_bm25 extension, there's also the pg_search extension to combine bm25 with vector searches which would probably make sense to add as well.

[skamensky]

pg_search would be awesome! Then I could try not adding elasticsearch to the stack.

[jankovicsandras]

I have no comparison data yet. Performance vs. Rust extensions is a very good question, but there are so many details that affect this, that probably only testing can decide this. I add this to the Roadmap / TODO.

[jankovicsandras]

@skamensky : I made a notebook with some comparison: plpgsql_bm25_comparison_with_paradedb_pg_search.ipynb A very rough estimate is that plpgsql_bm25 uses 1.3-1.4x more time for BM25 queries than pg_search (ParadeDB, Rust based).

[skamensky]

Very cool! That's not bad at all! Definitely makes using it an option for autocomplete usecases.

Do you know what accounts for the reduced correctness? Isn't the algorithm the same?

[jankovicsandras]

Yes, I noticed that pg_search seem to be more correct, but I think the cause is my bad, quick and dirty test method. I couldn't find info on ParadeDB tokenization for input questions (e.g. https://github.com/paradedb/paradedb/issues/1631 ), so I use a very hacky "word1 OR word2 OR ..." query, which is a different input than the other three (plpgsql_bm25, rank_bm25 and mybm25okapi) gets. The fact that plpgsql_bm25 results and scores are the same as rank_bm25 (which is used by e.g. LangChain) tells more about correctness. The test notebook has only 1, relatively biased dataset. I didn't have time for more throughout testing, but I recommend trying with your environment. :)

[philippemnoel]

:) Plpgsql_bm25 is cool, but far from a complete search engine implementation. If you're curious, I'd recommend reading: https://fulmicoton.com/posts/behold-tantivy/

[philippemnoel]

Hi everyone! Phil here from @paradedb. You can get "ParadeDB on Supabase" today by doing logical replication.

Supabase's docs: https://supabase.com/docs/guides/database/postgres/setup-replication-external
ParadeDB's docs: https://docs.paradedb.com/replication/pg_search

[philippemnoel]

Hi there! Sorry I missed this. We do have plans with many Postgres-as-a-Service providers for a direct ParadeDB integration. It is supported on Alibaba Cloud, Ubicloud, and Tembo today (with more coming soon). No concrete plans with Supabase yet, but we've discussed it many times :)

[FloMys]

@philippemnoel I would be very grateful and willing to pay if you added Supabase soon! 👍

[jonathan-nielsen]

@philippemnoel +1 for ParadeDB with Supabase. Currently hosting at Ubicloud but this adds more overhead, more costs and more delays to our solution. Would love to see your service integrated with our hosting!

[skamensky]

Really the main weakness with replication here is giving up on supabase's kick-ass auth integration.

RLS with search? Dreamy

[Strijdhagen]

@philippemnoel does paradedb work with postgrest atm?

[Rubenburdin]

Hey all,
Just catching up on this thread in 2025, the ParadeDB situation with Supabase is still a bit in limbo from what I can tell. Phil mentioned integrations with other providers but nothing concrete for Supabase yet.
@skamensky you raised the real pain point - losing RLS when doing logical replication to an external ParadeDB instance kind of defeats one of the main reasons to use Supabase in the first place. If your search needs to respect row-level policies, replicating out means reimplementing that auth logic on the ParadeDB side, which gets messy fast.
@Strijdhagen regarding PostgREST compatibility, pg_search creates its own index types and query syntax (the @@@ operator), so you'd need to use RPC functions to wrap those queries rather than calling them directly through the REST API. Not impossible, just means writing a stored procedure that handles the BM25 search and exposing that via postgrest.
For anyone stuck waiting on native pg_search support, Supabase's built-in full-text search with tsvector/tsquery is more capable than people give it credit for these days. It's not BM25, but with proper weighting on ts_rank_cd and good index configuration you can get decent results for most use cases. The docs walk through it here (https://supabase.com/docs/guides/database/full-text-search). Obviously if you need hybrid vector + keyword search, that's where pg_search really shines and there's no native equivalent yet.

[ElectricCodeGuy]

While waiting for native pg_search/pg_bm25 support, you can achieve solid hybrid search using PostgreSQL's built-in full-text search combined with pgvector. I've been running this in production with ~1M rows and text-dense chunks (1-20 paragraphs per row).

Table Structure

Add a tsvector column alongside your vector embeddings using a generated column:

CREATE TABLE your_doc_vec (
  id serial PRIMARY KEY,
  chunk_text text NOT NULL,
  embedding vector NOT NULL,
  -- Auto-maintained tsvector column
  chunk_text_tsv tsvector GENERATED ALWAYS AS (to_tsvector('danish', chunk_text)) STORED
);

-- GIN index for fast text search
CREATE INDEX idx_chunk_text_tsv_gin ON your_doc_vec USING GIN (chunk_text_tsv);

-- HNSW index for vector search
CREATE INDEX idx_embedding_hnsw ON your_doc_vec 
USING hnsw (embedding vector_cosine_ops) WITH (m = 32, ef_construction = 128);

The GENERATED ALWAYS AS ... STORED column auto-updates whenever chunk_text changes — no triggers needed.

RRF Score Function

Use Reciprocal Rank Fusion to combine vector and text search results:

CREATE OR REPLACE FUNCTION rrf_score(rank_val bigint, k integer DEFAULT 60)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
  SELECT CASE WHEN rank_val IS NULL THEN 0.0
         ELSE 1.0 / (rank_val::numeric + k::numeric) END;
$$;

Hybrid Search Function

CREATE OR REPLACE FUNCTION search_hybrid(
  p_embedding vector,
  p_search_text text,
  p_limit integer DEFAULT 30,
  p_rrf_k integer DEFAULT 60
)
RETURNS TABLE(id integer, chunk_text text, score numeric)
LANGUAGE plpgsql AS $$
BEGIN
  SET LOCAL work_mem = '256MB';  -- helps with large tables
  
  RETURN QUERY
  WITH vector_results AS (
    SELECT doc.id, doc.chunk_text,
           row_number() OVER (ORDER BY doc.embedding <=> p_embedding) AS rank
    FROM your_doc_vec doc
    ORDER BY doc.embedding <=> p_embedding
    LIMIT p_limit
  ),
  text_results AS (
    SELECT doc.id, doc.chunk_text,
           row_number() OVER (ORDER BY ts_rank_cd(doc.chunk_text_tsv, 
             plainto_tsquery('danish', p_search_text)) DESC) AS rank
    FROM your_doc_vec doc
    WHERE plainto_tsquery('danish', p_search_text) @@ doc.chunk_text_tsv
    ORDER BY ts_rank_cd(doc.chunk_text_tsv, plainto_tsquery('danish', p_search_text)) DESC
    LIMIT p_limit
  ),
  combined AS (
    SELECT * FROM vector_results
    UNION ALL
    SELECT * FROM text_results
  )
  SELECT c.id, c.chunk_text, sum(rrf_score(c.rank, p_rrf_k)) AS score
  FROM combined c
  GROUP BY c.id, c.chunk_text
  ORDER BY score DESC
  LIMIT p_limit;
END;
$$;

How It Works

Column	Purpose	Index
embedding (vector)	Semantic/meaning search	HNSW (vector_cosine_ops)
chunk_text_tsv (tsvector)	Keyword/BM25-style search	GIN

The function runs both searches in parallel, ranks each result set, then combines them using RRF. Documents appearing in both result sets get boosted scores.

ts_rank_cd uses cover density ranking which approximates BM25's term frequency normalization. Not identical to true BM25, but works well in practice.

Key Points

• Language config: Use 'danish' (or your language) for proper stemming. Use 'simple' if you have mixed-language content
• RRF k value: 60 is standard. Lower = more weight to top results
• No threshold filtering on vectors: Let HNSW do its thing, threshold filters block index usage
• Embedding dimensions: Use a model with lower dimensions for better performance. I use Voyage AI with their contextualized embedding model (int8, 1024 dimensions)
• Works with Supabase RLS since it's pure PostgreSQL

This approach gives you hybrid search with full RLS support today, no external services needed.

Feel free to reach out if you have any questions — happy to help!

[pavangupta352]

@ElectricCodeGuy's version is the right shape and matches what I ended up with. Two things in it are worth flagging for anyone copying it, because both fail silently rather than erroring.

plainto_tsquery ANDs the terms. plainto_tsquery('danish', 'opsigelse varsel periode') becomes 'opsigels' & 'varsel' & 'period', so only documents containing all three match. For a filter that is correct. For the keyword half of a hybrid search it is quietly destructive: a four or five word question usually matches nothing at all, the text CTE comes back empty, and the search silently degrades to pure vector search. You get results, they look plausible, and the half you added full-text search for contributed nothing.

On a twelve row test corpus a three word query matched exactly one row under AND. If you are running this in production, worth checking how often your text CTE is actually returning rows.

The fix is to OR the terms and let ranking do the work, since ts_rank_cd already scores documents matching more of them higher. Tokenise client side and combine one parser call per term with ||, rather than rewriting operators inside a parsed tsquery. The rewrite looks simpler and is wrong: 'a' & !'b' becomes 'a' | !'b', which matches every document that merely lacks b.

The window has to sit outside the LIMIT. In

SELECT doc.id, row_number() OVER (ORDER BY ...) AS rank
FROM your_doc_vec doc
ORDER BY ... LIMIT p_limit

the row_number() is evaluated before the limit, so it has to see every matching row. On a text query that matches a large fraction of a million rows, that is the whole match set, not p_limit rows. Ranking only the survivors gives the same answer for less work:

SELECT id, row_number() OVER (ORDER BY distance) AS rank
FROM (
  SELECT id, embedding <=> p_embedding AS distance
  FROM your_doc_vec
  ORDER BY distance, id
  LIMIT p_limit
) c

1.19ms against 0.85ms on 100k rows in my measurements, and the gap widens with the table.

The , id there is not decoration either. ts_rank_cd ties heavily: on one benchmark corpus it produced only 3 distinct values across 3,399 matching rows, so without a tiebreaker the rows chosen at the cut-off can differ between two identical queries.

On the RLS point @Rubenburdin raised: that is the strongest argument against the replication workaround and it does not apply to this approach at all. The query runs on your own connection against your own table, so RLS applies exactly as it does everywhere else. No second instance, no replication lag, no policy to reimplement.

None of this beats real BM25. pg_search is genuinely better than ts_rank_cd and if Supabase ships it you should use it. But this works today, on the database you already have.

---

I packaged the above after hitting all of it: pghybrid generates the statement with OR semantics, the window outside the limit, tiebreakers on both sides, and a doctor command that measures recall against exact search rather than assuming the index is fine. Python and TypeScript, MIT, and there is standalone SQL in the repo if you would rather just copy the query. There is also a Supabase specific guide with the RPC function written out.