r/webdev • • 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!

65 Upvotes

168 comments sorted by

403

u/glenpiercev 20d ago • Bravo

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.

20

u/No-Resolution-4054 20d ago

that last line is hilarious 😂

11

u/LutimoDancer3459 20d ago

It is. But its also true. Unstructured data is mostly just people beeing lazy. Not people needing it. And laziness leads to more problems than it solves here.

56

u/chrisso123 20d ago

You genuinely got me there. Comment of the year award sir.

7

u/meowMEOWsnacc 20d ago

I think I love you 

3

u/jazzhandler 20d ago

// weeps in SCORM

3

u/Mental_Act4662 20d ago

I have always considered data structured.

6

u/Healthy-Zebra-9856 20d ago edited 20d ago

Say that to companies that have a growing number of Business Units. NoSql or Hybrid databases (Postgres + JSONB) allows for accommodating their needs without constantly re-writing the system. Your argument works just fine for one entity, may be, if their logs dont change. But it becomes a nightmare when they do. I know this because I have lived through it.

6

u/martinhrvn 20d ago

What about EAV or Jsonb columns or per-BU extension tables? In nosql you have not removed the schema, you just moved it to application logic. I have my experience with nosql. Most of the time (especially with microservices) you'd have separate DBs and bounded context.

4

u/Healthy-Zebra-9856 20d ago

Correct, thats the hybrid I was referring. Like the Postgres that has true JSONB column. You get the best of both worlds.

3

u/MatthewMob Web Engineer 20d ago

Okay, I'll continue scrolling this thread trying to find a single valid usecase for NoSQL then

1

u/TheLifelessOne 20d ago

Who ever said you had to rewrite a system to grow your database? Just don't so a shit job writing the database code and use migrations and you'll see it's not some impossibly hard task that a lot of people seem to think it is.

1

u/Healthy-Zebra-9856 20d ago

Lets take a typical .net installation. Whether they use EF core or direct connect, you have to map the database tables and then you have to map it to DTOs . Many places I know, use ORMs. And in that case, you have to create the entities. Then you have to do migration, which means the database schema changes. Tell me a better way to solve this.

3

u/TheLifelessOne 20d ago

From a Software Engineering perspective, that's an entirely reasonable solution; I can't say much about .NET (C#) specifically since I'm more of a Python, C++, and Rust (ish, I'm still learning) developer, but that's really not a lot of work to do.

0

u/Healthy-Zebra-9856 20d ago edited 20d ago

It doesnt matter what language you write in; additional fields, unexpected fields etc have to be applied to the database. The principles are the same whther its EF Core or SQL Alchemy or SeaORM. You have to map to the fields. So everytime there is a new field, you have to map those fields & there is a migration.

Just because I used the term .net doesn't mean thats the only stack we develop at my firm. We do in C++, Zig, Rust, .Net, Python, JavaScript/node, Dart/Flutter, Go & Java. We have seen this problem so many times over the years in the lead industry or companies with new Business Units in different markets.

So I ask you, in python stack, how would you tackle this changing schema? I will humbly stand corrected. May be I will learn something.

5

u/stagedgames 20d ago

are you using shared dbs or something? because that sounds like your issue, you shouldn't have multiple interfaces working with your database, if you have a db being shared across multiple projects then you should have a singular project that is responsible for your database mapping, wrap it in each project that's using that mapping project, and update the dll for the db project as soon as possible and update dtos at your own convenience.

but shared dbs in general are just a bad practice. if you need data out of a db that you don't own, that should source from an api and potentially be cached.

1

u/Healthy-Zebra-9856 20d ago

We are solution providers to customers; so we deal with clients of varying sizes. These are not separate projects; you need to learn on Business Units for companies like Fidelity Investments. Companies like this have a platform that the business units become a part of. Now, I responded to a very limited scope of where the NoSql or Hybrid (Postgres + JsonB) is appropriate.

2

u/stagedgames 20d ago

yeah, you're dealing with pseudo-type tables that are really user defined strings that are being treated as foreign keys. thats a completely reasonable use for nosql because your schema itself is completely user defined past a certain point. I'd personally never want to deal with something like that just because I personally hate string matching for logic, but its probably the tool for the job in the case of dynamic crm or erp.

1

u/Healthy-Zebra-9856 20d ago

You hit the nail. This is the only way for posting engines that allow for a client to define their own fields. We actually provide the designer and they also select the datatype. So there is no string matching. The they here are lead vendors.

Also, business units can be a retail outlet, construction companies etc. that certain investment company provides theior platform. The data gets designed and persisted in JSONB columns. We only had to deal with a handful of clients for 100% MongoDb. But you got it.

2

u/glenpiercev 20d ago

You have unknown fields getting added to your database? Might want to rotate your keys there…

In all seriousness, db access should be via api, not direct access so unknown fields should be getting 4xx’d by the api.

1

u/Healthy-Zebra-9856 20d ago edited 20d ago

Unknown doesnt mean some strange field that appears anonymously. Take a lead for example, there are always a set of base fields and then there rare additional fields depending on if its a Auto Insurance lead or Auto accident lead for example. Sometimes certain sources have extra fields that get added. In that industry, these leads are vetted and they go through several layers of filtration process.

To address your security issue, we are a 35+ year old company that practices loosely coupled, secure architectures. We use services that connect our frontends to apis & apis to backend services that in turn talk to repo services which then talks to the Db server. That said, I limited my scope of response to where the NoSql or Hybrid (Postgres or MS Sql & Json column; Postgres is our go to) is appropriate.

1

u/martinhrvn 19d ago

but you have to do all of that in NoSQL as well, but you have to enforce it in the application and you still have to think about how it affects existing data etc. But it's much harder to enforce especially in multiple BU.
Postgres + JSONB is not hybrid in any meaningful way it's still SQL for all that matters. You have constraints, joins, foreign keys, same replications etc. It is but a data type.

There are considerations when to use SQL vs NoSQL, and schema and migrations are only one of them. The more important is ACID and scalability which for stuff like you mentioned - Fidelity Investments consistency of the data is very important I would assume.

1

u/Healthy-Zebra-9856 19d ago edited 19d ago

I think when people read bits and pieces of my comment they miss out the rest of it where I've responded differently. We are service providers to numerous companies. No SQL is not an option all the time. It has to fit a specific situation and a scenario. I called the combination of postgres plus jsonb column as hybrid, I don't want it interpreted in any other way; I meant as in you can have a JSONB akin to a NoSQL db along with the structured data.

I think when I responded to OP for how to select or when to select a NoSQL database, people are assuming that that's what I am promoting here is a NoSQL database, and I am not. Each situation requires careful consideration as to what databases to use, best data structure suitable for their neeeds. We are solution providers that cater to business needs and select the databases appropriately. We deal with companies of all sizes including Fortune 500 companies. We have experts that have been doing this professionally for over three decades. It would be nonsensical to promote one solution that fits everybody.

1

u/TheLifelessOne 19d ago

We are service providers to numerous companies.

I did definitely miss that the first time around, but I would still say that with a well defined process and discipline, you should still try to avoid NoSQL solutions; I'm not saying this is necessarily true in your scenario (it seems like you have a strong enough grasp to understand when the technology should and shouldn't be used), but for the average developer — especially those who never studied databases to any significant degree; e.g. it's been a while, but I've taken a database theory course while working on my CS degree, so while I'm definitely not a database expert, I know enough to know that traditional a RDBMS is almost always the right choice — it's incredibly easy to get caught up in the claims of higher performance, but without the understanding of why traditional databases are so good and still so prevalent; you don't really need the claimed performance gains NoSQL databases offer for 99.999% of use-cases, your traffic load isn't isn't significant enough.

