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.'
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.
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.
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)
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.
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.
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.
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.
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.
Like what? Postgres has json as data type as pro if it is what you refer to as complex the rest the thing postgres sucks (resilience as in active-active or clustering), super complex cross db queries (for eg when doing analysis),... yes it is usable and I found forks swearing on its head but looking MariaDb now the thing is next-level
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.
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.
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
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.
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.
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
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?
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.
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).
@@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.
may I ask : If I created a Postpress database with a username and password on my computer, it can only be opened with this name and password when I migrate it.
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.
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."
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)
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.
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.
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
sooo just use postgresql. i don't like this argument of "ease of use". you need to know SQL in both. if you are not a DB admin, you just write apps on top of DBs, you won't even touch the hard parts - and where you do, you'll be glad to have the one that can do it all. If you are DB admin, then .. what "ease of use" means anyway, since you are the supposed guru?
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!
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]".
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?
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.
I think you are mis informed… the main differences depending on version is pgsql has more SQL features then MySQL like window functions etc… but when it comes to stability and performance MySQL has a better story with galeria cluster and it’s many replica options…
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.
This video may assumed as a joke itself. Martin may have but little point if this video published 10 years ago. Otherwise it’s content complete bullshit. Both free and open source databases advanced far from his explanation at 2022, still being developed at a higher pace ever.
This topic deserves more than a 6 min video. It's very nuanced and the choice should not only be made while considering only these few points. There are counter points to be made on both sides depending on workload. Not a great video in my opinion.
I wish yoi would have elaborated, even a little on what the acronyms meant. 😢 Even if it were a high level, superficial description. As a beginner this video didn't really paint a clear difference betwewn the two (are they platforms like Snowflake or Terradata?) , and it didnt provide much context of when youd use one over the other. This video felt like the target audience was someone that already knows a lot about both Postgress and MySQL
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. :)
The biggest difference to note about MySQL is it is the only dbms I know of that allows changing the underlying storage engine. This is a underutilized feature, but a powerful one, as it allows you to highly optimize your database for your specific application. You can have everything from a Transactional database (innodb) to a simple one (MyISAM) and even memory, graph or columnstore database, even nosql style etc…You can even create your own custom storage engine completely from scratch and if it is really good people will pay you for it. There are lots of commercial storage engines as well as the built-in ones. There is also MariaDB which should always be mentioned alongside MySQL since it is newer and has more modern built in storage engines and switching to it is easy since it is a drop in replacement for MySQL. It also adds various optimizations and improvements and features.
@@philipgwyn8091 There are use cases, where ACID is not important, so you can have myisam for that cases, and win a lot, myisam is while faster, even in full "table-scan" query
