Blog This!   Lee Geistlinger's Web Log

Blog Home
Blog Archives
LittleGhost Home

E-mail: geistlinger AT gmail.com

Everyone loves lists Books, Movies and so on

My Online Aggregator

Database Notes

Just for fun - because I'm not - I recently rebuilt an ancient box I had Linux running on.

I wiped the hard drive and started from scratch.

I've installed Linux a fair number of time now (always from CD; still not a full-blown propeller-head), and so it was pretty much of a no-brainer.

The interesting part - to me - was getting the two databases I installed up and running: mySQL and Postgres.

Again, I've installed each of these at least three times in the past (mySQL on Windows, as well). But I never brought them up at the same time, attempted to get a "production type" system in place with each of them at the same time.

So the contrast was interesting.

Some observations:

• Ease of installation: This one is not even close: mySQL wins hands down. I've never had a painless Postgres installation - why do I have to build all these directories etc to start the database? Sure, if I want the database files somewhere else, I should be able to in a config file...why doesn't this exist? mySQL, while less flexible, it more of a Windows-like install (you just install and it's there, like where it is or not)


• Migrating data from existing databases: mySQL wins again. While there is the file import/export for Postgres - as well as pgdump - the tools out there for mySQL (I'm running SQLyog on Win2000) make migration a breeze. The tools that are out there for Postgres either 1) Suck, or 2) Aren't up to the level of the mySQL tools (like SQLyog). That's a problem.


• Security (locking down for Internet exposure): Postgres wins. The security model for mySQL - if you can even call it that is weak (GRANT....). I'm probably missing something, because I'm more of a SQL Server user than OSS database user overall, but ... wow... weak. Postgres' security is pretty damn good, which is to be expected from a database that is touted as an almost-Oracle RDBMS. Note: One of the reasons that Postgres is more cumbersome to set up is because of the security: You have to have a non-ROOT user running the server, recommended to add another user, set permissions on the database directories to reflect this etc. More work. More security. Depends what you need the database for.


• Ease of use (once installed and configured; data set): Let's see. Postgres is ANSI 92 compliant, an ACID database, supports stored procs etc......mySQL lacks subselects (that's my biggest beef with mySQL) and has weird - proprietary SQL (can you say CONCAT? sure, I knew you could...). Yes, Postgres wins hands down.

But - as mentioned above - it's what you need the database for. Like Cold Fusion, mySQL is "not up to playing with the Big Boys...", but how often do you play with the Big Boys?

Yeah, mostly it's whacking together a quick solution to a problem. mySQL is much faster to get up and going for simple tasks.

It's all about the best tool for a given job. My prejudices and preferences fall in the Postgres camp, but there are times when I need something simple and need it fast.

mySQL is there for me.

And yes, it's all about choice, as well. And with these two out there - goosing each other to some degree - we all win.