7 ms·

What are the downsides for implementing something like this rather than going for Elastic Search or SOLR? Is it possible to have production-quality search usin

What are the downsides for implementing something like this rather than going for Elastic Search or SOLR? Is it possible to have production-quality search using just postgresql?

ngrilly 8y ago
The upside: only one service to maintain (PostgreSQL) instead of two (PostgreSQL + ElasticSearch) and not having to synchronize PostgreSQL and ElasticSearch (which is more difficult than it sounds). The downside: ranking can be slow when your keywords match a large number of documents (have a look at github.com/postgrespro/rum for a solution), and faceting is more difficult to implement than in ElasticSearch
- deleted 8y ago
  [deleted]
- rbjorklin 8y ago
  What about only running SOLR/Elasticsearch? That would keep the upside and eliminate the downside, no?
  - aisofteng 8y ago
    Solr and Elasticsearch are not relational database systems and have none of the optimizations for relational data that Postgres does. They are built on top of Lucene and target document retrieval via an inverted index [1] rather than dealing with relational data. [1] https://en.wikipedia.org/wiki/Inverted_index https://en.wikipedia.org/wiki/Inverted_index
    - vesak 8y ago
      A rather small portion of software that requires search requires a relational database, though.
      - manigandham 8y ago
        It's not just relational features that matter but the decades of work on reliability, performance, flexibility, security, and general usability of these databases that makes them so great. ElasticSearch doesn't have ACID or very good OLTP manners. More importantly, it still continues to have data loss issues so it's not a good fit as a primary datastore.
        smittywerben 8y ago
        ElasticSearch looks like a REST api with http/json schema to pipe directly to browsers. Why would you need to store the results of ElasticSearch?
        manigandham 8y ago
        It's not about results, the data itself within ElasticSearch is not guaranteed safe as a primary datastore, so you should have a reliable database for the primary data that is then synced to ES.
        aisofteng 8y ago
        As someone who has used ElasticSearch in production for years, as far as I know this is simply not true.
        manigandham 8y ago
        That's good for you, but it sounds like you're extrapolating it on just your experience rather than research? Perhaps you've been lucky? Do you really have the info to objectively state it as "simply not true"? Read the jepsen tests (old but show core problems that aren't fixed): https://aphyr.com/posts/317-call-me-maybe-elasticsearch https://aphyr.com/posts/317-call-me-maybe-elasticsearch https://aphyr.com/posts/323-call-me-maybe-elasticsearch-1-5-0 https://aphyr.com/posts/323-call-me-maybe-elasticsearch-1-5-... Or just search for ES data loss: https://news.ycombinator.com/item?id=9475620 https://news.ycombinator.com/item?id=9475620 https://stackoverflow.com/questions/29841348/how-reliable-is-elasticsearch-as-a-primary-datastore-against-factors-like-write https://stackoverflow.com/questions/29841348/how-reliable-is... And if that's not enough, here's the official page listing several "ongoing" fixes for major durability issues: https://www.elastic.co/guide/en/elasticsearch/resiliency/current/index.html https://www.elastic.co/guide/en/elasticsearch/resiliency/cur...
    - deleted 8y ago
      [deleted]
  - mozumder 8y ago
    How would you build an order-processing & accounting system with only SOLR/Elasticsearch? Can you guarantee data reliability?
rpedela 8y ago
For some production use cases, it is possible and others it is not or extremely difficult. There are a few reasons for this. The quality of search is a combination of both precision, ordering by relevance, and recall, finding relevant documents in a corpus. PG FTS is primarily a recall engine since it does not have BM25, TF/IDF, etc built-in. Out of the box, it is like an easier to use regex rather than a full search engine. If you have a dataset with small N or relatively unique records then recall-based algorithms will still give good precision simply because there aren't that many results for a given query. However datasets with larger N or lots of similar records, precision becomes harder and you start needing BM25, machine learning, etc. It is possible to incorporate other relevance signals (eg recency, popularity) to improve precision in PG but depending on the specific signal, it can be more difficult than ES or Solr. Things like auto complete, supporting multiple languages, spell check, scaling are also harder to implement in PG at the same quality and performance as ES/Solr. Syncing the database and search server is a real problem so I usually recommend to stick with PG if you have a recall-based search problem. But if precision is important or one of those other features I mentioned is important than I recommend ES/Solr and just deal with the syncing problem because overall it will be easier.
- ngrilly 8y ago
  Great summary!
manigandham 8y ago
Depends on what you call production-quality, but definitely possible. Simplicity of having it all in 1 system is great, however the search quality itself is usually average and configurability is limited. A real search engine will give you much better relevance rankings, facets, fuzzy search, and other fancy things you can do with a general "similarity index" like recommendations.
brightball 8y ago
In short, yes. As your data grows, you might outgrow PG to a more dedicated option but it is absolutely good enough for a while. If you have a solution like Citus available to scale your DB horizontally, it might be enough forever though.
aisofteng 8y ago
>Is it possible to have production-quality search using just postgresql? It depends on your search use case. If you are doing fairly simple searches such as trying to find, say, usernames, you can certainly have production grade search using Postgres. However, if you have to handle searches that are not as targeted, Postgres will not be enough. One example is searching with synonyms - if you want a record containing "automobile" to match queries containing the term "car", ElasticSearch will serve you better. Postgres is a relational data store and ElasticSearch is an information retrieval system; each focuses on their intended use case. The fact that Postgres supplies some elementary search capabilities does not mean that Postgres can well serve a bona fide information retrieval user case.

