Topic: PostgreSQL vs MySQL

It seems that just about every coder I respect out there preaches pgsql.

The usual argument is that MySQL corrupts data and bombs out under heavy loads.

I went with MySQL since is the default recommendation for Rails, and was not too difficult to learn.

Now in my experience, it has performed like a champ, but I am curious to hear what you guys prefer. Any MySQL horror stories/pgsql zealots out there?

Re: PostgreSQL vs MySQL

I've had some trouble with MySQL on a good size PHP site. Once and a while it hogs up all the processes and goes crazy. But then again I may be overloading it a bit. If I ever switch to PostgreSQL, this would be the reason: performance. I have heard out of the box it isn't any better than MySQL, but once it's properly configured it can be faster. I'm going to give MySQL one more chance and try to properly optimize it, but if that doesn't work well enough I may give postresql a try.

I have also heard PostreSQL offers more when it comes to adding constraints. I keep the constraints in Rails and not on the database end so this doesn't really interest me.

Railscasts - Free Ruby on Rails Screencasts

Re: PostgreSQL vs MySQL

Well that's +1 coder I respect who is on MySQL, but it seems like you are borderline. I have heard many horror stories about MySQL, but they all involve PHP..maybe there is something to that.. you would think that such a widely adopted platform would be rock-solid, but sometimes thatjustisnt the case.

Im gonna keep jamming with MySQL for now..now that I am in deployment phase, I got enough things on my plate that I need to learn..

Re: PostgreSQL vs MySQL

I think a lot of the problems you hear with MySQL come from people who are experienced in more robust enterprise level RDBMS.  Below are some things I lifted from a website comparing SQL Server and MySQL.

    *  MySQL does not support Triggers, SQL Server does.
    * MySQL does not support User Defined Functions, SQL Server does.
    * MySQL does not have Cursor Support, SQL Server does.
    * MySQL does not support Stored Procedures, SQL Server does.
    * MySQL does not have FULL JOIN capabilities, SQL Server does.
    * MySQL has limited Import/Export capabilities, SQL Server has full support for Import/Export.
    * MySQL has limited Transaction Support while SQL Server offers full Transaction Support.

From what I've read it sounds like PostgreSQL has more of these features that make it more of a "real" RDBMS.  Had people in the PHP community adopted PostgreSQL over MySQL I don't think you'd really hear much about MySQL.  It's an easy DB to install, it's widely available on web hosts, it's got a lot of free tools (PHPMyAdmin) that make it easy to "administer" and it's free and easy to get.

I would LIKE to learn more about PostgreSQL but I don't see it as something that's instrumental in my development as a... developer. big_smile

Re: PostgreSQL vs MySQL

I think many of MySQL's problems stem from the 3.x days. I have never had problems with MySQL, and I've been running many popular sites with it. Since MySQL version 4.1 basically it's been absolutely rock steady.

Just use the InnoDB engine and not MyISAM, even though I have never had corruption on MyISAM. But InnoDB has support for rollbacks etcetera, which myisam lacks.

email the future with www.l8r.nu

Re: PostgreSQL vs MySQL

I'm pretty sure that not all of those comparisons to SQL Server even apply anymore.  As of MySQL 5.0, stored procedures are supported and I believe user defined functions are too.

Re: PostgreSQL vs MySQL

Strangely enough, the first DBMS I learned to use was Postgres because it was used as a basis for projects in an introductory DMBS class.  I never found compiling or setting up Postgres to be challenging.  From there, SQL is standard, and the Postgres documentation is great.  Then again, I haven't given MySQL a serious try either, so I don't know what I'm missing out on.  Perhaps Postgres is more of a headache on Windows clients.

Because it's what code craves.

Re: PostgreSQL vs MySQL

I really wonder if you can do subselects in mysql. I really can't understand how someone can live without this feature. Also the user management of postgres is more logic to me.

regards,
red

Re: PostgreSQL vs MySQL

redrat wrote:

I really wonder if you can do subselects in mysql. I really can't understand how someone can live without this feature. Also the user management of postgres is more logic to me.

regards,
red

I think you can, at least with more recent versions of MySQL.

10

Re: PostgreSQL vs MySQL

redrat wrote:

I really wonder if you can do subselects in mysql. I really can't understand how someone can live without this feature. Also the user management of postgres is more logic to me.

regards,
red

Subqueries have been in Mysql since 4.1 or so I believe (and before then you could usually have rewritten the query to use a join instead), and stored procedures and views are in 5.x.

vinnie - rails forum admin

Re: PostgreSQL vs MySQL

That's odd, I've always heard pretty much the opposite.  I've always read that MySQL was not including a lot of those features because they didn't want to just throw it together like PSQL or MS-SQL did.  MySQL has always been very strict about quality and performance requirements which is why development has been slower even though it is many times more popular than PSQL.

Anyway, as long as you keep all your coding in Ruby and use rake to do all your database configuration, you should be able to seemlessly switch between any of the Rails-supported databases (in theory).

From personal experience though, I admin the server that houses a government elections website and on election days, the site gets thousands of hits at a time from local citizens in addition to the data being continuously updated as election information comes in.  The database we use is, of course, MySQL and it has run without a hiccup.

Last edited by jdinkel (2007-05-21 10:05:13)

Re: PostgreSQL vs MySQL

Visit http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL for a comparison between the two database engines.

I personally go with PostgreSQL because it can be tweaked to be faster than MySQL, and because it has more to offer. It offers better support for checks, constraints, and triggers.

MySQL is better, I think, for people looking for raw speed or replication.