Which is better for production: MySQL5 or PostGres8.1?

Rails
1

I’m coming from an Oracle and SQL Server background. I’d like to use
OpenSource for my next RoR project. MySQL5.x and Postgres8.1 seem to be
the
most popular. I’d like some comments on which would be the better choice
for
a production environment and why. Here are my initial concerns in order
of
priority:

  1. Works well with RoR. ( migrations, etc)

  2. Stable enough for production use.
    a. Linux ( probably Fedora Core 5 )
    b. Windows

  3. Scales well. ( This will be a full replacement of a client server app
    with potentially hundreds of users)

  4. Views

2

“Larry” == Larry K. [email protected] writes:

I’m coming from an Oracle and SQL Server background. I’d like to use
OpenSource for my next RoR project. MySQL5.x and Postgres8.1 seem to be the
most popular.

If you’re used to real databases, MySQL will drive you up the walls.
Save your sanity and go with PostgreSQL.

	     Calle D. <[email protected]>
	 http://www.livejournal.com/users/cdybedahl/
  "Data on the network has cooties and will EAT YOUR BRAINS"
		   -- Ross Younger, BofhNet
3

Postgresql fits all four of your criteria. I’ve had stability and
performance (and data integrity) problems with mysql. Since I switched
to Rails though, I don’t use views any more. Nor stored procs (except
for triggers that can’t be done in Rails).

Joe

4

If you’re used to real databases, MySQL will drive you up the walls.
Save your sanity and go with PostgreSQL.

Care to elaborate?

Regards,
Rimantas

http://rimantas.com/

5

If you’re used to real databases, MySQL will drive you up the walls.
Save your sanity and go with PostgreSQL.

Care to elaborate?

Please don’t elaborate :). The net has seen more than enough threads
about pgsql vs MySQL. Google will probably give you thousands of hits.
Please do go there.

37signals, The Robot Co-op and others use MySQL. Shopify, CDBaby, and
more uses pgsql. I don’t think it really matters too much at this
point. If you’re using all the Rails techniques and working with an
application database (instead of an integration one), your database
engine should be more or less interchangeable anyway.

David Heinemeier H.
http://www.loudthinking.com – Broadcasting Brain
http://www.basecamphq.com – Online project management
http://www.backpackit.com – Personal information manager
http://www.rubyonrails.com – Web-application framework

6

I forgot - one problem I recently had was the nasty ripping out of
postgresql’s default handling code.

As for rails devs favoring mysql, just talk to robby (on rails).

7

I would choose postgres or firebird over mysql, based on the DBMS
features alone.

I have one vendor of a mission critical software service that is on
mysql, and they had frequent downtimes because of DBMS problems.

Postgres and firebird both offer full ACID compliance, which mysql does
not (yet). With InnoDB being purchased by Oracle, mysql is looking for
a new back end that will most likely be ACID compliant. Postgres and
firebird, on the other hand, are both self contained.

8

On 6/2/06, Joe [email protected] wrote:

Postgresql fits all four of your criteria.

There are least a few Rails plugins I’ve tried to use that do not work
with PostgreSQL or have otherwise limited functionality,
acts_as_taggable for example… there are missing fields in the group
by clause which is valid sql in MySQL but not in PostgreSQL.

The Rails developers seem to favor MySQL as per the many screencasts
they have published. Seems a safer bet to me.

9

“Rimantas” == Rimantas L. [email protected] writes:

If you’re used to real databases, MySQL will drive you up the walls.
Save your sanity and go with PostgreSQL.

Care to elaborate?

The MySQL people have always treated the SQL standards as pretty much
optional. MySQL also is not a monolithic entity, but a group of
several database engines flying in close formation. So even if MySQL
seems to have all the features you want, it may well be the case that
you can’t actually use them all at the same time (for example, a table
can’t have both transactions and full-text indexes).

All in all, my experience is that if you’re used to a database that
tries to adhere to the standards (Oracle, MSSQL, PostgreSQL, Sybase,
…), switching to MySQL will be quite frustrating, mostly because of
the different attitude driving it. And that goes the other way as
well, of course. Someone used to MySQL will probably find the rest of
the bunch obnoxious and restrictive.

As DHH says, though, if you use Rails’ database abstraction stuff it’s
not going to matter very much which you chose. I’m currently running
MySQL for development and PostgreSQL for deployment, which for the
most part works just fine. It’s happened once that Rails generated SQL
that MySQL swallowed and PostgreSQL barfed on, but that was probably
my own fault for writing code that didn’t make sense in the first place.

	     Calle D. <[email protected]>
	 http://www.livejournal.com/users/cdybedahl/
