PostgreSQL vs. MySQL comparison

prostoalex writes "Ever find yourself wondering which open source database is the best tool for the job? Well, wonder no more, and let your tax dollars do the work in the form of Fermi National Accelerator Laboratory publishing this unbiased review of MySQL vs. PostgreSQL. After reading it, however, it seems that MySQL ranks the same or better on most of the accounts." My poor sleepy eyes misread the date of posting on here; caveat that this is more then 15 months old.

No Digg

[AKAImBatman]

1. There's no such thing as unbiased. Especially on a page that gives a fairly abstract review.

2. This article is 2 years old. Everything in its comparisons is out of date.

Old news

[daffmeister]

From the site:

"Last modified: February 15, 2005."

stability

[oliverthered]

Having foreign keys, views, subselects, and transactions can all be very attractive in PostgreSql -
if you need them and you will make any use of them. If you don't need them or won't use them, then
you're probably better off with MySQL and its superior performance.

PostgreSql is more stable than MySQL, (and has better performance when saturated), shouldn't you take that into consideration?

Old and wrong

[ldapboy]

postgresql has a native Win32 version, complete with an installer, service support and does not depend on cygwin.

Not similar to my experience

[seebs]

I have been involved with a smallish ("hundreds") installation of Movable Type using a mysql backend.

One comment spammer can completely annihilate it.

One developer I talked to once did some testing. On one simultaneous connection, mysql was way faster. By five or so, they were close. At ten, PostgreSQL was definitely winning. At a hundred, he was simply unable to get a single MySQL server to complete the test successfully, let alone do it quickly.

The impression I get is that PostgreSQL uses more robust algorithms, with higher constant costs and lower quadratic costs. In any event, never had any problems.

As noted elsewhere, these comparisons are quite old...

But in any event, in my own experience, mysql is a lot easier to blow up by overloading than postgres is, at least if you have a lot of writes going on. For pure-lookup functions, it might do better -- but a lot of modern database apps are pretty compulsive about saving at least something every time someone touches them. (For instance, modern vBulletin saves last visits, threads seen, and so on; all of that adds up to a huge load on the database server.)

Re:Foreign Keys

[ShieldW0lf]

This is unbiased? Give me a break.

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2 and has most of their concerns addressed in that release, whereas MySQL is still at 5.0?

MySQL is a great database, if you need clustering but not referencial integrity or ACID compliance, that is.

There are several problems

[kahei]

1 -- This article is years old.

2 -- This article is posted solely to stir up (repetitive) discussion.

3 -- This article pretends that MySQL is a real database, even though in order to do so it has to make gigantic leaps like considering data integrity to be not really all that important in a database.

4 -- This article trolled me.

Outdated and Silly

[ClayDowling]

It's been a long time since any of their PostgreSQL statements were true. It's a very happy native windows service with a nice installer, and the administrative interface is very easy to use. Let's try posting current reviews of software, rather than reruns from a year or two ago.

Re:Foreign Keys

[Anonymous Coward]

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2 and has most of their concerns addressed in that release, whereas MySQL is still at 5.0?

That's because the comparison is dated Feb 2005.

Re:Foreign Keys

[Tet]

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2

That'd be because the article was written in 2005. Unbiased? Maybe. Vague, unscientific and out of date? Definitely.

Re: PostgreSQL is easy too

[ajaf]

You don't need to setup anything to run it for the first time, only if you want to play with performance, you can start to modify parameters as memory, max connections, etc. PostgreSQL is easy and powerful, just give it a try.

Re:Foreign Keys

[Phil John]

MySQL is a great database, if you need clustering but not referencial(sic) integrity or ACID compliance, that is.

Is that the same referential integrity and ACID compliance afforded by using INNOdb as your table type in MySQL? ;o)

Crap!

[CaptainZapp]

MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation.

I call pure, unadulterated crap on this one.

One of the major new features in Postgresql 8 was native Windows support. It runs just fine as a service.

This comparision is either very old news, incompetence in action, or, um! strongly biased.

Re:No Digg

[electroniceric]

Just to continue on your good points, especially troubling is the fact that this article compares the then-unreleased MySQL 5 to the Postgres 7.x series. Nearly all the drawbacks to Postgres that this article highlights have been addressed in the 8.x series.

