Why SQL is beating NoSQL, and what this means for the future of data
blog.timescale.com
blog.timescale.com
Anyhow, only point I'd add is that I'm not sure that SQL-like languages added on top of NoSQL solutions was really that bad of a thing. It's not as though there's one flavor of SQL to rule them all on RDBMSs. I get that the bones are largely the same - SELECT, FROM, WHERE, most join types, aggregates, etc. But take a really common analytic use case - persisted transactional data. Something like this -
SELECT A, B, C, FROM SomeTbl WHERE SomeDate BETWEEN SomeBegin AND SomeEnd QUALIFY RANK() OVER (PARTITION BY A, B ORDER BY SomeOtherDate DESC) = 1
Pretty sure that SELECT, FROM, WHERE will work just about anywhere. That QUALIFY clause is written for Teradata, and once you get into the ordered analytic functions (as one example), you need to know the implementation relevant to the RDBMS you're dealing with. It's not uncommon for a large enterprise to have MS SQL, DB2, Teradata, Oracle, etc. all under one roof. As a data engineer, you still need to know the differences - first to get the data out, and then to optimize.
Dunno, I could be way off on this, but that's been my experience at least.
I think the bigger issue is that you can use an RDBMS to do NoSQL-like things, such as key-value stores, with the flexibility to structure your data if you need that later. So why not start with a relational database?
I asked the same question. The biggest reason I've heard is RDBMS's don't horizontally scale well, meaning you can't easily have 50 replicated nodes across the globe and expect it to perform well, or setup easily, because it's fairly complicated with an RDBMS. There's things like Oracle's grid or SQL's high availability clusters, but they get complicated fast, particularly when you need a bunch of nodes.
Obviously it was a solution for a very large amount of unstructured or semi-structured data that needed to be redundant across networks. With NoSQL, the key/values are a lot simpler to replicate apparantly.
It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes.
You nailed it on both points. Very few organizations are going to need that kind of scale, but many want to think that they will!
Even when you get to horizontal scaling, you can simply replicate what needs to be replicated. Key-value is really just a subset of relational data, right? If so, I bet we can query and push only that data out across the nodes.
It's not to say non-relational models don't have a place, but they're the exception as far as I'm concerned. I prefer to start with relational and only move off of that if I can justify it. As I said upthread, though, I work in insurance and you might imagine relational works well for us.
The RDBMS is a little better at storing this type of data, but it has it's own problems. Backups quickly become huge and a massive undertaking. Development databases now have a bunch of missing data (because who wants to replicate that down to lower levels), you still have the same problem when you have a large amount of data that you need to shard across NAS/arrays, etc.
The NoSQL seems like a third option that may well be worth looking into. Again this is for unstructured data that you just need to reference and not necessarily query against. (like stored contracts).
This isn't a great reason not to use a filesystem--it's a reason not to let staff have at the filesystem like cowboys, and not to avoid replicating and backing up the keys and values on the filesystem as if they are really important.
That said, there isn't really a solid replacement for a database transaction involving both fields + blob, but most situations are okay with, say, stowing a blob in S3, and later making a half-hearted attempt to delete it if the associated database transaction fails.
In some cases, the companies were successful in storing the relevant information in a database and use that to generate the documents required based on document templates. This is not hard, but it can be tedious to set up. The companies also have to have a will to walk down that path and many do not see a benefit to this kind of process.
It takes a very insightful designer to manage and lead this process as well as a client who has an incentive to move in this direction. One of the considerations is that the approved contract must be in some unalterable form that can be tested against the contract generation process from the information within the database. So that there is always a means of ensuring that any information in the database cannot be corrupted (in a legal sense). That is a matter of safeguards not database information storage capabilities and there are many different ways to do this kind of security.
The problem of sharing of data and replication is not often thought through properly. Oft times, I have seen replication being performed wholesale instead of piecemeal. Similarly for data sharing and the consequences have been less than efficient. Oft times, the actual requirements for sharing and repliation are very small and it is a matter of understanding what is actually required and what is desired for the business.
Finally, very little data is unstructured if you look at it correctly. Many times, I have seen people talk about unstructured data (including documents of all kinds) and when you look at the data, it is highly structured. It becomes a matter of seeing what that structure is and working out what is variable and what is constant data within the structure.
There's no need to store the full details as a BLOB in your DB. That has nothing to do with SQL, NoSQL, distributed, or centralized. It's just a vanilla separation of immutable object data (the raw bytes of the PDFs) and application state (the record locator).
[1] https://technet.microsoft.com/en-us/library/bb933993(v=sql.1...
Contracts are not as unstructured as you would think. At least in my part of the insurance world, most of them are produced by a contract writing system of some kind. Think of it as assembling as a series of contract provisions, each having a valid set of choices to select. Use your auto policy as an example -
Liability - $100k Comprehensive - $10k / $1000 Deductible Collision - $50k / $1000 Deductible
Yes there are other attributes captured, but you see the pattern. It's provisions and boilerplate. The use cases needing the imaged executed contract where the attribute/provision soup is not sufficient is uncommon.
The operational terms of the contract exist in some other system (and database) where it's consumable in some structured way.
Very few organizations are going to need that kind of
scale, but many want to think that they will!
Not true anymore with "NewSQL" databases (Google Spanner, CockroachDB, ...)
A large part of what Spanner does to sustain its high cross-regional availability at strong consistency relies on Google's powerful network [1]. Spanner isn't just source code - it's vertically integrated custom hardware + software + Google network + Google SRE - you would have very limited use from Spanner if you had just source code.
Example: Recently Google introduced "hot potato" network tiers that closer match AWS and Azure offerings (aka degraded Google network)[0]
(work at G)
[0] https://cloudplatform.googleblog.com/2017/08/introducing-Net...
[1] https://cloudplatform.googleblog.com/2017/02/inside-Cloud-Sp...
Higher levels of automation and abstraction (Spanner) may cost more on paper, but when you start calculating the cost of dealing with shoddy network, cost of maintaining your own service, cost of equivalent to what Google SRE gives you, cost of upgrades/maintenance, sharding/replicating your MySQL database and so on, your TCO math may look a little bit different.
I'll opine that network egress is just one of the many factors that you should think about when evaluating a technology like Spanner. Kind of like price-performance. I encourage you to look at cost-value, not just cost.
(work at G)
This is specific to moving a largish companies data to the cloud. Typically, nobody wants to move it all at once. They want to move one thing first. Think like a trucking company. They have databases of customers, drivers, equipment, orders, invoices, quotes, etc, etc. With the current model where egress traffic is priced so high, it's difficult to move one of those subject areas to the cloud. The reason is because none of them act like an island. They are used together. So unless you move it all at once, huge amounts of data go back and forth all day long.
Not every customer with concerns is just someone that needs to be educated about how awesome Google is :) I do like Spanner. I'm just pointing out a barrier to adoption.
imagine you just received two TB data sets, and you'd like to join them and run a process. In hive, you create the schema (pretty simple) and join them. queries may take a few minutes but you get to doing the work you intended all along.
In RDBMS, you have to fus with its schema, "you said 125 characters for that string field and you just received 130, FAIL". Its a STRING JUST DEAL WITH IT. you have to create indexes and fus with all sorts of relational nonsense when it has little to do with the work you are trying to accomplish.
take your billion row database, and now you need to add a new field. its going to read the entire table into memory and re-write it back to disk, meanwhile you aren't doing the work you were trying to accomplish.
relational databases are great for small static data, but when you deal with new large messy data every day, they sap all your time.
Most (all?) RDBMS's have something like a varchar(max) which accepts reasonably large strings.
> you have to create indexes and fus with all sorts of relational nonsense
No, you don't.
> its going to read the entire table into memory and re-write it back to disk
Probably not, if you're adding a nullable column.
That's not "cheating", it's literally the only relevant factor.
Who would win in a fight: a superintelligent ninja cat with laser nunchucks and body armor, or a dog?
While standard SQL may have this problem, most concrete RDBMS do not, because they support a simple length-unspecified string type (or, in the case of SQLite, treat column types as documentation and mild hints rather than actual rules.)
> you have to create indexes
Absolutely not. You can, for efficiency, but (ironically, unlike some noSQL systems that require explicit indexes to support queries) you can query and join tables of relational databases without defining explicit indexes. This may, for some queries, be extraordinarily inefficient (it may not, if you've defined the schema well, because many RDBMSs implicitly create indexes based on schema features like PKs and unique constraints), but you can do it.
If all you need is a key/value store, I can see how that simplifies replication. Eg, if you have the key "foobar" and a 12-node cluster, you convert it to a number using a hash function, do "module 12", and that tells you which node to store the value on. If you want to store it on 2 nodes, do this again with a second hash function.
That's lovely - as long as you don't need to join records, and never will. It's a risky prediction.
Cassandra & CQL is a prime example. CQL become the de facto interface for getting data out of Cassandra, even without the benefits of JOIN's & sets.
I always felt that CQL did more harm than good for Cassandra. It's not like Redshift -- which implemented a full mostly-Postgres compatibility layer. Cassandra's CQL "looks like" SQL in the same way JavaScript "looks like" Java. Similarity by superficial appearance only; under the hood, totally different.
>> CQL does not even have SQL SELECT.
https://docs.datastax.com/en/cql/3.1/cql/cql_reference/selec...
This sure looks a lot like SQL SELECT. It returns a projection of data. Beyond that it's just pedantry.
OK. :)
The query language is a side effect of the underlying storage engine - you don’t choose it because you want a key value store, you choose it because you want horizontal scalability and cross wan HA
They don't start out that way, but they will be if they're successful, and if they're not they probably shouldn't be running their own databases anyway (go RDS it up or something).
Before that I have held positions in finance, dealing with realtime transaction processing. We did not work in petabytes.
If you are working in petabytes you are storing crap in your production database, and 99% of that data is wasted.
This get large rather fast when processing time series from multiple sensors.
But I agree with you otherwise, and it's not like you're storing satellite products in a database.
Now I'm curious what NSA is using :).
Collecting high resolution and filtering down to low resolution for lower importance is still difficult and can require petabytes for real world use cases
If you max out that single server, you could easily hire one database engineer to care about horizontal scaling.
You also probably want backups and possibly slaves that are updated inside transactions to avoid data loss. These things can lower your throughput even more.
I've tried that in the past and failed miserably.
1) values in a key-value table will endup needing to hold nested data structures, such as a JS object/hash. Ie. mykey={...}
2) turning values to JSON (or some other serialization) makes it impossible to concurrently update or search subkeys.
3) so you convert your complex (sets, hashes) key-value data into several rows that hold one subkey per row, so now you have updatable rows but still no indexable solution and a serious performance problem.
4) so you create a multi column type-segregated table (one column for each DB type) for indexing your key-values and making them searcheable. That also requires a metadata table to go with it so that you know in which column your key or subkey is indexed.
5) say you successfully implemented your key-value store with your RDBMS. You still don't have usable joins (you don't have relational data) or a real migration path out of your key-values.
Trust me, don't just put keys and values in a relational DB. Start with the right tool for the job, either make your schema relational from the beginning or use a proper KV or document store.
2. It has built in query and modification tools that can manage the nested objects and array and whatnot.
3. It let's you build indexes to help with all this, including reverse indexes for asking something like "is this string in the array inside this object".
4. You don't have to specify your schema in sone horrid way, you can just throw what you want at it and specify later, and get performance numbers that are competitive and even exceed some popular NoSql stores.
5. You can mix and match freely with all the niceties of a proper SQL database with transactions, joins, constraints, etc, as you see fit to evolve.
So yeah, don't just throw your keys and values into some text table somewhere; but use instead one of the best proper kv stores, that just happens to also be one of the best sql databases, and figure out how you want your schema you need it.
Postgres JSON is great for storing the original JSON data coming from external APIs and Webhooks, such as Stripe payments. But it's not a silver bullet and really not a first class citizen. SQL query syntax for JSON data is awful, non-standard and requires casting types, which defeats the purpose of schemaless. I believe Postgres JSON types solve a different use case: an app based on a relational schema that needs to store JSON structures eventually.
I don't recommend starting off your app basing it on Postgres JSON. Use the full relational Postgres instead and do it right from the start.
subject (varchar), key (varchar), value (varchar)
But that gets very messy very quickly. So we added a field to specify the value's type. That worked, but then there's an overhead reading and writing the data - because there's a constant conversion. So we then added the most common types as extra value fields. That was the best option. But then the problem is that it gets fiendishly complicated to extract the data. I mean, this was the simplest version that never made it to production. In the end, reporting killed the idea. Because using SQL Server Reporting Services and needing to pivot the data to create any reports became a massive performance issue. Around that time EF Code first was coming in the .Net world, so we went that route instead. And really, I feel like it was the right solution.
You just rarely see those bundled redis or elasticsearch servers that are crucial for some app to do its session management and search engine.
The most disturbing part of the NoSQL trend is that people are treating it like a battle between two competing systems.
I have at least one major system under my own belt that uses relational SQL for backend data, ES for search engine and cassandra for TSDB.
I need and trust all those services to work as one unit.
This is a good point and I didn't do a good job of acknowledging it in my prior comment. There are use cases for NoSQL, but to me it's a tool for a narrower set of circumstances. My default is relational, unless I have a clear idea of why that isn't the right fit.
select a, b, c from table where somedate between somebegin and someend and someotherdate = (select max(someotherdate) from table group by a, b)
which will work in all sqls. My point being, the advanced features make life nice, but are by no means necessary.
(would be more efficient as a join, but not sure if joining on subtables is always supported...)
SQL is super powerful and makes much sense in so many ways. Nearly all apps have a relational structure to them and SQL is a reasonable way to interact with them.
Some of my favorite conversations from the Postgres community 5-6 years back were when they were talking about a time when Postgres was being disrupted. The gray bearded DBAs (Hi Berkus and others) were talking about JSON like it's fad and how it's going to come to pass. They were saying so because they'd heard about this disruption before... there was this new thing XML and these document databases were so much more powerful. They waited a few years and added an XML datatype [1]... And then XML databases came and passed.
At first they scoffed a bit on JSON as the new hip thing, but came around a little in Postgres 9.2, and then in 9.4 [2] we all really got what we wanted.
SQL has never been a particularly elegant language, but it's always been a powerful one. The lone knock against has either been usability or scalability [3]. On the usability side that's a bit of a hard one as well it's not super pretty. But once you understand it's foundations around relational algebra or relational calculus you're so far ahead. On the scalability side there are definitely some options such as Citus, but also core Postgres is massively improving here.
[1] https://www.postgresql.org/docs/9.3/static/datatype-xml.html
[2] https://blog.codeship.com/unleash-the-power-of-storing-json-...
[3] https://www.citusdata.com/blog/2017/02/16/citus61-released/
Is there any theory or academic research on what an "object-relational database" might mean? Or just a buzzword (like Snodgrass titling his book "Time-Oriented Database Applications", a la object-oriented)? (Edit to add: In other words, is "object-relational" something that still gives some kind of principled direction to the project, something that informs decisions made by the core contributors?)
I sort of wonder if nowadays with JSON Postgres has become a "document-relational database". :-)
As things have evolved it hasn't actually given up on ACID or the relational nature as even with JSON you can have complex constraints and guarantees about your data. The Postgres community has simply applied a lot of the underlying principles to what has been build on top.
I think it's largely a leftover - the things that made it "object" back then are partially not used much, partially deprecated, partially removed, partially repurposed (inheritance as a partitioning mechanism).
You could perhaps just do a s/object/multi-modal with a focus on/. But that's a bit awkward ;). There's a bit document store (json, xml), arguably some graph (recursive queries), plenty people just use it for KV, ...
Also a lot of OO mechanisms become really useful without being clearly identified. For instance every table automatically generate an object definition (aka composite type aka rowtype) that you can use anywhere to ensure that a return type conforms to this definition. You can also check and validate single row well ahead of an insert attempts filter out and store unmatching data structure in an generic human readable rowtype while accepting correctly formated entries.
PS: I don't now if my examples were good or understandable but heck I tried, feel free to correct me if needed ;)
Edit: the object part was to build complex data types from simple type in the relational model, and had the database to manage the storage and querying of the complex object. E.g. build Location object from (x: int, y: int), and array of Location[].
Still evolving in its old age, along with postgres:
http://www.newtdb.org/en/latest/how-it-works.html
Other than that I'm aware of gemstone/s:
https://gemtalksystems.com/products/gs64/
Which can be paired with the maglev ruby implementation for similar (but presumably higher performing, more featurefull) experience with distributed objects in ruby:
I suppose that with jsonb, and a node/js server, one could indeed argue that postgres is (again) an object oriented db - but I don't think anyone would push that argument too strongly.
ReQL, what RethinkDB uses, was an interesting direction because it allows people to start reasoning about a particular query. This, IMO, is the biggest thing that SQL lacks - the ability to reason about a query. I'd love to see a way to extend SQL that solves this.
The other thing that really, really sucks is that SQL has some terrible documentation and tutorials. Even for PG, for example, it just mentally takes a long time to parse through the documentation to understand how to use a function that solves your use case. This might just be a lack of Stack Overflow posts out there, but it's definitely not as friendly as, say, finding answers for Ruby code.
But postgres I think continues to be at the forefront: first, the article misstated slightly; jsonb gives you not only full text search (who needs that?) but indexing all the way down. So you can eg say I want records where "field1 -> field2 has an element named md5 with a given value...". It turns out that a lot of plain normalized tabular data plus a medium about of schema-free jsonb really is the perfect combination for most purposes.
Whereas before I might need four read slaves to ensure that at peak load I'm handling all my transactions within X ms, now I can guarantee it on one server.
More importantly, in our industry where we're vastly more write-constrained than read-constrained and we're faced with e.g. MySQL not being able to easily spread writes over multiple servers simply, the appeal of something like MongoDB or Cassandra with built-in sharding and rebalancing to spread out both reads and writes sounds very appealing.
And again, I can move from a giant complicated, expensive, heat-producing multi-disk raid10 to a pair of SSDs in RAID1 (or better) and easily meet my iops requirements.
Without being able to upgrade to SSDs I think we would have been looking into other systems like Cassandra a lot sooner, but right now we can pretty easily throw some money at the problem and it goes away.
I see it in so many things tech. It's a pattern, and once you've seen it repeat a half-dozen times and also gain a depth of experience over that time, you can actually recognize when something represents genuine progress vs yet another passing fad. Spoiler alert: those that are most rabidly promoted are often the latter.
But, if you try to raise the point in the midst of the latest fad, you generally get shouted down. So, you wait until the less-jaded figure it out...again. It was plainly obvious for NoSQl, just as it now is for SPAs (or at least our current approach).
Don't believe me? Wait 5 years.
And, the thing with SQL that's so much like other tech is that we largely figured it out years ago. Improvements have been incremental at best and, frequently, not improvements at all.
I didn't mean the graybeard in a bad way at all.
I feel a bit 50-50 on some of the reactions to JSON as a new thing at the time. But, also the practicality of how JSONB finally came to exist, the foundation it was built on and the new index types[1] in Postgres to leverage it felt like it came in the right time and in the right way.
[1] http://www.craigkerstiens.com/2017/03/12/getting-started-wit...
Easy way to remember is use "a" for "American" in the American spelling, and "e" (for English) in UK (and presumably the rest of the Commonwealth).
EDIT: There do seem to be a couple of blogs that come up in Google pushing the idea of a difference between US/UK spellings, but that's definitely not something I or most people I know grew up with. Other search results indicate that there is only a modest correlation with location. Perhaps it goes more one-way, with both being in common use in the US but "grey" exclusively preferred in the UK?
Oh, no offense taken. What you read in my comment was genuine recognition on my part that I've likely crossed over into the graybeard camp--both in experience and disposition.
>I feel a bit 50-50 on some of the reactions to JSON as a new thing at the time.
Yeah, there have certainly been real improvements in tech that are perfectly reasonable. And, actually, I think it's the incremental improvements that add up over time which actually comprise the bulk of our real progress.
I think where things get off track is when we make sudden pronouncements that we must now cease doing everything we've done heretofore with tech Y in favor of new and shiny tech X. Every use case. Period. Whether it's killing XML because, JSON or killing SQL because, NoSQL or adopting J2EE for every "enterprise" app, or never writing Vanilla JS again because, Framework Fill-in-The-Blank; the list goes on.
There's just a tendency in the tech community to lurch in different directions with this extreme vigor. But, when you really think about it, once the foundations are laid there just aren't many quantum leaps that truly merit such strong reactions.
And, what's really strange is that we somehow manage to overlook glaring weaknesses in the new tech, and likewise disregard (or even impugn) the strengths of the tech we're displacing in ways that are obviously unfavorable. For instance, suddenly deciding we don't need ACID transactions. What?
You see that stuff and it's an automatic, "yeah, this will all blow over soon". We then generally a.) forget it ever happened or b.) settle into assigning the proper use-cases, adopting/integrating the best properties of the tech, and moving on. Incremental.
I hear that often. But SQL is close to a 1-to-1 mapping with sets of data. It's refreshing to use something as math like when programming. I find that elegant.
https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
(NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))
The query structure is odd as well. I think it should more like
from <tables>
[declare] <calculations, scalar variables>
[group by]
[where]
select
Farter more, I don't really think that all systems have relational data, it's just possible to store it in that format. Sometimes 3 document types are easier to handle from code than 30-60 tables.
SQL is also just a query language, that's literally the name. Any database can implement it, not just relational. Is SQL a great interface for both OLTP and OLAP? Yes, it's proven itself over decades. Is it the only valid interface? No. Does it work with other newer/different data systems? Yes, Spark, Hadoop, Kafka, and even these new distributed relational databases are all examples of such.
It would be far better for the industry if we can get past these ideological and faulty "debates" and move on to more important things.
The subtitle for this contains: "After years of being left for dead" and the author throws phrases around like: "And boy did the software developer community eat up NoSQL, embracing it arguably much more broadly than the original Google/Amazon authors intended."
Who? Where's the data? This blog post has a lot of links, references, and studies, but where's the data to back up the premise?
A quick search on Google Trends comparing SQL databases to NoSQL as an entire term, or any of the popular flavors of NoSQL, reveals that it is not even a blip in comparison.
But don't take my word for it, the author and their company had the "DUH" moment too (emphasis mine):
> ...we soon realized that we’d have to do a lot more work: e.g., deciding syntax, building various connectors, educating users, etc. We also found ourselves constantly looking up the proper syntax to queries that we could already express in SQL, for a query language we had written ourselves! One day we realized that building our own query language made no sense. That the key was to embrace SQL.
You might have had hubris stemming from discarding or not knowing all of the history that you decided to share with us in this blog post. A great deal of the NoSQL community was completely unpalatable for this reason. The folks who had been doing data for decades, and built stable and powerful systems on top of many prior decades of mistakes, built them for a reason.
And here we see the author trying to proclaim that suddenly SQL is back?
SQL never went anywhere. NoSQL is a neat tool that was developed and continues to be developed and probably isn't going anywhere. But the idea that NoSQL suddenly overtook SQL, and now SQL is seeing some huge resurgence, feels like it comes from the perspective of someone who only saw a window into the last 6-8 years of development.
The king is dead. Long live the king.
But to your point SQL the language or RDBMS was never dead, it just wasn't the prettiest girl in the room for all the new GI's that just shipped in.
If you had been following startup blogs and HN, then you don't need any more data to back their premise.
It's not like total data matters anyway -- what's important is what use cases people regularly encounter in their periphery and the part of the industry they work on, which might not be what some overall data will show.
I don't care for example if NoSQL only caught on with 1% of developers while 99% of Fortune 500 enterprises and companies in rural Idaho and southern Mongolia used trusty old MS-SQL Server.
For most of us here around HN, judging from posts, comments, and discussions, the NoSQL era was very real, in the kind of companies and environments we knew.
so you're favoring buzzword hype over actual production environments?
Who cares if people are talking about it but not really using it?
I was referring to queries such as: User needs to get a list of pharmacies they've gone to in the past 2 months that carries x-medication. Not overly complex, but annoyingly more code that would have to be written + architected in NoSQL vs a relational db.
Personally, I knew stuff had gotten stupid when I sat through a presentation by a gemfire evangalist who advised everyone present to just "do your joins in code". If you need to join data you should be using SQL.
Most SQL database packages are designed to have a wide range of queries stored with the database (many will always only exist in front ends or developer tools, of course), for several reasons. One reason is that it sheds light on data structure and business logic. Another is avoiding needless duplication, since a particular snapshot which seems only to be needed today may often be needed again.
Perhaps it would be better if there was more tidy separation of data vs. logic, it's hard to say until someone finds a way to do it that's a clear improvement for everyone. We have to work with the tools we've got right now, though, and straying too far from established practices just makes your work harder for the next person to decipher.
1. NoSQL of any kind is not the solution
2. Joining at the location of the data is always preferable to joining in application code due to the Principle of Locality[1]. The amount of data sent over a wire should always be minimized.
We had quite a few instances where by a judicious change in the join structure of the SQL meant a many order of magnitude change is speed due to the decrease of the cardinality.
Our process was to test different orders of joins in problematic SQL and see what effects would be produced by the DBMS. It usually required we knew how many tuples were in each of the tables and how they would be restricted. The upshot was that it should have been possible for the DBMS to do its own analysis to produce the best result. We found none of them capable of this task in any meaningful way.
Having said that, I’d your database doesn’t support joins, you have no option and are stuck having to do it in application code and that sucks.
I think at least 25% of modern software development involves slowly, painfully re-learning lessons we intentionally forgot ten years ago.
SQL has been around since 1974 according to Wikipedia. There was a ton of SQL Work during the first dot com era of the mid-90s.
On another note, I did a lot of Lotus Notes working during that time, which was clearly a no-SQL database.
On the other hand, SQL-the-language isn't essential to relational databases. I have often wondered where C.J. Date has been the last few years. I actually love SQL, but it does have its limitations. I wouldn't mind a solid relational database with an alternative query language. It's such a missed opportunity for a great VC pitch: Tutorial D, NoSQL before it was cool. :-)
The first implementation of SQL in C was probably Oracle V3 (released in 1983). IBM's equivalent SQL offerings around the same time (SQL/DS for VM/CMS and DB2 for MVS) were written in PL/I dialects and assembly, and COBOL, Fortran and PL/I were supported as application languages. I believe IBM's first forays into using C with SQL was the development of OS/2 EE Database Manager in C, somewhat later in the 1980s. (The current DB2 Linux/Unix/Windows code base is originally descended from OS/2 EE Database Manager.)
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_Q...
Oracle was originally written in fortran and then redone in C, which it has been written in ever since. In fact, when we ported Oracle to the mainframe for the first time we were faced with an issue -- do we rewrite Oracle in assembler (there was no c compiler on the mainframe back then) or do we write a C compiler? We wrote a C compiler.
On a side note, I remember watching an interview or reading an article (probably from Larry Ellison) who said IBM gave 1/3 of it's business to Intel, another 1/3 to Microsoft and the final 1/3 to Oracle. Pretty crazy.
IBM's problem was that they were slow to commercialise the technology despite having a significant head start. IBM built multiple prototype RDBMS systems but they weren't willing to ship the result as a commercial product, and Oracle beat them to it. And even then when they did finally ship, their initial offerings were restricted to their mainframe systems (first VM/CMS and then MVS), while Oracle back then was open to porting their database to just about anything. IBM didn't really come to the cross-platform party until the 1990s, by which time Oracle was well established in that market.
(Disclaimer: Former Oracle employee, although I never worked on the database side of the business, and I'm talking about stuff that happened when I was a kid, or even before I was born.)
I really believe that Spark's "more-than-SQL" query interface is how things should be. SQL is of course the gold standard and probably represents north of 90% of analytic workloads, but there a lot of queries that (especially for us programmers) are much easier to express procedurally/functionally, rather than purely declaratively.
I looked at what the code was doing and restructured into a set of queries that would do the same task in a simple sequence. It turned out that this simplification also sped up the entire update process by at least 1 if not 2 orders of magnitude.
There are few cases where processing the data procedurally is better than using SQL queries. Most time that it has been done procedurally, I have found that there are better ways to do what you require by judicious thinking and using SQL (the bronze standard).
I should say that my definition of procedural processing involves opening queries and processing tuples one by one. If you have a different definition then please let me know.
So I think I take the position that the machine is not perfect [1], and doesn't always provide a perfect abstraction of a fast declarative answering interface. Sometimes you really do need to tell it how to access the data. This is why, for example, some SQL query engines let the user provide join hints.
That said, I do agree that procedural queries are mostly a quick fix, and not very future-proof (against future improvements to the query engine).
And FWIW, Spark's DataFrame API [2] is not actually that procedural; it lets you specify something that feels like a direct query execution plan, but actually still gets optimized underneath.
[1] http://www.vldb.org/pvldb/vol9/p204-leis.pdf
[2] https://spark.apache.org/docs/latest/api/scala/index.html#or...
Could be something there with logic programming. Or could not be something there.
The strongest feature of SQL is the lack of specificity about "How it should return results" and primarily deals with the details of what the expected results are.
I work on a SQL engine which honestly breaks so many rules of "How to do things" and basically tries to avoid nearly everything a sane SQL engine engineer would do.
But the advantage of the lack of forcing implementation is that a new idea like this could still implement the "give me expected results" part of the implementation.
Whenever it doesn't - it needs fixing, not documenting in a "vs vs" comparison.
> I actually love SQL, but it does have its limitations. I wouldn't mind a solid relational database with an alternative query language
MDX is a pretty interesting thing to think with.
Mostly because if you're used to spread-sheets, it is a more natural way of expressing what you want generally in a straight forward lookup order - get me some columns from these rows, where some condition is satisfied.
That makes index & cube lookups so much easier to detangle for an engine than a more free-form SQL tree which has so much more variety in it.
SQL sounds like something from the Star Trek original series era to me. Read it with a Shatner voice:
Computer, SELECT course WHERE klingons = 0;And it's bizarrely rigid about certain rules, meaning "English-like" is more of a hindrance because you're tempted to think, "This makes sense in English," even though SQL doesn't allow it.
But as much as I think it's better than SQL, I don't think Tutorial D (or D as a concept more broadly) offers enough to really displace SQL.
Very well said. The concept of relational databases (based on relational/algebraic theory) is thoroughly independent of the dialects to access said databases. Currently it's SQL.
SQL fails to be relational because it doesn't deal with relations. At its base, it deals with bags of records and not relations of tuples.
Tutorial D is relational but has a lot of warts. D4 was relational but changed to allow access to SQL databases.
If my understanding is correct Rel (by Dave Voorhis) is a Java implemented Relational DBMS (a true RDBMS). There are a variety of projects also are designed to implement a true RDBMS. Another might be Andl (by David Bennett) and there is also SIRA_PRISE (by Erwin Smout). There are more and are documented at www.thethirdmanifesto.com, if you are interested.
[EDIT - correct spelling erro]
Why wouldn't you use a visual query tool for this? Tableau and similar apps generate pretty decent SQL queries nowadays. Even old-school BusinessObjects does a decent job (although requires way more initial modelling).
SQL was designed for mainframe programmers, made to look similar to COBOL and PL/I presumably with the idea that at some point in the future it could be integrated into one of those.
Its syntax has all the drawbacks of COBOL's syntax: too many useful words end up as reserved; it doesn't compose very well, leading to statements with very complex syntax rules; and it lulls users into a false sense of security by looking like natural language while being something very different (see HAVING vs. WHERE).
That's not true.
"Closeness to Natural Language - Clearly, the majority of users should not have to learn either the relational calculus or algebra in order to interact with data bases. However, requesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language for a more user-oriented source language."
Had more in mind the relational calculus, for which SQL is just a flawed expression query language for.
I've taught many business analyst to be quite functional in SQL.
I think when a lot of people talk about NoSQL they just want to rant against a certain kind of strawman programmer. You know the one. Young, stupid, naiive, too arrogant to learn nth normal form or define schemas. This programmer probably uses nodejs or some other such heresy and only wants quick results, integrity be damned!
Don't get me wrong, Relational Databases are really good, and fit a lot of problems really well. But there do exist legitimate use cases (not necessarily scale!) where an RDBMS will simply be a poor fit, or a lot more work. Don't dredge up the strawman of the programmer too arrogant to learn SQL because you're too arogant to learn the merits of something that isn't SQL.
From the ops side I actually find RDBMS more difficult to deal with cause the power of relationships is easy to abuse and they are not anti-fragile. Instead of smartly reasoning about the data, it is all to easy to just "JOIN ALL THE THINGS WITH MEGA TEMP TABLES!". I've taken more database outages from bad queries then anything else.
There are bad implementations on both sides. There are reasons to pick both sides over the other given a set a circumstances. At the end of the day, the technical facts don't matter to most people's decision making though.
Anecdotally, I have seen this shift quite a bit in the last decade. There has been a big move toward "try whatever is new and shiny" and I think a lot of that drove the NoSQL craze.
And don't get me wrong, there are some great NoSQL options if you select the right tool for the right job.
But a lot of people who gravitated to NoSQL did so because they designed poor queries in RDBMS. Guilty myself. Often the quick reaction was "wow, this is faster" instead of "well of course it's faster, I'm not getting X, Y and Z features of a relational database. Do I need those? Did I abuse the RDBMS?" etc.
This is so common, I'd love for the popular databases to add table flags that prevent it by accident. Letting me configure "this table most not full scan or file sort implicitly" would get rid of half the incident callouts I've been involved in. You could always override it in the query where needed.
In practice I don't want to know when it happens. I want an error to be raised instead so the database doesn't suddenly die.
Simultaneously too much and not enough.
At worst, I fear your users would learn to override it by default, as just one other part of the magic incantation needed to please the SQL gods.
I think it would be better to have the planner send out emails “this query has to use a full scan” or, “this query is on the brink of changing strategy compared to earlier runs”
But that's exactly what I'm asking for. "It's slow" means that it will work until the breaking point and then wake me up. "It doesn't work" with the right reporting allows me to teach someone about indexing during office hours.
Then the other shoe fell. The data was needed for reports. Reports require a static schema. NoSQL (and the developers who love it) despises static schema. "I can add properties whenever I want!"
This process of analyzing and reporting on production data becomes a very time-consuming, costly, and brittle exercise.
So then you have to determine, did we save enough money on the design side (using NoSQL over SQL) and then piss it away on reporting?
I'd argue AWS and other cloud providers need to create a SQL capable relational database as a service. This would (I hope) solve the problem.
But in the meantime, let's build our micro-services on relational databases so we can actually get aggregate data to stakeholders in real-time.
Isn’t that Aurora?
Like Google's Cloud SQL? https://cloud.google.com/sql/
I spent two years early in my career as a report writer. It sucked horribly, but I learned how important reports are to the business. I remember a professor telling me that any business application reporting is 50% of the value. This is something many developers don't even consider.
Having said that, the solution, as I understand it for the reporting issue with NoSQL is to move your NoSQL data to a proper warehouse for reporting/BI. You'll of course need an ETL layer to do all that, and the ETL layer will need to be smart enough to handle very nullable data and data structures that often get extra, unknown properties, etc.
This solution continues to work even when you have multiple, loosely coupled NoSQL databases handling various parts of a larger system and various separate systems.
This may be true, but reports also normally require a different schema from the OLTP database. Data warehousing with star schemas is a thing and it doesn't matter whether your OLTP database is an RDBMS or a NoSQL solution...you're going to have to push data to your warehouse to run those reports. The last thing you want to be doing is running reporting queries against a database that's required for the operation of your product, even if it is off hours. And reports will never run efficiently if they use a schema that's designed around the requirements of your business' application. For one thing, your update performance will tank since every write requires a ton of index writes. You can mitigate that a bit by maintaining separate indexes on a reporting slave, but that kind of maintenance has its own set of headaches.
So I'm not seeing how reporting is a good rationale for choosing one technology over another. Reporting, when done right, is a completely separate system with a completely distinct technology choice.
Reporting is nearly always an afterthought in system architecture and it should be a first-class requirement.
A data warehouse has nothing to do with the mechanism that stores it. It is a design that comes with its own challenges just like transactional systems do. Transactional systems are optimized for writing. Warehouses are optimized for reading. They way you choose to execute those optimizations is up to you. But getting data from one of those systems to the other is never going to be trivial, and there is no general purpose tool to get things from one format to another because the warehouse structure is too entity-specific to generalize beyond a certain point (and that point of generalization is really the process of creating the design. See the Kimball group's books for more information.)
Redshift is not a data warehouse anymore than Postgres is a data warehouse. In fact, Redshift is a fork of Postgres that leaves out the functionality, data types, and indexes that make it difficult to scale horizontally. Postgres also isn't a data warehouse. Nor is it transactional system. It's just a tool that you can use to build either one of those things with.
Relational model, where SQL is merely the querying language, will win again and again, for the simple fact that it enables data to stand by themselves, and data tend to outlive applications, both in longevity and in scope.
Edit: PostgreSQL came from Postgres, the research project started by Stonebraker and others in UCB.
[1] https://homes.cs.washington.edu/~billhowe/mapreduce_a_major_...
I would agree with that, but since they different monsters (RDBMS and NoSQL), it will take a bit of tweaking to find a good dialect (how do joins work, etc). Of course it makes sense to adapt the NoSQL databases to the existing ANSI SQL rather than make existing ANSI SQL users switch to a new type of SQL that accommodates NoSQL, but we'll see what happens.
Google create a "Standard SQL," but I'm not familiar with it.
Here is a link on Google Standard SQL:
https://cloud.google.com/bigquery/docs/reference/standard-sq...
Yes, I'm sure the inevitable "SQL for NoSQL Databases" book will be a good read :)
That is, the problem with most SQL databases is that you need some pretty specialized knowledge in order to construct good queries. Not shockingly, the problem with most modern key/value (or otherwise) datastores is that you need some pretty specialized knowledge in order to construct good queries.
Now, for things getting off the ground, this is probably fine. Most of the places you will go south with queries is in the ad-hoc style query. Of course, that style query is perfect for interactive use. Ideal, even. It falls on its face if it is supposed to support an automated case at high TPS. Unfortunately, automated cases at low TPS often turn into automated cases at high TPS. Worse, interactive cases at ridiculously low tps often turn into automated cases at low TPS. Which, of course, just feeds itself.
How this feeds itself, of course, is you can explore your data much more effectively if there is an ad-hoc query engine. So, we are now seeing the resurgence of ad-hoc queries and the learning that those can lead to some powerful insights.
Sql is a kind of rite of passage? If you can't learn it or can't be bothered to learn it, you don't need to go anywhere near a database because you can't be bothered to understand the right tool.
Granted, nosql has some uses, but those are few and far between and probably would have been handled more effectively and easily with old approaches like Prevlayer, but Prevlayer was a thousand lines of code and couldn't be monetized.
Which isn't tough, but is often unplanned.
A few years ago, I was refactoring a large legacy Perl application to be much less messy and a bit more object-oriented, and I accidentally a micro-ORM without really realizing it. I think it was a 1000 lines of code in the end, and 95% of all existing SQL usages in the application could be replaced by simple method calls on the ORM's objects.
The Third Manifesto (by Date and Darwen) highlight many of the problems of SQL and SQL DBMS. Though I find TTM to be lacking in various ways, it does, at least, start heading in the right direction in terms of a database query system.
Over the course of decades, I have written much SQL and have had to modify and document many systems that were based on SQL DBMS's. The language is a PITA, but you do what you have to do to get things working.
NoSQL was, to me, a retrograde step, though it has some usefulness in some situations. What I have found is that most databases have not been designed with regard to Relational Database Theory (RDT). In general, the attitude that I have observed has been that RDT is good in theory but bad in practice.
I have built (using mickey mouse systems), fairly complex, multiuser databases based on RDT. I have also seen many databases built (using ORACLE and SQL Server as the DBMS) that have not used RDT and they have been atrocious. One such system, built by some SQL guru, took 25 hours to do a run of one process. Had it been built according to RDT, the entire process (by my estimate) would have taken less than 20 minutes to run.
When designing any kind of database using any of the various technologies that have been developed over the 60 odd years, there are various design principles that will allow a good database (fit for purpose) to be built. It appears in many cases that none of these principles are known or if know are know then not adhered to.
Of course, what others might have found can be quite different.
Contrary to the hype it created, Rails was not ground breaking.
We were using ORMs with Rails like concepts and scaffolding in 1999, based on Tcl, our own mod_tcl and several DB bindings to Oracle, Informix, DB2, MS SQL Server, Sybase SQL Server, running on top of Windows NT/2000, Aix, HP-UX and Solaris.
An in-house application server which shared many design ideas with AOLserver, designed in 1995.
And just to prove that RDF is a model, but not a format, look at JSON-LD as a serialisation format for RDF and at the SQLGraph [1] paper from Google to see how RDF can be implemented on top of an SQL RDBMS.
[1]: https://static.googleusercontent.com/media/research.google.c...
People in this thread have commented that SQL is clunky, and JS definitely fit that description for a while. So I'm wondering if there are any alternatives that would prove SQL's clunkiness to me.
Just like JavaScript is not perfect, SQL is not perfect. I would rather have a general purpose programming language like Scheme to run queries against my database.
I don't know about transpiling but I use SAS to work around SQL's clunkiness. I can embed SQL seamlessly into my SAS code so it works well. I use SQL for things that are simpler in SQL (selecting, filtering, aggregating data and "case when" statements etc) and I use SAS's data step language for when I have to do more complicated stuff with the data like Arrays, loops, macros, weighted averaging, time series based stuff and regressions.
The thing I love about SQL is it is the lowest common denominator for all of my organisations data it doesn't mater if data is stored in an Oracle table, a Microsoft SQL Server table, or even Access DB or spreadsheet. The language to extract/merge/join all the data is the same and standardized.
We also have things called "Data historians" which are basically time-series datastores (usually they are linked to PLC vendors) and nothing frustrates me more when they use some proprietary "sql like" language which seems similar to ANSI SQL except things like aggregations (SUM, AVG, MAX etc) don't work or they do weird things with data types (especially dates) or similar everyone of these seem to be subtly different - annoying.
ActiveRecord transpiles from Ruby to SQL. Django ORM and SQLAlchemy are popular ones to write SQL in Python. Ecto is from Elixir. Every language has its own transpilers. Not all of them are easy to use and they are much harder to use than SQL for complex queries, maybe even impossible to build them.
Frankly I'm a little fed up to have to learn a new way of generating SQL for every language and framework. I'm working on or maintaining projects in Rails, Django, Web2py and Elixir for my customers and I'm coming to wish we were using plain SQL. After all I knew SQL long before any of those other technologies existed, I know how to create prepared statements and how to use positional parameters to avoid SQL injections. Then there is how to efficiently extract the data in the resultsets, create data structures in the original language and possibly cache queries. They don't look so hard (maybe handling joins gracefully.)
Who is that competition between developers? Users? Most of the places I have worked in the last 5 or 6 years have had both relational and non-relational databases. This is not uncommon. In none of those shops was there a competition between the two databases but rather they were complimentary.
This article and title seem to be very self-serving for their own product. Its seems to willfully conflate SQL the interface and SQL a general term for a relational database.
NoSQL has always been something of a misnomer - non-relational would have been a better term but it doesn't sound as buzz-worthy I suppose.
The SQL like interface has been in Cassandra for longer now than it was absent. The gain in SQL like interfaces for non-relational databases is because it's familiar and works really well. Anywhere there is a database there is going to be a need for selection, filtering and projections of tuples.
>"In Amazon’s own words, its PostgreSQL- and MySQL-compatible database Aurora database product has been the “fastest growing service in the history of AWS”.
Is this really surprising that the world's largest cloud provider is selling more databases than anything else? Almost everyone needs a database, given howrelational database, there are more people around that have experience with it.
>"To understand why SQL is making a comeback ..."
No, SQL never went away. Full stop.
>"But don’t take our word for it. Take Google’s"
No, this practice of blindly adopting ideas just because they work for Google needs to stop.
This post sounds as if the author(s) themselves bought into all of the NoSQL hype that buzzword-obsessed tech journalists were spinning and they are just now having an epiphany that much of that hype was just that.
Good! There certainly shouldn't be. Wish I could say the same. Magpie developers, people who want to seem edgy and forward looking, people who like making others look backwards at meetings, people who are worried they'll look like dinosaurs, people who've been around the block long enough to know an approach is totally unnecessary for their project but will be required on the resume for the next project. Trust me, they were all out there, in force.
And of course, many data stores that aren't SQL are absolutely great for the task. Try to solve the 6 degrees of separation problem with SQL joins, then do it with a graph database. There are all kinds of applications that weren't a great fit for a relational model, and many that are. This article does mention the moment where NoSql was defined as "Not Always SQL" (and dismisses it with "nice try"), but honestly, if this had been the approach up front, it would have saved us a lot of grief.
Probably. Maybe. I hope. Oh, who am I kidding. All that stuff I described above would have happened anyway.
Personally I actually would like an alternative language that would be capable to be integrated with the language in such way that it could also fall with type checking.
So for example if I rename a column in a database, the type checker would highlight all places in my code that were broken by this change.
JOOQ[1] seems to do something like that, but it's only for Java.
Also, looks like QUEL[2] would be a bit easier to be integrated with a language, too bad it died.
You don't take your car to go intercontinent, and you don't take the plane to go a couple of kilometers.
One thing NoSQL databases excel at, is scaling. Scaling in Cassandra for example means adding a node, everything else is just magic. THAT is what made MySQL and the SQL language popular.
SQL may be popular with developers, but scaling MySQL was a nightmare for quite some time, even with the introduction of vitesse etc.
Point is, please do not compare apples to oranges.
If you specify that a field is an integer, you shouldn't have to care how big that integer is. That is a concern for the DBMS and should not be yours. In point of fact, your only concern should be what fields you are using not how they are to be stored. You should never ever have to concern yourself with whether a field is a tiny_int, small_int, integer or big_int.
Any changes to the sizes that the DBMS has chosen for specific relations should be automatically propagated by the DBMS to any other area that refers to the original field. It should never require intervention by a DBA or programmer, ever.
Over the decades, I have read lots of recommendations for DBA's about how to manage the various databases stored in the DBMS. Most of these recommendations should have been put into the DBMS as a part of the attributes by which the DBMS controlled the database. These recommendations were often portrayed as a result of running various sets of queries and other functions in specific orders. All of them could have been automated out of existence, but to keep the DBMS simple, they forced these processes onto the DBA's and database designers.
Since we know what has to be done, surely the DBMS producers (like IBM, ORACLE, Microsoft, etc.) should be able to do so as a part of their product development.
Looking at, say, an eventually consistent distributed decentralized kv store, one might be tricked into believing it's simple enough to deploy with enough nodes and general enough interfaces that you can build a complicated system on top of it if needed, and rely on its supppsed simplicity and scalability the rest of the time. But nobody tells you about the shitty implementation. The replication that doesn't resume, the stodgy transfers, the imbalanced distribution, the consensus conflict, the infinitely expanding disk, the churning CPU. How at scale, if all the other aspects of your infra aren't scaling along with your data size, the whole thing blows.
Traditional SQL databases end up being many times simpler in practice, and because of their inherent scaling limitations, much easier to manage. And most importantly: their implementations aren't buggy.
SQL is just more reliable.
Did we have someone who managed that system? Sometimes, but mostly it just did it’s own thing. We ironically invested way more time on our MySQL database over the years because we couldn’t get that to scale the way we wanted to, but I think that was specific to a problem we were having.
Did we later invest a lot of time in that system (the NoSQL one)? Yes, because it was very cost effective for us to do so. At certain scale throwing people at optimization problems can pay huge dividends. But, this can be said of most infrstracture. It’s usually worth revisiting every year or two and seeing how what you can squeeze out.
Did we have churning CPU, infinitely expanding disk, replication issues, and more? Sure, but not very commonly and mostly it was fairly easily resolved. More importantly though, it was a solid system that was the underpinning of a colossal system, and it behaved admiraly more than 99.9% of the time.
Will most projects benefit from a hugely distributed KV store? Nope. But I’m still glad they exist!
One thing that helps is if people stop referring to things as SQL / NoSQL as what ends up happening is various things get conflated.
When talking about stores, it's important to be explicit about a few things:
1. Storage model
2. Distribution model
3. Access model
4. Transaction model
5. Maturity and competence of implementation
What happens is people talk about "SQL" as either an NSM or DSM storage model, over either a single node, or possibly more than that in some of the MPP systems, using SQL as an access model, with linearizable transactions, and a mature competent implementation.
NoSQL when most people refer to it can be any combination of those things, as long as the access model isn't SQL.
I work on database engines, and it's important to decouple these things and be explicit about them when discussing various tradeoffs.
You can do SQL the language over a distributed k/v store (not always a great idea) and other non-tabular / relational models and you can distribute relational engines (though scaling linearizable transactions is difficult and doesn't scale for certain use cases due to physics, but that's unrelated to the relational part of it).
Generally people talk about joins not scaling in some normalized form, but then what they do is just materialize the join into whatever they are using to store things in a denormalized model, which has its own drawbacks.
As to the comment above you, SQL vs NoSQL also doesn't have anything to do with the relative maturity of anything. Some of the newer non-relational engines have some operational issues, but that doesn't really have anything to do with their storage model or access method, it just has to due with the competence of the implementation. MongoDB is difficult operationally not because it's not a relational engine, but because it wasn't well designed.
Just like people put SQL over non-tabular stores, you can build non-tabular / relational engines over relational engines (sharding PostgreSQL etc.). In fact major cloud vendors do just that.
Old school sql replication is awful. It’s awful in Postgres (single threaded replay? A single vacuum can make your slaves fall behind). It’s awful in MySQL (go ahead and tell me how you do dual master without rewriting your app).
People use nosql not because it’s eady or flawless, but because doing it with sql is an order of magnitude harder.
Go ahead and describe resharding a sql DB as your company grows 100x
Or how you’ll handle a dc failover and fail back
Or how you’ll shrink your resources after the holidays - without downtime.
These are trivial with Cassandra and god damn near impossible for most MySQL users.
More reliable? Your “big iron” is still a single point of failure. If not a single cpu, a raid card or a top of rack switch or a DC in a hurricane zone.
I still believe in Curt Monash's cardinal rule of RDBMS development: http://www.dbms2.com/2013/03/18/dbms-development-marklogic-h...
IMO really the best option for distributed OLTP RDBMS/NewSQL right now is basically either Citus or Vitess, only because they are built on the backs of extremely mature databases. It really feels like all other options are not mature enough (except maybe VoltDB).
* Dependability. I can be completely certain that standard usage of the database won't lose my data or leave it in a bad state, at the very least.
* Community. There's an active IRC channel (or the like) where I can ask questions. When an error show up, I can Google it and easily find an applicable solution most of the time.
* Performance. Not just with synthetic (i.e. TPC-C, TPC-H, YCSB, etc.) workloads, but proven to have reasonably satisfactory QPS in many/most real production settings.
* Ecosystem. There's a good collection of first-party (and also ideally third-party) tools, constantly being improved. (Mere wire-compatibility with MySQL or Postgres might be good, but probably isn't enough, since 100% wire compatibility seems rare.)
MySQL? Nobody, and I mean NOBOBY is saying you should use MySQL. All of the things you mention is trivial using a decent SQL solution like PostgreSQL.
> Traditional SQL databases end up being many times simpler in practice, and because of their inherent scaling limitations, much easier to manage. And most importantly: their implementations aren't buggy.
Not MySQL. MySQL has almost none of the important features of a real SQL database management system. Nobody should be using MySQL for anything.
And if you ask the Youtube team for a recommendation for your project, they will not point you to MySQL.
this is kind of like telling everyone that they should ALL be using emacs, when vim/nano is still sufficient for the job.
Even before galera was a thing regular MySQL replication could use auto_increment_(increment|offset) to make each server generate unique id's.
Also, even if you do use auto increment with offsets for each server, you’ll have to know how many servers you’ll eventually have, or waste offsets and eventually risk outrunning your data types
For those that do use auto inc, galera will manage the offset/increment value based on cluster size automatically.
Allow me to elucidate: We query the database in a "query language" that is declarative and based on a expression. This SQL expression is then compiled down into imperative instructions. This is where the mistake lies: SQL is a leaky abstraction. We understand algorithms, we understand how to manipulate the Big-O of imperative instructions to get better runtimes, but in order to understand how to optimize SQL we have to understand how this "expression" compiles down into imperative code. There's no rhyme or reason for why "SELECT STAR" is bad unless you know beforehand the instructions it compiles down to. Actually, you don't even need to understand the instructions "SELECT STAR" compiles down to, you can build an entire career (DB admins) around just remembering that and other random rules as optimization hacks.
The bottleneck in web development is the database. The reason why we can use python or ruby as application servers instead super fast C++ apps is because the database will always be way slower. So it would make sense to optimize the database, yet decades of imperative algorithm theory is thrown out the window because you can't query the database imperatively. Instead of optimizing the bottleneck, we write out web apps in imperative code and our database queries in highly abstract leaky expressions.
The web app is not the bottleneck. Having a highly abstract functional language as the web app is perfectly ok as you tend to not have the web app run through complex search algorithms anyway. Instead we decide to write our webapps in optimizable imperative code and our bottlenecked database in SQL. It's all backwards.
I'm thinking the mistake with NOSQL was the fact that the databases weren't relational. Can we make a relational database with a query language that is NOSQL and imperative?
The query language side of things hasn't changed much but coming from automotive which is currently generating daily terabytes of long and flat data in the form of time series streams with very little relational characteristics nosql systems are very useful.
I think there is a tendency to go after old relational systems because of how they handled themselves with the data that was generated mostly by users. At this point my industry is dealing with data generated by machines and nature and thus the need for extremely scalable and distributed systems.
Whether these systems use sql i don't think is that important ( most of them will support sql or sql like )
But while that aspect of the SQL experience I'm comfortable with, I'm still just a developer and not a DBA. I don't even _want_ to be a DBA. And as someone putting the pieces of a startup together in a country where DBA skills are practically non-existent (meaning I can't reliably train someone up myself) a cloud hosted NoSQL solution seems a much better bet on paper.
I've noticed that when the topic of SQL vs NoSQL comes up, the issue of cost of infrastructure maintenance always seems to get glossed over.
However, there have been times where being forced to define a schema has been painful. If our app has to store dynamic keys/values it all winds up as records in a relational db table acting like key/value store. Whereas in Mongo we can just store the document, without affecting the entire table schema.
I want the best of both worlds...
https://www.linqpad.net/WhyLINQBeatsSQL.aspx
Also, it's such a small thing, but why not place the SELECT clause after the FROM clause? This would allow for easier auto-completion help from the query editor.
Like SQream or Kinetica
Still, Datalog as implemented by Datomic is a breath of fresh air.
Postgres is creeping up on Cassandra faster than Cassandra becomes usable. Especially the really cool work that Citus is doing.
Scylladb is creeping up on Cassandra though.
https://github.com/citusdata/citus
Which is why Postgres is creeping up on both scylladb and cassandra.
Plus PG 10 will have declarative partitioning built in. Pretty cool.
2. Kind hard to creep up on scylladb by seeing it's architecture and assuming you use it for what it's best at, the asyncness and raw performance.
Of course, having a common query language is a big advantage, but NoSQL varies in paradigms and implementation due to the subset of problems they choose to solve, thus having a standard query language does not make sense for NoSQL.
The math is solid, right?
Also: scaling isn't just speed, it's also capacity, HA, failover. Are you running 100x4T NVME in a single server?
Edit: fun little story. Some time back, I wrote a compaction strategy for Cassandra designed for time-series data. That strategy (known as TWCS) is now widely deployed, but for the first year or so, it was an uphill battle to get it accepted into the project over the existing options (high barrier to entry for fairly obvious reasons). I had given a talk at a conference and made the code available online - my employer was going to use it, so giving it out was a good way to help iron out any wrinkles, and maybe some early adopters could help me get it into the project.
There was a Fortune 500 company who sent me one of their products - unsolicited - as a thank-you, because they had deployed my compaction strategy, and it apparently helped them a lot. I asked how much, and their answer was something like "About $20k/month". In my mind, I thought that was great - probably let them decommission somewhere in the range of 5-20 servers, which sounded pretty good to me. Then they said "per cluster, and there's 10 clusters so far".
Now, the $200k/month savings is cool and all, but the real point is think about how many machines are involved here to be able to SAVE $200k/month just by changing the way data files are combined.
Many startups use NoSQL. Personally I prefer NoSQL for most use cases. That said I have nothing against SQL itself but I will NEVER go back to using an ORM - They're a dirty hack; always have been and always will be. I'd rather write the SQL by hand.
I love using MongoDB; it's very simple to use. I liked RethinkDB's ReQL even more (even though the company itself had to shut down, the open source project is still great).
The main problem with SQL databases is that the powerful features offered by SQL don't scale; so if you want to build a future-proof system, you have to avoid using certain features like joins; the advantage of NoSQL is that it's built to scale; the constraints are are feature.
Too bad it is kinda sorta the thing with a RDBM. Relationnal. When you have to work with data which are related and don't want to do stupid shit like updating the infos of an author in each of its written article you want SQL.
But nothing prevents you from using a NoSQL database on top or even multiple ones. If you have multiple way to use your data, having multiple read or write models is a plus.
About scale: rare are the people who can't just add more hardware to the problem.
One thing I learned writing SPAs is that joins are often unnecessary because with proper state management a lot of data is already present on the client side. NoSQL Query languages (for example Mongo or CouchDB queries) are more than sufficient for these use cases.
1. Single entry point.
2. Not having to share your filesystem.
3. Centralized security separated from your OS
4. Simple key/value access (no folder structure)
5. Abstraction of the filesystem (like #2)
6. Distribution of data over multiple nodes.
7. Separation of NoSQL admin and network admins.
8. Shiney new thing :)
Some databases also let you define secondary indexes.
It's a bit weird, but logstash & elasticsearch (and relating/extracting data when and where I need it into postgres) have made my life so much easier
Yep. Except sometimes without the benefits of a query planner, standardization, or comprehensibility. NoSQL is better in every way, right?