4 ms·

It's funny that this even needs to be a blog post. I can see why simple key-value stores have become so popular in recent years. Developers don't want to learn

by ThJ 8y ago

It's funny that this even needs to be a blog post. I can see why simple key-value stores have become so popular in recent years. Developers don't want to learn SQL properly, and end up using the database as if it were a key-value store, and at some point, they begin to question why they need SQL at all, and switch to NoSQL. Later, when they do need more sophisticated querying, they then have to slap Elastic on top of their key-value store to get that feature back, when they could've been using SQL all along. Full circle!

ben_jones 8y ago
I agree with what you're saying but I think its important to point out that there is a Developer experience in most mainstream languages and then there is the Operations experience of deploying, configuring, maintaining, scaling, and paying for, various RDBMS. 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.
- 0db532a0 8y ago
  Elasticsearch quite conveniently makes it so difficult and fucking annoying to do data analytics with their pile of turd custom query un-language shambles that it leaves anyone stuck with it rightly reinventing oblong wheels with diamond bearings lubricated with iron filings in code. Aah, but dev-mis-ops say that it’s easy to deploy and maintain on AWS. It scales too! Well, at least the whole set-up keeps everyone employed.
  - KennyCason 8y ago
    Could you elaborate more on your specific ES problems? Having worked with Elasticsearch extensively for many years, and do not share this sentiment. 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.
    - 0db532a0 8y ago
      I completely agree with the idea that ES might be good as a distributed search index. I really wish people would leave it at that. 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.
  - AznHisoka 8y ago
    What version of elasticsearch are you using? Even in version 1.x, aggregation queries are so intuitive and pleasant to work with, imo. Now the actual libraries written in Ruby and other languages may be clunky. 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.
    - 0db532a0 8y ago
      Challenge, with context in my other comment at the same depth: 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.
      - AznHisoka 8y ago
        This is an indexing problem. Add 2 extra fields. For each timestamp, you also need to index the 15-minute interval (ie 7:13:05 maps to 7:00-7:15) and the day of the week. 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.
        0db532a0 8y ago
        So you couldn’t write the query. It just isn’t practical to create an index for every query which comes to mind during research, and even to create the index, you have to use a terrible, undocumented embedded query language.
        AznHisoka 8y ago
        Not create a new index - reindex the existing documents 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.
geshan 8y ago
Are you trying to point in the direction that RDBMS is dead?
milesvp 8y ago
So, while I agree that too many developers don't write sql these days, my experience, is that usually the database is the bottleneck for any application I write on the web. There's a reason that there's been a trend away from doing things in the database for years, and a lot of it has to do with moore's law slowing down in the last decade. I can trivially scale out webheads to do many of the calculations that the database could be doing for me, so if I'm doing work on the database, I take a bigger and bigger risk that I'm going to need to add a read replica, and now my code is at least an order of magnitude more complex. I've supported systems where the latency of updating the read slave caused enough problems due to assumptions baked into the code, that we ended up just turning off the read replicas, and had to live with the degraded performance of the app. 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 https://www.youtube.com/watch?v=MC1EKLQ2Wmg).

by ThJ 8y ago

ben_jones 8y ago

I agree with what you're saying but I think its important to point out that there is a Developer experience in most mainstream languages and then there is the Operations experience of deploying, configuring, maintaining, scaling, and paying for, various RDBMS. 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.

0db532a0 8y ago
Elasticsearch quite conveniently makes it so difficult and fucking annoying to do data analytics with their pile of turd custom query un-language shambles that it leaves anyone stuck with it rightly reinventing oblong wheels with diamond bearings lubricated with iron filings in code. Aah, but dev-mis-ops say that it’s easy to deploy and maintain on AWS. It scales too! Well, at least the whole set-up keeps everyone employed.
- KennyCason 8y ago
  Could you elaborate more on your specific ES problems? Having worked with Elasticsearch extensively for many years, and do not share this sentiment. 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.
  - 0db532a0 8y ago
    I completely agree with the idea that ES might be good as a distributed search index. I really wish people would leave it at that. 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.
- AznHisoka 8y ago
  What version of elasticsearch are you using? Even in version 1.x, aggregation queries are so intuitive and pleasant to work with, imo. Now the actual libraries written in Ruby and other languages may be clunky. 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.
  - 0db532a0 8y ago
    Challenge, with context in my other comment at the same depth: 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.
    - AznHisoka 8y ago
      This is an indexing problem. Add 2 extra fields. For each timestamp, you also need to index the 15-minute interval (ie 7:13:05 maps to 7:00-7:15) and the day of the week. 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.
      - 0db532a0 8y ago
        So you couldn’t write the query. It just isn’t practical to create an index for every query which comes to mind during research, and even to create the index, you have to use a terrible, undocumented embedded query language.
        AznHisoka 8y ago
        Not create a new index - reindex the existing documents 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.

geshan 8y ago

Are you trying to point in the direction that RDBMS is dead?

milesvp 8y ago

So, while I agree that too many developers don't write sql these days, my experience, is that usually the database is the bottleneck for any application I write on the web. There's a reason that there's been a trend away from doing things in the database for years, and a lot of it has to do with moore's law slowing down in the last decade. I can trivially scale out webheads to do many of the calculations that the database could be doing for me, so if I'm doing work on the database, I take a bigger and bigger risk that I'm going to need to add a read replica, and now my code is at least an order of magnitude more complex. I've supported systems where the latency of updating the read slave caused enough problems due to assumptions baked into the code, that we ended up just turning off the read replicas, and had to live with the degraded performance of the app. 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 https://www.youtube.com/watch?v=MC1EKLQ2Wmg).