5 ms·
BM25 in PostgreSQL
- nitinreddy88 2y agoAny comparison results in terms of performance vs accuracy with: https://github.com/paradedb/paradedb/tree/dev/pg_search https://github.com/paradedb/paradedb/tree/dev/pg_search
- xenator 2y agoSeems like they know about ParadeDB, but for some reason don't publish banchmarks: """Another solution is ParadeDB, which pushes full-text search queries down to Tantivy for results. It supports BM25 scoring and complex query patterns like negative terms, aiming to be a complete replacement for ElasticSearch. However, it uses its own unique syntax for filtering and querying and delegates filtering operations to Tantivy instead of relying on Postgres directly. Its implementation requires several hooks into Postgres' query planning and storage, potentially leading to compatibility issues.""" So it is more apples to red than equal comparison.
- philippemnoel 2y agoHi folks, ParadeDB author here. We had benchmarks, but they were super outdated. We just made new ones, and will soon make a biiiig announcement with big new benchmarks. You can see some existing benchmarks vs Lucene here: https://www.paradedb.com/blog/case_study_alibaba https://www.paradedb.com/blog/case_study_alibaba This comparison isn't super fair -- ParadeDB does not have compatibility issues with Postgres and rather is directly integrated into Postgres block storage, query planner, and query executor
- emilsedgh 2y agoThis looks very neat. In our company, for the past 10 years I've bet heavily on Postgres have not allowed any other databases to be used. Stuff like this makes me hope I can continue to do so as our database is growing. But, it appears that we are hitting our limits at this point. We have a table with tens of millions of rows and the use case is that it's faceted search. Something like Zillow, with tends of millions of listings and tens of columns that can be queried. I'm having a tough time scaling it up. Does anyone have experience building something like that in pg?
- patrickhogan1 2y agopg_bm25 - Optimized for faceted search & PGroonga - Full-text search with JSONB support
- crowdyriver 2y agoUnrelated, but PGroonga reads funny in Argentinian. Better than gimp though
- pezezin 2y agoI read your comment with El Bananero's voice and now I can't stop laughing xD
- thund 2y ago<3 the license https://github.com/tensorchord/VectorChord/blob/main/LICENSE https://github.com/tensorchord/VectorChord/blob/main/LICENSE
- skissane 2y agoWhat happens at a lot of places: Elastic license not on legal’s approved license list - need to get special approval from legal to use this. Legal’s license list says AGPL needs case by case legal review - so need to get special approval from legal to use this. Immediate thought: is there another solution which I don’t need special approval from legal to use?
- isbvhodnvemrwvn 2y agoAll licences restricting commercial use will need to be cleared, that's the point.
- skissane 2y agoYes… but from my point of view, if there is an alternative solution without those restrictions, I’ll go with that. I’d only consider a solution with such restrictions if its other advantages were so compelling as to overcome that (and even then, if one has to ask legal, it isn’t guaranteed they’ll say “yes”)
- phoronixrly 2y agoYes, that is the point.
- rpcope1 2y agoI'd be interested to see how this stacks up against Manticore or Meilisearch.
- gaocegege 2y agoThanks for sharing your feedback! Could you let me know the reason behind it? Are you currently using Manticore or Meilisearch?
- Jysix 2y agoPersonally, I also compare it to using Meilisearch. What I find there is a superb doc, ease of use, full support for other European languages, possibility of sorting by field in addition to BM25, custom stop words (even better if the stop words for each language are already defined), custom ordering. If I could find all this in a Postgresql extension, it would be a dream. Congrats for the progress made, I'll give it a try!
- snikolaev 2y agoMeilisearch doesn't support BM25, does it?
- Kerollmops 2y agoNope, it doesn't. It's based on Cascade Ranking, also called [bucket sorting][1]. We released our new Hybrid search ranking system, combining the best full-text search results (our Cascade Ranking) with semantic results (with arroy, our full-Rust Vector Store). You can try that at https://wheretowatch.meilisearch.com https://wheretowatch.meilisearch.com. [1]: https://en.wikipedia.org/wiki/Bucket_sort https://en.wikipedia.org/wiki/Bucket_sort
- Kerollmops 2y agoThank you very much! We put a lot of effort into our documentation and be ready for the next version of our documentation coming soon. The experience will be even better and faster. We also put much effort recently into simplifying how people can migrate to the next engine version with the [dumpless upgrade feature][1]. We also stabilized our full Rust Vector Store and Hybrid search (AI-powered search) feature in v1.13. [1]: https://github.com/meilisearch/meilisearch/releases/tag/v1.13.0 https://github.com/meilisearch/meilisearch/releases/tag/v1.1...
- siquick 2y agoCan this be used on AWS RDS? I’ve seen a few things like this that would be great to use but without RDS support they’re unusable for us.
- jonathal 2y agoNo, not until AWS decides to add it as a supported extension, which will most likely never happen (you can see the current list of supported extensions here: https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-17x https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLRelea...)
- jszymborski 2y ago> Our next step is to fully decouple the tokenization process, transforming it into an independent and extensible extension. This will enable us to support multiple languages, allow users to customize tokenization for better results, and even incorporate advanced features like synonym handling. This would be rad
- deleted 2y ago[deleted]
- jankovicsandras 2y agoThis looks cool! Shameless plug: https://github.com/jankovicsandras/plpgsql_bm25 https://github.com/jankovicsandras/plpgsql_bm25 BM25 search implemented in PL/pgSQL, might be useful if one can't use Rust extensions with Postgres, e. g. hosted Postgres without admin rights.
- jillesvangurp 2y agoThere's more to Elasticsearch/opensearch than just bm25. It's a Swiss Army knife of stuff that you need to implement search for all sorts of things. Most of which is missing in action with solutions like this. The article mentions stop words, which is an outdated and primitive strategy to deal with often used words adding a lot of overhead to your search. These days with Elasticsearch the advice is actually to not rely on lists of stop words and avoid using them entirely. Reason: the search engine is smart enough to handle very common words like, "to", "not, "be", and "or" efficiently. Bm25 relies on term frequencies for scoring. So these words would have very high term frequencies. The "match" query uses some optimizations that avoid most of the overhead associated with juggling a lot of high frequency term matches and scoring a lot of matches that would score very low. If you are only going to look at five results, scoring 500000 documents that potentially match the query that includes a stop word is really expensive (because you need to score each document and then sort the results). But if you prioritize scoring to low frequency terms first, you can narrow down the result list considerably and avoid scoring most of the documents. Not filtering out stop words means that if you are indexing the works of Shakespeare, you'd actually be able to find the phrase "to be or not to be", which is entirely made up of stop words. Classic edge case to test for. Just an example of one of many optimizations that you'd find in Lucene based search systems that improve both precision and recall metrics as well as performance. There are a lot of wheels that need to be reinvented on the postgresql side to get to that level. It's not quite as simple as "apply bm25 for each hit and sort". Solutions like discussed in the article provide you a some of the low level primitives but without most of the high level abstractions and optimizations that you'd need to build a proper search that is both accurate and fast. Fine if you know what you are doing and can compensate for that but probably not a great starting point if that's not the case. And if you are thinking that it's convenient to index your database model, you'd be well advised to read up on ETL and the notion of optimizing what you index for search, rather than for simple storage. In most more sophisticated search systems what's indexed for search is not the same as what you put in your database and there are some good reasons for that. Even if you do want to use postgres for search, you'd be well advised to consider setting up two completely separate database clusters. One for storing your data and one for indexing and searching through your data. The ETL pipeline in between those is where most of the magic happens. And you typically don't want that on the critical path of simple CRUD operations because that magic tends to be expensive. Which is why you do it in an ETL pipeline so you can keep your writes and queries fast and allocate hardware resources as is appropriate. Basically the T in ETL (extract, transform, load) is where you do all the expensive stuff that you don't want to do when you are querying or interacting with your system and editing things. If you are Google 25 years ago, that's where you'd be calculating page rank, for example. These days they do quite a bit more work. Let's just say that BM25 doesn't quite cut it if you are Google these days. Vector search is a classic example of something that's fairly expensive as well that you don't want to block on while you are writing to a database or file system (e.g. when crawling the web). Doing things asynchronously and decoupling them via queues, databases, filesystems, etc. is basically what data engineering is all about. Of course if you are setting up two systems anyway (a db, and a search index), you might want to consider using something that has all the right tools for the job. Which just isn't postgresql unless your ambition level is relatively low and search is a low priority thing for you. Whether you can afford to be naive and unambitious is ultimately a business choice. But you should be considering what your competitors do and what the consequences are of not being quite as good. A lot of my consulting clients end up talking to me because they figure out they are not as good as they should be. I've helped transition several clients from naive postgresql based systems to Opensearch recently. Performance issues you can usually solve by throwing hardware at a problem. But quality issues require using better tools and knowing what to use.
- __jl__ 2y agoHow does this compare with pg_search (formally pg_bm25) from ParadeDB?
- mattashii 2y agoLooks like this is based on a fork of the pg_bm25 code: https://github.com/tensorchord/VectorChord-bm25/commit/1583865211b295afd55c972da7d31a2c897bdb16 https://github.com/tensorchord/VectorChord-bm25/commit/15838...
- VoVAllen 2y agoHi, I'm the tech lead of VectorChord-bm25. It's not based on pg_search (pg_bm25). We just chose the same name during our internal development, and changed it to the formal name VectorChord-bm25 when we released it.
- ngrilly 2y agoAwesome! How does it compare to RUM, another PostgreSQL extension solving a similar problem (make ranking fast by putting enough info in the index)?
- deleted 2y ago[deleted]