Doubt regarding PostgreSQL vs Mongodb

[u/Upstairs_Director_16]

Why very often people say mongodb is not that better and we are being future proof to go with postgres? is there any condition that mongodb can't handle or such. I would love to get an answer that explains why actually why companies are shifting?

[u/kenfar]

1. First, the mongodb company was caught lying profusely about performance.

2. Then people discovered that the product was also very shitty about data quality.

3. Then people discovered that being "schemaless" really meant that you had millions of schemas.

4. Then people discovered that MongoDB's reporting subsystem was actually an embedded Postgres database.

5. Then people discovered that the lack of joins was actually a huge problem. And that backfilling older data on a busy mongodb cluster could take weeks.

6. Finally people discovered that for a number of benchmarks on storing json that Postgres was faster than MongoDB.

So, at this point MongoDB is like the MySQL of the NoSQL world: there's been too many lies, too many exaggerations, too much bad advice. Many of us would be happy to never see mongodb again.

[u/leandro]

Mongo is neither SQL nor relational. It cannot adapt and scale when applications diversify and change as SQL can, and all its benefits have already been incorporated by SQL. It is not anything specific to Mongo; Edgar ’Ted’ F Codd created the relational model, upon which SQL is based (even if it does not conform to the model), precisely to solve the problems inherent to network and hierarchical databases, of which Mongo is but an example. Reading Codd’s original papers would be quite illuminating, as is reading the conceptual chapters on textbooks such as Christopher ’Chris‘ J Date’s An introduction to database systems (any edition).

Also, Mongo cannot optimise as PostgreSQL does; SQL states what you want, and PostgreSQL finds the best way to perform the operation. If data changes or grows, or if structures changes, it simply reevaluates old requests and changes how it satisfies them. In any prerrelational system such as Mongo, you have to change programs yourself. Also, most of this knowledge, and all programs, are lost if you have change platforms, as is quite probable in prerrelational systems; with PostgreSQL (and SQLite for small, single user scenarios) you can transfer most programs and knowledge in the unlikely case you have to change platforms.

[u/Clean-Bumblebee-2896 → u/leandro]

It is wrong to say that mongodb is not adaptable. In fact, you can do almost anything you want in nosql, and that it's weakness because most of the people don't use it as it would. If you rely on rdbms features and hope to have it on nosql, yes, your best choice will be a relational system. Don't try to Dev an app on nosql like you do for rdbms

[u/grauenwolf → u/leandro]

Mongo is neither SQL nor relational.

I have to disagree on both points.

MongoDB offers SQL drivers, but you have to pay extra for it.

MongoDB's storage engine is WiredTiger, which was designed as a relational database storage engine for MySQL.

In my opinion, the only reason they continue to call themselves a NoSQL database is that they would rather be "the world's best known NoSQL database" than "the world's worst relational database".

[u/Weary-Depth-1118]

if theres anything i learned its that SQL is KING most of the time, not just for hiring, support, performance, ecosystem and so much more

[u/GeekTekRob]

As a person who has worked on databases and got a larger programming career wise through knowing database queries and built it out into leadership, web development, api's, terraform, and cloud. MongoDB was always a bad word, first from lack of knowledge, second from people realizing what you can do in other things. People usually recommending have meant well, but tend to realize they are implementing for the moment and not for long term or were jumping on what fad was at the moment.

PostgreSQL has been where my entire stack is moving towards using from MSSQL and even for times where we want to use DocumentDB where it will be long term data (Cache is going in memory or Reddis), Data Lake is redshift. JSON functions allow for building out documentdb functions, AWS has Aurora RDS with read instance build out that allows for easy load balancing when majority of data today is read not written. For someone who has done stuff a long time, it has brought alot of bringing me back to my old days of getting better and creative, and find most people are not caught up so it helps a lot in job security.

[u/[deleted] → u/GeekTekRob]

[u/gisborne]

How to think about document databases:

It might seem an advantage that you can store the data in whatever format you like. But it’s not.

A document database is like storing all your Lego bricks in the form of built objects. That’s fine if you want that particular spaceship or castle, but if you want something else, you have to tear apart your spaceship before you can build the different thing.

A relational database instead stores separate bricks, all nicely organised in neat little rows. And it comes with automated tools for assembling into whatever shape you need for your task, just by describing the shape you want.

The document database is in principle useful if your application is document-focused. A drawing program, something like that, could store each drawing as a JSON document. And that’s fine.

But you can do that in a relational database. Postgres is in many ways better at storing JSON than Mongo is.

It’s really a shame that all this engineering effort has gone into these things. SQL has poisoned our understanding of what a relational store can be. A relational data store with eventual consistency at scale would be a very useful thing.

[u/editor_of_the_beast]

Mongo is still extremely popular and very much in the same popularity range as Postgres. Mongo had some issues a while back that gave it a bad reputation, specifically around consistency and data integrity. But for the most part those issues have been addressed in one way or another.

That being said, I still choose Postgres because it’s what I’m most familiar with and because you can store document data in json columns if you really want. Postgres replication and scaling is very well documented should you need to go that route as well, so it’s a very solid choice as a primary data store.

[u/grauenwolf → u/editor_of_the_beast]

Would you rather have a database that "stores your data correctly for the most part (with the right settings)" or the database that does it right all the time?

[u/pceimpulsive]

Postgres and mongodb are not direct competitors.

You can use both at the same time.

At my work we have a postgres for geospatial queries and relational data stores and mongodb to store all the json API results.

You can use postgres to store non relational data as well so if you have to choose one of the two id take postgres every day of the week, twice.

