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

Postgres Full-Text Search: A search engine in a database

blog.crunchydata.com

Post reply on HN
Print view View original

121–130 of 141 posts

Re: Postgres Full-Text Search: A search engine in a database

#121

Post by cmckn »

__jem wrote: post #104
nostrademons wrote: post #89

Earlier quoted context omitted.

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

I built a big feature with percolate as well and it really sold me on the possibilities of ES for product architecture.

I’ve never really had a good idea of what Percolator is. I understand it to be a “streaming” indexer that replaced a “batch” indexer based on MapReduce. ES allows indexing individual documents (or small batches) vs. rebuilding the entire index; is that the connection you’re making?

Re: Postgres Full-Text Search: A search engine in a database

#122

Post by rawoke083600 »

nostrademons wrote: post #89
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

>search ranking is a complicated and subtle problem

Absolutely ! I think never before the saying "the devil is in the details"... is more appropriate than here.

Sure most users agree on the extreme's the REALLY bad search result (putting in 'apple' getting out LCD TV's [maybe the tv's are in parent-category 'Electronics' and you have category and popularity boost to high' ?]) and the REALLY good (results that you expected)

Search Results are commonly evaluated with Recall(did ALL the documents that are relevant got returned) vs Precision (how many of the results are 'correct')

But that is "one" of many-many metrics.

The biggest issues are non-tech ppl (like your boss or manager) walking in and "discussing" his/her pet-peeve-search query, cause in his mind when he put in APPLE iPhone we should be returning ONLY apple-iphones and NOT apple-iPhone accessories) or maybe we should be returning ONLY the "latest iphone" not the model from 2 generations back

I've commented this before, you can usually only shoot for an "average amount of happiness" (sounds like Arthur Schopenhauer ? :P) for most users. Never "ok ppl, search works perfect" for everyone one now

As to the "practical matters", we found that building a "search-test-suite" where you put in the "manager's pet-peeve" as well as any angry-emails about search queries, and whenever do you search-tuning it's easy to see any-query-regression oh and of course this needs to be an automated search-test-suite.

Re: Postgres Full-Text Search: A search engine in a database

#123

Post by nuker »

jillesvangurp wrote: post #119
nuker wrote: post #111

Is there alternative to ES that scales nicely? I'm running ELK stack for logging using AWS Elasticsearch. Logs have unpredictable traffic volume and even overprovisioned ES cluster gets clogged sometimes. I wonder is there something more scalable than ES, and have nice GUI like Kibana?

It's more a matter of configuring it right. I'd recommend trying out Elastic Cloud. It's a bit easier to deal with than Amazon's offering and much better supported. AWS has always been a bit hands-off on that front. Their opensearch project does not seem to break that pattern so far. Also, with Elastic Cloud you get some access to useful features for logging (like life cycle management and data streams) that will hel…

Thanks mate. I did tried Elastic before settling on AWS ES, it was slower somehow. As for Kibana features, im happy with barebones :)

> you use e.g. file or docker beats for collecting logs.

My setup is custom app reading from CW Logs.

But can Elastic cluster scale automatically on indexing latency spikes, so my apps writes do not time out? If yes then how, please?

Re: Postgres Full-Text Search: A search engine in a database

#124

Post by hnarn »

nuker wrote: post #111

Is there alternative to ES that scales nicely? I'm running ELK stack for logging using AWS Elasticsearch. Logs have unpredictable traffic volume and even overprovisioned ES cluster gets clogged sometimes. I wonder is there something more scalable than ES, and have nice GUI like Kibana?

I don't know what features AWS provides but in general terms logs benefit a lot from compression, so if I were to set up this on my own I'd probably want to try something like a VDO or ZFS backed storage system as well as compressed transfers (perhaps in batch if that's required).

Re: Postgres Full-Text Search: A search engine in a database

#125

Post by simondotau »

nostrademons wrote: post #89
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

There is an incredibly large difference between effective scoring for data that is being gamed and data that is not being gamed. Most people's full text data isn't being gamed quite like web search SEO.

There is also an incredibly large difference between effective scoring for data that has deep relationships and data that does not. Most people's full text data doesn't have deep relationships like web pages do with inbound links.

Re: Postgres Full-Text Search: A search engine in a database

#126

Post by nuker »

hnarn wrote: post #124
nuker wrote: post #111

Is there alternative to ES that scales nicely? I'm running ELK stack for logging using AWS Elasticsearch. Logs have unpredictable traffic volume and even overprovisioned ES cluster gets clogged sometimes. I wonder is there something more scalable than ES, and have nice GUI like Kibana?

I don't know what features AWS provides but in general terms logs benefit a lot from compression, so if I were to set up this on my own I'd probably want to try something like a VDO or ZFS backed storage system as well as compressed transfers (perhaps in batch if that's required).

> something like a VDO or ZFS backed storage system

And what text search engine?

Re: Postgres Full-Text Search: A search engine in a database

#127

Post by radiospiel »

craigds wrote: post #84
jka wrote: post #72

Earlier quoted context omitted.

"Faceted search"[1] (aka aggregates in Elasticsearch) tends to be a popular one, to provide user-facing content navigation. That said, simonw has been on the case[2] demonstrating an implementation of that using Django and PostgreSQL. [1] - https://en.wikipedia.org/wiki/Faceted_search [2] - https://simonwillison.net/2017/Oct/5/django-postgresql-facet...

This is the key one for us that makes Postgres a non-starter for FTS (We use postgres for everything else ) We begrudgingly use Solr instead (we started before ES was really a thing and haven't found a need to switch yet) When you get more than about two different types of filters (e.g. types of filters could be 'tags', 'categories', 'geotags', 'media type', 'author' etc), the combinatorial explosion of Postgres quer…

You could run this in a single query `count(*) .. group by (tag, category_id)` and then post process the results.

counting by groups is still relatively expensive though in postgres; I built an implementation which uses estimate counting (via query planner) for larger buckets, and exact counting for only smaller buckets, but my use case allowed for some degree of inconsistency.

Re: Postgres Full-Text Search: A search engine in a database

#128

Post by johnx123-up »

nostrademons wrote: post #89
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

Did you get a chance to compare MeiliSearch?

Re: Postgres Full-Text Search: A search engine in a database

#129

Post by hnarn »

nuker wrote: post #126
hnarn wrote: post #124

Earlier quoted context omitted.

I don't know what features AWS provides but in general terms logs benefit a lot from compression, so if I were to set up this on my own I'd probably want to try something like a VDO or ZFS backed storage system as well as compressed transfers (perhaps in batch if that's required).

> something like a VDO or ZFS backed storage system And what text search engine?

I have no idea, but I’m assuming that regardless of which one you use there will be disk level duplication that could be taken care of to alleviate uncontrolled growth somewhat.

Re: Postgres Full-Text Search: A search engine in a database

#130

Post by dreamer7 »

IgorPartola wrote: post #110
nostrademons wrote: post #89

Earlier quoted context omitted.

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

So let’s say that you are building a search engine for performance car parts. There are going to be a bunch of technical terms you use there that are not necessarily going to stand out in the document itself but you know them to be important. For example, the amount of boost pressure a turbo can provide or the number of pistons in a brake caliper. Is there some structured way to specify the grammar which is used for…

When I implemented search for my app, I built a hierarchy of the different attributes weighted by levels and scored search phrases for how closely they matched. This is for mobile phones. For example, searching for "Silver iphone 11 pro" would give a higher ranking to all iphone 11 pros that are Silver, then comes differently coloured 11 Pros, followed by 11 Pro Max and 11s, lastly all silver coloured phones.
Post reply on HN