4 ms·

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 L

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...
        aisofteng 8y ago
        After reading the links you gave, I’d like to clarify that I meant that edge cases that can cause data loss are not common enough, in my experience, for one to be forced to treat ElasticSearch as an unreliable data store. Anecdotally, I’ve never seen it happen. You’re right, maybe I’m lucky; I would like to see a case study of some project where these issues were significant enough to treat ElasticSearch as an unreliable data store.
      - smittywerben 8y ago
        I don't understand the syncing. Why not write-only akin to UDP protocol?
        manigandham 8y ago
        What are you talking about?
- purerandomness 8y ago
  Can you name an example? I've never built something that did not need a relational database to model the domain, and search is such a common UX element that it's taken for granted in everything you use. I can't even think of something that could use search that's not relational. Can you?
  - threeseed 8y ago
    Yes plenty. Elasticsearch stores data in a JSON document store style format. So any situation where you need O(1) lookup for all data related to a particular entity you can have nested structures within JSON that facilitates this. In a RDBMS it is O(n) since you have to do a costly join for each embedded structure. 360 customer views are good example of this.
    - deleted 8y ago
      [deleted]
    - purerandomness 8y ago
      There's a lot of misconceptions there. If you want to save JSON documents, you can pick Postgres [1] or MySQL and still have a RDBMS for free additionally. Elasticsearch does that too, but its access certainly isn't O(1) - it has a complexity of O(log n), just like Postgres or MySQL would have, because all of them use tree-like index data structures. (Please note that ElasticSearch is not meant as a data storage layer) Now, if you just want to have the JSON, you're done. O(log n) for ElasticSearch, Postgres or MySQL. That's it. But normally, you want to do something with that data. You have a user and want all the products. Or you have a book and want all the authors, because you have a real app to write. Most data in our world is relational: A student can partitipate in courses, a store sells products, and so on. Queriyng your database using JOINs will make use of indexes in each of the tables that you have previously set up. These indexes will prevent the theoretical worst-case (the "Full Table Scan", where your complexity is O(n) for the table). The query planner will decide which index is better to use for the optimal response time. You can, however, choose to not leverage this feature of RDBMS. What do you do now, if you need all the courses of a student? Do you get all the JSONs and compare their IDs? Do you parse the JSON keys in your own app? But having your app iterate over all the JSON keys already has O(n) complxity. So you end up recreating tree-like indexes anyway somewhere in your stack. But all of that is already provided for you, with decades of debugging and performance optimization - for free. To sum up: Use Postgres JSONB type if you only need to write JSON documents. You might later want to query the nested JSON data structure and join it. I highly recommend "Mastering PostgreSQL in Application Development" [2] [1] https://www.postgresql.org/docs/current/static/datatype-json.html https://www.postgresql.org/docs/current/static/datatype-json... [2] https://masteringpostgresql.com/ https://masteringpostgresql.com/
- eska 8y ago
  There was a good article about that on here which you may find interesting http://blog.memsql.com/nosql http://blog.memsql.com/nosql
deleted 8y ago
[deleted]

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...
        aisofteng 8y ago
        After reading the links you gave, I’d like to clarify that I meant that edge cases that can cause data loss are not common enough, in my experience, for one to be forced to treat ElasticSearch as an unreliable data store. Anecdotally, I’ve never seen it happen. You’re right, maybe I’m lucky; I would like to see a case study of some project where these issues were significant enough to treat ElasticSearch as an unreliable data store.
    - smittywerben 8y ago
      I don't understand the syncing. Why not write-only akin to UDP protocol?
      - manigandham 8y ago
        What are you talking about?
purerandomness 8y ago
Can you name an example? I've never built something that did not need a relational database to model the domain, and search is such a common UX element that it's taken for granted in everything you use. I can't even think of something that could use search that's not relational. Can you?
- threeseed 8y ago
  Yes plenty. Elasticsearch stores data in a JSON document store style format. So any situation where you need O(1) lookup for all data related to a particular entity you can have nested structures within JSON that facilitates this. In a RDBMS it is O(n) since you have to do a costly join for each embedded structure. 360 customer views are good example of this.
  - deleted 8y ago
    [deleted]
  - purerandomness 8y ago
    There's a lot of misconceptions there. If you want to save JSON documents, you can pick Postgres [1] or MySQL and still have a RDBMS for free additionally. Elasticsearch does that too, but its access certainly isn't O(1) - it has a complexity of O(log n), just like Postgres or MySQL would have, because all of them use tree-like index data structures. (Please note that ElasticSearch is not meant as a data storage layer) Now, if you just want to have the JSON, you're done. O(log n) for ElasticSearch, Postgres or MySQL. That's it. But normally, you want to do something with that data. You have a user and want all the products. Or you have a book and want all the authors, because you have a real app to write. Most data in our world is relational: A student can partitipate in courses, a store sells products, and so on. Queriyng your database using JOINs will make use of indexes in each of the tables that you have previously set up. These indexes will prevent the theoretical worst-case (the "Full Table Scan", where your complexity is O(n) for the table). The query planner will decide which index is better to use for the optimal response time. You can, however, choose to not leverage this feature of RDBMS. What do you do now, if you need all the courses of a student? Do you get all the JSONs and compare their IDs? Do you parse the JSON keys in your own app? But having your app iterate over all the JSON keys already has O(n) complxity. So you end up recreating tree-like indexes anyway somewhere in your stack. But all of that is already provided for you, with decades of debugging and performance optimization - for free. To sum up: Use Postgres JSONB type if you only need to write JSON documents. You might later want to query the nested JSON data structure and join it. I highly recommend "Mastering PostgreSQL in Application Development" [2] [1] https://www.postgresql.org/docs/current/static/datatype-json.html https://www.postgresql.org/docs/current/static/datatype-json... [2] https://masteringpostgresql.com/ https://masteringpostgresql.com/
eska 8y ago
There was a good article about that on here which you may find interesting http://blog.memsql.com/nosql http://blog.memsql.com/nosql

deleted 8y ago

[deleted]