We run Postgres for our main business application and the main limitations are of two forms:
1) Depth of community
The Postgres community is great - very responsive and knowledgeable, but its size is a limitation in a number of ways. The ODBC driver is a bit of stepchild to the main project, and some key functions like dblink that address missing features like cross-database selects are relegated to /contrib, and rely on their individual authors for nearly all maintenance. This means that as a user you are more likely to bump up against the bleeding edge earlier than in communities where these outside-the-core projects are more supported.

For the same reason a key subset of its documentation is very sparse. Documentation for the core system is thorough, clear and concise, but anything in contrib or any projects like the ODBC or .NET drivers are much less like to have the same quality of documentaton. Postgres' extremely powerful GIST indexes are unparalled as a feature, but you need a background in theoretical CompSci to figure them out, thanks to limited documentation (note to aspire database index geeks - I would gladly buy a book on GIST aimed at proficient DBAs who are not giants of theoretical CS). Likewise its procedural languages: thanks to its architecture and open codebase, Postgres offers more server-side languages than any other database that I know of, but few of them have more than basic documentation, let alone the stacks of books you'd find with other procedural languages.

2) Postgres is very close to being a true enterprise contender (unlike MySQL, which is evolving that direction but distinctly further off), but lacks some key features like XML handling, a more comprehensible approach to result sets (anyone who's dealt with rowtypes and casting resultsets can attest to the steep learning curve), and a userbase that has put the product through the wringer. Now that some corporate heads are getting interested (e.g. Sun, Red Hat, EnterpriseDB) hopefully some of these shortcomings will be addressed in short order.

Don't let this outdated, apples to oranges comparison fool you: Postgres is a very solid and usable database.

Re:Foreign Keys

[Sparr0]

MySQL has no restrictions on commercial development. They have restrictions on non-GPL distribution. Just like every other GPL-ed product on the planet. Nice try.

Re:Foreign Keys

[Tony Hoyle]

Untrue.

The client library is GPL. That means you cannot create a commercial program that uses it without using the commercial licensed version. Which is $200 per client

You can't even create a library and not ship mysql - the mysql site is very clear that they consider distributing a program that *uses* mysql as being exactly the same as distributing mysql itself:

http://www.mysql.com/company/legal/licensing/comme rcial-license.html [mysql.com]

Typical examples of MySQL distribution include: ...
        * Selling software that requires customers to install MySQL themselves on their own machines.

Specifically:

        * If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries.

This makes mysql unusable for anything except large products. Our entire product only cost $70 for the single user version. No way in hell we're upping the price by $200 a copy.

Re:Not similar to my experience

[walt-sjc]

We use both PostgreSQL and MySQL for a large web-based application that does a reasonable mix of reads / writes (sessions / profiles are in MySQL so it gets MANY MANY writes.) Neither MySQL nor PostgreSQL has problems handling many many connections. Our load frequently hits around 1000 connections on Postgres and 4000 on MySQL on individual database servers (we replicate too.)

Obviously you need to tune your environment (there are a plethora of options including table types which can impact things a LOT) to the load, so if you are running into problems at 100 connections, something is wrong.

Re:Foreign Keys

[Tony Hoyle]

An app can do its checks with full knowlege of the structure of data it's writing, and often it's just the comparison of a couple of integers anyway and have no impact on speed. You don't want to rely solely on the DB to do that... you end up having to handle a lot of nasty exception cases. Far better to avoid them first. Put foreign keys in, but definately don't make them your first line of defence.

Ok, here is another outdated test

[xyvimur]

Ok, this is yet another outdated report comparing three mainstream RDBMS'es - MySQL, PostgreSQL and ORACLE. It was done for yet another physical experiment - for choosing the proper system for storing data about the construction process of one of the LHC detectors - ALICE.
And this report is at least professional, which cannot be said about the one mentioned in the article.
http://dcdbappl1.cern.ch:8080/dcdb/archive/ttraczy k/db_compare/db_compare.html [dcdbappl1.cern.ch]

Re:Not similar to my experience

[Wdomburg]

For all it's faults, MySQL does scale with a largely read-only data set. We currently have twenty-eight production servers running and about twenty development and testing machines. On the busiest servers we're pushing somewhere in the neighborhood of 4000 queries a second sustained.

Write performance can certainly be an issue, but it depends largely on the application and the table backend. For example, if you can avoid doing deletes on a MyISAM table INSERTs get appended, allowing concurrent reads.

I've not looked at how MT uses the database, but blog software would imply large variable length writes. Definitely not an ideal application for MySQL.

a more up-to-date comparison

[brentlaminack]

I did a presentation [laminack.com] at the Atlanta Unix Users' Group [auug.org] this month that is a more up-to-date comparison. It's available in Open Office [openoffice.org] format. You can also get to it from my home page [laminack.com]. I did a similar talk almost four years ago. My conclusion is that MySQL has closed the feature gap with PostgreSQL in recent years. I still give PostgreSQL the edge in features, and MySQL the edge in out-of-the-box untuned performance. I also discuss replication and clustering.

Re:Why we moved from MySQL to PG

[tcopeland]

> So, we run a full vacuum and reindex
> of our tables nightly through cron.

I've found that just enabling autovacuum seems to keep things in order. And you can tweak it for individual tables [postgresql.org] if you're so inclined.

Here's a recent (Nov. 2006) Performance Review

[drake]

Here's a comprehensive performance review between PostgreSQL and MySQL. It compares both DB's under load as well as comparing Intel/AMD chips. http://tweakers.net/reviews/657/6 [tweakers.net]

Re:Not similar to my experience

[AKAImBatman]

THERE IS NO ODBC FOR LINUX (or equivalent).

What the hell? If that were true, then what is this? [pgfoundry.org] And this? [unixodbc.org] Not to mention this. [mysql.com]

Are you making a reference to Windows-specific APIs or something? Because I don't understand how you can shout that ODBC doesn't exist for Linux/Unix?

Re:Foreign Keys

[Phil John]

1. MySQL isn't the only database where you have to declare foreign keys, so that one's a non issue. I don't want a database to "guess" what my FK constraints are, I want to tell it explicitly.
2. True, but most of the tools I use to create DB schemas do this automatically now (phpMyAdmin et al)
3. It's a good idea to have indexes on foreign keys, as you'll often be joining on them. As for silently ignoring, I've never seen that - I get an error message if I try to add a foreign key with no indexes created.
4. Not so much of an issue these days, every build I've used recently from ones for Mac OS X and those for Fedora/ES/CentOS always have it compiled in. Even cheaper hosting accounts now very often have InnoDB support compiled into their MySQL installs.

Don't get me wrong, MySQL is far from perfect, but if used properly (like any tool) it can power some very cool stuff.

We always make sure that our apps work on multiple database servers as we realise our clients don't need a one-size-fits-all solution. We used to have to work around MySQL's limitations, but these days that's no longer as big an issue.

Re:No Digg

[leonmergen]

Does it have clustering yet? How about load ballancing? Those were the main issues that stopped our deployment of postgres.

Yep, it does. [postgresql.org]

Re:Foreign Keys

[Anonymous Coward]

MySQL is a great database, if you need clustering but not referencial integrity or ACID compliance, that is.

...which is not a scenario which never comes up, as a developer. For websites and the like, that is far closer to the reality of the situation. (I know defending MySQL is taboo however, and I've seen others make this point more elegantly than I am willing to spend time doing at the moment..)

To be honest, my biggest personal "pet peeve"* is on that list as well: "\d" vs. "show tables". And to be fair, this is a problem that arises from SQL being a collection of so many different standards, that aren't (?) accessible as a reference card to the average database application developer. Instead, you end up using the manual for the DBMS you're using as a cheat sheet... and I find MySQL's syntax, where (presumably at least one of the two) varies from the standard, to be far more intuitive. This is a useful "feature", as a developer.

* and by pet peeve, I do not suggest that this is "as important as" things like ACID.. but it is one of the (many) things that keep MySQL in the "good enough for 90% of my projects" category.

Re:Foreign Keys

[Crizp]

Yes. You can sell it for as much as you like. Just remember to make the source available :)

Re:Foreign Keys

[insignis]

As both a DBA and programmer, I enforce referential integrity at the database level. I cannot fathom why you'd implement it at a code level--if you don't trust your DBMS, why are you using it? The database is the only common chokehold in a multi-user, multi-application environment. To implement it at an application level, you'd have to try to somehow synchronize communication between all those apps, some of which you might not maintain, and this would only be complicated by each app instance having its own transaction open. The database is already handling those transactions, which makes enforcing referential integrity easiest to do at the database level.

MySQL short on features

[Frater 219]

Does the Internet's favorite DBMS have an IP address datatype yet?

How about MAC address? CIDR block?

"An IP address is just a 32-bit unsigned int, duh. Any DBMS can store those."

Wrong. A datatype isn't just about storage, but also about operations. In PostgreSQL, when you do a SELECT across a table with IP addresses in it, you get them formatted and displayed as IP addresses, not as opaque ints. Likewise with CIDR blocks, like "192.168.42.0/23". There's also a comparison operator for asking whether an IP address is within a CIDR block.

If you're implementing a network registration system or an incident logging system, how much of your time do you want to waste staring at opaque ints like 3232246272 rather than IP addresses like 192.168.42.0 when you're trying to debug it?

MySQL is a bimbo, a fratboy: it's easy, but so shallow! The amount of time you save in one-time setup, you will lose many times over in all the little annoyances and deficiencies of a DBMS that was originally designed by folks who didn't really believe in DBMSes. Over time they've slowly been shamed into including many of the features they used to despise: transactions, relational integrity checks, and so on. But there's still so much missing ... not just essential integrity features, but little fiddly bits like good datatype support, the kinds of things that make your life easier (as a programmer or as a DBA) in the long run.

Re:Foreign Keys

[emurphy42]

It's worth pointing out that the parent is not being sarcastic. You can sell it for whatever price you like - but you still have to make the source available, and you can't place any restrictions on your customers selling copies for whatever price they like. In practice, this tends to drive the cost of GPLed software down to $0.

I'd like to see...

[spurioustruth]

How about an article on that neat analysis framework Fermi (and others) use for the terabytes of data they generate and have to sift through?

http://root.cern.ch/ [root.cern.ch] (large scale repository for data analysis)

And how would you use Postgres for something like that anyway? Maybe something like:

http://www.greenplum.com/ [greenplum.com] (biggy-sized Postgres based data warehousing)

Re:Foreign Keys

[dubious9]

I didn't think you could sell GPL software, only support. I thought with GPL it also had to be free as in beer. Am I wrong?

Yep [gnu.org], your wrong. It's a common misunderstanding though. You can charge 14 billionty dollars for GPL'ed software, you just have to be able to provide the source code for said software for a nominal fee.

This Was a Hit Piece - Better Data Here...

[skeeterbug]

i was using pgsql 8.x back in 2005 - so it existed. they just cherry picked versions to get the result they wanted. postgresql.org linked to a new study where both current versions (at the time, and it was recent - pgsql is 8.2 now) of mysql and postgresql were put throught the paces... http://www.postgresql.org/about/news.691 [postgresql.org]

Re:Foreign Keys

[Anonymous Coward]

You use MySQL, don't you?

Re:Foreign Keys

[killjoe]

Personally I think putting business logic in the database is wrong. It's harder to debug, harder to version control, harder to write.

Putting some code to handle data integrity is OK but not business logic. Performance isn't everything. I would rather have an application that runs slower and is easier to maintain then the other way around.

"unless the application is written in C. Databases are generally written in C/C++. Applications are generally written in Java,Perl,Python,Ruby. None of these can compete with C."

This is nonsensical. If I write in python the python VM compiles and then runs my code. The VM is written in C. If I write in PL/PSQL then the postgres interprets my PL/PSQL line by line (it never gets compiled). Running your business logic in java should be significantly faster then writing it in PL/PSQL except when the application needs to transfer an ungodly amount of data over the network.

Re:more recent benchmarks

[Anonymous Coward]

A more recent review of the quad core xeon: http://tweakers.net/reviews/661 [tweakers.net]
Linux 2.6.18 vs 2.6.15: http://tweakers.net/reviews/657/2 [tweakers.net]