Slashdot Mirror
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.
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.
Javascript + Nintendo DSi = DSiCade
From the site:
"Last modified: February 15, 2005."
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?
thank God the internet isn't a human right.
postgresql has a native Win32 version, complete with an installer, service support and does not depend on cygwin.
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.)
My blog: http://www.seebs.net/log/ --- My iPhone/iPad app: http://www.seebs.net/seebsfrac/
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.
-1 Uncomfortable Truth
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.
Whence? Hence. Whither? Thither.
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.
Easy Online Role Playing Campaign Management
That'd be because the article was written in 2005. Unbiased? Maybe. Vague, unscientific and out of date? Definitely.
"The invisible and the non-existent look very much alike." -- Delos B. McKown
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.
ajf
Is that the same referential integrity and ACID compliance afforded by using INNOdb as your table type in MySQL? ;o)
I am NaN
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.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
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.
Untrue.
e rcial-license.html
...
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/comm
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.
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, 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.y k/db_compare/db_compare.html
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/ttracz
I did a presentation at the Atlanta Unix Users' Group this month that is a more up-to-date comparison. It's available in Open Office format. You can also get to it from my home page. 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.
> 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 if you're so inclined.
The Army reading list
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
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.
I am NaN
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.
Yes. You can sell it for as much as you like. Just remember to make the source available :)
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.
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.
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.
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/ (large scale repository for data analysis)
And how would you use Postgres for something like that anyway? Maybe something like:
http://www.greenplum.com/ (biggy-sized Postgres based data warehousing)
Why, o why must the sky fall when I've learned to fly?
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
You use MySQL, don't you?
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.
evil is as evil does
A more recent review of the quad core xeon: http://tweakers.net/reviews/661
Linux 2.6.18 vs 2.6.15: http://tweakers.net/reviews/657/2