Forum: Ruby on Rails Which is better for production: MySQL5 or PostGres8.1?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Larry K. (Guest)
on 2006-06-02 20:43
(Received via mailing list)
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
Calle D. (Guest)
on 2006-06-02 22:59
(Received via mailing list)
>>>>> "Larry" == Larry K. <removed_email_address@domain.invalid> 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. <removed_email_address@domain.invalid>
		 http://www.livejournal.com/users/cdybedahl/
	  "Data on the network has cooties and will EAT YOUR BRAINS"
			   -- Ross Younger, BofhNet
Joe (Guest)
on 2006-06-02 23:46
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
Rimantas L. (Guest)
on 2006-06-03 00:19
(Received via mailing list)
> 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/
David Heinemeier H. (Guest)
on 2006-06-03 00:35
(Received via mailing list)
> > 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
Greg D. (Guest)
on 2006-06-03 01:49
(Received via mailing list)
On 6/2/06, Joe <removed_email_address@domain.invalid> 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.
Joe (Guest)
on 2006-06-03 08:15
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).
David J. (Guest)
on 2006-06-03 08:44
(Received via mailing list)
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.
Calle D. (Guest)
on 2006-06-03 11:09
(Received via mailing list)
>>>>> "Rimantas" == Rimantas L. <removed_email_address@domain.invalid> 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. <removed_email_address@domain.invalid>
		 http://www.livejournal.com/users/cdybedahl/
    "Rational thought. It's an acquired taste." -- Gunn, Angel: the
Series
Alder G. (Guest)
on 2006-06-03 13:30
(Received via mailing list)
On 6/2/06, Greg D. <removed_email_address@domain.invalid> wrote:
> On 6/2/06, Joe <removed_email_address@domain.invalid> 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 :)

--
-Alder

[1]
http://rubyforge.org/tracker/index.php?func=detail...
Larry K. (Guest)
on 2006-06-03 13:51
(Received via mailing list)
Good point.
-Larry
Curtis S. (Guest)
on 2006-06-03 19:58
(Received via mailing list)
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
David J. (Guest)
on 2006-06-04 01:06
(Received via mailing list)
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.
Curtis S. (Guest)
on 2006-06-04 23:10
(Received via mailing list)
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
David J. (Guest)
on 2006-06-04 23:59
(Received via mailing list)
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.
This topic is locked and can not be replied to.