5 ms·

Well the article is right in one sense: Postgres full text search is probably better than you think it would be and is very usable at non-massive scale However

by birken 12y ago

Well the article is right in one sense: Postgres full text search is probably better than you think it would be and is very usable at non-massive scale However, I still wouldn't recommend using it. The queries and indexes are all unintuitive, complex, not very readable and require learning a bunch of new syntax anyways (as you can see in the examples in the blog post). So just take the effort you would spend doing that and instead learn something with more features that will continue running no matter how large your scale. While the blog mentions elasticsearch/solr (which are awesome and powerful), it doesn't mention sphinx (http://sphinxsearch.com/ http://sphinxsearch.com/). If I were trying to make a simple search engine for my postgres/mysql powered site, I'd use sphinx. The indexer can be fed directly with a postgres/mysql query, and it includes simple APIs for all languages to interface with the searcher daemon. And of course it is super fast, has way more features and can scale larger than you'd ever need (sphinx powers craiglist's search).

michielvoo 12y ago
I was under the impression that Sphinx cannot return documents, only IDs. (The implication is that you need to query your data source if you want to show the results.) Is that correct?
- birken 12y ago
  Yes, which I think in simple cases is a benefit. For example if you are using an ORM, the massive queries in the OP are really a pain to deal with. With Sphinx, you perform the search using the search API and get back some ids, then just query those ids using your standard ORM constructs for getting the (small) set of whatever object/rows you are loading. Since you are loading small and fixed amounts of data by the primary keys, the performance shouldn't be an issue. If you are looking for caching and other more complex features, I'd recommend elasticsearch (and I do highly recommend it). But sphinx is simpler, and I think it is a good alternative for the type of functionality talked about in the blog post. Granted, I really don't think elasticsearch is all that complicated either, and it is also really well documented. But sphinx is just painfully simple for the basic use cases (like anything postgres can do).
  - aidos 12y ago
    Additionally it helps to alleviate issues with stale data in the search index (which is often updated periodically ). If you have a list of ids to query for, there's no harm if one of them is no longer in the db, you just won't show it. As ever, it's a tradeoff. I used to keep everything required in for search results pages in the search index (solr, at the time). Eventually I decided that the additional db lookup was well worth the extra few milliseconds to make sure I was working with reliable data.
- siegecraft 12y ago
  You can actually store a bit more than that in sphinx. You can add attributes to your sphinx documents that can either be used for filtering or just as extra metadata when returning query results. The downside is they get added to the index which has to fit in memory (I think, it's been a while).
mrweasel 12y ago
One interesting thing we learn from build an online store: People uses search alot less that you would think. We started to notice that customers will use the menu and filtering options, rather than searching for an item. Need something from the "electronics" category, click the electronics menu tab, and pick the subcategory. From there customers browse or filter until they find what they're looking for. Of cause so do use the search, and most people would have better results if they did search directly. My point is: Maybe we could have just used Postgresqls full text search in this case. The queries will be rather simply anyway. The new syntax won't matter much, you need to learn something new anyway if you use Solr or ElasticSearch. Search is fare less important than people think, it just needs to be there and be "good enough". Yes, Sphinx is awesome, if you're doing plain searches, filtering can get complicated pretty quickly.
- chdir 12y ago
  The menu & filtering option can benefit a lot from Facets (or aggregations in ElasticSearch for more advanced options).
  - mrweasel 12y ago
    That's actually what we use. I should have been more clear, it's the fulltext search that doesn't matter to much.
- aw3c2 12y ago
  As a user I never trust myself to know the "right" search terms nor the search box to show the "right" results. If I browse, I know that I see all the items and I can filter with exact, pre-defined (trusted) attributes.
- orbifold 12y ago
  I think that is not so much because of the fact that people do not want to use search, just that by experience they've learned that it is usually next to useless on most sites. At least that is my experience.
- narag 12y ago
chdir 12y ago
What about the cost of running a separate service like ElasticSearch for a small startup. It should be easy to swap out PG text search with a more mature solution later on as you scale.
- birken 12y ago
  I think if you are concerned with swapping out to a different system as you scale you should use a "search as a service" company and then bring it in-house if the cost becomes too much. The interface provided by a "search as a service" company is going to be a lot more similar to elasticsearch/solr than it is to postgres, so it would be a much easier switch. If you want to switch from PG full-text search to something else, you'll essentially have to start from square one because all of the ways you are interacting with your search mechanism (defining, inserting, updating, querying, retrieving documents) are completely different between PG and all the other search programs. A blog post like this makes it all seem so easy, but I assure you maintaining these long queries, dealing with the extra load on your database, preventing XSS and just overall making administering your database a little more difficult also has a real cost.
  - chdir 12y ago
    Useful insights, thank you. Would love to read a more detailed analysis if you ever feel like writing one.
- frik 12y ago
  Especially with cloud hosting providers, were more memory gets expensive, I would recommend to go with native C/C++ applications like Postgres/MySQL/SphinxSearch/SQLite-FTS. (add Lucene based Solr and ElasticSearch to the list if memory is cheap)

by birken 12y ago

michielvoo 12y ago

I was under the impression that Sphinx cannot return documents, only IDs. (The implication is that you need to query your data source if you want to show the results.) Is that correct?

birken 12y ago
Yes, which I think in simple cases is a benefit. For example if you are using an ORM, the massive queries in the OP are really a pain to deal with. With Sphinx, you perform the search using the search API and get back some ids, then just query those ids using your standard ORM constructs for getting the (small) set of whatever object/rows you are loading. Since you are loading small and fixed amounts of data by the primary keys, the performance shouldn't be an issue. If you are looking for caching and other more complex features, I'd recommend elasticsearch (and I do highly recommend it). But sphinx is simpler, and I think it is a good alternative for the type of functionality talked about in the blog post. Granted, I really don't think elasticsearch is all that complicated either, and it is also really well documented. But sphinx is just painfully simple for the basic use cases (like anything postgres can do).
- aidos 12y ago
  Additionally it helps to alleviate issues with stale data in the search index (which is often updated periodically ). If you have a list of ids to query for, there's no harm if one of them is no longer in the db, you just won't show it. As ever, it's a tradeoff. I used to keep everything required in for search results pages in the search index (solr, at the time). Eventually I decided that the additional db lookup was well worth the extra few milliseconds to make sure I was working with reliable data.
siegecraft 12y ago
You can actually store a bit more than that in sphinx. You can add attributes to your sphinx documents that can either be used for filtering or just as extra metadata when returning query results. The downside is they get added to the index which has to fit in memory (I think, it's been a while).

mrweasel 12y ago

One interesting thing we learn from build an online store: People uses search alot less that you would think. We started to notice that customers will use the menu and filtering options, rather than searching for an item. Need something from the "electronics" category, click the electronics menu tab, and pick the subcategory. From there customers browse or filter until they find what they're looking for. Of cause so do use the search, and most people would have better results if they did search directly. My point is: Maybe we could have just used Postgresqls full text search in this case. The queries will be rather simply anyway. The new syntax won't matter much, you need to learn something new anyway if you use Solr or ElasticSearch. Search is fare less important than people think, it just needs to be there and be "good enough". Yes, Sphinx is awesome, if you're doing plain searches, filtering can get complicated pretty quickly.

chdir 12y ago
The menu & filtering option can benefit a lot from Facets (or aggregations in ElasticSearch for more advanced options).
- mrweasel 12y ago
  That's actually what we use. I should have been more clear, it's the fulltext search that doesn't matter to much.
aw3c2 12y ago
As a user I never trust myself to know the "right" search terms nor the search box to show the "right" results. If I browse, I know that I see all the items and I can filter with exact, pre-defined (trusted) attributes.
orbifold 12y ago
I think that is not so much because of the fact that people do not want to use search, just that by experience they've learned that it is usually next to useless on most sites. At least that is my experience.
narag 12y ago

chdir 12y ago

What about the cost of running a separate service like ElasticSearch for a small startup. It should be easy to swap out PG text search with a more mature solution later on as you scale.

birken 12y ago
I think if you are concerned with swapping out to a different system as you scale you should use a "search as a service" company and then bring it in-house if the cost becomes too much. The interface provided by a "search as a service" company is going to be a lot more similar to elasticsearch/solr than it is to postgres, so it would be a much easier switch. If you want to switch from PG full-text search to something else, you'll essentially have to start from square one because all of the ways you are interacting with your search mechanism (defining, inserting, updating, querying, retrieving documents) are completely different between PG and all the other search programs. A blog post like this makes it all seem so easy, but I assure you maintaining these long queries, dealing with the extra load on your database, preventing XSS and just overall making administering your database a little more difficult also has a real cost.
- chdir 12y ago
  Useful insights, thank you. Would love to read a more detailed analysis if you ever feel like writing one.
frik 12y ago
Especially with cloud hosting providers, were more memory gets expensive, I would recommend to go with native C/C++ applications like Postgres/MySQL/SphinxSearch/SQLite-FTS. (add Lucene based Solr and ElasticSearch to the list if memory is cheap)