SQL vs. NoSQL Is the Wrong Distinction

https://www.softwareatscale.dev/p/sql-vs-nosql-is-the-wrong-distinction

by todsacerdoti • 5 years ago

108 62 5 years ago

Let’s try to distinguish these databases by strict vs. loose schema validation, instead of SQL vs. NoSQL, and things might become less confusing for the next generation of developers.

That along with denormalization is how I’ve always thought about it. I learned SQL first and I’ve always seen the queries as just different ways of expressing “give me all of the documents where x is true”.

To me, Mongo is just the one where you can accidentally have different schemas in the same table (collection), you can have lists and embedded documents in a document, and the data is already JSON. Make your data as relational or nonrelational (or both) as you want.

ako• 5 years ago

Schema on read versus schema on write?

chrismarlow9• 5 years ago

I tend to think of it as a representation of feature sets in products. I know I might want feature x or y, but I might not know how frequent the use case is or how it might fit into the scalability aspect of my schema and architecture. So the ability to slow roll something new into a well defined (SQL) schema by amending it with a JSONB field property is the perfect time/complexity trade off. Once I have a clear understanding of how a user might actually use it, and if it offers value, it becomes an official part of the schema.

There are other parts of the process to determine where a new property fits for me( for example it might actually need an entire state store if its own), but in general this is where I begin.

dvt• 5 years ago

Generally speaking, I'd argue that data should almost always have a strict schema. But often times you don't have this kind of luxury: APIs are inconsistent and you may not have the CPU cycles to spare for a transform step; you're quickly iterating on a proof of concept and aren't even sure what your final schema is going to look like; and so on.

From a practical perspective, SQL is much more idiomatic than mongoDB's confusing query "language." But, cards on the table, almost every single new project I start up, I start with MongoDB :)

rocqua• 5 years ago

Even if you want a strict schema, schema migration and updates are still nicer outside of sql.

chousuke• 5 years ago

I often hear mentions of "no schema" when people talk about NoSQL, but I think that's a lie; it's not possible for data to have no schema because an application that assumes its data has no structure can't do anything with that data. However, a schema can be dynamic.

Having a dynamic schema implies that you get no guarantees from the database, so the application has to discover and validate the schema at runtime to be able to do useful things.

I like data integrity so I prefer static schemas, but sometimes it can be useful to have a component in your tables where you do the extra work of dynamic inspection or just transport it as an opaque blob.

SPBS• 5 years ago

But these two camps of databases are slowly converging too, with JSON datatype support in MySQL and PostgreSQL, and pseudo-JOINs and transactions in MongoDB.

That's not enough to justify 'Oh I guess SQL vs NoSQL isn't about relational vs non-relational anymore'. It is 100% about relational vs non-relational. That SQL databases now support JSON is besides the point: nobody is saying to model your data as JSON documents just because SQL databases now support JSON. Similarly I don't believe that MongoDB is saying to model your data in a normalized relational fashion just because it supports 'pseudo joins' (are they even efficient?). The relational model is powerful because it stores data in a query agnostic way, and NoSQL databases are very much not that. If they were, they'd be SQL databases.

* yes, relational database != SQL. But SQL is the only widely used implementation for relation modelling, for better or worse. Show me a NoSQL databases that is built on relational modelling.

mulmen2• 5 years ago

All you need for normalization is a consistent schema. SQL DDL on an RDBMS can do that but you could just do it with OO patterns. This is what my team does.

My prediction is that the next fad in database land will be normalized NoSQL. We can call it NoNoSQL.

e: Better idea. No2SQL.

gregjor11• 5 years ago

I’m not sure you understand normalization. A consistent schema is the product of proper normalization, not the other way around.

Normalization is a logical process, part of database design. It’s not a by-product of a schema or design pattern, nor is it an implementation detail.

OO is ultimately based on pointers, even when hidden as inheritance, or called something else (references, composition). That form of organizing data is not relational pretty much by definition. You can’t” just do that [normalization] with OO patterns.” I’m not even sure what that means.

valenterry• 5 years ago

Upvoted for the No2SQL pun.

vlaaad• 5 years ago

Datomic: https://www.datomic.com/

ausbah• 5 years ago

Mongo has strong relational capabilities I believe

fulafel• 5 years ago

