8 ms·

SQL vs. NoSQL

55 points by thinkerer 12y ago

Theres a lot of debate on this and I understand theres no clear answers. Just wanted to hear some of your experiences working with both and whats applicable for what type of systems. I myself am working on a database for a web app analytics tool and witnessed some benefits of NoSQL while SQL is supposedly more reliable. Understand companies like Amazon have shifted to NoSQL to manage their data. In short, I do think that SQL is for high processing, low disk space while NoSQL is vice versa in layman terms. Im pretty new to programming and appreciate a healthy discussion on this. Please do not draw lines and have hate posts and all. Keep the discussion clean and intelligent. Thanks!!

SchizoDuckie 12y ago
A project from 2010 that's stalled half-way through in alpha state. Still, he is quite correct. You still need a Structured Data Query Language.
- mantrax5 12y ago
  SQL remains a useful tool in the box, but while SQL and NoSQL are fighting it out to be the default go-to database technology, something else on the horizon is threatening the concept of having any database separate from the app at all: persistent in-application state. We don't need a database if we can just load the application state in RAM and save it back via serialization. This works well in surprisingly many cases.
  - nawitus 12y ago
    The problems with that are easy to see though: you need lots of RAM if your database is non-trivial in size. You also need to write the state to the database continuously to prevent data loss from failures. Not saying the idea doesn't have use cases, of course. Redis (for example) is pretty popular these days, but I don't see how it'll become the default go-to database.
    - mantrax5 12y ago
      Yes, you need to write continuously. Basically you need a transaction log. Redis unfortunately botches that part a bit (it has no performant and reliable way of persisting every change before acknowledging a "commit"), but that's roughly the idea. However not all of your state will be persistent, so you write only the persistent parts. For example, if you need to have an index for quick access to items based on some criteria, you don't need to store the index on disk, you can always rebuild it on startup from the rest of the data. While there's an upper limit for the amount of data you can store this way (it has to fit in RAM), it fits very favorably in web scenarios, where you have read-heavy write-light data access patterns, and you never have to touch the disk in order to read a piece of data (guaranteed).
  - _delirium 12y ago
    That's a fairly traditional no-DB approach [1], but what's the reason to believe the dominance of that approach is on the horizon? [1] Common Lisp has a particularly nice system for doing it transparently, http://common-lisp.net/project/elephant/ http://common-lisp.net/project/elephant/, and I believe HN is implemented using a DB-via-serialization strategy as well
    - mantrax5 12y ago
      Actor systems. Actor systems allow this to scale and cross the machine boundary without having some dedicated database product doing it for us. I've noticed that the more I work with actor systems (each actor is stateful and keeps its own state), the less I use databases. In fact, the idea of keep state separate from the code reading it and changing it begins to look oddly broken as a concept.
nawitus 12y ago
>The NoSQL movement is partially a reaction to antiquated database servers, and also a response to a fear of SQL borne from ignorance of how it works I think the NoSQL movement was created for practical reasons. Since SQL didn't scale properly for certain applications, new kinds of databases had to be invented. What often happened is that a software used a typical SQL database with locks, and as that didn't scale, the locks were decreased to a point ACID wasn't theoretically guaranteed anymore. And it worked. If the database in production doesn't guarantee consistency, you might aswell design a new database which is based on that idea, which is a core reason NoSQL databases were created. There's other reasons, too, of course..
- jsemrau 12y ago
  "Movement" is a bit much, no? It is a solution for a niche problem.
  - Pacabel 12y ago
    "Movement" is a good term for it. There are a lot of people out there who have a quasi-religious attitude toward NoSQL databases. It's a cause for them to rally around. In some ways, it gives them something to "fight" for. Objective consideration about whether it's the best technology to use in a given situation is often disregarded. There are a very small number of very rare situations where such technology is the best or most feasible approach. Otherwise, it's merely something that a lot of people get involved with in order to intentionally avoid learning how to use SQL and relational databases, or to feel like they "belong" to some greater cause.
    - nawitus 12y ago
      >There are a very small number of very rare situations where such technology is the best or most feasible approach. I don't agree that it's a very small number. In fact, usually SQL brings a lot to the table of which very little is actually useful.
