So I would say, that in the spirit of the article, we probably aren't writing enough SQL, there are definitely things that SQL can do that are essentially free in terms of computation/memory/network. I will warn that we should do it with eyes open, and treat the database as a scarce the resource it will likely become. And often it's important to ask, "will this query that returns the exact data I want have less load on the db than a simpler query that returns more data along with it's network load?". I've certainly had battle scars even with trivial SQL queries.
Once we were sorting 10 small results in the SQL, which led to huge load on the database, since the sort was on a TEXT blob, and even though the sort could be done in memory, MySQL required a file_sort, which meant that it had to create a temp table, which meant it had to touch the disk, which meant I/O latency, and huge application degradation. The solution ended up changing the TEXT field to a varchar(65000), but it was still a wakeup for me with just how alien and non intuitive the SQL ecosystem can be. (As a side note it led me to realize just how foreign modern computer architectures are in general compared to the CS I learned as an undergrad. I also highly recommend Martin Thompson's talk on mechanical sympathy https://www.youtube.com/watch?v=MC1EKLQ2Wmg).
Developers grow up crashing their programs. Crashing your DBMS (or similiarly messing it up) is not as friendly or immediate in feedback/consequence, so people shy away from it.
The end result is that many developers are influenced to use code more and databases less.
Many of the ES queries fundamentally aren't meant to have a 1-to-1 equivalent in SQL as ES's goal is to specialize as a distributed search index, and not a general relative database.
If your point is that ES is being more frequently used as an alternative to just writing/scaling/using a SQL service to it's full potential, then I can somewhat understand the sentiment.
The company where I last worked had a main product which would send in readings every second. The only interface to these readings was Elasticsearch. dev-mis-ops were convinced that this was a good idea. Even the front end would send its queries to Elasticsearch to display statistics.
My task was to look at the average energy usage over one year for the seven days in the week divided into fifteen-minute intervals. This is literally just a SELECT MOD GROUP BY in SQL. I am not even talking about more advanced SQL features here like windows, transactions, joins and so on.
I tried for ages looking through terrible documentation trying to get it to work, at which point I thought I’d have one of the resident ES experts take a bit of his own medicine. It took about five attempts of back and forth over two days to get it right.
This wasn’t the only query we had problems with. At some point, I just asked them to give me a CSV interface so I could do it in AWK. You can’t even download CSVs of data sets without some external tool! They were still convinced that ES was fit for purpose.
In fact, I am not sure why people need all those fancy BI tools. 99% of the time, Elasticsearch aggregations gives me the data I need.
Suppose you have a table which includes power usage readings for all devices, sent every five seconds. Something like this:
(id, timestamp, power)
Write me an ES query which gets the average power usage for device id=x over the last year over the seven days in the week, each divided over fifteen-minute intervals.To clarify:
The groups will be the product of the days of the week with the ninety-six fifteen-minute intervals in a day, making 7*96 unique groups.
You will not be able to do this in any simple way.
After that, do a terms aggregation on day of week with a subaggregation on 15 min interval.
If you can’t reindex all your data, then of course it will be hard.
But once the data is indexed and mapped the right way, its easy.
I have personally managed 10+ TB clusters and reindexed multiple times when I need to add new fields. The key is to take some time to clearly understand what analytics you want to conduct, then add the fields you need afterwards.
What I see from your proposal though is that the work for building the same query is split over re-indexing plus another now slightly less complex query.
What I have missed in Elasticsearch is the ease of expressing a data problem. I am also no stranger to learning new languages when needed. You might think differently, but I am of the opinion that the problem that I described translates a lot more easily into SQL.
What I need in this context is to have a language and tool where it is easy to try lots of different queries in succession; to be able to get a feeling for how the data works and test hypotheses. I haven’t been able to do this in Elasticsearch. I am not saying that there’s nothing which Elasticsearch is good for, but it’s just about using the right tool for the job.