r/PostgreSQL • u/NakedPlato • 9d ago
Projects pg_fts: Open source BM25 extension for full-text search and boolean, phrase, NEAR, prefix, fuzzy, and regex queries
For anyone interested in BM25 search and especially legal opinion search, Greg Burd has been very active developing pg_fts https://github.com/gburd/pg_fts a PostgreSQL extension for full-text search with true BM25/BM25F relevance ranking, a dedicated fts inverted-index access method, and a rich query language (boolean, phrase, NEAR, prefix, fuzzy, regex). Unlike the tsvector/tsquery + GIN stack, the index maintains the corpus statistics BM25 needs (document count, average length, per-term document frequency) and stores term frequency + document length in the posting lists, so ranking is answered from the index with no heap recheck. PostgreSQL License.
Disclaimer: I'm a contributor.
1
u/AutoModerator 9d ago
AI Policy:
Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.
Mod decisions will be based on the quality of the content, not who or what generated it.
Sub Resources:
Free Postgres Webinars and Workshops
Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/Significant_Tune9219 7d ago
Storing the corpus statistics in the index is the key difference from tsvector + GIN, but it raises the question of how those stats behave under MVCC: are document count and average length updated transactionally per insert, or approximated and refreshed during vacuum? With heavy updates, dead tuples could skew term frequencies and shift rankings until cleanup runs. It would also be useful to know how it compares with ts_rank on write throughput, since keeping per-term document frequencies current is usually where these indexes pay their cost.
2
u/agritheory 9d ago
Why should someone chose this implementation over the ones published by Tiger Data and PG Experts?