Postgres vs MySQL

From Zanecorpwiki

Jump to: navigation, search

I favor Postgres over MySQL. In taking this stance, I understand I'm going against the tide and the common wisdom. The onus is thus on me to prove my position, so here it is.

This article was written against the best information I could find on and personal experience with Postgres 8.x and MySQL 5.0/5.1. My history with Postgres goes back to Postgres95, so my traditional experiential bias clearly favors Postgres. However, clients foolishly demanding MySQL[notes 1] be used in their products, my more recent experience has been much more even.

Contents

Summary

Postgres is, in general, a higher quality product that does a better job of doing what it's supposed to do. MySQL is much more likely to be standards incompatible, and slop tolerant in harmful ways.

  • +Postgres: much better error messages; bad queries in MySQL result in "problem with syntax near", and even that's sometimes wrong; postgres can often tell you what's wrong with the query
  • +Postgres: lots of useful, advanced, and standards compliant features like partial indexing, partial support for deferred constraints
  • +Postgres: small, dedicated community; high signal to noise and more advanced users
  • -Postgres: smaller community; less usage and penetration
  • ++MySQL: tools! Most tools are shit, but I'm very impressed with MySQL Workbench.
  • +MySQL: possibly more tunable
  • +MySQL: better replication support
  • +MySQL: large community; lots of feedback and forums
  • -MySQL: large community: lots of noise
  • -MySQL: lacks support for 'sequence'; everytime someone asks about this on a forum, the first answer is always 'use auto increment'; this is usually the wrong answer
  • --MySQL: fails to die in common error conditions; starting a transaction when transactions are not supported (MyISAM) should absolutely, positively, and without question fail with exception; ignoring the instructing and continuing as if there is no problem is the worst possible thing to do
  • --MySQL: now owned by Oracle, lots of good reasons to worry about their shepherding the project
  • --MySQL: implementation and culture give little respect to standards

Having said all this, Postgres and MySQL are and/or can be used almost interchangeably so long as the developer is willing to expend a modicum of effort towards that goal.

Who Cares?

First and foremost, if you're a PHP developer working on data input/data output sites (i.e., sites that don't have oodles of business logic and are mostly about getting data into and out of a database), none of this really matters all that much.

Topics

Tool Availability

I'm thinking primarily of MySQL Workbench, which is a really sweet tool for visualizing your table structure. I'm actually not a huge fan of visual tools. I think they're actually a bad idea for design (in most cases) and in my experience those that rely on tools are often terrible at schema design. I also find I don't need them very often.

However, there are cases where a good visualization is very useful. I can also see that other's may be more visual, so mileage may vary.

On a related note, the fact that it's MySQL Workbench and not SQL Workbench is a perfect example of Why You Shouldn't Trust MySQL.

Remainder is under edit, it rambles too much

Examining the Benefits of MySQL

MySQL is clearly the more popular database. By some measures, it may be the most popular database there is. Popularity, in-and-of-itself, is a poor measure of quality or best fit, but neither should it be discounted entirely.

The size of the MySQL community is a direct benefit to the user of MySQL. There's more questions and answers to Google on the net, so self support can be easier. To my mind, this effect is not overwhelming, and there is a downside as well. Postgres is not obscure by any means, so any common question is likely to have been answered on both. Plus, to the extent that question is really an SQL question, any answer will do regardless of the database being used (to the extent that the DB servers are SQL compliant).

This effect does come into play when obscure or advanced questions are involved. On-line backups, database tuning, that sort of thing. You'll find answers for both, but you're more likely to find friendly explanations for MySQL. Obscure technical issues, such as technical workarounds is another area where you may have more luck with MySQL (though you more be more likely to need it too...)

