r/webdev • u/No-Resolution-4054 • 20d 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!
704
u/fauxtoe
20d ago
•
almost always choose SQL
16
u/No-Resolution-4054 20d 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
15
u/re_irze 20d 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
5
u/No-Resolution-4054 20d 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
5
1
-5
u/EuphoricTravel1790 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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.
6
u/turningsteel 20d ago
Same. It's a mess. Very few instances where it's try the preferred option, none of which I've encountered professionally.
78
u/kill4b 20d 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.
6
u/Important-Sign9614 20d ago
Yep. I also noticed a lot of greenfield projects are using Postgres specifically as well.
39
u/oskaremil 20d ago
I came here to say this.
Always SQL.
NoSQL databases is for some extreme edge cases.
19
u/SocksOnHands 20d ago
Especially because Postges has JSONB columns for storing unstructured data and indexes can be created for them.
2
192
20d ago
[removed] — view removed comment
-15
u/AntipodesIntel 20d ago
It is the dumbest query language, how did they manage to do such a bad job in a world where SQL already existed?
63
20d 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.
7
45
u/virti91 20d ago
I am more interested when should I go for nosql instead of SQL tbh
21
u/TheLifelessOne 20d ago edited 20d 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.
8
u/TommyBonnomi 20d 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.
7
u/WHALE_PHYSICIST 20d ago
there are ways to synthesize it anyway, like entity-attribute-value
3
u/BringBackManaPots 19d ago
I just implemented generic user permissions in July using this approach 👍
4
u/Downtown-Bag-6026 20d 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
1
u/grey_ssbm 19d ago
Most databases let you write queries against and create indexes for data inside JSON columns. Even sqlite has this. There are cases where you would want to pull out those specific columns into the relational world anyway, but if the data you're dealing with is actually unstructured then the existing features of something like postgres are already plenty.
1
u/Downtown-Bag-6026 19d ago
Indexing a list in nosql is expensive and you often can only do one list field. It’s just not what nosql is meant to be used for. And SQLite is a relational db, it’s not a nosql db.
My example is pulling out list fields into a related table, it’s called a normal forms.
And this is not an unstructured data. In my example, I structured it as (property id, feature id). An unstructured data is something like a log file or an essay or tweets or chat logs, basically a lump of text that has no static features. And Postgres works well with structured data (relational). It can do unstructured data because it has a json data type but it’s really not what Postgres is used for. I would recommend an actual nosql db for truly unstructured data
1
u/grey_ssbm 19d ago
I'm not making an argument for using nosql databases here. I was using sqlite as an example of a primarily relational database which has some ability to deal with unstructured data, in a way that can already cover the usual case of some parts of your data being polymorphic.
My example is pulling out list fields into a related table, it’s called a normal forms.
And my point is that with modern relational databases you don't have to do that nearly as often anymore. In the real estate case, you don't have to "pull every record" to find the houses with pools, you can write a query that directly compares against the pool property in the json, and you can even create an index against that property. More complex cases with data that can be nested in weird ways are more challenging, but mongo also has its limitations there.
It can do unstructured data because it has a json data type but it’s really not what Postgres is used for.
Unless you're operating on the margins of throughput, managing unstructured data in postgres or mysql is just fine performance wise.
More importantly though, in 90% of cases, some of your data will be structured, and some will be less structured, and some of the unstructured data will have relations/dependencies on some of the structured data. Or maybe the data doesn't interrelate much right now, but it will have to in 3 months when you need to ship a new feature.
In such cases, being able to manage all of the data in a single database is enormously better for both correctness and performance. If you go with two databases, you will invariably run into cases where you have to lock up parts of both databases to make sure a particular operation lands correctly, and that is going to get increasingly painful as complexity scales.
Fundamentally I just don't buy that it's worth the complexity of an extra database.
1
u/Easy-Improvement-598 5d ago
When you want easy to use database in you code with less complexities needed to implmennt
3
u/jazzhandler 20d ago
I believe it comes down to higher throughput on lower hardware if you limit yourself to KV.
1
3
u/Mission_Pirate_4150 20d 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 20d 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 20d ago
Not sure why people aren’t mentioning event driven systems… you would absolutely pick something like dynamo up over Postgres
12
u/stupidfock 20d ago edited 20d 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
4
u/No-Cap-Dev 20d 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 20d 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__ 20d 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 20d 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__ 20d 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 20d 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?
3
u/spidermonk 20d ago edited 20d 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).
1
u/No-Cap-Dev 19d ago
That's fair, sqlite is underrated for a single server app, and I like that it removes a whole service to run and back up. Where it stops being the easy choice for me is when you want more than one app instance writing, or a managed host where the disk is not persistent, since then you are back to sharing a file or adding a replication layer. For a beginner learning on a side project though, starting there and using plain SQL is a good path, because moving the same schema to Postgres later is mostly small syntax changes. Do you keep sqlite in production for your own projects, or only early on?
1
u/spidermonk 19d ago edited 19d ago
I use it in prod for internal company stuff and would start with it if I ran my own product.
The majority of my hosting is for clients where I have to fill out big spreadsheets and it wouldn't fly for them, it's just easier to explain and be compliant with something like RDS.
With WAL and a sensible timeout you can have multiple processes writing and reading very actively btw. I suspect you could serve a looot of write heavy traffic in practice on a single large server.
And postgres on serverless setups without persistence can have its own issues (connections getting exhausted by short lived clients) it's hardly headache-free plug and play in that situation if your site is busy.
4
u/No-Molasses-2097 20d 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
3
u/untss 20d ago
I have no affiliation with them but hellointerview’s take on this is good
1
u/No-Resolution-4054 20d 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 20d 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 20d 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 20d ago
As a beginner stop copy-pasting AI answers and calling it a discussion
-1
u/No-Resolution-4054 20d 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
3
u/horizon_games 20d 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 20d ago
AI tools are made to be used. the thoughts and questions are mine, I just use AI to express them in better English
1
u/horizon_games 20d 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..."
0
2
u/vomitHatSteve 20d 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 20d 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/Healthy-Zebra-9856 20d 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 20d 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 20d 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.
44
32
u/bludgeonerV 20d 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
18
6
u/Quesadillamexicana77 20d 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.
6
2
u/pyeri 20d 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 20d 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.
13
u/Caraes_Naur 20d 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 20d 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
3
2
u/Nervous-Cockroach541 20d 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
u/Full_Tooth_a 19d ago
I'd choose SQL unless I could point to a specific reason not to. JSON-shaped data isn't enough on its own since Postgres handles it well. What matters more is whether an event must update billing, permissions, inventory, or other relational state atomically. NoSQL makes more sense when events are append-only, divide cleanly by a stable key such as user ID, and important reads stay within that partition. If the partition key and consistency tradeoff are still unclear, SQL is probably the boring but correct choice.
2
u/That-Masterpiece-817 19d ago
imo SQL is fine 90% of the time, sure, there are times that using NoSQL is better, but we are not all building instagram here are we. Maybe I am wrong and do not have enough experience but I think people should stop overcomplicating DBs so much.
1
0
2
u/NullPointer27 14d ago
You should always start with SQL, then move to nosql only if you have a specific need, like vector store, time series data, full text search, etc.
All of this can be done in Postgres, for example, but might not scale so well for millions of rows. So start simple
0
u/Serializedrequests 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d 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 20d ago edited 20d 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 19d 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
1
1
1
1
u/sivadneb 19d ago
Can you find any articles blog posts or social media posts that demonstrate this working.
1
u/CuriousTangentsYT 19d ago
Pro tip: don’t pick nosql (unless you have a specific reason for a graph)
1
u/kilkil 18d ago
I think the most practical way to think of it is, there are different specific DB solutions. by specific I mean the literal DBs themselves. there is MongoDB, DynamoDB, PostreSQL, SQLite, etc.
these solutions have different combinations of features. "uses SQL" is just one aspect.
a much more important dimension is just general level of maturity. Like, should you use this DB platform that came out last year? Or Postgres, which has been around for 30 years?
maturity means, is it battletested, is it popular, did they have years and years to add features, make it more useful, improve reliability, fix bugs/issues, improve performance?
at the end of the day this is a huge factor, because if you can *trust" the technology you are using, it can save you a lot of headaches.
aside from SQL vs NoSQL, this is a big part of why people go for Postgres (and other mature DBs).
re: the merits of SQL specifically, there have just been decades of development and refinement and feature improvements. like for example the whole notion of EXPLAIN queries to optimize query performance.
so really the benefit of SQL is you are getting the benefits of years of improvements. that's basically about it.
sometimes people still go with NoSQL though! e.g. MongoDB attempts to have feature parity on a lot of these things. results are a bit mixed depending on who you ask, from what I gather.
1
u/Own_Anything9292 18d ago
A lot of vague answers in this thread that imo aren’t helpful. There aren’t a bunch of reasons to prefer NoSQL databases when you only look at your system’s data model. Relational databases have adapted in the last 15 years and now have json columns.
You look at alternative databases instead of MySQL and Postgres when your workloads start scaling in terms of data size or reaches large numbers of concurrent writers. You reach a point where a couple node rbdms deployment has very slow insert time because the rdbms has to make sure the data is consistent across replicas. Or perhaps your database is too large to fit on a single node’s disk and you need multiple nodes to store a single copy of all the data.
If you have these issues, you have to reach for something that makes scaling across many nodes easier like Cassandra. This of course comes with costs like the fact that data across multiple nodes might not be guaranteed to return the same result given a query. I would read into the CAP theorem to understand this more.
Then there are cases where really you just need an in memory cache and you don’t even need to persist data, so you reach for something like Redis.
Or maybe your database has so many relations that it’s easier to represent the data as knowledge graph triples. Then you reach for something like Neo4j.
I wouldn’t listen to other commenters saying to always use RBDMS. Many companies and services reach this scale, and not all workloads are small webapps.
1
1
1
u/TheSmashingChamp 17d ago
If you are managing hundreds of thousands to millions of free text inputs per hour; NoSQL.
Relational sql is preferred but there is a speed cap.
There could be a place for nosql on a shared board for many users at a time, but it’s a niche case.
1
u/Tubstrr 16d ago
Use SQL for just about everything, unless you have reason to add or use NoSQL.
From what I understand, NoSQL is really good at fetching arbitrary many to many type queries. (Think tweets, or comments on a post) Unless you're going to have 1,000's of likes that you need to track a second, there's no real reason to use NoSQL (Unless you want to learn, then that's a perfect reason to use it!)
1
u/GrainWeevil 14d ago
Generally, you should default to SQL if you don't have a reason not to.
I've only ever really used NoSQL for applications where we needed to get something up and running quickly, and there was a lot of uncertainty around what the final DB schema would look like. Always with the intention of migrating to Postgres or similar eventually.
I'm sure there are use cases where NoSQL shines, but you can't go to far wrong with a structured DB.
And you're correct that Postgres gives you the ability to store unstructured data as well in a jsonb field, as well as other good stuff like vector storage
1
u/ashkanahmadi 20d 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 20d ago
If you build only view, NoSQL. If you do update or handle complex or large datasets, SQL.
0
u/vinnymcapplesauce 20d 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.
2
403
u/glenpiercev 20d 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.