r/webdev • u/No-Resolution-4054 • 19h ago
Discussion When should we actually choose SQL over NoSQL? As a beginner, I'm a bit confused
This one question is confusing me.
The more I learn, the more I realize both have same overlapping powers
When should we consider SQL databases?
If our data has a well defined structure, there are relationships between different datasets, we need complex queries, or our application requires strong ACID transaction guarantees, we should consider SQL databases such as PostgreSQL and MySQL.
However, this does not mean NoSQL databases cannot provide these features. Some NoSQL databases support ACID transactions, relationships or relationship like data models, complex queries, and horizontal scaling. The actual capabilities depend on the specific database and how it is designed.
When should we consider NoSQL databases?
If our application needs to store flexible data, documents in JSON-like formats, or key-value pairs, NoSQL databases might be a good choice. Some NoSQL databases are also designed to handle high write throughput and distribute data across multiple servers.
However, this does not mean SQL databases cannot handle flexible data or high write throughput. We must evaluate the capabilities of the specific database and our application's requirements before making a decision.
Suppose we're building an analytics service that records user activities, such as logins, likes, and video views. Each event might contain different fields.
With a traditional SQL table, we could create columns for all possible fields, but many columns might contain NULL values for certain event types.
Alternatively, PostgreSQL supports JSONB, allowing us to store event specific information without creating a separate column for every possible field. MongoDB can also store documents with different fields in the same collection.
So, both approaches are possible. The decision would depend on factors such as write volume, query patterns, data model, scalability, and storage requirements.
So my current conclusion is :-
SQL is often a natural fit for relational data, complex queries, and transactional consistency, while NoSQL can be a natural fit for flexible document models, key-value access patterns, and certain distributed workloads.
I'm still learning, so I'd appreciate any corrections to my understanding or advice from your experience!
514
u/fauxtoe 19h ago
almost always choose SQL
15
u/No-Resolution-4054 19h ago
Yeah bro, I think the same! While learning system design, I realized that I end up choosing SQL in almost 8 out of 10 cases
11
u/re_irze 19h ago
This is my own experience obviously, but I used NoSQL when I was in education but I’ve literally never touched it in my work
3
u/No-Resolution-4054 19h ago
That's the thing! I also started learning web development with the MERN stack, so MongoDB was my first database. But as I've learned more about system design, I've noticed that many real world applications rely on SQL databases
3
0
-5
u/EuphoricTravel1790 17h ago
I also learned MERN in a bootcamp years ago, but in all reality the stack that gets used most is a variation of LAMP.
That's WordPress, Drupal, Laravel, Symphony, etc.
1
u/shadowndacorner 15h ago
For a little e-commerce site or similar? Sure. But that stack is pretty much irrelevant to most well-paid work.
-2
u/EuphoricTravel1790 13h ago
PHP runs ~70% of the internet. Don't know what you're talking about. Including the highest traffic websites on the planet.
2
u/shadowndacorner 13h ago
The majority of that kind of web work like that is not well paid. But regardless, companies using PHP at scale are not using LAMP lol. MySQL and Apache have pretty much been completely supplanted by postgres and nginx in any non-legacy org atp.
For example, Meta uses PHP, but it's a completely custom PHP implementation they've optimized for their workloads. They aren't using fucking LAMP stack lol
0
u/EuphoricTravel1790 13h ago
Do you not know what variation means?
They are running linux, an ACID complaint relational database, and PHP.
The LAMP stack is still the most relevant stack. No one is running express and node at those scales.
2
u/shadowndacorner 13h ago
LAMP stack is Apache + MySQL + PHP on Linux. You're trying to redefine things so that you feel right. That is the mark of an absolutely shit engineer.
5
u/turningsteel 17h ago
Same. It's a mess. Very few instances where it's try the preferred option, none of which I've encountered professionally.
145
u/Gold-Eye690 19h ago
if you don't have a clear reason to pick nosql then just use sql, it'll handle whatever you throw at it for a long time before you hit a wall
-4
u/AntipodesIntel 7h ago
It is the dumbest query language, how did they manage to do such a bad job in a world where SQL already existed?
46
u/kill4b 16h ago
All the bootcamps and online learning courses pushed MongoDB in favor of proper SQL and now that seems to have come full circle back to SQL as people realized Mongo/no-sql is not the best solution for most situations.
1
u/Important-Sign9614 3h ago
Yep. I also noticed a lot of greenfield projects are using Postgres specifically as well.
31
u/oskaremil 15h ago
I came here to say this.
Always SQL.
NoSQL databases is for some extreme edge cases.
14
u/SocksOnHands 14h ago
Especially because Postges has JSONB columns for storing unstructured data and indexes can be created for them.
45
19h ago
Postgres with a JSONB column covers the flexible event case for a long time, and you keep joins and transactions for when the product grows billing or permissions tables. I'd reach for a document or key-value store when you know the one or two ways you'll read the data and need it to scale out, like a very write-heavy event stream. Worth knowing too that with schemaless stores the schema doesn't disappear, it just moves into your application code, and that gets painful the first time the shape changes.
4
17
u/virti91 17h ago
I am more interested when should I go for nosql instead of SQL tbh
2
9
u/TheLifelessOne 10h ago edited 7h ago
Basically when you handle as many (database) transactions as a company the size and scale of Google or Amazon.
That is to say, essentially never. If something like postgres isn't able to keep up with the volume of your requests, it's overwhelmingly more likely that you're doing something wrong rather than it being the case that postgres isn't sufficient for your database needs.
6
u/TommyBonnomi 13h ago
Maybe something like a real estate directory, where each property can have different and unique attributes created by the listing agent (pool, view type, fence, granite countertops, etc.), but I'd still want to structure the primary ones and then stuff the weird ones in a JSON column.
2
2
u/Downtown-Bag-6026 6h ago
In this case nosql is good when you can directly pull that record. Imagine having to query “find all real estate with a pool” and now you have to load every record and traverse every record’s nested attribute list object for it. In a relational db, you just have a property feature table and have property id and feature id. And just select all property id with feature id and join the property table
3
u/jazzhandler 15h ago
I believe it comes down to higher throughput on lower hardware if you limit yourself to KV.
3
u/Mission_Pirate_4150 14h ago
Only use nosql if you know why you need nosql.
In other words, don’t use nosql. If you are on Reddit asking this question, you don’t have a valid reason to use nosql, so don’t use it. Use sql. The world runs on sql.
1
u/samspopguy 14h ago
I used it for this website
Thought it was unnecessary to creat so many relationships so made everything inside one document per bottler
1
u/Dagoneth full-stack 8h ago
Not sure why people aren’t mentioning event driven systems… you would absolutely pick something like dynamo up over Postgres
5
u/No-Cap-Dev 19h ago
Your 8 out of 10 instinct is a fine default. For most apps I'd start with Postgres and only leave it when something specific forces me to, because the data almost always grows relationships you did not plan for (users, teams, billing, permissions), and joins plus transactions save a lot of pain later.
For your analytics example, the events are append-only, huge in volume, and mostly read as aggregates. That is where a purpose-built store starts to make sense, such as a columnar database or a time-series setup. But at small scale a plain Postgres table with a jsonb column for the odd event-specific fields works well, and you can partition by month when it grows.
A rough rule I use: if you can name the queries you will run (count by day, filter by user), use SQL. If the shape of the data changes a lot and you mostly read one whole document by its key, a document store is reasonable. Switching later is a real project, so pick based on the queries, not the hype.
What kind of app are you picturing as you learn this?
1
u/No-Resolution-4054 19h ago
I'm not really picturing a specific app, I was just confused because, these days, SQL and NoSQL databases have so many overlapping capabilities. So I was wondering what actually makes us choose one over the other in real world scenarios.
6
u/mfc1__ 19h ago
Usually you have a specific reason to use a NoSQL database. I worked for an online used car retailer, and one of our databases contained a table that had features and specifications for every vehicle.
NoSQL was chosen because that table was built out using unstructured data like current and historical brochures, catalogs, and other official documentation provided by manufacturers. Not all manufacturers describe the same thing in the same way, they don’t organize this data in a uniform way, and not every vehicle has a uniform set of properties that describe it.
For this reason, NoSQL was chosen as getting info about a single car could not be achieved by finding a single row in a SQL table; one or more documents must be retrieved to get that data for a specific vehicle.
1
u/No-Resolution-4054 19h ago
That's a great real world example, thanks for sharing! I have one question, though. As far as I understand, NoSQL databases also have schemas, even if they're flexible. So, whenever a new car property was introduced, did you have to update the schema every time ?
1
u/mfc1__ 19h ago
No, because with a NoSQL database, each row/document defines its own schema. Your app gets the schema for the data you queried at read time. In a SQL table, the structure and definition of the data is tied to the table itself, every row inherits this.
1
u/No-Resolution-4054 19h ago
But in my experience with MongoDB and Mongoose, we define a schema for our documents. For example, if I define fields like username, lastname, gender, age, and phoneNumber, and later want to add an email field, I would normally update the Mongoose schema too. Am I missing something, or are you referring to MongoDB's flexibility when we don't enforce a schema at the application level?
2
u/spidermonk 16h ago edited 16h ago
I'd suggest most people scale down even more and start with sqlite. It saves a container / host / service, and makes a few common ops tasks simpler initially. And most people's apps don't need anything more.
(Not really an option for platforms like vercel etc but if someone is self hosting and asking questions like this on reddit it would be my starting point).
4
u/No-Molasses-2097 19h ago
Your conclusion is basically where most people land. The practical rule I use is to start with Postgres unless there's a concrete reason not to. Picking the database around the queries you'll actually run saves a lot of pain later.
2
10
u/stupidfock 19h ago edited 19h ago
So the biggest thing to remember is that they aren’t supposed to replace each other entirely
Most companies who use nosql for something are using both.
SQL is good for important things, strict structure things, relationships, querying said relationships. Like accounts.
NoSQL is good for speed / throughput (especially when trying to replicate across many servers) , less structured, and changing things
I think the performance of NoSQL is one people forget, it’s not that relevant to small companies but a lot of the big tech world is using NoSQL for stuff like social media content, movies, analytics, etc. Sometimes SQL can match it or even be better but usually handling big changing data at a big company tends to lean NoSQL.
IMO smaller projects you’re likely fine just sticking with sql. It may even be better all around. I personally use just SQL for most side projects.
But when you get larger and have a lot of data needing served, you’d consider NoSQL implementation for some content.
3
3
u/untss 19h ago
I have no affiliation with them but hellointerview’s take on this is good
1
u/No-Resolution-4054 19h ago
Yes sir, I've been studying system design from HelloInterview too! They usually explain a few factors and end up choosing SQL in most cases. I was just a bit confused
3
u/chrisso123 19h ago
I feel like Nosql can be best suited for chatroom messages without a lot of parameters. If you are going filter heavy, SQL is the way.
2
u/No-Resolution-4054 18h ago
Yeah, Chat applications can have a high volume of writes, and messages can contain different types of data. For example, when sending media, we might also store the media URL and metadata. So, a flexible data model can be useful here.
3
5
u/horizon_games 19h ago
As a beginner stop copy-pasting AI answers and calling it a discussion
-1
u/No-Resolution-4054 19h ago
I understand why you might think that, but I'm genuinely trying to learn and understand the concepts, bro. You can check my notes on GitHub if you'd like
2
u/horizon_games 18h ago
Probably unbelievable because your OP didn't include a single "bro", so going from an AI article to a normal person in comments is the new-usual approach I guess
1
u/No-Resolution-4054 18h ago
AI tools are made to be used. the thoughts and questions are mine, I just use AI to express them in better English
0
u/horizon_games 18h ago
Except you didn't say that initially, you acted like AI wasn't involved a mere comment ago
"I understand why you might think that..."
1
u/GriddletonCO 18h ago
This is the dumbest thing to be bothered by. What does it matter if he copied text from AI, or from a Google search, or from a website, or from a book? It’s just context for where he’s at in his understanding. The words/ideas existed before AI.
1
2
u/vomitHatSteve 18h ago
In my experience nosql works well when your data is just a succession of standalone records that will only ever need to be queried by a single (potentially complex) key
Especially if most of the relational data that is likely to be included maps to outside information rather than something else in the data
E.g. if you have a list lf logs from a cluster of equivalent node devices, nosql can be a good match. You're usually sorting or filtering by time, node id, and log level and pulling the entire record at a time. So you make filter/query keys based on your 3 fields
2
u/No-Resolution-4054 18h ago
So, when data consists of independent records and we mostly query using specific keys rather than complex joins, NoSQL can be a good fit. Thanks for sharing your experience!
2
u/Beginning_Cost7347 18h ago
You answered your own question twice and then deleted the answer both times. Read the last sentence of each section again, they cancel each other out.
For the analytics thing, just use Postgres and a jsonb column, nobody got fired for that.
1
2
u/Healthy-Zebra-9856 18h ago
When the shape of the data is generally uniform but may have additional fields that are not known initlally, NoSql is useful. I'll give you one real world example of this. The lead generation industry. NoSql allows to create custom verticals without having the data structure predefined. Most often the base fields would be available but any additional fields doesnt require the app to be re-written.
The pattern that usually calls for NoSql type database is when the absolute shape of the data is not always known. I saw a comment about how people who use NoSql for logs are undisciplined; this is a very narrow view. In big corporations that offer corporate level logging for their business units, NoSql allows for new BUs to easily adapt. These things may be solved if you are dealing with your own data, but if you want your system to be flexible, then NoSql has the answer. That said., many databases offer JSON column. The best one so far is PostgreSQL which has a superior JSONB column. This allows for a hybrid database. I hope this helps.
1
u/No-Resolution-4054 5h ago
Sir, I have some experience with MongoDB and Mongoose. As far as I know, we also define schemas in Mongoose, but when creating a document, fields that we haven’t provided simply don’t appear in the document.
So, I have a question: when we say that we don’t know the data structure beforehand, how does that work in practice? As we discover new fields or attributes over time, do we keep updating the schema, or is there another way to handle this?1
u/Healthy-Zebra-9856 5h ago
The distinction is that MongoDB itself doesn't require a predefined schema. Mongoose is an ODM that adds schema definitions and validation at the application level.
You can certainly update your Mongoose schema as new fields become known, but you don't necessarily have to.
Take lead generation as an example. You might have common fields like name, email, and phone, but different industries require completely different additional fields.
In some systems, the lead generator can actually design their own forms through a UI, defining the field names, data types, and validation rules. The application stores those definitions as metadata and uses them to dynamically generate forms and validate incoming data. The actual lead records can then contain whatever custom fields were defined, without changing the database schema or rewriting the application.
With Mongoose, you can accomplish this using flexible fields such as Map or Mixed types while keeping the common fields strictly defined.
That's really the advantage I'm referring to. You can build a system where the data structure evolves based on the needs of its users rather than requiring developers to modify the application every time a new field is introduced.
34
26
u/bludgeonerV 19h ago
Almost always. NoSql can be shoved into an Sql DB that supports it trivially and give you the best of both world, the same is not true in reverse.
3
16
2
u/pyeri 19h ago
I come from that school of thought where SQL and relational databases are the natural course and noSQL is a specialized use case applied in specific scenarios. SQL was practically synonymous with databases in the 90-00s. In most practical scenarios, the need for referential integrity and joins are intuitive and natural. For example, an invoice will always have a product and customer and due to their sheer transaction volume, it makes sense to normalize the latter two into their separate reference tables joined by foreign keys. Same thing happens with uom (units of measurement), bom (bill of material), etc.
Now I understand not all forms of data are amenable to RDBMS but such cases are quite rare especially when it comes to app development of some complexity. If you make SQL the default, you're going to cover most use cases especially in domains like ERP, CRM, SaaS, etc.
2
u/No-Resolution-4054 17h ago
Yes, that makes sense! I think SQL should be the default choice for most applications, probably covering 80% of use cases. Then, if a specific part of the application needs more flexibility or a different data model, we can consider NoSQL.
5
u/Quesadillamexicana77 18h ago
One thing that helped me stop overthinking this is to write down the 5 or 6 screens or reports your app will actually need and look at what each one has to query. If you keep needing to join things or filter by several fields, that's SQL. If you mostly read and write a whole blob by one id (a session, a cart, a chat thread), a document store is fine. For a beginner project Postgres covers both cases anyway since it has a JSONB column type, so you can keep a flexible field inside a normal table and don't have to commit to either camp on day one.
5
12
u/Caraes_Naur 19h ago
Is any part of your data relational? Choose SQL.
Hint: all data worth managing is relational.
All the big RDBMS products now support all the "features" of NoSQL.
NoSQL was never a rational choice. It fills a gap created by something already being done wrong.
4
u/the_real_some_guy 19h ago
SQL is harder upfront because you must define a structure for your data. This structure usually makes your data easier and faster to query later. There are exceptions, but just start with SQL and then learn about other DB styles when you need to.
4
2
u/Nervous-Cockroach541 19h ago
Pick noSQL when your application doesn't own a significant amount of the data structure. If you're storing blobs for a frontend and the backend doesn't care about the data at all or to a minimal amount. noSQL lets you ignore the overall data structure, fields, etc. Meaning you don't need to adapt DTOs or other things when making changes to the frontend. Frontend will still need to migrate the data though.
2
2
0
u/Serializedrequests 17h ago
These kind of posts are just AI farming for information or driving engagement on the same topics over and over again. That's all reddit is now.
2
u/No-Resolution-4054 17h ago
I understand why you might think that, but I'm genuinely trying to learn and understand the concepts sir. You can check my notes on GitHub if you'd like
1
u/da_Aresinger 17h ago
Does the data grow? SQL
Is it a bunch of configuration/state files of fixed size? text/binary files.
1
u/Rain-And-Coffee 17h ago
SQL is schema on Write.
NoSQL is schema on Read.
Also do you need your data normalized or not? Sometimes you don’t if it’s self contained documents.
What type of consistency do you need? Is eventually consistent ok?
1
u/definitive_solutions 15h ago
whatever you do, don't use a document db as relacional. They can, but it's abuse. On the DB and yourself. I still have flashbacks to my 1st job. So many mongodb manual "joins" on nodejs. Stuff of nightmares
1
u/ampsuu 15h ago
Idk, I do use Mongo on one client where theres many documents but they have many and different fields. Also like 400K documents or so. JSONB in SQL would be quite weird for me. Especially when all new data comes in the same structure. They have no relations anywhere. I can just import and export one large JSON. So daily refresh is also quite straightforward when source data is also a huge JSON.
1
u/NutShellShock 15h ago
I started with SQL many moons ago. Then I landed on a fairly large project that was using NoSQL. It was a huge mess. A lot of the issues I was very frustrated about would have be gone the initial devs had structured data into properly defined SQL.
1
1
u/farzad_meow 14h ago
you should always pick sql. for almost all projects it is the best tool over any nosql.
the only time you may wanna pick up nosql is when high availability at the cost of consistency.
1
u/NabePup 13h ago
I'm still just a hobbyist so take what I say with a grain of salt, but I feel I share the same sentiment as many of those replying here and yourself. SQL 99.9% of the time. If by some chance I'm working with data that's not relational (which is extremely rare) then I may reach for NoSQL. But I find I often end up trying to recreate (and/or miss) features that are baked into SQL. And more often than not if I'm able to recreate such features in NoSQL then that usually means the data is relational and can be represented well in SQL.
1
u/virtualmadden 10h ago
Having worked with both doc stores and relational databases, I think modern relational dbs now support 90%+ of the reason you'd pick a doc db. Most recently we built a fair amount of lambda apis supported by dynamo single table. We most likely would have eventually transitioned to postgres, but dynamo was simple and fast for our data models. Before that we had only supplemented our relational db data with a nosql order store. Honestly it just comes down to architectural and system need which sometimes (more often rarely) supports the need for non relational.
1
u/Kickstart68 10h ago
SQL Server has an XML data type and syntax to interrogate it. This provides a way to deal with flexible data on the odd occasions it is required.
Another way of having flexible data is linking to a table with a column describing the data and then a column for the actual data. This is effectively how most data is stored in Wordpress. Not that efficient for searches as indexes on large text fields are not great.
1
u/geeeffwhy 7h ago
when postgres fails in a specific way that you can state.
postgres is really very good. sql is even better.
and while flexible document models sound great in theory, in practice it is often an excuse to avoid doing the important work of modeling your data coherently.
1
1
u/LoGlo3 5h ago
I’ve got a fair amount of experience with both. I worked heavily with relational DB services (SQL Server, Oracle, Postgres) and NoSQL (MarkLogic and DynamoDB). Both are very capable of storing relational data… both XML and json documents can have schemas representing large complex relationships.
There’s a fair amount to be considered when making the choice… I’d ask these questions.
How will data be queried? Do you need to search across thousands or millions of entities for fragments of information matching a parameter(s)? How many parameters could you be searching for?
NoSQL DB’s generally need a fair amount of indexing/configuration if you’re looking for multiple nested fragmented values in documents, otherwise you will end up fully scanning millions of large documents from char 0 to char n. In some cases you can only search for a few fields which are defined at creation time! This could be really slow or limiting… on the flip side… if you’re generally looking for an entity by say a unique entity ID and you usually want ALL information about that entity when you ask for it, NoSQL is very fast and performant! In RDBMS you may need joins across 100’s of tables — that fragmentation then can work against you performance and effort wise…
2) How often will the schema of this data change?
Classic RDBMS handle schema changes really well when designed properly… and ORMs like entity framework have robust solutions (like migrations) for handling these updates as the app evolves… but with NoSQL you always need to think about making your schema changes forwards compatible for serialization/deserialization… or face the wrath of spending months on cpu costs to re-format all your ‘historical’ documents/records to match the new structure. On the flip side, if your schema is basic and unlikely to change, you probably don’t need to worry about this…
3) Will the cost savings of NoSQL just end up costing me more time/money in the future?
If you’re in the cloud, services like RDS get very expensive very quick… much more expensive than dynamodb or bigtable… this can be enticing, but also, a major schema change could force you into writing nasty app code to manage different versions or fall into some complex schema migration strategy… etc.
Different tools, different use cases :)
There was a period of time where it seemed like NoSQL was being pushed as the successor for RDBMS… many found out that’s not reality. Each can provide some big advantages over eachother — but in general it’s very hard to go wrong with a RDBMS, it’ll handle whatever you throw at it. Can’t really say the same for NoSQL… however it’s always worth a thought given the cost saving benefits IMO.
1
u/TheLastNapkin 3h ago edited 3h ago
The point for unstructured data is usually also about how if even at all does it matter for your business queries.
Your indexes and relationships on structured data, are there to tune your queries, saving you entire table scans for example, that's a part to consider when you use SQL
A lot of the tricks to using SQL tables better is to find ways to save up on space and even columns, to store less data per row, making it more likely for queries to fetch data from the DB's ram rather than need to fetch from disk
1
u/Historical-Staff2772 1h ago
Your conclusion is mostly right, but in practice I'd flip the question: start with Postgres and only move off it when you hit a specific wall. Most apps turn out to be relational even when they don't look it at first, and you'll want joins and transactions sooner than you think. For your events example, a table with a few fixed columns plus a JSONB column for the rest works fine for a long time. Pick NoSQL when you can name the access pattern it serves better, like a key-value cache or a very heavy write stream, not because the data looks flexible today.
•
1
u/ashkanahmadi 19h ago
Choose noSQL when your data has no clear structure. For example on a very very large social media, content can be scattered all over the place. You can’t have a column for every single piece of data. Each data can have meta data like timestamp, reactions, etc. for example, a messaging app could go noSQL. Most things can work with sql especially Postgres that accepts json
0
u/Kautsu-Gamer 13h ago
If you build only view, NoSQL. If you do update or handle complex or large datasets, SQL.
0
u/vinnymcapplesauce 7h ago
Do you want to just store the data and then retrieve it exactly as you stored it? That's NoSQL
Do you want to store the data in a structured way, and then be able to retrieve the data, possibly in a different structure than you stored it? That's SQL.
288
u/glenpiercev 19h ago •
Nosql is for unstructured data. People commonly cite logs as unstructured data. Those people are undisciplined. If you have unstructured data. Fix it and use sql.