The Teaser

I hope anybody reading this doesn't need to be told that PostgreSQL is an open-source database management system (DBMS) whose primary competition is MySQL. But just in case you do, a DBMS is a system for automating the organizing, storing, labeling, retrieving, and updating data, typically large or larger quantities of it. Oooooh.... Turned on? Keep reading...

Committed

I've run my humor site http://www.navehumor.com on the back of a MySQL database for nearly a year now and I'm generally pleased with MySQL, not to mention rather heavily invested in it (in time, not money) You can read my review of mySQL if you like, it's at: http://www.epinions.com/ensw-review-7BC-151CAD2C-39F37132-prod5 My humor database is fairly straightforward--the two main types data are jokes and members. MySQL has been criticized for not being fully compliant with SQL92 (i.e. not transactional oriented) and not as flexible. This weakness is actually its strength. When you drive a Corolla, you just drive it and it goes. When you drive a higher performance beast, you have to spend more time and energy tinkering with it to get the performance you paid for. The same is true of DBMS's.

But... sob sob... you said you loved ME!

So why am I flirting with that sexy other DBMS postgreSQL if I am already commited to MySQL? Well, maybe because it' s so easy. PostgreSQL was included my RedHat Linux install and it comes with other Linux flavors as well, and it's FREE. Or maybe it's because I took on myself the task of implementing a major opensource project and snatched the last version of the distribution just before they went commercial. This project has higher income bracket database needs and it comes already set up to run postgres.

Vroom Vroom!

Yes postgres is a higher performance machine. As configured out of the box, my benchmarks had mySQL running faster, but PostGRES has more aspects that can be optimized. It has some pretty high-horsepower concurrency features as well as several levels of security. It has a query optimizer which would be nice for folks who (unlike yours truly) don't always write optimal queries. It has a rollback system. The documentation specifies that if you have a series of updates that need to be performed in an "all-or-none" fashion to preserve your data integrity, Postgres lets you lock them, although I didn't test this.

Ka-splut!

The downside of all this transactional stuff is that postgres leaves chaff laying around, which you occasionally have to "vacuum." The other downside is that I believe more companies run MySQL so there would be a smaller pool of experts for Postgres than for MySQL. More active users in an open source project translates to more pro bono support and more third party software, though to be fair to Postgres, they have an active support system on their pages, as well. Postgres has not crashed on me yet but I have not truly put it to the test yet. It stands to reason that it would be a little less reliable, being that it is more complex. Unlike MySQL, postgres does not "do Windows."

User interface

The only truly intuitive interface is the nipple. (straight off NaveHumor) Everything else is learned. One could argue that keeping everything confined to a program as mySQL does is more intuitive, or conversely, the fact that postgres has commands for single functions makes it easier to do things without opening a lot of doors. I'm not gonna get involved here.

It all boils down..

to the following: Is your data complex and do you need rollbacks? Are you willing to spend the time to get the performance you deserve? Will you have Huge Enormous gigabit datafiles? Go with postgres. If your data is straightforward and relatively small go with MySQL.



Recommended: Yes