SimeVidas 12y ago
> Or Why You Still Need SQL Great way to make people close the page immediately after reading the title. (I personally don't need SQL for my small web app => the title's lying => close tab).
- jimktrains2 12y ago
  I don't need a hammer to screw in a screw, so I don't need a hammer. QED.
daleharvey 12y ago
There is some irony in opening the post proclaiming how important SQL is because of webSQL, webSQL has hung on by being the only choice with Safari, with Apple almost certainly adopting IndexedDB webSQL will cease to exist in any relevance pretty quickly (its already fairly niche). This post is just filled with naive strawmen arguments, I work on a NoSQL database, show me a SQL database that is implemented embedded inside every browser and allows offline operations to sync between masters transparently ... Trying to define or dismiss 'NoSQL' as a singular movement is not going to work, there are lots of tools, I use different ones for different things, some of them even have a SQL interface.
- zik 12y ago
  NoSQL is a strange term. It really just means "everything else that isn't a traditional SQL database". It encompasses a massive range of completely different database technologies. SQL has been astonishingly successful in recent years and it's a testament to its dominance that the term NoSQL even exists. It's kind of like calling all meats except for beef "NoBeef". I'm really happy to see some different database technologies getting some attention these days. They each have their own niche. There's no SQL vs redis vs MongoDB vs cassandra vs whatever "winner", just databases with different strengths which can benefit us in different ways.
  - Pacabel 12y ago
    I don't think that's necessarily the definition of "NoSQL database". Databases that predate Codd's work, or long-established ones like dbm and BDB, may appear similar to modern-day NoSQL databases in how they operate, but they surely aren't the same. Those systems couldn't use relational theory or SQL because they didn't exist yet, or at least didn't reject them outright as one of their main goals. NoSQL databases, on the other hand, are completely about rejecting relational theory and rejecting SQL. That's at the very core of their philosophy. True NoSQL databases have been developed as a reaction to several things: 1. A very, very, very small number of situations where relational DB systems cannot easily scale. 2. The far more widespread ignorance of the basics of relational theory, and a lack of willingness to learn about it. 3. The far more widespread ignorance of SQL, and a lack of willingness to learn about it. 4. An urge to be "different" solely for the sake of being different, even if it brings no technological benefit. 5. Unmitigated hype surrounding the term "NoSQL". Of those, 1) is perhaps the only legitimate reason for using a NoSQL approach today. The number of times this sort of a situation actually arises is remarkably small. The other four are why those databases have become more widely used, especially within the web development community. As anyone who has dealt with such systems knows, they're rarely about safely and reliably storing and managing data, and they're rarely about doing this efficiently. They're merely a shortcut that some developers use to avoid learning how to use a RDMS.
twic 12y ago
The problem with SQL is it seems everyone hates its guts. It is a weird obtuse kind of "non-language" that most programmers can't stand. I'm baffled. I'm a programmer and i rather like SQL. Most programmers i know use SQL quite happily. Indeed, i often hear them wishing they could just write SQL to solve some problem rather than use some API that purports to save you from writing SQL. I certainly prefer SQL to MongoDB's query language, which is verbose and unexpressive by comparison. Am i in a tiny minority? Or is this SQL hatred a popular myth?
- dickinurass 12y ago
  LOL makes me want to dip my penis into your coffee
