PostgreSQL vs MySQL
ฝัง
- เผยแพร่เมื่อ 22 พ.ค. 2024
- Deploy PostgreSQL → ibm.biz/BdPSXZ
Get Started with MySQL→ ibm.biz/BdPSXY
Most Relational Database Management Systems rely on SQL, but two of the most popular RDBMS - PostgreSQL and MySQL - deliver different value propositions. IBM Master Inventor Martin Keen explains which might be appropriate for your needs. A lot of it comes down to the simple question: what’s more important to you, performance and scalability or ease of use and speed? Plus, Martin gets AI to tell a joke.
Get started for free on IBM Cloud → ibm.biz/sign-up-today
Subscribe to see more videos like this in the future → ibm.biz/subscribe-now
You missed the biggest differentiator between the two: licensing. Depending on what you do, your use of MySQL may require a commercial license. Even if not, MySQL Community Edition is licensed under GPLv2 which contains some strict rules and requirements. Postgres is licensed under the PostgreSQL License, which basically says "do whatever you want with it, including embedding it in your product. Your only requirement is to include our license notices.'
Thank you!
I was actually surprised as the video omitted it. It's the elephant in the room. We had moved from MySQL to Postgres for this specific reason.
Gpl v3 is a different license from v2
Great explanation 👍
WOW! Time for me to use PostgreSQL heavily! Thank you!
Problem with many 'feature, checkmark' comparisons is that they often skip important nuance and depth. postgres's json support blows away anything else in the market because it's directly embedded into the type system, you can do things like, "select json_agg(f) from foo f;" Relating to this comparison, the distinction between performance and speed doesn't make any sense. One of the specific features relating to speed, partial indexes, is in both databases.
It's not the feature, but how it's implemented, that determines product performance. Postgres has a lot of limitations when handling lots of writes with replicas.
I totally agree with you. Those comparisons are really confusing.
Most of the time, it comes from inexperienced authors who just paraphrase the words from the documentation and haven't spent enough time on each or don't really understand them. That's why I expect more from the IBM Technology channel.
@@zomgneedaname A lot of those problems have been solved as of PostgreSQL 12 using the new ZHeap implementation of storage.
I loaded PostgresQL and MySQL (Maria.. too) with half a million selects, insert and updates per hour on an old PC. No tuning, just "apt-get install". MySQL died immediately, Postgres has worked like a charm ever since, I couldn't believe it as processors run on maybe 20% of their capacity (both via JDBC and some Rust connector), magic. I thought the opposite for 30 years, "mysql is lightweight and fast, Postgres is a complicated, object-oriented engine". My employer, the biggest IT environment in a European country, uses Postgres only now for all new projects, slowly migrating Oracle DBs. How stupid we all have been not trusting Postgres?!
What specific features of Oracle keeps you on it? What are limitations where PostgreSQL simply doesn't perform well as Oracle? I am planning to do similar migration.
You can't make performance assumption based on the default installation. 500,000 Q/Hour it's nothing. If you make say 1 change only after you install MySQL, increase the InnoDB buffer to a proper size, your server might serve thousands Q/Second easily. Different RDBS are just designed differently, so they might need different amount of basic configuration to start with.
You were not wrong about PostgreSQL performance. I've used both for nearly 20 years. Earlier MySQL can outperform easily. Since PostgreSQL 9.0, its performance was significantly improved. And each version it becomes faster and faster. Still, for default installation and trivial query, MySQL seems to be faster. But for average case, PostgreSQL is one of the fastest in the market. In our environment, it's even faster than commercial products and MongoDB.
It depends on the operating system how the configuration file is delivered. This is the first mistake, where I do not need to read more.
@@kittipongpiyawanno315mongodb it quite slow no? Specially on joins
Actually the full text capabilities of Mysql are abysmal. In Postgresql using the gin_trgm index is an absurd amount faster then anything accomplishable in mysql. (This creates trigrams, allowing one to do superfast searches on fragments)
Best feature of postgres over mysql that i found is returning results in update queries, very helpful for bulk update use case scenario
As an ex PostgreSQL developer, I can say that it's a completely wrong explanation and hides tons of essential details.
as a non PostgreSQL developer, I've learned nothing about pstgresql from the video
I am wating for your video
Man, it's so strange to be searching for info on PostgreSQL and stumble across Martin "Homebrew Challenge" Keen.
Just remember CAP theorm. Most of your db related choice will start to make sense.
Apps do scale, and data structures do change over time. What matters is how well you manage it and organise it for smooth transitions between these tools :)
Remember that MySQL speed is very dependent on the storage engine used. Myisam is very fast if you don't need transactions or acid compliance. If you need transactions and rollback and triggers, then the storage engine you need will remove the speed benefit.
Isn't myisam still only very fast if you don't write too much data?
I recently had to choose between the two. We went with postgres because citus is kept up to date with postgres, but vitess is very out of date and missing many features of modern mysql, including some features that were important for us.
I wouldn't really say mysql was any easier to develop with etc though. There are a few differences like citext extension vs case insensitive collations but not really had any problems with it.
Thanks for introducing me to Citus. Just looked it up and it seems really powerful.
You forgot the mysql h.a. with innodb cluster and mysqlbackup for doing online compress backups
We use postgresql for the power of relational and flexibility of no schema all in one platform by putting gin indexes on jsonb fields.
I think the only thing that I've ever missed in mySQL was recursive queries.... other than that, I love things like delete and updates using inner joins. Postgres's biggest selling point to me are the modules such as UUID and geospacial data. That's my point of view as a developer but I know a lot of people who would much rather rely on OracleDB
thank you a lot, Martin!
In fact, i don't see too much usage of mysql except if there is any php code. As i see: javascript developers often prefer mongodb, golang developers prefers postgres and rust developers prefer postgres or sqlite.
Usually usage of mysql are limited with php.
But u know how much more php is used. Also node js devs love for mongodb has reduced quite a lot.
I’ve heard lots of people moving away from mongodb and going towards postgresql
The AI has the humor of a software engineer
I love Supabase, which is a BAAS based on Postgres with amazing free quota and visual interface.
Very interesting. I am very impressed at your skill of being able to write neatly in mirror form.
There is not much of skill in it. The video is just horizontally flipped.
@@sanjavnice. I actually thought he was lefthanded
This really clear things up for me. Thanks
postgres is my choice for all kind of small projects aswell, runs great on even the oldes raspi etc..
Wait... I think I missing your explanations about speed in Mysql vs performance in Postgresql
I have used both for many years, among others. I agree that MySQL is quicker and easier to get started and use. MySQL is also very fast at large transactional databases but not overly complex ones. PostgreSQL is far more featureful and deals much better with complexity. For example, you can embed a variety of languages for stored procedures. Also, PostgreSQL has much broader capabilities of applying constraints and many other things. PostgreSQL scales out to multiple peer servers for vastly faster and larger data storage, as where MySQL only supports master-slave replication. Overall, MySQL is more featureful than SQL Server, is fast, and scale up pretty well, but not anywhere near PostgreSQL. MySQL's main strength, though, is that it's easier to get up and running and smoother to develop for. And for most web applications, that's really what matters more. For comprehensive enterprise data processing, what PostgreSQL has is what matters more.
Compared to MySQL, PostgreSQL makes simple things complex, and complex things become a nightmare - from the admin's point of view. Replication in PostgreSQL is lacking compared to MySQL. What exactly is meant by "PostgreSQL scales out to multiple peer servers"? Did you mean MySQL Cluster here, by chance? ;)
If something is not fit into mysql it becomes a disaster or has huge problems with performance. Postgres support much much more cases, and even if it's not supported you can write a module by yourself.
Have you used pgmodeler? It's a somehow obscure project that people should know more about. It makes interacting with psql extremly easy.
I hardly noticed a difference setting up the two. You can get a docket container running Postgres in minutes
The notion of smoother development only for mysql is debatable because many web frameworks employ database abstractions, such as Spring Data JPA. This allows developers to switch between MySQL and PostgreSQL by merely changing drivers (though a database migration script library is needed for an easy switch).
What impresses me the most is how easily he handwrites in mirror.
Search on "lightboard videos".
can we just appreciate the quality of the video, and the topics they choose to teach people for free.
Had to use Postgresql after 30 years of Oracle experience. Was like time traveling back to the end of the 90s. Lack of features in sql, no parallel queries, not able to scale horizontally (RAC). Miss it.
If they were relational that would mean that they organize data into relations (that can be depicted as tables but that are not actually tables) that can be operated on using relational algebra/calculi (closed over relations) on which SQL was inspired although it does not adhere to it by many reasons, right?
Always love to see the impassioned debates generated by software paradigm comparisons.
Most if not all web hosting companies come with MySQL installed. That in itself is a huge relief…you really don’t want to keep installing and updating your database software
Could you prepare video stream about CDC transactional replications in PostgreSQL .
Really odd comparison. Where is MySQL faster than PostgreSQL? I remember those comparisons 15 years ago, but PostgreSQL isn't difficult to use. I would say that it can be used everything MySQL is used for, but can also be used for far more complex, serious and advanced things than MySQL.
for example MySQL does not suffer from Vacuum and double buffering
Comparing MySQL to Postgres is like comparing Microsoft Access to Microsoft SQL Server. The first is a sloppy attempt to create a storage system, the latter is a database.
One of the happiest days of my technical career was when i moved from MySQL to Postgres. It is so obvious that the core of Postgres is built by people that understands databases, and that the same is not true for MySQL.
The learning barriers caused by the strictness of Postgres is paid back in the first week.
Just look at the documentation, Postgres is far superior and honest.
Guys, the thumbnail has the name wrong. it is not Postgre it is Postgres or PostgreSQL
Good catch!
Thank you for this quick and fun comparison video
I am just starting out with my data analytics journey, and have started out figuring out what I need to learn to get a job as a DA asap, and I've downloaded both MySQL and PostgreSQL, but have yet to install either. I figured I would install both, and play with each, but my questions is, which should I use to learn SQL etc? I realize that once I learn one, it will be easy to learn the other, as they are similar, but, the question persists, which should i train myself on?
Initially I thought PostgreSQL, so that I'm already ahead of the game, but that might take me longer to learn SQL. I don't know, I'm just guessing, which is why I ask.
Thanks!
The problem with this video is that it basically chooses very similar terms for each side giving what seems like the same checkmark for both. I mean it would be hopeful to sort of explain why, like why is complex multi-type queries better in Postgres, I’ve certainly done those in mysql and never noticed any issue. Mysql has clustering which gives high availability like Postgres. As you state at the beginning they are extremely similar. Yes there are small features that work better for one need vs another, and with modern ORMs it may not even be noticible by a developer. To me it’s which one does your organization have the most experience with?
That was a good explanation
MVCC is used by both, PostgreSQL and for example InnoDB storage engine for MySQL.
Honestly thought the presenter was going to make a joke about how everything he wrote under PGSQL was also under Mysql since MySql uses MVCC and is king when it comes to OLTP (aka “web app”). He clearly only did surface-level research to prep this video.
@@phpnotasp MySQL is way better, so when people compares it to postgre, they have to compare previous versions XD
Actually I didn't understand difference between performance and speed. Are they different thing?
You explained very good thank you so much.
BTW the final joke was really awkward.
Oracle DBA had good scope n future or which dba is good for fresher can anyone answer
what about MariaDB, with Galera Cluster?
What is the difference between performance and speed ?
Does this mean that both of them support the same data types and operations
How that glass blackboard works and mirrored?
thank you alot really helpful i kinda got the humor😂
I flip between both of them for different projects... and to be honest, I've never had a need for the "Object" part of postgres... I/we use it in a traditional RDMS way.
You know what RDMS I wouldn't use for any of my projects? --DB2. And I used DB2 for 14 years on z/OS and I'd not touch it with a 10 foot pole on any other platform.
You should try it, the objects are phenomenal. You can return a row as a variable.
Thank you.
this is great video thank you
what about IBM's own RDBMS DB2 vs postgreSQL and mySQL?
DB2 is a nightmare from admin point of view... some syntax is also too old. As I know, IBM itself is trying to replace it with Postgres. I worked to improve Postgre's performance on Power CPU.
Thank yu i also use postgres for my enterprice application
You are an IbM worker that is not talking about in4mix or db2 all day!!
You are a shiny unicorn.
3:20 what do you mean with multiple datatype support? isn't this a basic thing in any DB?
I think they mean "more" data types
Start with entity-relationship modelling - that way you understand what the hell you are trying to do.
If I can, I'll use Postgres every time. If not, you maybe can get along with MySQL.
As the old joke goes, if your only tool is a hammer, all the problems look like nails.
(Oh, and there are FOSS tools to translate ER models into SQL, at least to 3rd normal form, which gives you some security. Or just use posiit noyes on a while board. It all helps to think first)
I've worked with both for a couple years and I think they are both very easy to setup (a few hours) using modern technologies like Docker
Isn’t it rather a few seconds with docker?
@@leonie9248 maybe a nonproduction database
Shoutout to this man for writing backwards
Search on "lightboard videos"
For me, personally, the fact that Postgres supports transactional DDL is a big advantage.
You can alter columns, create tables, drop tables, migrate data to them, and ROLLBACK if you messed up.
MySQL doesn't support this (AFAIK), nor does MsSQL. Maybe Oracle, or IBM DB2 does (haven't checked).
Meh, you shouldn't be messing with the DB structure if you think you may need to roll back, is my opinion...
@@HMan2828 that's way too simplistic. DB structures change all the time. no point in making the process more difficult than necessary
If anyone can, please inform me about the tools(+equipment) used to make the above video. I tried searching for it but I could not find it. I am really impressed by the quality of this youtube video. Thanks in advance.
Seach on "lightboard videos"
great vid!
How is *speed* different from *performance* in this context? Also, saying that MySQL is great at web applications is not a feature but a result of circumstance. People used it in that way just because!
each db has specific purpose either complex queries or managing schema & shemaless data objects, scaling load balancing or license free dbms
Thanks for this. For the newbies mentioning MariaDB in relation would have been a nice touch.
I think MariaDB shares the same values as MySQL.
MariaDB is barely-altered MySQL
@@AristoHadisoeganda I think not anymore... they did much more recently than MySQL.
@@AristoHadisoeganda Mariadb has got more features than MySQL. It's got features that you'd only get in Oracle or possibly MySQL enterprise.
Percona MySql has every oracle mysql enterprise feature available free and open source.
Pls can you make tutorial about the writing board?
th-cam.com/video/Uoz_osFtw68/w-d-xo.html
i was woundering why the video was so bad for 500k subs.. then i realized that its from ibm
I agree with wadesworld, you should have started with the licensing aspects of these databases. The rest is totally irrelevant between these products.
wow this guys is writing in mirror inverted fashion. mind blowing
Me, a person with adhd, just realized I missed most of this video because I was enamored with how this guy is so good at writing backwards...
See ibm.biz/write-backwards
@@IBMTechnology Be sure to leave any clothing with asymmetrical writing or logos back in make-up. ;-)
hm...can you make some study cases to prove your meaning ?
They should make a video on how they make their videos!! 🙂
Already th-cam.com/video/Uoz_osFtw68/w-d-xo.html
Captions at 3:00 say "that enables the simultaneous occurrence of RIGHT [emphasis added] operations and reads. And there's also business", when it should say "that enable the simultaneous occurrence of WRITE [emphasis added] operations and reads. And there's also business". Captions at 4:19 say "and running and get started. And that really goes to my next point, which is speed. So FAR, [emphasis added]", when it should say "and running and get started. And that really goes to my next point, which is speed. So FOR, [emphasis added]".
Nice explaination
Facebook , TH-cam, Google and so forth using mysql for their heavy load server side apps.
Must be customized MySQL, not the current public release.
Google uses Spanner
@@akogepayo It is not customized much; FB is currently on 8.0.18 as of Mar'22. They presented on this at Percona Live. For the most part, they use Percona Server, not community.
How's your transactional DDL statements going MySQL? There's never been a good reason to use MySQL over Postgres. Having used Postgres on and off since the 1990's, and giggled at MySQLs not really a database DNA, and all the problems that has caused. Friends don't let friends use MySQL.
Thanks for comparison! But JSON pronunciation made me squirm :)
MySQL is not scalable that’s why nosql like mongodb (read scalable) and Cassandra (read/write scalable) exists. Unless you use Vitess or Citus. This list of of differences is incorrect with regards to the latest version of each. For example some of the big differences are stored procedures, data types, licenses, connect limits.
MariaDB is scalable with Galera Cluster
MySQL is scalable, but is hard to configure, without using externals like Galera, or Percona, but you can use the native scaling method, the "binlog", is hard really hard to configure, but it works pretty well
Another IT guy brewing a beer 😂❤️👍
I worked with both over a decade and MySQL ist the PHP of the Databases.
I do not know why everyone says MySQL is easier to use because it lacls so many features, it encourages bad habits.
Explain is a joke and sometimes gives wrong numbers.
There are obsure bugs for example: An developer accidentally created an cte with the same name as an table which segfault the server process.
When I came to my current employer I introduced them to postgres und EVERY developer was happy with the change because so many problems suddenly disappeared.
The type system an the ability to use arrays, composote type, hstores etc. alone makes it so much easier and faster.
Apart from wordpress MySQL is a nogo for us.
This is so true. Postgres is far superior to MySQL.
Oh -- I think you read the punchline to the joke wrong. The real punchline is "This is the only developer that wanted to use MySQL -- all the others left."
But how do you make beer from them ?
Weird how I know this guy as a homebrew guy and the database guy
This may seem like a weird question, but are you the same guy who produces the Homebrew Challenge Channel on TH-cam?
Not weird at all! Indeed, Martin is the guy behind that channel: www.youtube.com/@TheHomebrewChallenge
...showed HIM, not HER 😂
He writes backwards. What a crazy feature of a man
Elephants are cooler than dolphins
I have never heard it pronounced "Jiy-son" 😂 I've always pronounced JSON as "Jason"
I say Jay-s(on).
haha ok but have a listen to your pronunciation at 1:32. You definitely say "Jiy-son" 😂@@IBMTechnology
very good explanation
I love IBM pls hire me
Wait... you're the homebrewer I follow!
Which is better for querying homebrew recipe 😂😂
I worked for IBM for Years why not recommend DB2
Wuuuut I didn't know you're also a fellow programmer, fellow brewer
So do you say if we want to use in medium to small apps then we should use MySQL
then why does uber moved from Postgres to mysql
also mysql is primary db for facebook
can we have explaination on that please as those are large scale based products
Most probably he's talking about the open source versions of the databases. When we go to Enterprise features MySQL is way better as they develop all the Enterprise tools in house while the Enterprise features for Postgres are offered by third party companies. Uber and Facebook and not only have their core business running on MySQL Enterprise Edition.
Because this video is entirely incorrect. The real value of MySQL and Postgres is completely different.
Fortunately, most of the real difference between such DBs you can find on very high-load projects or very complex projects.
But it's not for 5 min video because you need to understand how MVCC and index management work in such dbs.
PS facebook and uber use MySQL mostly as smart key/value storage and they have their own DB on top of it. But again, it's a simplification and not fully correct.
@@paulpetrescu5260 FB and Uber do not use MySQL Enterprise. They use Percona Server for MySQL (Source: They've publicly stated this numerous times)
But I still like the Mysql .
Incredible how easy he can write mirrored text 😲
See ibm.biz/write-backwards
I think this is not a very useful description. Saying "PostgreSQL is for OLTP and MySQL is for Web" and saying one scallable and fast and the other isn't, is a massive oversimplification. You can definitely use PostgreSQL for web applications without it being an issue, and you'd need to scale it very, very far before you start to run into data ingestion issues you can't easily engineer yourself out of. And MySQL isn't just "fast" its fast under specific circumstances, and slower in others.
Without specific cases and specific measurements its not a useful discussion. I say this as someone who's worked with either database system for things running on a national scale.
What about oracle RDBS database ? vs Postgres ? vs Mysql
"web app". "banking sector". "performance". what do you more need to know? haha
oralcle is not open-source anyway, so it's better to compare oracle to ms-sql (which also nobody cares)
How does he write backwards so quickly and accurately?
The video is flipped around the y axis.
You also missed a very big issue: SQL is SQL. Regardless of mysql or postgres or DB/2, if you don't know how you will query (that's the "Q" in the name") you'll suck with any option. So yes, working with SQL/DS and with nearly anything having a "Q" in their name, what you want to query and how you want to query is the key to performance & storage. Just my 2 cents. :)
Noone:
Me:
Are you bloody writing in mirror?
MySQL was really really cool ... right up until they were acquired by Oracle. RIP MySQL.
pgvector on PostgreSQL
It's unclear why MySql is `easy to use` but Postgres doesn't....