Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

docs.paradedb.com

Post reply on HN
Print view View original

51–60 of 72 posts

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#51

Post by dekimir »

benpacker wrote: post #46
wkoszek wrote: post #43

Hey 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 slo…

I’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’)*

If you do that, I can update postgres-searchbox [1] to use it for better frontend experience.

[1] https://www.npmjs.com/package/postgres-searchbox

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#52

Post by retakeming »

wkoszek wrote: post #43

Hey 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 slo…

Thanks!

We released support for metrics aggregations a few days ago, including 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.

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#53

Post by francoismassot »

retakeming wrote: post #52
wkoszek wrote: post #43

Hey 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 slo…

Thanks! We released support for metrics aggregations a few days ago, including 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.

Nice! I would be very interested by your benchmark, don't hesitate to jump in the quickwit discord server to talk about the results. https://discord.quickwit.io/

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#54

Post by olivermuty »

philippemnoel wrote: post #42
ralusek wrote: post #38

Earlier quoted context omitted.

For 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…

Our 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 a…

Will you be providing this for bring-your-own-compute in general? There is a gaping hole in the market for this. All the big vendors that provide postgres as a service require you to be on very specific types of hosting like aws fargate, google gke etc (looking at you Crunchydata).

We are using Scaleway (french cloud) which is heaven when it comes to GDPR and Schrems compliance, but once we grow out of their managed db offerings or if we want something their managed db offering does not provide we are out of luck.

Been looking for a year more or less now and I am simply unable to find something that doesnt amount to us just paying a fraction of a consulting FTE to be our lightweight DBA. There are only so many ways you can set up postgres HA, it is amazing that no one has made a product out of doing it for someone else yet.

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#55

Post by jillesvangurp »

aiunboxed wrote: post #6

I wonder how do legacy search players like elastic / solr compete against the new age startups combining semantic and regular search ?

They 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.

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#57

Post by zombodb »

hardwaresofton wrote: post #5

pgrx 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

Thank you. I’ll pass this on to the team.

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#59

Post by arecurrence »

samokhvalov wrote: post #22

I 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 co…

This sort of thing is more common with postgres than you'd think. I interviewed a candidate once whose company completely replaced querying in their postgres with elasticsearch because they could not figure out how to speed up certain text search queries. Nothing they tried would use the index.

Re: Pg_bm25: Elastic-Quality Full Text Search Inside Postgres

#60

Post by iamdanieljohns »

iamdanieljohns wrote: post #41

Seems 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?

Ok, 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.

Post reply on HN