- nknighthb 12y ago
  I'm probably one of those programmers you think "use SQL quite happily". You're wrong. SQL is an abstraction with myriad problems in both its core design and its disjointed implementations. ORMs and similar interfaces are crappy abstractions on top of a crappy abstraction. You get the problems of SQL combined with the problems of the ORM. Preferring to remove a set of problems and be left with only the problems of SQL is not the same thing as liking SQL, it is merely preferring the lesser of two evils.
  - mamcx 12y ago
    In what sense SQL is bad?
    - nknighthb 12y ago
      I see no reason to reproduce Wikipedia's laundry list: http://en.wikipedia.org/wiki/SQL#Criticism http://en.wikipedia.org/wiki/SQL#Criticism
      - jeltz 12y ago
        Wikipedia's laundry list contains of two items: * SQL not being relational algebra * SQL implementations being incompatible Both are real but I have not experienced either of these being a problem in real usage. Despite the problems caused by NULLs and that SQL allows duplicates I do not think the alternative would be better for real world programming. And the incompatibilities are avoided by picking one database per project and sticking with it.
dorfsmay 12y ago
He is right, the issue has been servers not scaling horizontally. SQL the query language is great, the concept of set theory applies really well to data. SQL can be and is used on non-SQL database, impala for example implements SQL to query data burried in hadoop. NoSQL stores become popular because they scale horizontally (able to use more than one server) naturally. Once the SQL servers can do automatic partitioning, I suspect people will start migrating back. The truth is that there is no pixie dust, at the end of the day you need to index. I see nosql proponent having the same strugles sql people have with indexing, but right now they have the upper hand because they can spread the work over several servers.
- mattdeboard 12y ago
  I guess this might explain some of its popularity but I think it has much more to do with the fact you don't have to do any advance planning about your data (i.e. no need to write schemata). "Rapid prototyping" is much easier when you don't have to think about the your data, its types nor the relationships between them. Of course, moving out of this phase becomes a massive headache, since basing your product on essentially unstructured data is a very good definition of "technical debt". And if you're using structured data in your rapidly prototyped object, why not just a RDBMS in the first place? :)
  - vertex-four 12y ago
    > Of course, moving out of this phase becomes a massive headache, since basing your product on essentially unstructured data is a very good definition of "technical debt". Of course, it's possible to use things like JSON-Schema to validate your data if you choose to. > And if you're using structured data in your rapidly prototyped object, why not just a RDBMS in the first place? Because where's the RDBMS with a "natural" query language that is well-suited to complex, dynamic queries and document structures? How do people model a document store, with the ability to point to differently-shaped data, in an RDBMS without giving up all that safety?
    - mattdeboard 12y ago
vertex-four 12y ago
Personally, I find that SQL is hard to generate. It's hard to reason about the creation of dynamic queries, say, for some types of searches. It's not reasonable to create a generic function that can operate on any same-shaped piece of data. And it's ridiculously hard to manage the concept of a pointer to a piece of data which could be one of many types, a problem I'm sure most people have faces. Stored procedures can help with some of this, but then you've got to remember to update them every time you need to access data in a slightly different way, and it's hard to figure out when you can get rid of old stored procedures. Tools for managing them aren't up to scratch with tools for managing "real" programs. Personally, I'm keen on RethinkDB. It's a document-oriented NoSQL database which doesn't lose your data, has built-in clustering, and an extremely strong query language built on chaining method calls. While it doesn't have transactions, the query language is powerful enough that you can easily model most forms of deterministic data transformation within a single query.
bitL 12y ago
I'll just put a few points here to understand when you should use which architecture. NoSQL: - reading is fast, writing is expensive (if all data are pre-processed/denormalized for reading during the writing phase) - often schema-less - low latency (for key-value storage) - offline batch processing (classical Map Reduce) - no ACID, choose 2 of 3 in CAP - demanding on SW engineers to get client-side conflict resolution, tricky in general - Petabytes of data can be suddenly processed - huge variation of different paradigms, key-value, document, graph, batch etc. - haywire indexing SQL: - writes are fast (normalization), reads are expensive (JOINs) - ACID (well, only to some extent, clustering messes up many ACID properties unfortunately and conflicts arise in corner cases) - set operations and a neat math theory behind them - stable indexing, easily constructable real-time JOINs - OLTP - easier for developers - non-flexible schema - tradition, well-known recipes on how to do things Basically, if you want to have low-latency access, your concurrency model allows eventual consistency, or you have a need to store your data in non-standard structure such as graphs/trees, use NoSQL and pre-process all data to be exactly in the format you require for reading. If you need 99.999% guarantee of consistent data, amount of data you need to handle is under 50TB, you can put your data into a fixed schema and latency doesn't matter that much, use SQL. I would recommend you to ask yourself a question - is your app/business read-heavy or write-heavy and decide accordingly.