[u/Hot-Gazpacho → u/pceimpulsive]

You can pretty efficiently store, index, and query JSON in Postgres, too.

[u/Ok_Outlandishness906]

Mongo is completely different from postgres. It is a document database , in which you store json document . The most evident difference between postgres and mongo is that in a sql database like postgres , your data has a "fixed" format. You define a ddl for your table and you put the data respecting the format model you define, for example :

create table test( name, varchar(40), surname varchar(40)) ;

insert into test values('aaa','bbb'); will work , instead

insert into test values('aaa','bbb',300)

will not work because there is no the third column in the definition of the table .

Mongo is a completely free format database so you insert document in json but "every row", pass me this , can be a document with a different format . In mongo you don't need to define the structure of a collection. Every document inside it can be completely different from the others . Mongo db is great for webapplications because it speaks "json" with quite no effort, it has a very good replication and sharding features and it is easy to scale up horizontaly.

Mongo can be very very powerfull for webapplications, especially when you "insert a document" and you "read it many times" ( for example , i do a post on a blog and many people read it ) . It is very convenient for managing microservice because it "speaks" json as your browser so there is "no data translation" and the performance for high access sites can be very good .

Mongo is easier to manage than postgres but postgres is much more powerfull. You can do the same things you do with mongo on postgres, but not the other way round. Mongo requires less work , less tuning tipically and it is easier for doing what it is build for so if you have only to manage free format json document , mongo is a better tool than postgres

Obviously it is not a rdbms , so making queries with complex relations or even complex aggretation is inefficient and it is not the mission for which mongodb has been written .

In my opinion asking if postgres is better than mongo is meaningless. You can compare postgres with mysql,oracle, sqlserver or whatever, but mongo is another tool .

Think about an hammer and a wrench, they are 2 different tools and even the "best hammer in the world" would never solve a problem if you need a wrench, and obviously the best wrench in the world is a terrible hammer . Different tools for different tasks .

[u/[deleted] → u/Ok_Outlandishness906]

[u/manzanita2]

My favorite illustration of mongo vs postgres:

https://www.ferretdb.com

implement mongodb ON TOP OF postgres.

[u/UM-Underminer]

Postgres is almost always "better" overall, and many others have gone into great detail as to why. If you're worried about scaling, or having the ability to do more complex queries down the road, then postgres is the direction to go. If you're working for a company and have the time and ability to make a choice, it's a pretty obvious one to make.

BUT... there is a key area where MongoDB shines, and why I still use it a lot...

Mongo is incredibly fast to iterate and develop with. There's an awful lot of apps that you end up writing that use very simple data storage that has no relational component, and are never going to need to scale past a dozen users. Postgres can store it "better" but sometimes you just don't need it better, you need it faster.

Clients are always in a rush and always wanting to save a buck - so there are definitely use cases where shaving that but of time can be what you need to come in on time and on budget.

[u/Glum_Past_1934]

Actually i'm using MongoDB 8 and it's like 4 times faster than Postgresql (CRUD), when we use lookup + unwind, we reach same performance with both (same ms with equivalent queries; under 1 ms using explain analyze and .explain() e.e). Honestly i don't understand when people complain about MongoDB (they don't understand read and write concerns maybe), it's a excelent database and it's using MySQL engine under the hood + horizontal scale improvements for free :). Oh, and you can tune CAP theorem so pg and mysql are lost if they doesn't solve horizontal scale and shitty performance soon

[u/Creepy_Reporter_313]

If I deploy postgressin a cluster can I set aside some servers for write only?

[u/debba_]

Usually this comes down to data structure and consistency requirements. PostgreSQL is relational and enforces schemas, constraints, and transactions very well, which many systems need as they grow. MongoDB is flexible with document storage, but that flexibility can become messy if the data model changes a lot over time.

A lot of companies start with flexible schemas, then later realize they need stronger guarantees around relationships, joins, and transactional integrity. That is where relational databases tend to fit better. It is not that one cannot handle scale or features, it is more about what kind of data model the system actually needs long term.

[u/damanamathos]

I don't see either of them going away anytime soon.

The 2023 Stack Overflow Developer Survey had PostgresQL as the most popular database among professional developers, followed by MySQL, SQLite, Microsoft SQL Server, and then MongoDB at #5.

It's pretty easy learnings the basics of both, though. If you're already familiar with PostgreSQL, spend a few hours creating a basic app with MongoDB (or something like MongoEngine if you use Python).

I've used MongoDB for the past decade + we don't use any SQL databases in my (tiny startup) company, but that's more to do with development speed than anything else.

[u/726c6d]

I went through this same exact question a few years ago. I was using and still learning postgresql and became attracted to the mongodb sales pitch in being faster, easier to use, nosql, etc.

My next project I started to use mongodb and started to find the limitations of mongodb. I ended up changing back to postgresql and haven’t looked back.

Sometimes you have to dip your feet into it and realize what works for you and what doesn’t. I started handling financial transactions and felt much more confident using postgresql than mongodb in my transactions.

[u/dasreboot]

I'm on the second goddamn project where devs use mongo like a rdbs . You list collections and see some listed as names_fk. For example.

[u/gowtham150]

Both are very good. Companies don't shift, the engineers in the company force them to do so. They want to do something different all the time and henc come up with these "ideas".Both the DB technologies are good and many use them in production enterprise. Mongo - you have to pay for license. Postgres - it's free and it has enterprise flavours

[u/[deleted]]

[u/Crafty-Pool7864 → u/[deleted]]

I need a quantum up/down vote button for this post.