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

91–100 of 141 posts

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

#91

Post by eric4smith »

Postgres FTS is normally quite good.

But it does not know how to deal with languages like Chinese, Japanese and Thai.

For that you have to use something like PGroonga extension.

The rest of PostgreSQL mostly handles things ok, unless you try to sort on one of these languages and the same things happen again.

There are all ways around these problems. But it’s not as easy as turning on Unicode and just expect everything to work!

Yes I’m native English speaker who started to develop in Asia and discovered all of this recently.

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

#92

Post by SOLAR_FIELDS »

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…

Nice insightful comment. Can you give a bit additional insight related to when it makes sense to use ES vs Postgres? My takeaway from your comment is that unless your product depends on search as a central component that inbuilt search is good enough. Is that an incorrect takeaway?

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

#93

Post by james_in_the_uk »

craigkerstiens wrote: post #80
grncdr wrote: post #78

Earlier quoted context omitted.

Cool cool If I could have your other ear for a moment: support for any EU-based cloud provider would be super nice. Ever since the Privacy Shield fig leaf was removed, questions about whether we store any data under US jurisdiction have become a lot more frequent.

Do you have a preferred one? We're currently on the big 3 US ones, in process of working on our 4th provider, our goal is very much to deliver the best Postgres experience whether on bare metal/on-premise or in the cloud, self hosted or fully managed. Edit: Always feel free to reach out directly, I'm always happy to spend time with anyone that has questions and usually pretty easy to track me down.

Dublin usually works well from a regulatory perspective.

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

#94

Post by nostrademons »

SOLAR_FIELDS wrote: post #92
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…

Nice insightful comment. Can you give a bit additional insight related to when it makes sense to use ES vs Postgres? My takeaway from your comment is that unless your product depends on search as a central component that inbuilt search is good enough . Is that an incorrect takeaway?

That's probably a decent takeaway. If full-text search is a "nice to have" (a bonus feature for advanced users, for example) you can probably rely on built-in Postgres search. If search is a fundamental way that users interact with your product, you want to spend the time and money to get it right, and that probably means ElasticSearch and a custom ranking function.

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

#95

Post by mrslave »

zombodb wrote: post #85
Conlectus wrote: post #76

Earlier quoted context omitted.

Though I have only a recreational interest in datastores, I would be pretty wary of a service that claims to strap a Consistant-Unavailable database (Postgres) to an Inconsistent-Available database (ElasticSearch) in a useful way. Doing so would require ElasticSearch to reach consensus on every read/write, which would remove most of the point of a distributed cluster. Despite this, ZomboDB's documentation says "compl…

> Doing so would require ElasticSearch to reach consensus on every read/write ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes. This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage. > They also claim that transactions will abo…

ZomboDB scratches an itch in a way I find fascinating, though I have yet to do more with it than shoehorn it into a prototype that was a bit square-peg-in-a-round-hole. It is in my catalogue of technologies I hope exploit someday. And I hope you're enjoying the work and making some $$$ too.

While I'm here might I ask, are you finding the hosted PostgreSQL services (AWS, Azure, etc.) growing or shrinking your market opportunities? Also, does it play nice with Citus?

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

#96

Post by Conlectus »

zombodb wrote: post #85
Conlectus wrote: post #76

Earlier quoted context omitted.

Though I have only a recreational interest in datastores, I would be pretty wary of a service that claims to strap a Consistant-Unavailable database (Postgres) to an Inconsistent-Available database (ElasticSearch) in a useful way. Doing so would require ElasticSearch to reach consensus on every read/write, which would remove most of the point of a distributed cluster. Despite this, ZomboDB's documentation says "compl…

> Doing so would require ElasticSearch to reach consensus on every read/write ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes. This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage. > They also claim that transactions will abo…

I suppose I must have misinterpreted this line

> As such, any sort of failure either with ZomboDB itself, between Postgres and Elasticsearch (network layer), or within Elasticsearch will cause the operating Postgres transaction to ABORT. [1]

In my defense, there is a fairly important distinction between "any error that ES is capable of reporting back" and "any sort of failure within Elasticsearch".

That said, I and trust that you're more familiar with consistency levels than me, so I'll bow out here.

[1] https://www.zombodb.com/documentation/things-to-know/

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

#97

Post by mjewkes »

simonw wrote: post #63
mjewkes wrote: post #19

Earlier quoted context omitted.

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22 I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00... That's using websearch_to_tsquery() which was added in PostgreSQL 11: https://www.postgresql.org/docs/11/textsearch-controls.html#...

Try https://simonwillison.net/search/?q=%22your+own+benchmarks%2...

Looks like we get 37 results, of which 2 are true positives.

Looks like "your" and "own" are both contained in the english.stop stopwords list. So you could fix this by removing stopwords from your dictionary.

While disabling the stemmer is relatively easy (use the 'simple' language setting for your ts_query), altering the stopword dictionaries is more involved, and not easy to maintain or pass between developers/environments, and not at all easy to share between queries.

And so the most common suggestion is to use ILIKE.

Lucene has no problems with any of this.

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

#98

Post by zombodb »

Conlectus wrote: post #96
zombodb wrote: post #85

Earlier quoted context omitted.

> Doing so would require ElasticSearch to reach consensus on every read/write ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes. This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage. > They also claim that transactions will abo…

I suppose I must have misinterpreted this line > As such, any sort of failure either with ZomboDB itself, between Postgres and Elasticsearch (network layer), or within Elasticsearch will cause the operating Postgres transaction to ABORT. [1] In my defense, there is a fairly important distinction between "any error that ES is capable of reporting back" and "any sort of failure within Elasticsearch". That said, I and t…

I can definitely reword that if it's confusing.

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

#99

Post by mjewkes »

e12e wrote: post #36
mjewkes wrote: post #19

Earlier quoted context omitted.

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

Even if using ILIKE over the result of an imprecise query?

ILIKE itself is a linear scan. The only way to index them are trigram indicies, which are very inefficient (and sometimes not usable) if you're searching document-length content.

Whether or not it works in your specific situation depends on your use case.

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

#100

Post by mjewkes »

rattray wrote: post #64
mjewkes wrote: post #19

Earlier quoted context omitted.

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

Pardon my ignorance – what is exact phrase matching and why doesn't it work with tsvector?

Exact phrase matching is what google (sometimes? used to?) do for you if you put your search terms in double "full quotes".

It returns only results that contain the exact multi word sequence in exactly the same order.

Post reply on HN