July 7, 2008

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:

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”

  1. Adrien on July 7th, 2008 11:40 am

    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.

  2. Curt Monash on July 7th, 2008 1:09 pm

    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

  3. Zurek on July 7th, 2008 2:41 pm

    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.

  4. Daniel Weinreb on July 8th, 2008 6:48 am

    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.

  5. Jonathan Moore on July 10th, 2008 11:59 am

    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.

  6. How is MySQL’s join performance these days? | DBMS2 -- DataBase Management System Services on July 10th, 2008 6:27 pm

    […] a comment thread on a recent post comparing MySQL to Postgres, Jonathon Moore chimed in based on experience with both […]

  7. Neil Conway on July 10th, 2008 7:31 pm

    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).

  8. Pushback on the PostgreSQL vs. MySQL comparison | DBMS2 -- DataBase Management System Services on July 10th, 2008 7:40 pm

    […] 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 […]

  9. Bob Zurek on July 10th, 2008 9:59 pm

    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.

  10. Jonathan Moore on July 11th, 2008 11:10 pm

    @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.

  11. aFree4U on March 8th, 2009 8:46 pm

    I’m trying to figure out witch database can hold more records? Mysql or postgresql. if you know please comment back.

  12. Curt Monash on March 9th, 2009 4:45 am

    Both can be configured to hold a LOT of data. As in petabyte-scale lots.

    You need to say more to get useful advice. 😉

  13. Eric on April 10th, 2009 10:34 am

    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.

  14. Curt Monash on April 11th, 2009 1:25 am

    How are you doing queries if not via SELECT?

  15. Eric on April 12th, 2009 10:28 am

    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”)

  16. mnsharif on May 30th, 2009 5:36 am

    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

  17. Curt Monash on May 30th, 2009 6:46 am

    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

Leave a Reply




Feed: DBMS (database management system), DW (data warehousing), BI (business intelligence), and analytics technology Subscribe to the Monash Research feed via RSS or email:

Login

Search our blogs and white papers

Monash Research blogs

User consulting

Building a short list? Refining your strategic plan? We can help.

Vendor advisory

We tell vendors what's happening -- and, more important, what they should do about it.

Monash Research highlights

Learn about white papers, webcasts, and blog highlights, by RSS or email.