SQL vs. NoSQL

55 points by thinkerer 12y ago

SchizoDuckie 12y ago

A project from 2010 that's stalled half-way through in alpha state. Still, he is quite correct. You still need a Structured Data Query Language.

mantrax5 12y ago
SQL remains a useful tool in the box, but while SQL and NoSQL are fighting it out to be the default go-to database technology, something else on the horizon is threatening the concept of having any database separate from the app at all: persistent in-application state. We don't need a database if we can just load the application state in RAM and save it back via serialization. This works well in surprisingly many cases.
- nawitus 12y ago
  The problems with that are easy to see though: you need lots of RAM if your database is non-trivial in size. You also need to write the state to the database continuously to prevent data loss from failures. Not saying the idea doesn't have use cases, of course. Redis (for example) is pretty popular these days, but I don't see how it'll become the default go-to database.
  - mantrax5 12y ago
    Yes, you need to write continuously. Basically you need a transaction log. Redis unfortunately botches that part a bit (it has no performant and reliable way of persisting every change before acknowledging a "commit"), but that's roughly the idea. However not all of your state will be persistent, so you write only the persistent parts. For example, if you need to have an index for quick access to items based on some criteria, you don't need to store the index on disk, you can always rebuild it on startup from the rest of the data. While there's an upper limit for the amount of data you can store this way (it has to fit in RAM), it fits very favorably in web scenarios, where you have read-heavy write-light data access patterns, and you never have to touch the disk in order to read a piece of data (guaranteed).
- _delirium 12y ago
  That's a fairly traditional no-DB approach [1], but what's the reason to believe the dominance of that approach is on the horizon? [1] Common Lisp has a particularly nice system for doing it transparently, http://common-lisp.net/project/elephant/ http://common-lisp.net/project/elephant/, and I believe HN is implemented using a DB-via-serialization strategy as well
  - mantrax5 12y ago
    Actor systems. Actor systems allow this to scale and cross the machine boundary without having some dedicated database product doing it for us. I've noticed that the more I work with actor systems (each actor is stateful and keeps its own state), the less I use databases. In fact, the idea of keep state separate from the code reading it and changing it begins to look oddly broken as a concept.

nawitus 12y ago

>The NoSQL movement is partially a reaction to antiquated database servers, and also a response to a fear of SQL borne from ignorance of how it works I think the NoSQL movement was created for practical reasons. Since SQL didn't scale properly for certain applications, new kinds of databases had to be invented. What often happened is that a software used a typical SQL database with locks, and as that didn't scale, the locks were decreased to a point ACID wasn't theoretically guaranteed anymore. And it worked. If the database in production doesn't guarantee consistency, you might aswell design a new database which is based on that idea, which is a core reason NoSQL databases were created. There's other reasons, too, of course..

jsemrau 12y ago
"Movement" is a bit much, no? It is a solution for a niche problem.
- Pacabel 12y ago
  "Movement" is a good term for it. There are a lot of people out there who have a quasi-religious attitude toward NoSQL databases. It's a cause for them to rally around. In some ways, it gives them something to "fight" for. Objective consideration about whether it's the best technology to use in a given situation is often disregarded. There are a very small number of very rare situations where such technology is the best or most feasible approach. Otherwise, it's merely something that a lot of people get involved with in order to intentionally avoid learning how to use SQL and relational databases, or to feel like they "belong" to some greater cause.
  - nawitus 12y ago
    >There are a very small number of very rare situations where such technology is the best or most feasible approach. I don't agree that it's a very small number. In fact, usually SQL brings a lot to the table of which very little is actually useful.

SimeVidas 12y ago

