5 reasons why i'm using PostgreSQL instead of MongoDB
Vložit
- čas přidán 24. 07. 2024
- I've been using MongoDB as my go to database for side projects for many years. I've now changed that and when i need a database now, i reach for PostgreSQL. In this video i go through 5 reasons why did the switch to PostgreSQL.
Links to resources mentioned in the video:
* MongoDB vs PostgreSQL benchmarks: • MongoDB vs. Postgres B...
* Graph query in Postgres: www.alibabacloud.com/blog/pos...
Chapters:
00:19 1. One version you rule it all!
00:45 2. Structured query language
01:28 3. Modern features (lots to explore)
02:22 4. Point in time recovery
03:19 5. Great performance
1. One version you rule it all
2. Structured query language
3. Modern features (like json, geo queries, graph lookups)
4. Point in time recovery (rollback)
5. Great performance
Thanks, i didnt mean to hide the point in the video. Chapters have now been added to the description for easier navigation (or skip to another video if they aren't interesting).
My last job was using Postgre json b for everything, so flexible, and cuts down on so much time mapping everything out to fields etc.
Did they used postgre for big files such as videos, high quality images?
Postgres can also do realtime stuff using the LISTEN and NOTIFY functionality
Good point! A Mongo application using change streams can still migrate to postgres without loosing that ability.
lot's of things i didn't know about like PostgreSQL's point in time recovery. and i didn't know MongoDB had a separate crippled version
Yeah, the point in time recovery is not that complex to work with either. It seems much more involved than it actually is.
Yes, it's called community. And the community version have a weak selfhosted backup story. Atleast last time I checked. I'm nog picking on Mongo, other "community" version databases usually also have this limitation, deep down in their docs. Which makes them not usable in production environments.
Now I sound like I don't want database vendors to make money 😅 that's not what I mean. Of course companies should charge for their products. I'm just saying postgres is good enough/better in most cases 😇
I am in dillema... Want to create something but don't know which one is better for database... Sql or mongodb? 🤔
I'm not a fan of mongodb (anymore), so I vote postgres any day of the week 😎 👌
Didnt know about those displaying as JSON features on postgres, thats really cool, ill def. take a look into that
Yeah it works, i had to struggle a bit to get the names of the json props that i wanted, but it worked out in the end. And the SQL in applications aren't anything you write live anyway, you have time to fiddle with it and get it to do what you want before you commit it, which makes it a non issue in my mind.
Thanks for sharing bro 🙌
Thanks for watching.
Am I crazy in noting that certain features of postgres are really annoying? Such as not being able to do a select all except column query or the fact that default value functions can't reference another row. I haven't read anyone pointing this out yet, but it seems to be one thing that mongodb really does better. That being said, they're both pretty fantastic, though I'm kind of hoping surrealdb takes off.
Hello! Where can I learn postgres well? All courses on udemy seem very basic and introductory. For example you converting normal rows into json sounds so cool. I always use postgres but I am not good at it. Is there a course you could recommend?
I don't know. The docs are good. I dont consider myself good at postgres either, I'm very basic. Just learning new stuff as new challenges comes up. as for courses, I don't belive in them very much, especially not if you already have been using something for a long time, as it would most likely be lots of repetition for you.
Do we have something like atlas in postgres?
How to you migrate database from one server to other?
You can try supabase, they had a free starter plan. I don't use managed postgres. I migrate via wal shipping.
@@lindblomdev Cool
I haven't used it myself for anything, but people seem to like it. I do believe that you cannot do continuous backups on the free tier, but you don't get that with atlas either 🙃
@@lindblomdev Yeah, true.
How are job opportunities for postgres DBAs?
I have no idea. Im a developer that believes that all developers should have a little DBA in them 🙃
This video inspired me to use PostgreSQL jsonb support instead of MongoDB as query database. Sure, Mongo is distributed and can scale but my app is not that big so postgres gonna cut it.
Im glad to have inspired you. Good luck on your pg journey. 😊
And who told you that Postgres cannot be used in distributed mode and cannot scaled?
@@anonymous_anonymity A lot of people
@@anonymous_anonymityhorizontally scale is what he meant. And there are cases where you don't need relationship in db and then it can be an overkill using SQL like storing logs for example
PostgreSQL can be up to 10x faster than Mongo. So, it won't even need to do horizontal scaling until you reach 10's of millions of rows. Really important to note for non-super-huge apps.
Since each have their own benefits. I would say using Postgre for structured elements and MongoDB for less-structured elements is best way to go. I wish Mongo had better support for structued data, that is, being able to operate much faster by doing additional optimizations that leverage structure in data.
Mongodb allows you to horizontally scale! For a while in SQL world you can only vertically scale. I'm not sure if that has changed. Also running MongoDB in cluster mode is just a breeze. The information on running postgres in cluster mode seems very limited. Also storing nosql data in SQL data seems like such an anti pattern.
I haven't worked on a project that need to scale their primary datastore horisontally. What is the project?
What is nosql data?
Not trying to be a duche, just want to gather more context to write a reply :)
@@lindblomdev no offence taken. By nosql data I meant the data that is stored in JSON format (without any relationship as such a unique key of one document bound as a foreign key in another document as we do in a traditional relational database.)
Data with no relationships is a lie. People always end up writing schemas that are bolted on anyways.
I'm still not understanding how this is different from MySQL
This was about switching from mongodb to postgres. I haven't looked at mysql in many years, maybe it has cought up with postgres more now. It still has different editions, don't know how the community edition is limited. If it works for you, keep using it 👍
What about scalability ?
It depends on your applications. How it needs to scale. I'd say there is not silver bullet for general scalability. Leaving some room for vertical scaling is a good idea in case you need to buy some time to figure out something more complex for you app. It's a great problem to have i think, and by that time you should have multiple places where scaling can be done. I got Mongodb atlas spitting blood due to excessive writes, application level dirty checking fixed that right up👌it was initially intentionally left out to decrease time to market.
What about MySQL?
Great question, I haven't used MySQL in a long time. After a quick search on google it seems that the community edition is crippled in favor for the Enterprise edition. This is the type of open source databases that i shy away from. With that said, will mysql work for you? Probably, but I wouldn't use it as it doesn't have any benefits over PostgreSQL only negatives, as far as I can see.
If you are about to decide between PostgreSQL and MySQL, the best option is always PostgreSQL. Same goes with mariadb
I think that there are pro and cons for both ! Those json things came far later on postgresql whereas mongodb was build on that idea and also the fact that mongodb is schema-less by default (big advantage for companies who don’t want to store relational data. Mongodb is also big in the enterprise world what make it “business” compatible (become more and more popular). PostgreSQL isn’t making any money (thanks to them) whereas mongodb is a company working for companies (like redhat for example). Note that I really love PostgreSQL especially with those recent json/jsonb things. It’s really powerful and flexible. So, let’s conclude that both have their own “use cases”. Ps: that jsonb came probably of that mongodb BSON idea btw.
Thanks for you comment. Do you have an example where it would be an advantage to be schema-free? I often find that I either want exact same structure in my collections, or I want to allow some custom fields on the documents(but still most fields are common). Both of those cases benefit from being in postgres compared to mongodb. I do still run Mongo in production, mostly due to the fact that data is already there. I don't see any benefits with it anymore. But it's not bad enough to switch it out. It's actually not bad at all, just not as good as postgres. :)
Lol!! MogoDB is a product created by a profit making company. That is why there is a hype. Lots of marketing. And clearly, people fall for the marketing.
MSSQL vs PostgreSQL?
I've never really evaluated sql server as it used to be windows only and not having a free version. Both things have since changed, although the free comes with limits. We do use it at work for legacy reasons and we haven't had any issues. Also, management studio is the best GUI I have come across for interacting with DBs, and it's also free which I like 🙃
Me: maybe i should try learning Mongo
CZcams: why Postgres is better
welp, nevermind then.
didn't know about point in time recovery, that seems really useful.
Haha I bet you can find others saying the same thing but the other way around, that you should use Mongo instead of postgres if you search for it 🤪 yeah PITR is one of the those things that can really save you one day. I have a couple of videos where I show this, they have been in editing for months and some segments have blurry text and need to be remade 🙈 reading about PITR might make it seem like a hassle, it really isn't once you've done it once.
authors has no idea what is the purpose of sql/nosql or CAP theorem. very naiv way to decide what database is suitable for specific archicture. but anyway thanks for sharing.
Who are these authors you are referring to? I used to beat the nosql drum pretty hard with couchdb/rethinkdb/mongodb, and I still run a few mongodb applications in production. But I have a very hard time to see when any document database has an advantage over postgres (I haven't played much with graph databases due to lack of continuous backups without going enterprise level). If you have a scenario where it's better suited, please feel free to share. My option is that it's very easy in the beginning to get started, but over time the flexibility becomes cumbersome. And ad-hoc queries is a disaster compared to sql. Also, most collections will contain documents of the same structure anyway, so why not save some space and use columns. Regarding the flexibility, I prefer flexibility in adhoc queries and clear structure during development.
@@lindblomdeva bunch of report logs and non relational tables easy. Don't see an issue why both can't be utilised
I used to work with MongoDB - for some 4 years, and now for 6 months I am working with Postgres - I hate Postgres, complete with the SQL. The level of complexity Postgres adds is enormous, it lack sharding, it scales only vertically, the only way to keep Postgres in production is RDS or any other managed DB service - which is much-much pricier than Atlas. As NodeJS developer with Mongo I have single techology streamlined stack. With Postgres - I need write queries, or use ORM - which also adds to the complexity and often not suitable for production.
Thanks for your comment. What is the application you are working on? And what is the reason you cannot self host postgres in production? I usually add a cache or search engine to the mix to offload the primary datastore. Maybe it's not a possibility in your case.
Regarding sql. I guess it's a matter of taste. Personally I feel sql I cleaner to work with than the aggregation pipeline when complexity creeps in.
Hey y'all... I just wanted to share the code he used to make for his example. I was having a hard time understanding because of all the aliasing and renaming and overwriting of them. Hopefully this helps:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name TEXT NOT NULL
);
INSERT INTO users VALUES (1, 'Christopher');
INSERT INTO users VALUES (2, 'Lindblom');
INSERT INTO users VALUES (3, 'allan');
CREATE TABLE tags (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
value TEXT
);
INSERT INTO tags VALUES (1, 2, 'age');
INSERT INTO tags VALUES (2, 2, 'name');
INSERT INTO tags VALUES (3, 1, 'age');
INSERT INTO tags VALUES (4, 1, 'skill');
-- my_tags
select DISTINCT t.user_id, (
select jsonb_agg(_)
from (select id, value from tags as tt where tt.user_id = t.user_id) as _
) as my_tags
from tags t;
-- below line doesn't work because out of scope
-- select * from my_tags;
with user_tags as (
select DISTINCT t.user_id, (
select jsonb_agg(_)
from (select id, value from tags as tt where tt.user_id = t.user_id) as _
) as my_tags
from tags as t
)
select u.id, u.name, COALESCE(t.my_tags, '[]'::jsonb) as other_tags
from users as u
-- user_tags is in scope because of "with" keyword
left join user_tags t on (t.user_id = u.id);
Great initiative 👍 let me know if you think I should copy it to description.
There is a company behind MogoDB. They created a lot of hype (via huge marketing campaigns) to sell this product. Whereas Postgres is an open source and free product. Nobody is doing any marketing for Postgres. Nobody gives a damn if you use or do not use Postgres. But Mongo people loose profit if people stop using it.
Choice depends on what your app needs to be honest. MongoDB is good for if you really need flexible databases
i have yet to encounter an app where i want flexibility in the data layer not available in postgres? i have on the other hand regretted a really flexibile app i built as it grew and refactoring started to happen, it became a real mess, as the documents and the code no longer matched. You could add validators to mongodb, but that is overhead and extra work you would get for free with something like postgres.
Now i prefer to have columns for the common fields and jsonb columns for data that can be flexible (could have mulitple jsonb i in the same table, if they serve different purpose), or use separate tables and just join the old school way.
But SQL is boring as fuck :( I've been using and hating it for years.
I want something fun and exciting when im not working with clients. Most NoSQL just does that for me,
Boring is a feature when it comes to data 😅 but I hear you, sometimes it's nice to have it a bit more relaxed.
I hear ya mate, at my work it's all about SQL sadly even if we have logs and a bunch of non relational db and then just use the foreign key for getting user name that will never change.
Some SQL die hard aproach is rly annoying.. I like SQL but start to dislike it due to ppl not understanding beauty of using both
6- mongodb deployment sucks and not supported everywhere and expensive :(
yeah, agree. It has always bothered me about mongo that you need to have a 3 node cluster to be production ready. If you can accept possible downtime if something is wrong, then you can easily have a production ready setup using only one node if you go with another database.