There is a downside to the large community, however: noise. It may be easier to dig out the quality answer for Postgres as the user base tends to be more sophisticated. They have, after all, explicitly chosen the less popular choice. They must have some amount of sophistication in order to make that choice. (Unless they're simply being contrary.)

MySQL does have some useful extensions. REPLACE OR INSERT, certain methods of searching, that kind of thing. Some of these can be very useful. On the other hand, they can be dangerous as well. Using these extensions locks the developer into MySQL, and perhaps a particular table engine. Because they're non-standard, there's always a question about robustness and future support. On the whole, there is often a strong case for avoiding such proprietary extensions.

Why is MySQL so Popular?

To jump ahead, my conclusion is that Postgres is the better database server. As MySQL is the more popular of the two, his assertion, however, seems to go against crowd wisdom. My studied opinion is that MySQL's popularity is rooted in historical chance.[notes 2] I do not question the wisdom of crowds, but crowds can make the wrong choice.

MySQL's success has a lot to do with PHP. PHP first database was mSQL, which is related to MySQL. Support for MySQL was added PHP3[1], released in June 1998[2]. As far as I can tell, there was no mainline support for Postgres until PHP4, which was released in 2000. Even then, it's not clear whether Postgres support in PHP4 was on by default or had to be enabled.[notes 3]

Setting aside the historical time line, there is no question that MySQL is synonymous with PHP whereas Postgres support is a feature only really important to Postgres users. If you come to PHP first, you're going to default to MySQL. As the popularity of PHP grew, so did that of MySQL.

Another big reason for MySQL's popularity is it's speed. Postgres was built first and foremost as an SQL compliant, real database. MySQL was built to do what most people needed to do as fast as possible. Compliance be damned. Speed may kill, but it also appeals. At least, that's the perception. We take a closer look at the speed question later on.

Finally, there's the culture surrounding the projects. The MySQL folks promote MySQL in order to drive business to the their commercial support operations. The Postgres folks promote Postgres to promote Postgres. Because of the direct commercial interest, MySQL has always been more interested in the mainstream and the end user. In other words, the commercial side of MySQL results in a better tuned message as well as market support. The MySQL message is clearer and louder.

Comparing Apples to Apples

My intent with all that was to say The reasons why MySQL is more popular don't have a lot to do with which is better. I want to get that question out of the way so we can compare apples to apples.

Gotchas

Postgres is simply easier to use.

This is my biggest complaint about MySQL. I run into MySQL gotchas with some frequency. I've never run into a Postgres gotcha, though they are there. Not only are there more MySQL gotchas, but they are far more serious.

A gotcha I run into all the time is the default MySQL table engine (MyIsam) non-support for transactions[3]... which are about as fundamental as it gets when it comes to modern databases. True, you don't always need transactions, but when something walks like a duck, talks like a duck, and then blows up in your face when you say, What a sweet duck, it's disconcerting. The big issue here is transactions. Transactions are essential to database programming and data integrity. Without them, you need to do a lot of extra work at the application level, and it'll never be as safe. I've had more than one occasion when working with MySQL when I forgot that I have to specify the 'table engine' in order to get basic functionality.

Which is bad, but the real sin is how default MySQL handles fails when transactions are used: it doesn't. That's the real gotcha. You tell the system, Hey, I need a transaction here, it says, Sure, no problem, and then silently does nothing. Unforgivable. There's no excuse for not throwing an error.

There are other cases where MySQL is simply hides errors. This is always, always, always the wrong solution. Things just silently fail and you end up spending hours and hours digging through perfectly correct code before you remember, Oh yeah, MySQL lies to me.

Now, if you come from a MySQL background, then these things aren't as much of a problem because you grew up with them. Of course MyIsam dosen't support transactions, and everyone knows it fails silently, that's just the way it works.

ACID Transactions

Now-a-days, both Postgres and MySQL support ACID transactions. Postgres, however, was built from the ground up to be an ACID compliant database. MySQL was originally designed with no transaction support whatsoever, let alone ACID support.

This isn't to say that there's any problem with the MySQL ACID support per se, but the ACID support is far more mature in Postgres than with MySQL. For my own usage, ACID transaction is the single most important factor, so this maturity is not without value.

Plus, there are practical issues with MySQL and ACID transactions. Because not all table engines support ACID transactions--the default MyIsam in particular does not--it's easy to end up with practical issues. Transactions not working when one expect them to, bizarre behavior when multiple table transactions have different levels of support, that kind of thing.

Administration

This one's harder to judge because what you know is always going to be easier and seem more natural. Still, adding users, granting access, creating indexes, etc. all take fewer commands on Postgres and are, I believe, easier to figure out. Take a look at the 'bin' directory of admin commands in MySQL and Postgres. The former has a few dozen, while the latter has less than a dozen. To my mind, that's pretty strong evidence about which is easier to use.

On the flipside, this is an area where MySQL's popularity comes into play. You'll find more documentation on how to do things in MySQL, but like I said before, you also need more documentation.

Table Engine

MySQL was built from the beginning to use modular 'table engines'. This sounds good (more features are better right?) except that it really complicates things. In serious database uses, where transactions and rollbacks and things like that are in use, what do you do when your table engine doesn't support what you need? Even worse, what do you do when some tables do, and other's don't? You can easily get into situations where very bizarre things happen.

This multiple table stuff makes everything else that much harder. Some tables support some features, others do not. Both usage and administration are greatly complicated by this multiplicity of tables.

Ease of Use

Taking the above all together, the simple fact is that Postgres is easier to use. This is, of course, a subjective statement, and as I said, if you cut your teeth on MySQL, it's going to seem simpler because it's what you know. However, it would be hard to make an objective case for MySQL here, while the Postgres case is pretty easy.

Speed

Historically, speed has been a factor in the discussion of which is better. Speed is still debated, but all in all, my assessment is it's close enough. This wasn't always the case, but now-a-days the discussions generally talk about 10% advantages. Who cares? They're both crazy fast enough, 10% on either side is the last thing a developer should worry about.

Having said that, Postgres has done better recently about getting faster. This is somewhat to be expected as MySQL was the faster to begin with and it's easier for the slower to get faster than it is for the faster to get even faster.

The other issue to be aware of is that you always have to compare apples to apples in speed tests. That means, the InnoDB table to Postgres. MyIsam tables don't use transactions and so of course will be faster (though some reports say even against MyIsam, Postgres is comparable). Most comparisons seem to suggest that Postgres is faster than MySQL-InnoDB.

Finally, speed on a serious database is all about tuning, and this is an area where there's less MySQL help than you might think. To tune a serious MySQL database, you have to tune InnoDB, which has less information available since it's not the default table. All Postgres tuning docs, however, are relevant.

Clustering

In a nod to MySQL booster who's read this far with an open mind, I'll leave with a big win for MySQL: clustering and replication. MySQL has built in clustering/replication and it's fast. Postgres does not, and the replication available is slower.

However, there is a rather big Achilles heel in MySQL clustering: it must put all data in memory. If you have a multi-gig database, then you can't use clustering/replication in MySQL.

The upshot is that MySQL clustering is much better until it fails completely. That being said, even many enterprise DBs can fit into RAM now-a-days, so this weakness isn't as bad as it sounds.

Aesthetics and Quality

When we look at things like the gotcha, and administration processes, I think a fairly good argument can be made that Postgres is, in some ways, the higher quality database. As a gut reaction, I also like the fact that the Postgres forums talk about things like query optimization and fixing bugs whereas the MySQL forums tend to focus more on user questions.

As a user, the latter is obviously useful, but, also as a user, I like to see the former. Why? Because it means that they're concerned about making the product better, not just getting it out there. Again, this is gut talk hear--jaw wagging if you will--but I'm much more comfortable basing systems off of Postgres than MySQL. Even the primary MySQL author agrees that MySQL has real quality problems.

I've never personally had MySQL blow up (just been annoyed by gotchas), so I'm not saying it's *that* bad or anything. Still, I've been developing for a long time, and lots of little things, from the gotchas to the clean directory layout and concise commands to the build process all make me pretty confident that Postgres is the higher quality DB. For whatever that's worth.

Deferrable Constraints

One feature that would be *really* helpful is deferred constraint checking. This is in the SQL standard and basically says that one can set constraints to be checked at the end of a transaction rather than after each command. This is not only nice, but fits with the spirit of transactions. This isn't something one needs very often, but when it comes up, it's a real pain to hack your way around.

Still, Postgres does have partial support for this feature--it supports it for foreign key constraints. That turned out to be just enough to allow me to squeak by with a sane implementation of the solution that led me to look into this feature. It's also heartening to see that Postgres has general deferrable constraint support planned. MySQL has no support and, as far as I can tell, no plans or even hints of support.

In most applications, this isn't going to be a vital feature, but this is one case where I've seen that had I been using MySQL, I would have had to switch to Postgres for purely functional reasons.

Community

In the introduction to this essay, I said that community and commercial focus had both helped to make MySQL the more popular database and did many positive things for the platform. Recently, however, the commercial focus has started causing problems within the community. The problem is this: first Sun acquired MySQL. Sun's relation to the open source community is interesting, but at least they have one. Still, the move caused strain within the community. The recent Oracle acquisition of Sun has caused major problems.

Oracle is a bad company as far as the average open source advocate is concerned. The natural response is to start forking the MySQL code base to keep it safe from Oracle. The problem now becomes which fork to follow? Forking itself isn't bad, and can lead to better projects, cross pollination of ideas, and re-energize technologies.

All this potentially undercuts what is arguably MySQL's one clear advantage: the large cohesive community of users. On the other hand, there is no chance that the Postgres community will fragment any time soon. Nothings happened yet, but things are happening.

References and Further Reading

  1. ↑ The Complete Reference: MySQL. Viswani, Vikram. McGraw-Hill Osborne Media; 1 edition (December 18, 2003). Page 485.
  2. ↑ http://en.wikipedia.org/wiki/PHP
  3. ↑ http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

Further reading:

Notes

  1. ↑ Demanding MySQL isn't foolish, it's demanding anything that's foolish. Nine times out of ten, you've got some guy with some idea that he wants implemented and this random dude who's never done a line of code in his life is specifying language and technology requirements? It's like going to a mechanic and demanding they use metric wrenches on your car.
  2. ↑ Unfortunately, it's hard to find good references on this matter, so what follows is a combination of my own memories, research, and extrapolation.
  3. ↑ Getting a copy of PHP4 could probably answer this, though the situation may change at different points in the PHP4 version history.
Retrieved from "http://www.zanecorp.com/wiki/index.php/Postgres_vs_MySQL"
Personal tools
NEW CHANGED