> Or Why You Still Need SQL Great way to make people close the page immediately after reading the title. (I personally don't need SQL for my small web app => the title's lying => close tab).

jimktrains2 12y ago
I don't need a hammer to screw in a screw, so I don't need a hammer. QED.

daleharvey 12y ago

There is some irony in opening the post proclaiming how important SQL is because of webSQL, webSQL has hung on by being the only choice with Safari, with Apple almost certainly adopting IndexedDB webSQL will cease to exist in any relevance pretty quickly (its already fairly niche). This post is just filled with naive strawmen arguments, I work on a NoSQL database, show me a SQL database that is implemented embedded inside every browser and allows offline operations to sync between masters transparently ... Trying to define or dismiss 'NoSQL' as a singular movement is not going to work, there are lots of tools, I use different ones for different things, some of them even have a SQL interface.

zik 12y ago
NoSQL is a strange term. It really just means "everything else that isn't a traditional SQL database". It encompasses a massive range of completely different database technologies. SQL has been astonishingly successful in recent years and it's a testament to its dominance that the term NoSQL even exists. It's kind of like calling all meats except for beef "NoBeef". I'm really happy to see some different database technologies getting some attention these days. They each have their own niche. There's no SQL vs redis vs MongoDB vs cassandra vs whatever "winner", just databases with different strengths which can benefit us in different ways.
- Pacabel 12y ago
  I don't think that's necessarily the definition of "NoSQL database". Databases that predate Codd's work, or long-established ones like dbm and BDB, may appear similar to modern-day NoSQL databases in how they operate, but they surely aren't the same. Those systems couldn't use relational theory or SQL because they didn't exist yet, or at least didn't reject them outright as one of their main goals. NoSQL databases, on the other hand, are completely about rejecting relational theory and rejecting SQL. That's at the very core of their philosophy. True NoSQL databases have been developed as a reaction to several things: 1. A very, very, very small number of situations where relational DB systems cannot easily scale. 2. The far more widespread ignorance of the basics of relational theory, and a lack of willingness to learn about it. 3. The far more widespread ignorance of SQL, and a lack of willingness to learn about it. 4. An urge to be "different" solely for the sake of being different, even if it brings no technological benefit. 5. Unmitigated hype surrounding the term "NoSQL". Of those, 1) is perhaps the only legitimate reason for using a NoSQL approach today. The number of times this sort of a situation actually arises is remarkably small. The other four are why those databases have become more widely used, especially within the web development community. As anyone who has dealt with such systems knows, they're rarely about safely and reliably storing and managing data, and they're rarely about doing this efficiently. They're merely a shortcut that some developers use to avoid learning how to use a RDMS.

twic 12y ago

The problem with SQL is it seems everyone hates its guts. It is a weird obtuse kind of "non-language" that most programmers can't stand. I'm baffled. I'm a programmer and i rather like SQL. Most programmers i know use SQL quite happily. Indeed, i often hear them wishing they could just write SQL to solve some problem rather than use some API that purports to save you from writing SQL. I certainly prefer SQL to MongoDB's query language, which is verbose and unexpressive by comparison. Am i in a tiny minority? Or is this SQL hatred a popular myth?

dickinurass 12y ago
LOL makes me want to dip my penis into your coffee
nknighthb 12y ago
I'm probably one of those programmers you think "use SQL quite happily". You're wrong. SQL is an abstraction with myriad problems in both its core design and its disjointed implementations. ORMs and similar interfaces are crappy abstractions on top of a crappy abstraction. You get the problems of SQL combined with the problems of the ORM. Preferring to remove a set of problems and be left with only the problems of SQL is not the same thing as liking SQL, it is merely preferring the lesser of two evils.
- mamcx 12y ago
  In what sense SQL is bad?
  - nknighthb 12y ago
    I see no reason to reproduce Wikipedia's laundry list: http://en.wikipedia.org/wiki/SQL#Criticism http://en.wikipedia.org/wiki/SQL#Criticism
    - jeltz 12y ago
      Wikipedia's laundry list contains of two items: * SQL not being relational algebra * SQL implementations being incompatible Both are real but I have not experienced either of these being a problem in real usage. Despite the problems caused by NULLs and that SQL allows duplicates I do not think the alternative would be better for real world programming. And the incompatibilities are avoided by picking one database per project and sticking with it.

