loading...

Convince me to use SQL

patarapolw profile image Pacharapol Withayasakpunt ・1 min read

Let me say first that, I care most about data integrity, monitoring and maintainability, rather than performance; and I do have relational data.

There was a talk, and pretty much consensus amongst experienced and vocal people, that PostGRES is preferable to MongoDB.

However, I see this as not necessarily between RDBMS and NoSQL, but as the SQL syntax, and availability of services.

Also, there are many kinds of NoSQL and even NewSQL. I see it more as newer implementations, with or without sacrificing older (and sometimes important) features.

I don't need that many rows right now, BTW.

Open source databases are actually free to host, but hosting servers are usually not free; not to mention maintenance costs and security. (How far do I need to know DevSecOps?)

Furthermore, how much are research in embedded database currently being done? Are we pretty much stuck with SQLite? Or, are there better ones for which languages? (maybe, Neo4j or H2?)

Posted on by:

patarapolw profile

Pacharapol Withayasakpunt

@patarapolw

Currently interested in TypeScript, Vue, Kotlin and Python. Looking forward to learning DevOps, though.

Discussion

markdown guide
 

NoSql databases have their place, but it's not a replacement for relational databases. A document db such as MongoDb can't replace and shouldn't be used as a replacement for a relational database such as Oracle, PostgreSql or SQL Server.

They have different capabilities and different purposes - different strength and weaknesses also.

If your data is relational, hard structured, a relational database would be my choice.

If your data has a more fluid structure - A document db might be a better fit.

 

You can still easily do relational data in NoSQL. The difference is for relational data in NoSQL you need to know ahead of time the specific access pattern in order to model the relationship. Whereas SQL you normalize all your data and you can access it anyway you want and add new access patterns with a breeze. However, at scale NoSQL will outperform SQL any day because you model your data for those specific access patterns making it super efficient compared to doing all of these joins across tables.

In short, have known and consistent access patterns? Use NoSQL. Otherwise use SQL.

 

have known and consistent access patterns

It would be impossible NOT to be known and consistent, actually.

I believe denormalized database is fast, but how much can I trust aggregation framework? Especially on the $lookup part.

The real problem that cannot be denormalized here is -- MongoDB cannot have nested objects that are passed by reference. (I saw closed issue on this somewhere.)

It's not impossible to have known and consistent access patterns. There are many apps that can have known patterns, even with NoSQL it's not impossible to add new access patterns just a little bit harder.

I also do not use $lookup I do not use MongoDB at all. I use DynamoDB mainly.

 

OP wrote "I care most about data integrity, monitoring and maintainability, rather than performance;" - IMHO, a well designed relational database makes it easy to enforce data integrity, and with some carefully planed indexes, should have no performance problems.

I'm no expert when it comes to NoSql but I have been working with MongoDb for a couple of years (thought admittedly, not a very intensive work) and I've yet to encounter anything it can do faster than SQL Server - but that might be because of a lack of MongoDb knowledge on my part.

My point is, for a hard structured, relational data, I see no reason not to work with a relational database. Query speed optimizations can be made when needed, even at the cost of denormalized (flatten) data - yet still keeping the data integrity.

Sure maybe what OP wants in this specific case is SQL I wont argue that side. I also said at scale NoSQL is faster. SQL is faster when you are working with small datasets. However, once you have an application that is at scale, like say the company I'm working with right now that has 100 million records SQL is no longer performant. However, after being switch to NoSQL DynamoDB every single access pattern they have can be queried in under 40ms every single time.

 

Nosql is great for many uses, perfect for storing things like API responses where you have no control over data versioning, for instance. But there is typically a dependence on the app to do all data validation, which makes sharing a db between apps a matter of trusting that each will enforce data integrity rules, if there are any. Also, reporting is a pain with nosql and interactive browsing and summarization of data with simpler queries and spreadsheets is much harder in nosql

 
 

I know. And MongoDB is not.

Actually, I am considering Neo4j. Should be stable and well-developed enough. However, embedded is in Java only.

 

I thought MongoDB was ACID-compliant now mongodb.com/. "Distributed multi-document ACID transactions with snapshot isolation."

Am I misunderstanding some ACID requirements?

Mongo has been ACID compliant since v4

So does Couchbase and it has an amazing SQL variant for it's query language

 

my stance is that if you understand nosql (my choice is mongodb) enough, you can develop apps at least as performant as sql, if not more performant. I find mongodb query language easier to understand than some sql queries. that being said your app should enforce some data entity schema to make apps more maintainable. just because you can go schema free in nosql doesn't mean you should imho. I've only been working with mongodb for about a year and my life has never been easier. I don't think I'll ever start a new project with sql again. I've begun migrating all of my personal projects to mongo also.

 

Interesting topic. I just started exploring mongodb with my new project. I have been using SQL previously.

What's most amazing about mongodb in my experience is how fluid it is like previously mentioned. I think this is what makes it much more scalable in comparison to SQL. However, joins and data validation are an obvious issue.

I am looking for performance and so, wouldn't mind personally validating the data with an api. Therefore, mongodb is my goto db especially for the fact that my project is a start-up and the data is highly likely to change overtime.

 

Why not have the best of both worlds?

Couchbase, a NoSQL database that used N1QL, a SQL variant. Yes you can index and query this NoSQL database with a query language that meets the SQL++ standard. What you know about SQL it's mostly all transferrable to working with Couchbase.

As well it's the most scalable of all of the document databases. Please check it out if you have the time!

 

It seems that an SQL co-creator joined Couchbase -- datanami.com/2017/11/01/sqls-co-cr...

However, as it has pricing after one month, it is hard to say OK, unless I am very committed.

 

I prefer NoSQL as well because BSON is a lot like JSON and for me learning mongoDB was a lot easier than mySQL and postgresql.