Yes SQL, No SQL...
Long, long time ago... there was pen & paper. In fact "pen and paper" have been there for a very long time. Maybe it wasn't exactly "paper" or "pen" in the way we intend them today, but still stuff that allowed signs to be written (or traced, or carved) onto it was.
And the things that were "written" onto it were mostly... lists. Lists of stuff to be precise. Products that were stored in warehouses, products that were exchanged for other products, oroducts that needed to be transported, taken, given and so on. Stuff that we, today, would call "databases".
I've already said that databases are one of the oldest invention of the world, and have been used, in some form, for millennia. And there is nothing wrong with that. But... A Database is a very specific and peculiar tool and it is used to do a very specific and peculiar task and has to be used just for that task. As said before, if you try to use a tool for the wrong task, it doesn't work very well.
And as usual, some people knows a tool very well and he try to use it for everything, even when the tool is completly wrong for the situation at hand.
Now, if you are of the "old school", the term "database" is probably evoking images of monsters that eats tons of data and spit out some sort of SQL (one of the many forms), if you are a bit younger, you are still thinking to similar stuff but maybe less monstruos and a lot cheaper (in money).
All these tools are based on a very simple but precise set of rules that distinguish and define "databases" from other stuff that maybe can "look" as databases to the untrained eye (and don't get me started about Excel), but databases those things are definitively not.
All this long and convoluted introduction to explain (once more) that there is no such thing as a "silver bullet" to solve all your problems and for every situation you have to look for the best solution that fit. And if your "beloved" tool doesn't look like fitting, maybe is better to be left in the toolbox.
And now let's talk about $tripped, a company that did... several things, one of which was to run some sort of application that stored different data and then was presenting the same in tidy and neat tables.
The source data were collected in different ways, most of the time through automated procedure that read files or such (on and off line) and all this stuff was chucked into a database that was then analyzed to build the resulting tables. They weren't using cellular network otherwise I'd say that all this was very reminescent of $wemeasurestuff of which I've already talked about.
In fact, their problems were almost the same, except that in this case the problem wasn't the application structure, but the fact that they decided, long time ago, to use a well known SQL database as data collection point... without doing anything that required SQL then.
I'll try to explain.
A SQL database usually has some sort of "optimizier" to turn the queries that are provided by the meatbag into a series of "filters" that are then applied to the data in a way to minimize the amount of data the database itself has to look at before being able to present its information. In order for this optimizer to work ...her... optimally, there should be a precise and clear distinction between the TYPE of data stored and the way they are stored. This in turn require a very careful construction of the tables and indexes used to maximize the use of indexes and minimize the amount of tablescan required to do... everything basically.
The more the database uses the indexes to access the information and the fast and accurate is the information retrival.
So... What happens when we have to maintain a considerable mass of data that are basically all similar and devoided of a precise structure and where the data actually don't have very much a "type" distintion?
Well, it happens that the structure we get doesn't really work in a "classic" structure and try to fit this into that gives a system that is very, very inefficient.
And "inefficient" is a bit an understatement for this. Besides waiting time that would've tried Job's patience, beyond some level a lot of "unexpected errors" began to present themselves. At least, the errors were "unexpected" for $tripped's developers and the various UL/SL, for me, after looking at the whole contraption, I was actually expecting that the timeouts were unavoidable when the system was trying to process millions of rows to extract maybe an handful of them and then process another few millions in order to match the few found before and turn out with a table that maybe contained 3 rows.
Obiously, $tripped began to complain with us about the "unaccpetable slowness" of the whole infrastructure. As expected, the slowness was "unacceptable" but this didn't meant they were going to bring their stuff to some other provider, even the ones that ($tripped said) "was going to guarantee performances that wer 3 to 4 times better", my reading of the whole thing was that maybe the other provider were say so, but after payment of a very large sum of money that $tripped really didn't wanted to pay.
And as usual, since the world is still a ball, somebody was "volounteered" to "optimize" the whole stuf. And as usual, all the geniuses that are super-expert of every single thing under the sun, as long as it is a matter of talking at the coffee machine and throwing buzzwords around in meetings, but when it is a matter of DOING things practically, everybody is super-busy and all their projects are "very urgent and quite late", but not so "urgent" and not so "late" that you're thinking of getting in the office on time isn't it?
So I ended up in a meeting trying to explain, for the millionth time, to a developer (CL) that has no clue what he is doing and to his boss (UL) that has even less clue, that the idea to use a relational database to store information that relational are truly not, is not a very good one.
Me - ...hence, your data structure is completely wrong for this type of things.
CL - The data structure wasn't designed by me, it was already like this.
Me - And that's ok. But the fact that is wrong for this kind of things is still a fact. And that's the main reason your system is so slow.
UL - (that doesn't know a thing of databases, doesn't understand a thing of computers, has no idea about the application's structure and is unclear what he is doing in this meeting, besides annoying me) But if we try to optimize the database structure which improvement of the performance could we get?
Me - There is nothing to optimize. Is like to try to race a Formula One Grand Prix with a tractor.
CL - What has a tractor to do with it now?
Me - It has power in quantity but is designed to be slow. And that's your problem. You database has power in quantity but is built to do something completely different.
UL - But if it has power in quantity why can't it go faster?
Me - Which part of "designed to be slow" wasn't clear?
CL - And how can we change it to make it go faster?
Me - Replacing it with something completely different. Given the use you want to do of it, I think that something like Mongo or Redis should be more in tune with it.
CL - Monwhat?
Me - No What, Go. Mon-Go. Never heard of non-relational databases?
CL - Non-relational? How can you have a non-relational database? If it's non-relationa ain't a database!
Me - Well ok, referring to it as "database" is maybe a bit of a stretch on the term, but since we don't have a better word for it...
UL - And why your database doesn't support this mongothing here?
Me - It's YOUR database, not ours, we provide the platform, you decide what to run on it.
CL - But let say we need to run a query that make a join...
Me - No, you don't make joins, what you do is to fetch all the data and then you look up matches with other parts, if you want to do it.
CL - But we have all the queries that...
Me - ..don't work. And that's the problem. With your data structure and the way you want to manage the data you can't make "queries". You have to think in a different way.
CL - Impossible! Change the approach would require a complete redesign of the backend!
Me - Indeed.
UL - (still hasn't figured out what we were talking about) And if we put the mongothing on top of our current database?
Me - No, it doesn't work like this.
CL - Redesign the whole backend? It can take months!
UL - $otherprovider has guaranteed us that their structure will provide 4 times the performance of yours!
Me - Then I suggest you go to them, and I'm not going to tell you to actually TEST those performances and get them in writing in the contract because I assume they aren't stupid and won't be in any measurable way.
UL - (understanding that I'd like to see them going somewhere else, as opposed to what MarketingMan would like) ...but if we instead focus on the optimization of the database? Like, if we add more ram to it?
...No, they never learn or understand.
Davide
23/09/2019 10:38
