3 ms·

As the author, it's maybe worthwhile that I explain the goals of this post. The first goal is about using your current architecture to solve small/medium searc

As the author, it's maybe worthwhile that I explain the goals of this post. The first goal is about using your current architecture to solve small/medium search needs and help your project to introduce search without requiring to add components to your current architecture. It's not a solution on the long run if your application revolve around search. I'm hoping that with the introduction of JSONB in 9.4, the idea of using Postgres as document store raise more attention on the search which can to be improved. But for an out-of-the-box feature, I think that the Postgres community did an amazing job. You can imagine that you get something working with Postgres in matter of hours if you are already using it (and you are comfortable with PG), but if you have never used SOLR/ElasticSearch it will take you much longer to introduce it in your project and get started (Ops, document sync, getting familiar with query, ...). The second goal, is about introducing full-text search concepts. The post try to guide the user to build a search from nothing to a quite decent full text search in English / French (I cannot give feedbacks on other languages) The third which is probably the less clear is that people are still using MySQL search sometimes which is IMHO an horrible search solution. I think this happen because some web framework like django provide easy access to match in the ORM. In this context, the post is aimed also to provide MySQL user some insights about what can be done with Postgres and being more aware about its features. If you are interested by the topic then I suggest you to have a look to the amazings posts from Tim van der Linden who did an amazing job of going into more details about the subject. http://shisaa.jp/postset/postgresql-full-text-search-part-1.html http://shisaa.jp/postset/postgresql-full-text-search-part-1.... http://shisaa.jp/postset/postgresql-full-text-search-part-2.html http://shisaa.jp/postset/postgresql-full-text-search-part-2.... http://shisaa.jp/postset/postgresql-full-text-search-part-3.html http://shisaa.jp/postset/postgresql-full-text-search-part-3.... Postgres full-text search is not the silver bullet of search but matter of your needs, it's maybe good enough ;)

chdir 12y ago
FWIW, I submitted this link here because I found your blog post immensely useful. Its an excellent intro for someone wanting to integrate text search on their site in a few hours. I think the title has led a few posters here to believe that you are recommending it as a panacea for search. Although you have listed a few points where full text search is not enough, it might be a good followup to explain some cases where ElasticSearch / Solr / Sphinx etc. are needed.
frik 12y ago
> people are still using MySQL search sometimes which is IMHO an horrible search solution Some probably use "text LIKE '%foo%'" SQL queries on any database. MySQL supports several database engines, especially ISAM and InnoDB come with full text search support. You can also install Sphinx Search as MySQL DB engine. So your statement is not true in general. If you meant the old ISAM engine the newer InnoDB is default and usually the preferred solution.
NDizzle 12y ago
Hey, thanks for the article. I'm still going through it now. Would it work for my scenario? I'm full text indexing large legal documents - 50 to even 300 pages. What I've been doing is just throwing the entire document (OCR) into a big mysql (Mariadb 10) LONGTEXT columns. Then I'm using a JDBC river to shoot those over to elasticsearch. I don't fully understand elasticsearch, because like you said it's insane. The query that I build to perform a simple search (think a google-like search box with a go button) is about 100 lines. Should I try out postgres full text search for my purposes? I guess I could change to indexing page by page, and then I could group on the document hash to stamp out duplicates. That would also get me a list of page numbers where the results were. I'm not sure if I could get the snippets though. (What I call a snippet is the word surrounding the match - I'm returning that to datatables so that the user can search within their search to further refine the search.) Here's what my search (and results) looks like now: http://imgur.com/Fjckva6 http://imgur.com/Fjckva6 As you can tell, I'm not too familiar with elasticsearch (A shameful offset input!) and I'm more comfortable in a RDBMS.
- rachbelaid 12y ago
  Which languages? English only? Anykind of fuzzy search required? I don't have experience with big data set but you may to read about GIST/GIN to pick the right index in your case (probably GIST) But from what I saw when I prepared this post, some people are getting some decent performance with full-text search on dataset like the size wikipedia. https://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf https://wiki.postgresql.org/images/2/25/Full-text_search_in_... I think that doesn't take long to try Postgres FTS if you are already using it but it may require more investment if you need to move from MySQL to PG.
  - NDizzle 12y ago
    Thanks for the reply. English only. The only fuzzyness I've told my users about is "phrase here"~20 which would search for the given phrase within 20 words of each other. That helps for the imperfect OCR'd documents. A lot of the stuff we're indexing is best-guess OCR so it's nowhere near perfect. They are boring agreement documents that people have scribbled all over (setting up the redactions) so fuzzy is somewhat interesting to have. But it's not fuzzy in the sense of a typo, it's fuzzy in the sense that it's OCR'd garbage.
