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

Postgres full-text search is good enough

blog.lostpropertyhq.com

Post reply on HN
Print view View original

21–30 of 60 posts

Re: Postgres full-text search is good enough

#21

Post by justin_vanw »

grey-area wrote: post #18
justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

This is probably the most common case I would recommend it: You already are using Postgres, and you just want to add on some basic search functionality to data that is already in there. Perhaps this is true for more sites than you imagine - you started your comment saying that it is not a reasonable solution to most search use cases, but the vast majority of site search on the web is subpar and way below psql FTS. So…

I'm not convinced that you can magically improve search on those 'tail' sites. Postgres' full text search is nontrivial to set up and use, even if you have some SQL experience. I've done substantial work on text search in the past, and I still have trouble following the docs for FTS. The interface is cumbersome at best.

Finally, if search on a site is especially bad, probably the people that made it can't do better, or don't care. I would suspect that most of them are in the "can't do better" camp. One of the not-so-surprising things I have learned about these sorts of programmers is, they don't spend any time reading up on programming related topics, so they'll never see blog posts like this!

Re: Postgres full-text search is good enough

#22

Post by icebraining »

mercurial wrote: post #17
raquo wrote: post #14

Earlier quoted context omitted.

I think it's more likely that no_future was shadow-banned for their previous comment.

What I don't like about shadow-banning is that it's a death sentence without warning appeal for a single misstep. Drank a bit too much and typed a comment you're going to regret the next morning? Shadowban. At least, that's what it looks like from the history of the few shadowbanned people I looked at.

I believe that if you have a good history, you're much less likely to be shadowbanned. In this case, no_future actually has negative karma.

Re: Postgres full-text search is good enough

#23

Post by philh »

mercurial wrote: post #17
raquo wrote: post #14

Earlier quoted context omitted.

I think it's more likely that no_future was shadow-banned for their previous comment.

What I don't like about shadow-banning is that it's a death sentence without warning appeal for a single misstep. Drank a bit too much and typed a comment you're going to regret the next morning? Shadowban. At least, that's what it looks like from the history of the few shadowbanned people I looked at.

Counterexample: no_future has a lot of downvoted comments, and vis total karma is (slightly) negative. (I would guess that it was slightly positive until yesterday, and then ve received a lot of downvotes on a single comment and got autobanned.)

This wasn't a single misstep, it was just the first one that attracted much attention.

Re: Postgres full-text search is good enough

#24

Post by tensor »

justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

This post shows the problem when people ignore theory. Elasticsearch and Lucene are simply inverted indexes. PostgreSQL full text search is also an inverted index, but there are some small architectural issues that make the difference.

Depending on your use case, you cannot replace like or ilike with full text search. You may need to use a trigram index, which you cannot do with ElasticSearch and Lucene.

PostgreSQL text search is absolutely good enough for a great many cases. Maybe not for specialized cases like yours, but for general search it is fine.

Re: Postgres full-text search is good enough

#25

Post by tensor »

mdemare wrote: post #8

no_future (dead) says: I've actually tried both Postgres FTS and Elasticsearch(in conjunction with a Postgres DB) and found Elasticsearch easier for search. It's much more robust - for my specific case I needed a bunch of Japanese text to be searchable alongside english text; Postgres has very poor support for this - and its API is excellent and its very quick and painless to set up(documentation is not stellar thoug…

Yep, this is a genuine complaint. Multi-language support requires a lot more work.

Re: Postgres full-text search is good enough

#26

Post by saurik »

justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

> You can write your own tokenizer, but it has to be as a C extension.

Another option is to tokenize the data before insertion into PostgreSQL (which, ironically, I'm currently doing in one of my projects using Lucene).

Re: Postgres full-text search is good enough

#27

Post by dakrone »

tensor wrote: post #24
justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

This post shows the problem when people ignore theory. Elasticsearch and Lucene are simply inverted indexes. PostgreSQL full text search is also an inverted index, but there are some small architectural issues that make the difference. Depending on your use case, you cannot replace like or ilike with full text search. You may need to use a trigram index, which you cannot do with ElasticSearch and Lucene. PostgreSQL t…

> You may need to use a trigram index, which you cannot do with ElasticSearch and Lucene

You definitely can create an analyzer to generate trigrams in Elasticsearch. Unless you mean something different with "trigram index" than indexing trigrams?

Re: Postgres full-text search is good enough

#28

Post by justin_vanw »

tensor wrote: post #24
justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

This post shows the problem when people ignore theory. Elasticsearch and Lucene are simply inverted indexes. PostgreSQL full text search is also an inverted index, but there are some small architectural issues that make the difference. Depending on your use case, you cannot replace like or ilike with full text search. You may need to use a trigram index, which you cannot do with ElasticSearch and Lucene. PostgreSQL t…

This is not a case of 'ignoring the theory', it's a case of you being pedantic.

I didn't say "replace 'like' queries with identical functionality", I said 'replace', as in 'supersede' and 'improve upon'. Except for insane cases such as the search 'ant' matching the word 'pedANTic', which is to say whenever whole tokens are intended to be matched, postgresql's full text search will replace 'like' queries for you with better functionality (such as matching non-continuous tokens in a search text) and better performance (assuming an index is created).

However, having full control of the tokenization pipeline means you can certainly satisfy any special cases your old 'like' queries would satisfy. For example, in Solr it is trivial to tokenize additionally on word/camel case/number-letter boundaries, so that the tokens generated from something like EM22C can be EM 22 C, EM22 C, EM 22C, EM22C, etc.

That is a specific example, so please don't respond that I am ignorant of the theory, I fully realize that you can index any string, not just the exact ones I mentioned there.

Speaking of being pedantic, Postgresql's full text search is not an 'inverted index'. It is not an index at all, although it can use indexing to improve performance. In addition to GIN (Generalized Inverted Index) it can also use GiST (Generalized Search Tree)

Re: Postgres full-text search is good enough

#29

Post by justin_vanw »

saurik wrote: post #26
justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

> You can write your own tokenizer, but it has to be as a C extension. Another option is to tokenize the data before insertion into PostgreSQL (which, ironically, I'm currently doing in one of my projects using Lucene).

Sure, but that gives away most of the benefit of using it. You have to have your tokenizer algorithm available at query time (so you can tokenize the query the same way), usually at least.

You also have to undertake some batch process to reindex things that have been added/changed. You don't have to handle deletes, which is certainly the trickiest thing to try to keep in sync, but once you have some external cronjob or whatever running your code over all the full text entries, you might as well just make that job insert the data into ElasticSearch. Most of the difficulty in setting up full text search outside of the DB is creating and managing/monitoring the process to shuttle the data out of the db and into ElasticSearch, and if you are going to do most of the work, you might as well get the benefit, rather than just shuttling the data out of the database and then back into the database.

Post reply on HN