[Rails] mysql vs postgres

[Ara.T.Howard]

On Wed, 9 Mar 2005, Joe Van Dyk wrote:

> I've used mysql for quite some time now.  Other than crashing when the
> partition gets full, I've had no problems with it.
>
> But I've heard great things about postgres and have seen some people
> say it's much superior to mysql.
>
> So, with a Rails application, is there any reason why I would want to
> learn/use another DB besides mysql?  Any pragmatic benefits?


postgres:

   - views

   - table inheritence (really nice to specify one schema, inherit for production
   and development schemas - changes one place take place all over)

   - triggers

   - plruby totally rocks (you can write db functions in ruby!)

   - foreign keys

   - time handling is about 1 million times better

   - fine grained concurrency control

   - cursors

   - ansyhronous notification : you can have the database notify our
     application when something happens (insert, delete, etc)

   - extremely standards compliant

   - very good open-source and free replication toolkits

   - official redhat db

   - fine grained authentication (users, groups, etc)

   - better liscense (BSD)


mysql :

   - faster

   the mysql docs are full of little tidbits like this:

     #
     The MySQL server performs only basic checking on the validity of a date: The
     ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31,
     respectively. Any date containing parts not within these ranges is subject to
     conversion to '0000-00-00'. Please note that this still allows you to store
     invalid dates such as '2002-04-31'. To ensure that a date is valid, perform a
     check in your application.

   i don't like to find things like this buried in the docs, nor do i like to
   add data integrity code to my app - but maybe that's just me...

interesting:

   http://mark.aufflick.com/dev-blog/one-entry?entry_id=1898
   http://mark.aufflick.com/dev-blog/one-entry?entry%5fid=1913
   http://openacs.org/philosophy/why-not-mysql
   http://sql-info.de/mysql/gotchas.html
   http://www.databasejournal.com/features/mysql/article.php/3288951
   http://www.webtechniques.com/archives/2001/09/jepson/
   http://www.devx.com/dbzone/Article/20743


and, of course, much of this is in the process of changing.  i think any
objective observer can say that the mysql is typically about 5 years behind
the postgresql guys in terms of features and standards compliance and, more
importantly, tends to favour enhancements of speed over data integrity.

before all the flames start let me also state that i've developed on both and
setup dozons of HA setups for both.  my expereince is that in our building
(3000 people or more) the mysql users expereience 'occansional' data
corruption and blinding performance.  the postgresql groups see zero data
corruption and great performance.  this is based on my personal knowledge of
only about 10 groups.

take your pick - that's my 2cts.

-a
-- 
===============================================================================
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===============================================================================