6 ms·
Pg_bm25: Elastic-Quality Full Text Search Inside Postgres
- tristan957 3y agoInteresting that you guys are the same people behind Whist. I once interviewed there at your behest, and never heard back. It seems like that venture fizzled out?
- aiunboxed 3y agoI wonder how do legacy search players like elastic / solr compete against the new age startups combining semantic and regular search ?
- ntonozzi 3y agoBy adding the features that those new age startups launch: https://www.elastic.co/guide/en/elasticsearch/reference/current/knn-search.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... Building a classic text search engine is way harder than building a KNN engine, and bolting a KNN engine into a term search engine is easier than the other way around.
- vb-8448 3y agoReading "legacy" near "elastic" make me feel a little bit old :D :D BTW, if you are one of the leaders of the market, you don't need to continuously improve, just wait and let your competitors do the research job and implement only when the feature is mature.
- aiunboxed 3y ago:D :D Sorry my question was on the basis of the quality of the results, simply put .. how does players who have good semantic search turn out against "legacy" players who had good text search
- kriz9 3y agoWho is the competition besides Algolia? Last I checked most of the competition is either very expensive or very feature limited compared to Elastic/Solr.
- binarymax 3y agoLots of reasons: 1) switching search engines is hard when you’ve built your information needs around one. I’ve led lots of search engine migrations and they’re not fun. I even gave a talk on the problems companies face when doing so. https://haystackconf.com/us2020/search-migration-circus/ https://haystackconf.com/us2020/search-migration-circus/ 2) lots of the new search startups don’t offer full feature coverage. So just because a company is the new hotness it doesn’t mean it can fill the need of someone entrenched in Solr/elastic 3) why risk going to a startup when they haven’t proven they’ll be around in 3 to 5 years? 4) incumbent search engines eventually catch up at the speed of the enterprise market. Why spend a year migrating when the engine your using will implement the feature for you within that timeframe?
- philippemnoel 3y agopg_bm25/ParadeDB author here. What we're doing is building an opinionated alternative within PostgreSQL. If you are not using Postgres, or want your system to be separate, Elastic is still the best choice and will likely remain so. Other people have brought up great points for why or why not to switch. Our vision for this is that ParadeDB is not merely "better" than Elastic, but rather different. Elastic will never be a PostgreSQL database, and we'll never be a NoSQL search engine. If you want one or the other, you'll pick either ParadeDB or Elastic.
- jillesvangurp 3y agoThey are part of the hype. Lucene has vector search capabilities. Elasticsearch and Opensearch have support for that (slightly different implementations). I assume solr has similar capabilities. The combination of traditional search and vector search makes a lot of sense from a cost control point of view. Vector search at scale is expensive. The smaller the result set, the cheaper it is to do vector search over it. So using a cheap traditional search to limit the results before you run vector search makes a lot of sense. Also, bm25 holds up well against vector search. A well tuned model can outperform it but many off the shelf models struggle to do that. Vector search is a useful tool but so far it's not a one size fits all solution that "just works". It's something that can work really well if you know what you are doing and with a lot of tuning. With things like Elasticsearch you can try both approaches.
- antman 3y agoAn important step, could be a good combination with pg_vector if they are fast enough
- pritambaral 3y agoI believe the parent project — paradedb — already does that, for their support of HNSW indexes.
- philippemnoel 3y agoThat's right, we do support pgvector (it is pre-installed on ParadeDB) and support full HNSW. In fact, we even have another extension, called pg_search, which is the combination of searching on pgvector and pg_bm25 for better results! Topic of another blog post to come sometime soon :)
- mkleczek 3y agoThis is really exciting and I hope to try it out at my company ASAP.
- hardwaresofton 3y agopgrx is one of the greatest enabling innovations in the PG ecosystem in a long time. Awesome to see so many high quality extensions come out of it. https://github.com/pgcentralfoundation/pgrx https://github.com/pgcentralfoundation/pgrx
- philippemnoel 3y agopgrx is awesome and making pg_bm25 would've been infinitely more challenging without it. Check them out if you want to make a Postgres extension, we can't recommend them enough
- zombodb 3y agoThank you. I’ll pass this on to the team.
- canadiantim 3y agoParadeDB and the work they’re doing with this extension is incredibly exciting. Love to see it.
- est 3y agolooks like a cool project https://github.com/paradedb/paradedb https://github.com/paradedb/paradedb
- phamilton 3y agoWith an AGPL license, does that make it unlikely to be included in hosted environments like RDS? My understanding of the spirit of the license is that it should be fine as long as modifications are made available. Anyone know of any existing extensions in RDS that are AGPL?
- klysm 3y agoI forget, does AWS let you use custom extensions from pgrx?
- adobrawy 3y agoNo, they allow use Rust for custom functions (alternatively to PL/SQL) only.
- adobrawy 3y agoSee who made pg_bm25 - vendor of database based on PostgreSQL. Most likely they would like offer that as hosted solution itself, so they attempt avoid Elasticsearch / Terraform-like drama using AGPL license from beginning.
- allan_s 3y agoRelated question, could it be possible that at some point postgresql natively implements that algorithm ? Or as there is already an extension doing it , regardless of the licence , it is unlikely that patches in that direction will be accepted ?
- j45 3y agoRunning it for your own purposes as part of a solution that includes search should be fine under AGPL. If your product is elastic search built into Postgres as a repackaged and direct competitor to this search plug-in, that’s where my understanding is over the line.
- philippemnoel 3y ago
- mugivarra69 3y agois this better than lucene
- benpacker 3y agoThe underlying engine, Tantivy, has better performance characteristics than Lucene. You can compare Lucene to Tantivy and can compare Elasticsearch to pg_bm25 or ParadeDB
- samokhvalov 3y agoI checked the benchmarks and was surprised to see that native search is (a) so slow (seconds), and (b) demonstrating O(N) behavior – with indexing, it should not happen at all. Indeed, looking at the benchmark source code (thanks for providing it!), it completely lacks index for the native case, leading to a false statement the that native full-text search indexes Postgres provides (usually GIN indexes on tsvector columns) are slow. https://github.com/paradedb/paradedb/blob/bb4f2890942b85be3e9736bb3e8f17dcf659c0a1/benchmarks/benchmark-tsquery.sh#L77 https://github.com/paradedb/paradedb/blob/bb4f2890942b85be3e... – here the tsvector is being built. But this is not an index. You need CREATE INDEX ... USING gin(search_vector); This mistake could be avoided if bencharks included query plans collected with EXPLAIN (ANALYZE, BUFFERS). It would quickly become clear that for the "native" case, we're dealing with SeqScan, not IndexScan. GINs are very fast. They are designed to be very fast for search – but they have a problem with slower UPDATEs in some cases. Another point, fuzzy search also exists, via pg_trgm. Of course, dealing with these things require understanding, tuning, and usually a "lego game" to be played – building products out of the existing (or new) "bricks" totally makes sense to me.
- philippemnoel 3y agoOne of the ParadeDB authors here, hey! Thanks for pointing this out, you're completely right. That's an oversight on our end. We'll update the benchmarks and re-run them to correct this :)
- gvkhna 3y agoGreat to hear, a benchmark against trigram searching with gin index would also be great. There are multiple ways to do full text search with postgres and they’re all insanely fast and memory efficient. Benchmarking various methods for comparison would be helpful. https://www.crunchydata.com/blog/postgres-full-text-search-a-search-engine-in-a-database https://www.crunchydata.com/blog/postgres-full-text-search-a...
- philippemnoel 3y agoThanks for sharing, will look to add a benchmark for that as well
- stopman 3y agoExcited to give this a try.
- retakeming 3y agoBlog post author and one of the pg_bm25 contributors here. Super excited to see the interest in pg_bm25! pg_bm25 is our first step in building an Elasticsearch alternative on Postgres. We built it as a result of working on hybrid search in Postgres and becoming frustrated with Postgres' sparse feature set when it comes to full text search. To address a few of the discussion points, today pg_bm25 can be installed on self-hosted Postgres instances. Managed Postgres providers like RDS are pretty restrictive when it comes to the Postgres extension ecosystem, which is why we're currently working on a managed Postgres database called ParadeDB which comes with pg_bm25 preinstalled. It'll be available in private beta next week and there's a waitlist on our website (https://www.paradedb.com/ https://www.paradedb.com/).
- ralusek 3y agoFor what it's worth, the single biggest selling point to a better search, for me, would be not having to deal with additional infrastructure and all the hassle that comes with keeping data in sync. I would be very reluctant to move off of RDS/Aurora, and therefore have my principal motivation to use something like this is greatly negated. I understand that it becomes very hard to monetize if you're not able to offer your own hosted service, and I don't have a solution for that, but not supporting RDS is going to really diminish the product for many people.
- philippemnoel 3y agoOur goal is for one day ParadeDB to be a viable alternative to AWS RDS/Aurora, so that like you say, you don't need to keep data in-sync and can just use one system (ParadeDB). Soon it will be possible for you to have ParadeDB running on your AWS (utilizing your cloud credits+all security/privacy guarantees) but be managed via the ParadeDB dashboard, similar to how Aurora works from a developer UX. Of course if you are 100% attached to AWS RDS itself (rather than the convenience of AWS RDS, which is replicable by ParadeDB), then there's not much we can do here, as we also need to eat :')
- olivermuty 3y ago
- rawsh 3y agoIs it possible to use this for hybrid search in combination with pg_embedding? My understanding is that hybrid search currently requires syncing with Postgres
- philippemnoel 3y agoYes! We have another extension, pg_search, which is specifically for hybrid search using pg_bm25+pgvector. You can find it here: https://github.com/paradedb/paradedb/tree/dev/pg_search https://github.com/paradedb/paradedb/tree/dev/pg_search
- iamdanieljohns 3y agoSeems really really cool. Is this a full DB, as in they have to take PG source, put in tantivy and their sauce, compile, and distribute? Or is this an extension? If it's the latter, what's the point of putting DB at the end of the name?
- iamdanieljohns 3y agoOk, all caught up now. Great work and best of luck! When it comes to the business model: it seems an acqui-hire by Supabase/Neon/etc would be the best bet. It insures the team's focus is on the core product instead of the litany of things to figure out when creating a pg hosting service (payments, downtime, upgrades, customer support, ...) in this highly competitive and demanding market.
- wkoszek 3y agoHey guys. Congratulations - this is an exciting development. Can you show some benchmarks around showing the count of matches -- `select count() from table where text match is there`? This was the top reason that made us (Segmed.ai) give up on PostgreSQL FTS -- our folks require a very exact count of matches for medical conditions that are present in 20M reports. And doing COUNT() in PostgreSQL was crazy, crazy slow. If your extension could do simple len(invertedindex[word]) that would already be a great improvement. ELK has it immediately, but at a cost of being one more thing to maintain, and the whole Logstash thing is clunky. I'd love to use FTS inside of PostgreSQL.
- benpacker 3y agoI’m not sure if Postgres could support that type of operation directly via count() since I don’t know if the fact that no other filters are present is available to the Index Access Method API. It might be possible to do a separate function though, like: select pg_bm25_direct_count(‘term’)*
- wkoszek 3y agoThat would be fine--basically any way of achieving it would be fine. As of now, in PostgreSQL's FTS, I don't think there's any way to do this fast enough to give it back to the user.
- dekimir 3y agoIf you do that, I can update postgres-searchbox [1] to use it for better frontend experience. [1] https://www.npmjs.com/package/postgres-searchbox https://www.npmjs.com/package/postgres-searchbox
- retakeming 3y agoThanks! We released support for metrics aggregations a few days ago, including count: https://docs.paradedb.com/aggregations/metrics#count https://docs.paradedb.com/aggregations/metrics#count. We haven't gotten around to benchmarking aggregations - that's the focus for next week and we'll publish them once they're done. I would suspect that it's a lot faster than Postgres aggregates since it leverages Tantivy Columnar.
- eclectic29 3y agoIs BM25 still used by "modern" search engines? I wasn't aware.
- anon373839 3y agoThis is very exciting. BM25 in Postgres will enable really nice search experiences to be built in projects where Elasticsearch is just too much complexity.
- ckok 3y agoDoes this also cover some kind of facetted search? (Counting the different colored and sized t-shirt) in an efficient way? As that is also a large part that elastic can do but PostgreSQL isn't very good at.
- machty 3y agoWhat kind of "consistency" do bm25 indexes offer? e.g. I think ElasticSearch is eventually consistent and is constantly indexing in the background and classic Postgres GIN indexes have configuration like `gin_pending_list_limit` and `fastupdate` functionality to avoid slowdowns on insertions (and then you get slowdowns when an insert hits the threshold and triggers the catch-up indexing).
- philippemnoel 3y agoParadeDB and pg_bm25 offer weak consistency. pg_bm25 doesn't slow down transactions for indexing, and like ElasticSearch it becomes become eventually consistent shortly after (typically at most a few seconds, altough your mileage may vary based on the amount of data modified in the transaction(s)).