wuliwong 12y ago
Thanks a lot. I'm a guy that likes to spin up MVP ideas and am always looking for quick+dirty solutions which I can swap out later if needs be. All of my searching is performed by basic queries, but I will definitely start to use these methods. I here people griping about what is missing but I'm very excited by what is already included. Thanks again!
sbilstein 12y ago
I wrote an extremely basic search using postgres full text search (documented here http://tech.pro/tutorial/1142/building-faceted-search-with-postgresql http://tech.pro/tutorial/1142/building-faceted-search-with-p...) that was very easy to get up and running. It got us pretty much 95% there for a usable product search for the ecommerce site I was on at the time. While it might be limited for full blown search; it does pretty damn well for handling english language search of product descriptions, labels, and any other categorization. I would definitely recommend it if you need a fast solution that is easy to implement (and doesn't really incur much tech debt) so you can get back to working on things that might matter more to your particular project.
MichaelGG 12y ago
I understand the point if you're already proficient with PG, and want to keep it in the same system. But I will say Elasticsearch is surprisingly easy to get started with. Install, POST some docs, GET a search query. That's pretty much it. I implemented it for a project and it literally was a few hours to get rolling with it (and improve search speeds by orders of magnitude). But sure, it is another system to have to deal with.
- sanderjd 12y ago
  While it's definitely easy to get started with ES, I've found the tedious part to be ongoing coordination between two systems. Unless there's no interaction at all between the documents stored in your search index and the data stored in the database, it is necessary to coordinate them at the application layer, which is more complex and can be less performant than doing everything in the database.

chdir 12y ago

FWIW, I submitted this link here because I found your blog post immensely useful. Its an excellent intro for someone wanting to integrate text search on their site in a few hours. I think the title has led a few posters here to believe that you are recommending it as a panacea for search. Although you have listed a few points where full text search is not enough, it might be a good followup to explain some cases where ElasticSearch / Solr / Sphinx etc. are needed.

frik 12y ago

> people are still using MySQL search sometimes which is IMHO an horrible search solution Some probably use "text LIKE '%foo%'" SQL queries on any database. MySQL supports several database engines, especially ISAM and InnoDB come with full text search support. You can also install Sphinx Search as MySQL DB engine. So your statement is not true in general. If you meant the old ISAM engine the newer InnoDB is default and usually the preferred solution.

NDizzle 12y ago

Hey, thanks for the article. I'm still going through it now. Would it work for my scenario? I'm full text indexing large legal documents - 50 to even 300 pages. What I've been doing is just throwing the entire document (OCR) into a big mysql (Mariadb 10) LONGTEXT columns. Then I'm using a JDBC river to shoot those over to elasticsearch. I don't fully understand elasticsearch, because like you said it's insane. The query that I build to perform a simple search (think a google-like search box with a go button) is about 100 lines. Should I try out postgres full text search for my purposes? I guess I could change to indexing page by page, and then I could group on the document hash to stamp out duplicates. That would also get me a list of page numbers where the results were. I'm not sure if I could get the snippets though. (What I call a snippet is the word surrounding the match - I'm returning that to datatables so that the user can search within their search to further refine the search.) Here's what my search (and results) looks like now: http://imgur.com/Fjckva6 http://imgur.com/Fjckva6 As you can tell, I'm not too familiar with elasticsearch (A shameful offset input!) and I'm more comfortable in a RDBMS.

rachbelaid 12y ago
Which languages? English only? Anykind of fuzzy search required? I don't have experience with big data set but you may to read about GIST/GIN to pick the right index in your case (probably GIST) But from what I saw when I prepared this post, some people are getting some decent performance with full-text search on dataset like the size wikipedia. https://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf https://wiki.postgresql.org/images/2/25/Full-text_search_in_... I think that doesn't take long to try Postgres FTS if you are already using it but it may require more investment if you need to move from MySQL to PG.
- NDizzle 12y ago
  Thanks for the reply. English only. The only fuzzyness I've told my users about is "phrase here"~20 which would search for the given phrase within 20 words of each other. That helps for the imperfect OCR'd documents. A lot of the stuff we're indexing is best-guess OCR so it's nowhere near perfect. They are boring agreement documents that people have scribbled all over (setting up the redactions) so fuzzy is somewhat interesting to have. But it's not fuzzy in the sense of a typo, it's fuzzy in the sense that it's OCR'd garbage.

wuliwong 12y ago

Thanks a lot. I'm a guy that likes to spin up MVP ideas and am always looking for quick+dirty solutions which I can swap out later if needs be. All of my searching is performed by basic queries, but I will definitely start to use these methods. I here people griping about what is missing but I'm very excited by what is already included. Thanks again!

sbilstein 12y ago

I wrote an extremely basic search using postgres full text search (documented here http://tech.pro/tutorial/1142/building-faceted-search-with-postgresql http://tech.pro/tutorial/1142/building-faceted-search-with-p...) that was very easy to get up and running. It got us pretty much 95% there for a usable product search for the ecommerce site I was on at the time. While it might be limited for full blown search; it does pretty damn well for handling english language search of product descriptions, labels, and any other categorization. I would definitely recommend it if you need a fast solution that is easy to implement (and doesn't really incur much tech debt) so you can get back to working on things that might matter more to your particular project.

MichaelGG 12y ago

I understand the point if you're already proficient with PG, and want to keep it in the same system. But I will say Elasticsearch is surprisingly easy to get started with. Install, POST some docs, GET a search query. That's pretty much it. I implemented it for a project and it literally was a few hours to get rolling with it (and improve search speeds by orders of magnitude). But sure, it is another system to have to deal with.

sanderjd 12y ago
While it's definitely easy to get started with ES, I've found the tedious part to be ongoing coordination between two systems. Unless there's no interaction at all between the documents stored in your search index and the data stored in the database, it is necessary to coordinate them at the application layer, which is more complex and can be less performant than doing everything in the database.