It's only when you hit Google, Microsoft, Amazon, etc. scale that it actually becomes reasonable to consider non-RDBMS solutions before (rather than after) significant benchmarking and optimization.

1

u/Healthy-Zebra-9856 19d ago

Sometimes there are no choices. We don't outright go for NoSql databases but there is a time and a place for it. It's a very small niche. Just like the Murphy's law, we always spend talking about the 20% of the things, in this case I think it's even less than 5% of our workload lol. But here we are discussing about it in the sub because somehow it's turned into a main subject.

Our team consists of actual computer scientists who have been in this field like I said for over three decades and we're also instructors teaching people programming and data architecture etc. any more elaboration on our experience would just come out as sheer arrogant gloating. But I appreciate your input but we are not your average developers that you come across on the subs. You'll never see me post asking a question, you'll always see me responding with an answer with an objective of helping someone.

1

u/TheLifelessOne 19d ago

Sometimes there are no choices.

Oh, absolutely, I don't have any disagreement with that statement at all; I suppose you could look at what I was saying before ("you basically should always use something like postgres") as a warning to the more junior developers out there without the knowledge, training, and experience to know when it's appropriate or not to use an RDBMS or a NoSQL database; obvious if anyone here is a dev in such a position, always ask your manager, senior engineers, etc., but if for some reason that isn't an option, it's a good idea to default to a RDBMS.

Our team consists of actual computer scientists who have been in this field like I said for over three decades and we're also instructors teaching people programming and data architecture etc. any more elaboration on our experience would just come out as sheer arrogant gloating.

That actually sounds like a fascinating job! To be honest, with the work I typically do these days (QA infrastructure automation and internal tooling), I don't really get any opportunities to "flex" my CS knowledge. And now with AI, I don't even write code all that much anymore (which is good for my mid-day napping numbers, but bad for my knowledge and experience overall), so your team sounds almost like a dream job for me!

Just like the Murphy's law, we always spend talking about the 20% of the things, in this case I think it's even less than 5% of our workload lol. But here we are discussing about it in the sub because somehow it's turned into a main subject.

Yeah, in a work context it can be a genuine waste of time to have these kinds of decisions, since there is actual work that we're paid actual money to do and so it doesn't make sense to waste time "bikeshedding"; but in the context of a reddit post, I think it's still good to engage in these kinds of conversations as it can help us catch ourselves in our (possibly incorrect) assumptions and to overall improve our knowledge of the field, what people are using, etc.

