Which Database?

[bakaDeshi]

To mods: I wanted to post this in the Dev forum but no polls there. Please move as you see fit.

I'm starting to learn about dynamic web pages with php, perl, etc. and need to learn about databases. My choices probably boil down to MySQL or PostgreSQL mainly because of cost but I was interested to hear what other people use and which database is considered the best or if different dbs have different strengths for different tasks. (and I wanted to post a poll;))

p.s. I listed Oracle as well even though it doesn't run on X and is beyond most budgets.

[Cobalt]

Originally posted by bakaDeshi
I'm starting to learn about dynamic web pages with php, perl, etc. and need to learn about databases. My choices probably boil down to MySQL or PostgreSQL That choice will be a difficult one. Here's my list of the pros and cons for each.

PostgreSQL (a.k.a. PGSQL): Its license really makes use of the DB free. The MySQL license technically restricts you from using it in some commercial applications, even though people use it anyway without paying.
The command line tool for PGSQL, called psql, seems to have a lot more help functionality and info-providing tools than the MySQL command line tool. It certainly makes work inside that tool easier when dealing with PGSQL than with MySQL.
PGSQL has historically been much more of a true relational database management system - ie. it has more relational and advanced functionality. (Note that I'm not a professional DB admin, so I may not be using terms properly). There have been flame wars about, for example, transactions support.

MySQL: There are lots more open-source packages (eg. PHP or Perl scripts) that work with MySQL than with Postgres (although some can use either)
There may be more people out there to help answer questions.


I've used both, but I prefer PostgreSQL by far. It's been gaining a lot in popularity in the last 6-12 months, so you'll be seeing more and more of it. Of course, from the perspective of writing PHP code, there are ways of writing your code so that you don't care which DB you're using (check out PEAR at pear.php.net for details). But of course, if you want to really delve into the depths of database capabilities, you'll want to make sure your database can support the feature you want (and you're more likely to find support in PostgreSQL than in MySQL).

Two geek friends of mine, both of whom come from a *nix background, surveyed the choices and both also went with PostgreSQL. They decided that it was more full-featured, had a better license for use, was more stable, etc.

[mikemc]

I voted MySQL, because I like it.

While I advise learning the command line to manage it in a pinch, after you get the basics down, then you can move to the nice phpMyAdmin for managing the database, which is great!

[9KILLER]

With MySQL and Perl, we can rule the world! That's practically what the MySQL website says, anyway. And they're not far wrong. Actually, you should see some of the amazing stuff that my company has produced using LAMP (Linux, Apache, MySQL, Perl) compliant solutions.

(I guess XAMP would be the new twist - Mac OS X, Apache, MySQL, Perl)

PowerUP (http://www.powerupdbml.com) is our home-brewed solution that freakin' rocks. (that site is still under-development, BTW. Busy, busy, busy.)

[Vanish]

Like most things *nix, this is a religious battle more than anything.

PostgreSQL has a few more features than MySQL, this is true, but very few, and most not used by the average joe. MySQL is catching up fast, and will shortly surpass PGSQL. One HUGE benefit to MySQL is it's active development team, and their incredible drive to provide the "right" solution, instead of the fast feature. PGSQL, on the other hand, is uch better at handling large databases than MySQL is. Again, that's bound to change.

The single biggest benefit to using MySQL over PGSQL?

Ease of use. if you're new to this arena, grab MySQL. There are volumes of more examples for interaction with, and control of MySQL than almost any other database solution in existence. (Trust me, I know. I'm a DBA / Webmaster. I work with all, from Oracle to MSSQL to MySQL to FileMaker Pro)

Speaking of new to dynamic development, use PHP and install the ADODB lib designed for it ( http://php.weblogs.com/ADODB ). It's MUCH easier to manipulate the database with it, and transfering from one database to another at a later date (Say, MySQL ot PGSQL) is as simple as changing one word for the entier site.

Originally posted by Cobalt
The command line tool for PGSQL, called psql, seems to have a lot more help functionality and info-providing tools than the MySQL command line tool. It certainly makes work inside that tool easier when dealing with PGSQL than with MySQL.

This is semantics and nothing more than personal preference. Everyone needs to make their own decisions about this. I won't say either one is more versatile or easy than the other.