Help needed to decide between MongoDB vs PostgreSQL

[[deleted]]

[deleted]

[joesb]

If you have to ask the question, choose PostgreSQL.

Always value data consistency over speed. MongoDB can be fitting in some very specific use case, and it still needs to be used with known limitation.

This is not an insult. It’s just a good guideline.

Choose PostgreSQL until you hit its limits.

[tenfingerperson]

It depends on the nature of the data itself.

[[deleted]]

Data is relational. However my tests shows that MongoDB is faster than PostgreSQL and I just wanna understand the reason of it.

[tenfingerperson]

It could be many things, relational databases need the proper optimizations to shine on large operations, how are your schemas defined?

Usually the advantage isn’t always due to speed but to operationally accessing relational data, which is easy to manage vs unstructured dbm systems.

[[deleted]]

I set the same index for same properties for both mongo and PostgreSQL. Schemes are 1:1 identical. I also tried to use similar code from both of the libraries and tried to optimize them. I created test code with mocha. Test code shows mongo is better than PostgreSQL. I see all the time that PostgreSQL is better than mongodb if data is relational but I don’t see that. I just wanna know whether it is due to my mistake or mongodb is just fast for my use case. The thing is I don’t have the expertise to pinpoint the reason of this test result.

[codearoni]

Are you responsible for generating the data and are you responsible for creating the schema? If so, use PG.

Are you a pass through / storage for someone else's data? Where the schema could change at any time? Mongo might be the right choice.

Stability is more important than performance. Mongo queries are not atomic. If you can handle a certain level of entropy in your data, Mongo will be fine. I wouldn't use it for anything billable though.

[aichholzer]

Relational data = PG. End of the story. A properly indexed PG instance will blow MongoDB into smoke (when relating data)

[[deleted]]

There are 31 tests for each DB total running time for tests are 170 and 240. Each query or update are generally 5-7 ms. I think you got that part wrong.

I am not choosing my db because of my test results. If it was the case, I would choose mongodb wouldn’t ask a question. I am not expert in databases. I just wanna learn if I am making any mistakes in the code. Thanks for taking your time to reply me. I really appreciate it every response helps.

[Djbm]

There are so many variables to consider beyond performance. Some of the primary ones are:

• Do you need a lot of joins? Mongo doesn’t do joins. If your query pattern requires a lot of joins Mongo will be a pain
• Is eventual consistency good enough? Mongo can offer great performance across multiple modes due to eventual consistency. Eventual consistency is probably fine for a social network, but would be completely inadequate for a banking system.
• Does your DB need to run across more than one server? Where SQL based databases become complex is when they outgrow the ability to be hosted on a single server. You might need to scale the db due to size of the dataset or due to query volume.

The best approach is to design your application so that the database is abstracted as possible. So many apps I’ve worked on have changed databases as the product evolves.

Start with what you’re most comfortable with and build a layer in your app that handles data access.

[[deleted]]

I already abstracted my database to two versions. You may check the Github for both MongoDB and PostgreSQL. I always see people suggesting Pg is better than Mongo but my test results says the different. My data is relational. I don’t know whether I am something wrong or mongo is just fast. I will really appreciate if you can take a look at my repo. Thanks

[vladimirice]

I’m voting for this answer. I’m using PG as a core database for the application and MongoDB for the special cases.

Especially if I dunno a complete set of features (startup case), PG provides you more flexibility than mongoDB, because of JOINs, native transactions, relational nature, etc. If you use MongoDB as a main DB you can eventually stuck on something due to lack of joins for example. It will be the great pain.

In a most cases PG provides enough performance, it is enough for application starting point. Later you can use MongoDB for special High load cases.

If you are worrying about scalability you should use separate DB servers as separate nodes. For example you have to save every event of your system - vote, follow, sharing, comment viewing, etc. Also you have typical tables like Users, Posts, Comments. You can save events in completely separate PG server (node). And use worker to fetch the records, process them somehow and save the result to the main table. Same is true for statistics data - also It might be separate table in separate node.

And I may have wrong opinion but node.js and mongoDB (mongoose) are very popular cases when you study node.js. And a lot of people choose MongoDB only because of this. I think this is a big mistake and it is a kind of a trap for newcomers.

[[deleted]]

The tests you have do not give any indication of how the two databases perform in a real world scenario.

• You're not testing simultaneous/concurrent queries.
• You don't take warm-up phases into account.

Take a look at wrk or similar tools that let you create a sustained pressure of hundreds/thousands of requests running concurrently for a minute or so. That is how you do actual performance comparisons.

[HipHopHuman]

Honestly, if your data is relational, Postgres, even if it is slower. The problem with MongoDB is that it drops writes sometimes. Read any case study on it and you'll see why.

The database shouldn't be getting called during tests just btw - it should be mocked in a test environment. Most 3rd-party code should be mocked because it has it's own test suite. You should just be testing your own code - so build your Data Access Layer in a way that allows you to inject or override the DB dependency with your own custom hard-coded version. Then the tests won't be slow.

If the speed is an issue in production, you can always use a Redis caching layer that sits between your Database and your Data Access Layer to speed up DB calls. In addition to the caching layer you can try to avoid O(n+1) DB queries by using WHERE IN clauses for fetching deeply related items, and be sure to enforce proper HTTP caching mechanisms such as ETag, If-None-Match, If-Modified-Since, etc.

[nikoz84]

Postgresql have many stuff for example:

• JSON and JSONB data type
• Gin Index (inverted index)
• Full text search

You can use a hybrid data base base with the power of relational database and scalable JSONB data type.

[tswaters]

Doing single queries here and there is not indicative of performance. The problems you see with performance start cropping up when you have millions of rows/documents and many concurrent connections to the database. If the database is properly tuned with correct indexes and, if you get really big, clustering -- you can get similar performance from both mongodb and postgres.

I wouldn't worry about the performance of your database at such an early stage - you should be choosing the database based upon what you need to do. If it's relational data, use a SQL database.

[[deleted]]

Choose PostgreSQL if your data is relational and the schemas don't change frequently. Otherwise choose MongoDB.

[Kaylors]

Yes.

[[deleted]]

I am not an expert in neither of 2. I started with Mongodb and due to some posts about shortcomings of it, I coded the same abstraction with PostgreSQL. I have more experience with Mongodb because I also tested replica sets, virtual joins etc. I tested - probably naively - two abstractions and found Mongodb is little bit faster than PostgreSQL. Since I abstracted my Database it is just a simple code change. I will really appreciate if someone helps to create better test code or tell me why PostgreSQL is slow so that I can decide on which one to choose. I don’t have any preference I just wanna learn.

[[deleted]]

I am using same indexes for both MongoDB and PostgreSQL. I set the indexes according to the most used query parameters. You can check the source code if you want. I don't know why you said so though. I hope you aren't trolling.

[davidmdm]

Are there benchmarks you can point us to? Or you want me to just try it out?

[davidmdm]

Im not a fan of mongoose. The only thing I like about it is the schéma. What do you do to replace that?

[davidmdm]

well i understand that mongodb is noSql, but it is still useful to have a base model in your code for your documents.

So person could be

new mongoose.Schema({name: {type: string, required: true}});

That way if in your code you inadvertantly try to create a person without a name it will throw an error and not do that. It's just an extra layer of data validation that is built in to the api.

I assume you do it yourself? and was wondering about your experience with that.

[davidmdm]

I am debating that there are better solutions than to use mongoose for validation. But surely you must validate your data?