When do you need large heap for Elasticsearch?

Why do we need an RDBMS (like MySQL) when an indexing engine like Elasticsearch can be used for querying?

• An indexing engine is much faster and can be queried for the same data that we are looking for in an RDBMS, so why do we need an RDBMS then? Are there some specific functions that an indexing engine can't perform?

• Answer:

  Yes. There are many examples; let me give the one I know best: with highly relational data, an RDBMS gives many more ways to query the data--and much faster--than with an indexing engine. Indexing engines only have individual stores that cannot be joined.  Also, indexing engines, when storing individual documents, require that everything associated with that document be placed into the document. So imagine that I am going to have a database of all of the homes in the U.S. along with all of the sales transactions for each home.  Each sales transaction consists of a seller, a buyer, a date, and a value.  I want to find all homes that were purchased by a particular seller after a certain date and above a certain value. With a RDBMS, this is simple; I have tables for homes, people, and transactions, and I search a join of those. With something like Lucene, this is very difficult.  If I make each document a home, then I have to pile all of the transactions into that document.  The obvious way I would do this would not allow me to do my search--if I search for documents (homes) with transactions after 1/1/2000 and with a value > $100,000 (e.g. "+transactionDate:[2000-01-01 TO *] +value:[100000 TO *]"), I will get all homes with transactions after 1/1/2000 OR transactions with a valuation > $100,000.  There is no way for me to do an "AND" query against multiple objects stored in a single document unless I pregenerate some additional values for common queries (e.g. "+transactionDateAfter2000AndOver100K:1") The alternate, then, in Lucene, is have a document store for each table I would have in the RDBMS.  But I can't do joins across document stores in Lucene, and so this is going to require multiple queries and I'm going to have to implement join logic in my code--and the queries are going to be a lot slower than in a RDBMS. In general, indexing engines just don't support the query featuresets of RDBMSs.  Another example: grouping results (like SQL's "GROUP BY") in indexing engines are at best very limited in features.