What are the downsides for implementing something like this rather than going for Elastic Search or SOLR? Is it possible to have production-quality search using just postgresql?

ngrilly 8y ago

The upside: only one service to maintain (PostgreSQL) instead of two (PostgreSQL + ElasticSearch) and not having to synchronize PostgreSQL and ElasticSearch (which is more difficult than it sounds). The downside: ranking can be slow when your keywords match a large number of documents (have a look at github.com/postgrespro/rum for a solution), and faceting is more difficult to implement than in ElasticSearch

deleted 8y ago
[deleted]
rbjorklin 8y ago
What about only running SOLR/Elasticsearch? That would keep the upside and eliminate the downside, no?
- aisofteng 8y ago
  Solr and Elasticsearch are not relational database systems and have none of the optimizations for relational data that Postgres does. They are built on top of Lucene and target document retrieval via an inverted index [1] rather than dealing with relational data. [1] https://en.wikipedia.org/wiki/Inverted_index https://en.wikipedia.org/wiki/Inverted_index
  - vesak 8y ago
    A rather small portion of software that requires search requires a relational database, though.
    - manigandham 8y ago
      It's not just relational features that matter but the decades of work on reliability, performance, flexibility, security, and general usability of these databases that makes them so great. ElasticSearch doesn't have ACID or very good OLTP manners. More importantly, it still continues to have data loss issues so it's not a good fit as a primary datastore.
      - smittywerben 8y ago
        ElasticSearch looks like a REST api with http/json schema to pipe directly to browsers. Why would you need to store the results of ElasticSearch?
        manigandham 8y ago
        It's not about results, the data itself within ElasticSearch is not guaranteed safe as a primary datastore, so you should have a reliable database for the primary data that is then synced to ES.
        aisofteng 8y ago
        As someone who has used ElasticSearch in production for years, as far as I know this is simply not true.
        manigandham 8y ago
        That's good for you, but it sounds like you're extrapolating it on just your experience rather than research? Perhaps you've been lucky? Do you really have the info to objectively state it as "simply not true"? Read the jepsen tests (old but show core problems that aren't fixed): https://aphyr.com/posts/317-call-me-maybe-elasticsearch https://aphyr.com/posts/317-call-me-maybe-elasticsearch https://aphyr.com/posts/323-call-me-maybe-elasticsearch-1-5-0 https://aphyr.com/posts/323-call-me-maybe-elasticsearch-1-5-... Or just search for ES data loss: https://news.ycombinator.com/item?id=9475620 https://news.ycombinator.com/item?id=9475620 https://stackoverflow.com/questions/29841348/how-reliable-is-elasticsearch-as-a-primary-datastore-against-factors-like-write https://stackoverflow.com/questions/29841348/how-reliable-is... And if that's not enough, here's the official page listing several "ongoing" fixes for major durability issues: https://www.elastic.co/guide/en/elasticsearch/resiliency/current/index.html https://www.elastic.co/guide/en/elasticsearch/resiliency/cur...
  - deleted 8y ago
    [deleted]
- mozumder 8y ago
  How would you build an order-processing & accounting system with only SOLR/Elasticsearch? Can you guarantee data reliability?

rpedela 8y ago

For some production use cases, it is possible and others it is not or extremely difficult. There are a few reasons for this. The quality of search is a combination of both precision, ordering by relevance, and recall, finding relevant documents in a corpus. PG FTS is primarily a recall engine since it does not have BM25, TF/IDF, etc built-in. Out of the box, it is like an easier to use regex rather than a full search engine. If you have a dataset with small N or relatively unique records then recall-based algorithms will still give good precision simply because there aren't that many results for a given query. However datasets with larger N or lots of similar records, precision becomes harder and you start needing BM25, machine learning, etc. It is possible to incorporate other relevance signals (eg recency, popularity) to improve precision in PG but depending on the specific signal, it can be more difficult than ES or Solr. Things like auto complete, supporting multiple languages, spell check, scaling are also harder to implement in PG at the same quality and performance as ES/Solr. Syncing the database and search server is a real problem so I usually recommend to stick with PG if you have a recall-based search problem. But if precision is important or one of those other features I mentioned is important than I recommend ES/Solr and just deal with the syncing problem because overall it will be easier.

ngrilly 8y ago
Great summary!

manigandham 8y ago

Depends on what you call production-quality, but definitely possible. Simplicity of having it all in 1 system is great, however the search quality itself is usually average and configurability is limited. A real search engine will give you much better relevance rankings, facets, fuzzy search, and other fancy things you can do with a general "similarity index" like recommendations.

brightball 8y ago

In short, yes. As your data grows, you might outgrow PG to a more dedicated option but it is absolutely good enough for a while. If you have a solution like Citus available to scale your DB horizontally, it might be enough forever though.

aisofteng 8y ago

>Is it possible to have production-quality search using just postgresql? It depends on your search use case. If you are doing fairly simple searches such as trying to find, say, usernames, you can certainly have production grade search using Postgres. However, if you have to handle searches that are not as targeted, Postgres will not be enough. One example is searching with synonyms - if you want a record containing "automobile" to match queries containing the term "car", ElasticSearch will serve you better. Postgres is a relational data store and ElasticSearch is an information retrieval system; each focuses on their intended use case. The fact that Postgres supplies some elementary search capabilities does not mean that Postgres can well serve a bona fide information retrieval user case.