"Rational thought. It's an acquired taste." -- Gunn, Angel: the

Series

10

On 6/2/06, Greg D. [email protected] wrote:

On 6/2/06, Joe [email protected] wrote:
There are least a few Rails plugins I’ve tried to use that do not work
with PostgreSQL or have otherwise limited functionality,
acts_as_taggable for example… there are missing fields in the group
by clause which is valid sql in MySQL but not in PostgreSQL.

The acts_as_taggable issue you’re referring to is a well known bug
with a working fix[1] (which is caused, incidentally, by MySQL
accepting non-standard and potentially ambiguous SQL). Which other
plugin compatibility issues with PG do you know about?

I’ve switched to PG recently, after more than 4 years of developing
with and for MySQL. All I have to say about my experience so far is:
not going back :slight_smile:

–
-Alder

[1]
http://rubyforge.org/tracker/index.php?func=detail&aid=2482&group_id=923&atid=3629

11

David J. wrote:

I have one vendor of a mission critical software service that is on
mysql, and they had frequent downtimes because of DBMS problems.

And we have several clients with huge MySQL backed databases (several
tables with millions + records–2GB database) that work flawlessly. The
only downtimes have been due to incorrect, but “valid” data; and Java
issues (or plain old developer stupidity). And it runs on a Windows
2003 Server box (which MySQL isn’t suppose to do as well on).

You’re going to get as many stories as there are people. Look at the
features lists, then decide…you will find your own “best path”.
Personally we usually use the standard develop on sqlite3, deploy to
MySQL or Postgres. With Rails it really is pretty translucent with an
application database. Don’t let anything but your code (and DBAs that
know the DB) touch it and you’ll be fine.

-Curtis

12

Good point.
-Larry

13

David J. wrote:

In my world, a 2 gb database is essentially empty. It’s certainly not
on a level that warrants any level of boasting.

I wasn’t aware that I was boasting, I suppose that came from the
emphasis on “huge”. I’m guessing the databases I deal with on a regular
basis at this point would be fairly large to a typical RoR based RDBMS
discussion. My point was that you’ll get a story for every situation.
And database selection is another religious discussion. It’s best to
look at the features and decide what you need from what it can do.

-Curtis

14

On Sat, 2006-06-03 at 09:56 -0600, Curtis S. wrote:

David J. wrote:

I have one vendor of a mission critical software service that is on
mysql, and they had frequent downtimes because of DBMS problems.

And we have several clients with huge MySQL backed databases (several
tables with millions + records–2GB database) that work flawlessly. The
only downtimes have been due to incorrect, but “valid” data; and Java
issues (or plain old developer stupidity). And it runs on a Windows
2003 Server box (which MySQL isn’t suppose to do as well on).

This is where the scaling question comes up.

In my world, a 2 gb database is essentially empty. It’s certainly not
on a level that warrants any level of boasting.

Part of our vendor’s issue was simply that he was (like many people)
under the impression that a couple of gigs was “large”. But we added
that amount to his database every day, seven days a week, without
downtime. It took him a year to figure out that he needed real DASD and
database management to handle the workload he had contracted for.

You’re going to get as many stories as there are people. Look at the
features lists, then decide…you will find your own “best path”.
Personally we usually use the standard develop on sqlite3, deploy to
MySQL or Postgres. With Rails it really is pretty translucent with an
application database. Don’t let anything but your code (and DBAs that
know the DB) touch it and you’ll be fine.\

IMHO, it is asking for trouble to develop on one platform then deploy to
another. Been there, done that, fixed the installation at 3:00 AM,
several times … but never again. If the distribution platform is free
(or adequately licensed), develop to it and sleep easier at install
time.

Most RDBMS’s have free “lite” version available specifically for small
contractor development efforts. This includes Oracle, DB2, MS-Sql,
etcetera. So in today’s world there is no reason not to develop on the
target platform.

With that said, I am all in favor of testing on multiple platforms
beyond the initial intended deployment.

15

On Sun, 2006-06-04 at 13:06 -0600, Curtis S. wrote:

David J. wrote:

In my world, a 2 gb database is essentially empty. It’s certainly not
on a level that warrants any level of boasting.

I wasn’t aware that I was boasting, I suppose that came from the
emphasis on “huge”.

Please accept my apologies, email doesn’t carry “tone” very well. My
mis-interpretation of your intent is entirely my responsibility.

I agree that people get unreasonably religious over their preferred
RDBMS, when the truth is that most of them are nearly plug-in compatible
for most purposes.