dorfsmay 12y ago

He is right, the issue has been servers not scaling horizontally. SQL the query language is great, the concept of set theory applies really well to data. SQL can be and is used on non-SQL database, impala for example implements SQL to query data burried in hadoop. NoSQL stores become popular because they scale horizontally (able to use more than one server) naturally. Once the SQL servers can do automatic partitioning, I suspect people will start migrating back. The truth is that there is no pixie dust, at the end of the day you need to index. I see nosql proponent having the same strugles sql people have with indexing, but right now they have the upper hand because they can spread the work over several servers.

mattdeboard 12y ago
I guess this might explain some of its popularity but I think it has much more to do with the fact you don't have to do any advance planning about your data (i.e. no need to write schemata). "Rapid prototyping" is much easier when you don't have to think about the your data, its types nor the relationships between them. Of course, moving out of this phase becomes a massive headache, since basing your product on essentially unstructured data is a very good definition of "technical debt". And if you're using structured data in your rapidly prototyped object, why not just a RDBMS in the first place? :)
- vertex-four 12y ago
  > Of course, moving out of this phase becomes a massive headache, since basing your product on essentially unstructured data is a very good definition of "technical debt". Of course, it's possible to use things like JSON-Schema to validate your data if you choose to. > And if you're using structured data in your rapidly prototyped object, why not just a RDBMS in the first place? Because where's the RDBMS with a "natural" query language that is well-suited to complex, dynamic queries and document structures? How do people model a document store, with the ability to point to differently-shaped data, in an RDBMS without giving up all that safety?
  - mattdeboard 12y ago

vertex-four 12y ago

Personally, I find that SQL is hard to generate. It's hard to reason about the creation of dynamic queries, say, for some types of searches. It's not reasonable to create a generic function that can operate on any same-shaped piece of data. And it's ridiculously hard to manage the concept of a pointer to a piece of data which could be one of many types, a problem I'm sure most people have faces. Stored procedures can help with some of this, but then you've got to remember to update them every time you need to access data in a slightly different way, and it's hard to figure out when you can get rid of old stored procedures. Tools for managing them aren't up to scratch with tools for managing "real" programs. Personally, I'm keen on RethinkDB. It's a document-oriented NoSQL database which doesn't lose your data, has built-in clustering, and an extremely strong query language built on chaining method calls. While it doesn't have transactions, the query language is powerful enough that you can easily model most forms of deterministic data transformation within a single query.

bitL 12y ago

I'll just put a few points here to understand when you should use which architecture. NoSQL: - reading is fast, writing is expensive (if all data are pre-processed/denormalized for reading during the writing phase) - often schema-less - low latency (for key-value storage) - offline batch processing (classical Map Reduce) - no ACID, choose 2 of 3 in CAP - demanding on SW engineers to get client-side conflict resolution, tricky in general - Petabytes of data can be suddenly processed - huge variation of different paradigms, key-value, document, graph, batch etc. - haywire indexing SQL: - writes are fast (normalization), reads are expensive (JOINs) - ACID (well, only to some extent, clustering messes up many ACID properties unfortunately and conflicts arise in corner cases) - set operations and a neat math theory behind them - stable indexing, easily constructable real-time JOINs - OLTP - easier for developers - non-flexible schema - tradition, well-known recipes on how to do things Basically, if you want to have low-latency access, your concurrency model allows eventual consistency, or you have a need to store your data in non-standard structure such as graphs/trees, use NoSQL and pre-process all data to be exactly in the format you require for reading. If you need 99.999% guarantee of consistent data, amount of data you need to handle is under 50TB, you can put your data into a fixed schema and latency doesn't matter that much, use SQL. I would recommend you to ask yourself a question - is your app/business read-heavy or write-heavy and decide accordingly.