we could try to divide them up by relational vs. non-relational (perhaps that’s how most of us think of the distinction anyway). But these two camps of databases are slowly converging too, with JSON datatype support in MySQL and PostgreSQL, and pseudo-JOINs and transactions in MongoDB

They might be converging if you use non-relational to mean something pretty specifc (like "MongoDB and PostgreSQL JSON"). But unless we want to coin another term that means something else than the natural english meaning, it's problematic.

There are lots of databases that are not relational, but still have schema and structure in non table centric ways, like schemaful graph databases, object databases and things like Datomic and triple stores.

neochau• 5 years ago

I always prefer to SQLite3 & MySQL(>=5.7) .

gregjor11• 5 years ago

In the context of databases the terms “relational,” “schema,” and “normalized” have specific meanings. Relational databases have specific properties and guarantees that have nothing to do with SQL. Mongo doesn’t get “more relational” by supporting joins any more than Postgres gets less relational supporting JSON types.

HelloNurse• 5 years ago

And conversely leaving keys, constraints and foreign keys to goodwill and application layers makes traditional SQL-centered databases less relational, while draconian validation of JSON trees makes traditional "NoSQL" databases almost relational.

gregjor11• 5 years ago

Sure. What you describe -- leaving keys, constraints, foreign keys to goodwill and application layers -- is fairly common. I'm not sure if that comes from ignorance or a fanatical devotion to OOP. When OO runs into the relational model (the so-called object/relational impedance mismatch) OO seems to "win" in the sense the RDBMS gets turned into dumb backing store.

I think it's obvious why this particular use (or misuse) of relational databases fails to scale, or gets hard to manage across multiple clients of the database possibly written in different programming languages. I have seen the same consistency guarantees implemented in Java and Python application code when the RDBMS could be doing that, and then hearing "SQL databases" blamed for the inconsistencies and scaling problems.

smadge• 5 years ago

SQL is a dialect of a relational algebra. It assumes a relational model. It is used to query relations.

moring• 5 years ago

"Even the query language of SQL is loosely based on a relational algebra, though the operands in SQL (tables) are not exactly relations and several useful theorems about the relational algebra do not hold in the SQL counterpart (arguably to the detriment of optimisers and/or users). The SQL table model is a bag (multiset), rather than a set."

from: https://en.wikipedia.org/wiki/Relational_algebra#Implementat...

I remember how this suprised me a lot after thinking that SQL and SQL databases are based on relations, thinking "how can two equal rows co-exist in a relation". SQL needs things like "hidden identifiers / row keys" and so on to be squeezed into being "relational".

twoodfin• 5 years ago

I'd argue from practical experience that an SQL which universally enforced set-relations would introduce significant implementation overhead: DISTINCT on large relations is v. expensive!

I believe it would also add complexity on net for users, as they would have to re-introduce those "hidden identifiers / row keys" in the not-uncommon cases where bag semantics are desired.

reilly3000• 5 years ago

You can certainly query relational data without using SQL syntax i.e. datalog. You can also query documents with SQL these days. I think the distinction lies along the lines of: does the system process queries using a query solver based on relational algebra?

galaxyLogic4• 5 years ago

I think the main feature of relational databases is that

1. They store data as tables 2. The result of any query is also a table

THEREFORE you can apply queries to results of queries as long as you want to. You can compose queries out of smaller queries which makes them easier to reuse and understand. They are like LEGOs.

kqr• 5 years ago

Well, you could say that about key--value stores and document stores, too, couldn't you? Ex: 1. They store data as lists of documents, 2. The result of any query is also a list of documents.

If composition of queries is all you want, you can get it from way more things than just relational databases.

galaxyLogic4• 5 years ago

That is true. But there is more richness to tables/relations with their set-theoretic operations. What are the operations you can perform on a LIST? Well maybe the LISP like operations CAR CDR etc. But those are not very good set of DATABASE operations, like SELECT etc.

So relational means every operation produces a result to which all the same great set-theoretic database-operations are applicable. That is why it is a great basis for databases.

zbentley4• 5 years ago

Not quite; relational refers not only to the storage of relations but also (more importantly) to the ability to request operations against those relations declaratively using a set-theory-esque set of predicates (usually SQL).

