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.

[skamensky]

Interesting strategy! Are there any numbers anywhere comparing performance with the rust extension? I'd assume performance is an issue.

[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. paradedb/paradedb#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]

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

[skamensky]

Cool! Do you know of any plans or non-plans on the supabase side for a direct integration?