PostgreSQL vs. MySQL, as per EnterpriseDB
EnterpriseDB put out a white paper arguing for the superiority of PostgreSQL over MySQL, even without EnterpriseDB’s own Postgres Plus extensions. Highlights of EnterpriseDB’s opinion include:
- EnterpriseDB asserts that MyISAM is the only MySQL storage engine with decent performance.
- EnterpriseDB then bashes MyISAM for all sorts of well-deserved reasons, especially ACID-noncompliance.
- EnterpriseDB asserts that row-level triggers, lacking in MySQL but present in PostgreSQL, are the most important kind of trigger.
- EnterpriseDB claims PostgreSQL is superior in procedural language support to MySQL.
- EnterpriseDB claims PostgreSQL is superior in authentication support to MySQL.
Of course, the question of MySQL performance with a transactional storage engine — i.e., InnoDB — is somewhat controversial. E.g., there are many confident guides to InnoDB tuning. (Another post I found was this one, focused on Drupal. ) The bottom line seems to be that MySQL can be both transactional and decently-performing, but the price is sacrificing some of the load-and-go ease one gets from a default configuration. And apps written for MySQL/MyISAM will not necessarily perform well over MySQL/InnoDB.
Comments
17 Responses to “PostgreSQL vs. MySQL, as per EnterpriseDB”
Leave a Reply
All this discussion may be true for open source. But if you want more than decent performance and functionalities, you need to go a little deeper in your pocket and pay a company that knows how to make a storage engine. We have chosen Infobright and I do not regret it at all.
Adrien,
I think that depends on the nature of your application. For data warehousing, you’re 100% correct that specialty technology is the way to go. For generic OLTP and so on, the Postgres family or in many cases even MySQL could be just fine.
Best,
CAM
On the topic of storage engines for MySQL. Looks like the Maria storage engine effort and the Falcon storage engine efforts are internally competitive at Sun 😉 trying to fight back against Oracle’s InnoDB offering.
I don’t follow MySQL closely, but what I’ve been told is that if you want good behavior out of MySQL, you should use InnoDB. I was wondering how this was playing out what with Sun having bought MySQL and Oracle having bought InnoDB. However, I suppose what I was told is obsolete now.
Perhaps a MySQL supporter would care to comment here and reply to what EnterpriseDB is saying. I generally hear from people that PostgreSQL is superior to MySQL. And yet MySQL is clearly being employed in many successful applications and systems. I’d appreciate hearing more specific, up-to-date, and well-justified comparisons between the two.
Having worked extensively with both DB in high performance environments I think I can speak to this issue.
First mysql. MyISM is crazy fast for reads but dose table level locking for writs so it just can not scale if you need to change your data much. Innodb’s preforms at a similar to postgres with respect to performance for simple quires but is a IOT which can cause real issues with a high insert rate. When I have had to get good performance inserts I had to set the, required, primary key on my innodb table to a sequence so it did not have to constantly shuffle data around from page to page. Regardless of what back end you choose the mysql query engine is week. It only supports nested loop scan, no merge join, no hash join, nothing fancy. Further sub quires can’t even use indexes so they are near useless. The rule when using mysql is *don’t join*. So the case where mysql can be put to good use is where all your quires are over a single table, which is why it has done well for web apps I wold suspect.
Postgress on the other had is a very full featured DB, merge join, hash join, materialized views and sub quires, partial indexes, bitmap indexes, ect. Not every feature that Oracle has but not too many you will miss. Postgress still has it’s weaknesses. Every connection to the DB requires a process and is not cheep to create. This limits the number of clients that can be connected to the DB to thousands. The postgres wire protocol is bloated storing every thing as strings. Vacuums are required to reclaim transaction ids of which there are only two billion. Pre 8.3 things were worse on this count as even selects were run in transactions all the time.
So from my perspective there are a few cases where I would use mysql, where I have very simple quires which have no joins and I want lots and lots of connections. Other wise I would choose postgress because it gives me lots more tools to get my quires running faster. I prefer postgres but mysql is some times the correct choice.
[…] a comment thread on a recent post comparing MySQL to Postgres, Jonathon Moore chimed in based on experience with both […]
Jonathan: The Postgres wire protocol does not necessary send everything in text — there is a “binary mode” that allows values to be sent in a binary encoding; unfortunately the binary encoding is not guaranteed to be portable among different architectures (e.g. if the server uses IEEE754 floats but the client does not).
Also, Postgres does not support on-disk bitmap indexes. It allows multiple index scans on the same table to be combined via an in-memory bitmap, but that is a distinct feature (called “RID list scanning” in some other DBs). PG also doesn’t have materialized views in a very meaningful sense (e.g. nothing akin to DB2’s level of support for MVs).
[…] should come as no surprise that not everybody agrees with EnterpriseDB’s views on the PostgreSQL/MySQL comparison. In particular, the High Availability MySQL blog offers a detailed rebuttal post, with more in the […]
One big issue with MySQL is that they continue to suffer from shipping products very late and with big quality issues.
You won’t find that with PostgreSQL. Nor EnterpriseDB Postgres Plus and Postgres Plus Advanced Server.
This week, a number of mentions about the Marten Mickos of MySQL being interviewed in the Wall Street Journal. He talks about his secret sauce being his teams culture. Seems like this culture is having problems getting a release out the door in a timely fashion and as planned. According to an article in ComputerWorld on April 15, 2008 of this year, (from the article) “Marten Mickos, the former CEO of MySQL who is now a senior vice president at Sun….When we released MySQL 5.0, it didn’t really meet our quality standards, he said today at the start of the MySQL Conference & Expo in Santa Clara, Calif. “With 5.1, we are being much more conservative, much harder on ourselves…..Mickos said it will release the production version by the end of June, or about three months later than planned.”
Here it is July and no signs of 5.1 shipping. This week Bruce Momjian of the PostgreSQL community will be speaking in Boston about the open source development process for PostgreSQL at the Massachusetts Technology Leadership Council Open Source Meeting. Maybe MySQL could learn something from Bruce’s talk as PostgreSQL has had a great history of shipping highly reliable and very high quality product releases on time. Its ready when its ready and when it IS ready, it is made available. I heard from a MySQL sales executive a month ago that the field sales team is very frustrated with the delay in their product shipping.
@Neil Conway, thank you for correcting me. Especially for the pointer about the binary protocol. Now my only two wishes are for a more compact row/index format and a lighter wait connections or connections I can multiplex more then on query on.
I’m trying to figure out witch database can hold more records? Mysql or postgresql. if you know please comment back.
Both can be configured to hold a LOT of data. As in petabyte-scale lots.
You need to say more to get useful advice. 😉
Hi guys,
We currently have a postgresql web application, using a lot of join queries.
Some times we need to process a lot of INSERTs / UPDATEs, we don’t use SELECT massively.
We are now thinking about rewritting our app, and now that mysql matured, we are considering it for our new version. What interests us the most in “MySql Cluster” which seems to allow an easy / fast way to raise our capacity when expecting use pikes.
At this time, April 2009, would you seriously considere both solutions, with maybe a preference for mysql for the reasons described above ? or would you rather stick to postgresql (though at that time, we don’t use procedures / views … )
Thank you for your answers.
How are you doing queries if not via SELECT?
we DO use SELECT of course, it’s just that for our most demanding app, we use mostly INSERT and UPDATE.
We “cache” as many objects as possible, which reduces the amount of SELECTs a LOT.
For instance, in this application and for 1 single operation, we have 2 SELECT / 3 INSERTS / 6 UPDATES.
This “single operation” is repeated tons of time each minute, and we basically need to be able to allow more and more of these in the future (that’s why we were thinking about “mysql cluster”)
Hi all,
Currently we deploy Oracle on 5 yo Solaris machines with active/passive using Veritas clustering. With additional business needs coming up, we are upgrading our machines (going intel and linux) to have something similar to no-single-point-of-failure architecture (a hw loadbalancer in front, 2 machines for app server, 2 dedicated database machines). This new set of machines were thought of keeping Oracle’s RAC in mind. However, the licensing costs are prohibitive for us.
We are looking out for alternates and are stuck between MySQL and PostgreSQL (or even the thought to bear the cost of Oracle if MySQL and Postgre and not up to the task).
regards,
mnsharif
Hi,
No choice you have is ideal. MySQL is in transition, the EnterpriseDB company is untrustworthy, and Oracle “Classic” is expensive.
If I were consulting to your project, I’d try to understand more about your application needs, now and in the future, to try to figure out in what areas you need to pay up (if need be) for top-end features and in which areas cheap/good-enough gets the job done.
http://www.dbms2.com/2009/02/25/even-more-final-version-of-my-tdwi-slide-deck/ focused on analytics rather than OLTP, but if I did an OLTP one, it would have a similar general philosophy.
Best,
CAM