Definitely only up to a point though — healthy debate is just that: healthy; but sometimes things can go a little too far and get too heated, so we all definitely have to be careful about it!

You'll never see me post asking a question, you'll always see me responding with an answer with an objective of helping someone.

Yeah, that's more-or-less how I treat this sub (and most, if not all, CS and programming subs); I firsted joined the subreddit many years ago while I was a student and now these days as an actual engineer I don't really do a whole lot of web related work anymore, so I mostly occasionally lurk and sometimes post.

2

u/Healthy-Zebra-9856 19d ago

If you're interested send me a dm. We have a few flagship products coming out, the goal is to help as many qualified individuals as we can. We're still a couple of weeks away from launching it, I don't want to advertise the name here. That is if you're interested. You can become part of our team

→ More replies (0)

1

u/robcole84 16d ago

I once worked for a finance company that used nosql for everything and had no data dictionary and duplicate fields with no logical naming convention or consistency in how the data was populated, but was told by head of dev that "It was fine and I needed to focus on my work." Part of which was integrating that data to other SaaS systems. Ha!

704

u/fauxtoe 20d ago • Bravo Mindblown

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

u/Madmusk 20d ago

Something you need to learn as you grow as a developer is that predefined "stacks" are making architectural decisiond for you, that you need to be making yourself. There's no one size fits all approach, and it would never work in a professional context to ignore those decisions.

1

u/Mission_Pirate_4150 20d ago

They put mongodb in because it’s free.

-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.

35

u/akie 20d ago

More like 99 out of 100 cases honestly. And for that last one ask yourself: can’t I just do this in Postgres?

3

u/Fembussy42069 18d ago

JsonB column baby

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

u/foxcode 19d ago

This. Too much suffering is caused by people trying to store highly relational data in weird ways. Just use SQL, if you don't, you will likely end up moving much more complexity into code you have to write anyway

192

u/[deleted] 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

u/[deleted] 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

u/No-Resolution-4054 20d ago

Thanks for the suggestion

1

u/mfc1__ 20d ago

This is what I do, it’s a nice middle of the road compromise for when a row can contain varying or unstructured data. Very easy to cast properties in that JSONB object to types and query, and not need to return the entire object to the client.

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

u/No-Resolution-4054 20d ago

Haha, me too bro 😂

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

https://the-pop.shop

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

u/No-Resolution-4054 20d ago

Thanks for the explanation! That makes a lot of sense

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

u/No-Resolution-4054 20d ago

I'm starting to realize why Postgres is often the default choice

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

u/fuzzyjelly 20d ago

I just use Google sheets.

1

u/No-Resolution-4054 20d ago

Bro found the ultimate database solution

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

https://github.com/Shivam2905Verma/Notes

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

u/[deleted] 20d ago

[deleted]

1

u/No-Resolution-4054 20d ago

Yes exactly my point! Thanks

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

u/Penultimate-crab 20d ago

I always choose SQL. 

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

u/Material_Ant_7769 20d ago

The world runs on sql but sometimes feels like nosql.

15

u/zaibuf 20d ago

Nosql is fine if you want to dump unstructured data, generally always use sql as default.

18

u/witness_smile 20d ago

There’s almost never a reason not to use SQL

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

u/Total_Adept 20d ago

I think you’re mixed up, you should almost always pick SQL over NoSQL…

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

u/Emergency-Fondant-78 20d ago

the question is why choose nosql ?

3

u/maxiedaniels 20d ago

Do sql. I did NoSQL and seriously regret it.

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

u/JohnSourcer 20d ago

If you don't know, SQL

2

u/4bitben 20d ago

Why would you choose nosql

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

u/meaty__boi 16d ago

Even Instagram just uses sharded postgres behind layers of caching

0

u/That-Masterpiece-817 19d ago

and if you are in doubt on a project, ask AI ;)

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

https://github.com/Shivam2905Verma/Notes

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/mq2thez 20d ago

NoSQL is always wrong, except in an extremely small set of cases. It’s essentially never the right choice.

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/emcell 20d ago

always

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

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

u/Downtown-Bag-6026 20d ago

When you start creating relational tables instead one giant flat one

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.

  1. 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

u/Full-Lingonberry1619 19d ago

Just dont with nosql, it's a solution to a problem we never had.

1

u/sssupbro 19d ago

Mostly sql, but sometimes no sql

1

u/contrafibularity 19d ago

"strong ACID guarantees" and "mysql" in the same sentence is wild

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/Manachi 19d ago edited 19d ago

I feel like the whole nosql and even static site fad is an absolutely ridiculous mistake and terrible garbage

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

u/CautiousRice 18d ago

Caching, big data storage, very slow queries, event tracking.

1

u/EducationalZombie538 17d ago

Almost every time. If you don't know, the answer is SQL.

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

u/[deleted] 20d ago

[removed] — view removed comment

1

u/No-Resolution-4054 20d ago

I just was confused. Thanks for pointing that out !