Re:Postgres Full Text Search is better than
by imaurer on Wednesday April 26, 2023 @08:25PM
(#35721720)
I hope someone implements BM25 and combines it with Pgvector to bring hybrid search to Postgres. I feel like that is the jsonb of the next couple of years.
Re:Postgres Full Text Search is better than
by justinclift on Thursday April 27, 2023 @01:09AM
(#35723546)
> Most search engines use TF/IDF or BM25 for relevance calculations ...
Ahhh. That's what's SQLite's "bm25()" function is for.
Was white listing the SQLite FTS functions a few days ago for DBHub.io (eg so people can use them), but don't really understand (yet) how most of them are actually used. Some day I'll get around to learning about them properly. :)
It does have one surprising limitation: it calculates relevance based on just the current row, rather than being able to take statistics across the whole corpus into account.
Most search engines use TF/IDF or BM25 for relevance calculations, which consider the relative common-ness of terms in comparison to the rest of the corpus. PostgreSQL FTS can't do that as far as I know.
SQLite's built-in FTS CAN do relevance calculations like this! Surprising to see a feature as significant as that show up in SQLite but not in PostgreSQL.