MySQL -vs- Postgres

[Netopia]

Hey Everybody,

In my ongoing endevour to learn as much as I can, I've come to a slight impass. If you wanted to put forth your best effort towards learning one of the two most popular SQL servers, which would you choose, MySQL or Postgres? WHY would you choose one over the other?

To a slightly different tone, if you had a project and it was on SQL and you didn't have to administrate it yourself, which would you choose and why?

There is SOOOOO much to learn in the *nix world, that I'm trying my best to figure out what items give the most "bang for the buck" or more appropriate might me the "most payoff for the time invested", but you know what I mean. Windows is a first thing to learn the ins and outs of because it's got somewhere over 85% marketshare... but I don't see that trend lasting forever, so I'd like to learn about the eventual (I believe) replacement.

What are your opinions on these two venerable free SQL servers?

Thanks for you time and at least WITH ME, you opinion really does count.

Joe
who's thankful to be a part of The AnandTech Community

 

[Buddha Bart]

Originally posted by: Netopia
Hey Everybody,

In my ongoing endevour to learn as much as I can, I've come to a slight impass. If you wanted to put forth your best effort towards learning one of the two most popular SQL servers, which would you choose, MySQL or Postgres? WHY would you choose one over the other?

Postgre if I was looking to teach myself database design and programming, MySQL if I just wanted to learn basic SQL and what I'm most likely to run into.

To a slightly different tone, if you had a project and it was on SQL and you didn't have to administrate it yourself, which would you choose and why?

MySQL if I needed any sort of complicated load balancing and replication, Postgres if I was creating a relativly complex business app and wanted triggers/stored procedures and such.

There is SOOOOO much to learn in the *nix world, that I'm trying my best to figure out what items give the most "bang for the buck" or more appropriate might me the "most payoff for the time invested", but you know what I mean. Windows is a first thing to learn the ins and outs of because it's got somewhere over 85% marketshare... but I don't see that trend lasting forever, so I'd like to learn about the eventual (I believe) replacement.

What are your opinions on these two venerable free SQL servers?

Thanks for you time and at least WITH ME, you opinion really does count.

Joe
who's thankful to be a part of The AnandTech Community

You have to ask yourself are you 1.) A person who wants to learn about administering database servers on linux, or 2.) a developer who wants to learn about designing and programming for databases. If #1, go with MySQL as its not important that you have every little RDBMS trick up your sleeve, and its far more likely to be experience you can call upon later. If #2 go with Postgres because it supports important things that real bussiness applications need.
If you consider yourself "in the middle" and the sort of person who's probably just gonna put up some web-scripts and such, MySQL is probably a better choice because of all the tutorials, howto's, documentation, and general user base.

 

[n0cmonkey]

Postgres supposedly runs better on BSD. I think it has to do with threading implimentations, but I haven't heard much recently. MySQL is supposed to be faster. Neither of these are things I can backup or prove at the moment, just what I have heard.
I run MySQL on Linux because all the tutorials I found for what I was doing at the time used MySQL.

I'm wondering if this will be as much of a religious war on AT as it would be on other forums and lists.

 

[Sunner]

I've hard many complaints about MySQL, ranging from not being able to handle heavy loads well, to lots of minor and some major deviations from standards.
Never had any problems myself, but I've never used MySQL for anything serious, forum database, homemade inventory system written in php, etc.

 

[burnedout]

Informative comments so far.

I dived into mySQL more as a matter of need than anything else. We have requirements at work for simple database-driven applications. mySQL, along with PHP, addresses these requirements quite nicely.

 

[Sunner]

I might add by the way, that compared to MySQL, PostgreSQL's documentation blows.

 

[Netopia]

Thank you everyone for the comments thus far. Buddha Bart, your "choose A if... choose B if" made a lot of sense.

I think that I'm going to end up in the MySQL boat since what I'll mainly be doing for learning projects are BBS's that use SQL and maybe some photo-galleries. All this just for extended family, so I'm not thinking that there will be gobs and gobs of data. As you've just about all stated, MySQL has better doc's, which is a HUGE advantage for a newbie!

Anyone else going to chime in?

Joe

 

[jarich]

I don't have much experience with Postgres, but I've used MySQL a ~lot~. In regards to the load it can handle, at my last company we had a 70+ gig database with more than 250 million rows in various tables and it was great. We used it for live data access. It's much faster than Oracle or MS Sql Server (out of the box) for our application.

MySql runs very fast 'out of the box' and if you need to move to really big databases, it's can be tuned to handle that too.

 

[hasu]

I have tried both of them just for the sake of trying!
I found Postgresql to be better because of its features.
MySQL didn't support transactions (which is very much required to guarantee data integrity). I think newer versions of MySQL has (or is in the process of having) transaction support.
Whereas PostgreSQL is free with no strings attached, MySQL is not free for commercial use (but it is very cheap compared to other proprietory databases). I think, for enterprise level of applications PostgreSQL will better suited compared to MySQL. (PostgreSQL will have native widows counterpart soon).

 

[Buddha Bart]

Originally posted by: hasu

MySQL didn't support transactions (which is very much required to guarantee data integrity). I think newer versions of MySQL has (or is in the process of having) transaction support.

If you use the table type "InnoDB" transactions are supported. Unfortunatly this is not the default table type, so you have to know to specify it.
http://www.mysql.com/doc/en/ANSI_diff_Transactions.html

Whereas PostgreSQL is free with no strings attached, MySQL is not free for commercial use (but it is very cheap compared to other proprietory databases).

MySQL is free as in freedom, licensed under the GPL
http://www.mysql.com/doc/en/Using_the_MySQL_software_for_free_under_GPL.html