That's not a pedantic distinction; RDBs are a specific concept, and discussion in this area is rife with confusion about what is/isn't "relational", so it pays to use specific words.

I'd also argue that a critical feature of many relational databases (in practice; not inherently related to the relational model but facilitated by many implementations of it) is transactional guarantees and otherwise predictable management of concurrent data access.

musingsole• 5 years ago

RDBs are a specific concept, and discussion in this area is rife with confusion about what is/isn't "relational", so it pays to use specific words.

This argument is getting a lot of are time in this thread. It might be worth considering that if you're arguing words have specific meanings...they no longer do. Databases are technology with decades of historical effort. Any specific terms you come up with will grow fuzzy as new entrants stretch and strain old ideas.

galaxyLogic4• 5 years ago

the ability to request operations against those relations declaratively

That is true but I think the main significance is not that you can "request operations ... declaratively" but that the results of those operations have the SAME FORM as the thing/relation/table they were performed on.

That is what makes relational algebra as a concept so powerful. It is like numbers. You can perform operations on numbers and you get ... numbers. You can perform operations on tables (a.k.a "relations") and you get ... tables.

foxes• 5 years ago

They are categorical duals of each other.

jstx1• 5 years ago

Isn't this pretty trivial and well-understood? I don't get the point of the post or why it's popular.

mbfg• 5 years ago

imo the main difference between databases is some databases have an address for data and some don't, meaning, when you want to go update some value, some databases say, ok, the data is there, go update it, other databases say, i don't care where it was written before, just append it here. The later has huge performance advantages if you are willing to live with the design constraints that puts on you.

zxspectrum1982• 5 years ago

There are multi-paradigm databases, e. g. Intersystems Caché, which allow you to access and manipulate the same set of data in no-SQL (MUMPS language) and SQL (and also as objects).

IIRC the open source YottaDB also has a SQL access mode, in addition to MUMPS.

iliasku• 5 years ago

There is also N1QL (https://docs.couchbase.com/server/current/getting-started/tr...) that lets you query JSON documents while using familiar SQL commands. It also supports ACID transactions (https://docs.couchbase.com/server/current/n1ql/n1ql-language...) (disclaimer: I work for Couchbase, the creator of N1QL)

ianpurton2• 5 years ago

I choose SQL every time. In fact Postgres.

I want a decent query language and I don't believe the hype about performance with NoSQL.

I certainly don't want to manage schema in code.

I was hoping the NoSQL thing would die a bit like XML. Perhaps it needs more time.

zbentley4• 5 years ago

Many NoSQL options have schema management systems as well. That's not what makes NoSQL NoSQL.

ianpurton2• 5 years ago

That won't be enough to get me off Postgres. There would have to be a real benefit to switching.

snarkypixel• 5 years ago

Instead of dividing databases by SQL vs. NoSQL, we could try to divide them up by relational vs. non-relational

Yeah, that's pretty much how I translated that whole NoSQL thing. As far as I'm concerned, when the industry uses SQL, I assume they mean relational with schemas.

thdxr• 5 years ago

Relational vs non-relation is one of the biggest misconceptions about NoSQL

Data is usually relational, there's no getting away from that just because of the database you use. NoSQL can manage all kinds of relations, just with an extremely different approach.

If you're interested in learning how this works instead of repeating the tired meme of "mongodb lol" checkout Alex Debrie's book on DynamoDB and Rick Houlihans YouTube content.

I also wrote a brief thread about this on Twitter: https://twitter.com/thdxr/status/1394903426023272452?s=19

greymalik• 5 years ago

Book link for the lazy: https://www.dynamodbbook.com/

bokwoon• 5 years ago

I've seen a very good article on this: https://www.trek10.com/blog/dynamodb-single-table-relational.... It's an insightful dive into how KV stores can model relational data. However I also feel that these 'NoSQL can do relational modelling!' proponents are kind of hijacking the term: a relational model is understood to be based on relational algebra, and DynamoDB's Everything is One Big Table design ain't it.

NoSQL can model relational data, but it is not a relational model (as understood by E.F. Codd).

Also, NoSQL data access patterns are pretty much set in stone, which is kind of what the relational model explicitly avoids.

SQL vs. NoSQL Is the Wrong Distinction

Related Stories