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? Thanks, Joe
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 ===============================================================================
On Wed, 9 Mar 2005 14:48:25 -0700 (MST), Ara.T.Howard <Ara.T.Howard-32lpuo7BZBA@public.gmane.org> wrote:> 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 > -- >Awesome, thanks! Does Rails have better support for one over the other? Does Rails provide access to Postgres extra features?
Bonjour Joe, mercredi 9 mars 2005 à 22:15:47, vous écriviez, mon cher :> I''ve used mysql for quite some time now. Other than crashing when the > partition gets full, I''ve had no problems with it.Please tell me what Database can handle a such "Partition full" overkill constraint ? ;-) OpenSource ? ;-) Or perhaps you mean that one "Unbreakable" is coming "from" this sealor guy Larry ? ;-) I agree 10^4 with Ara.T.Howard <Ara.T.Howard-32lpuo7BZBA@public.gmane.org> previous post. Zero admin onto Mysql is a myth, Postgres is also very robust, got plenty of systems around I forgot, since they are running fast & smoothly. Views, triggers provided by PG are in a Rails point of view a strong benefit IMHO.. Pointing pure performance between theses DB''s.. hum.. If "it work" so tune da with index, hdparm, mod_gzip.. Have you ever computed the real CPU used by your web app includind the client rendering work...? Ciao'' Mathieu
Joe Van Dyk wrote:> So, with a Rails application, is there any reason why I would want to > learn/use another DB besides mysql? Any pragmatic benefits?If you''re in the mood to explore, I''d go a different track entirely and try SQLite. It''s a full-featured embedded database which eliminates the need for a RDBMS server. It''s faster and has a smaller memory footprint than MySQL. Jamis Buck wrote some great bindings and we have solid Rails support. Plus you may neatly skirt the irrelevant advocacy wars. 1. Install SQLite 3.1.3 on your platform of choice http://sqlite.org/download.html 2. Install the Ruby bindings gem install sqlite3-ruby 3. Create a database file cd /my/rails/app/db sqlite3 development.db < ''create table accounts (id integer primary key, name varchar(255));'' 4. Configure config/database.yml development: adapter: sqlite3 dbfile: db/development.db 5. Take it for a spin $ ./script/console > class Account < ActiveRecord::Base; end > Account.create(''name'' => ''holy file-based db'') > Account.find_all jeremy
On Wed, 09 Mar 2005 14:28:40 -0800, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:> Joe Van Dyk wrote: > > So, with a Rails application, is there any reason why I would want to > > learn/use another DB besides mysql? Any pragmatic benefits? > > If you''re in the mood to explore, I''d go a different track entirely and > try SQLite. It''s a full-featured embedded database which eliminates the > need for a RDBMS server. It''s faster and has a smaller memory footprint > than MySQL. Jamis Buck wrote some great bindings and we have solid > Rails support. Plus you may neatly skirt the irrelevant advocacy wars. > > 1. Install SQLite 3.1.3 on your platform of choice > http://sqlite.org/download.html > > 2. Install the Ruby bindings > gem install sqlite3-ruby > > 3. Create a database file > cd /my/rails/app/db > sqlite3 development.db < ''create table accounts (id integer primary > key, name varchar(255));'' > > 4. Configure config/database.yml > development: > adapter: sqlite3 > dbfile: db/development.db > > 5. Take it for a spin > $ ./script/console > > class Account < ActiveRecord::Base; end > > Account.create(''name'' => ''holy file-based db'') > > Account.find_all > > jeremy >Not a bad idea. When would you probably "need" to have a full-fledged RDMBS with a Rails app? (you''ll note I inserted that qualifier to keep this OT :-) Joe
Jeremy Kemper wrote:> 3. Create a database file > cd /my/rails/app/db > sqlite3 development.db < ''create table accounts (id integer primary > key, name varchar(255));''Oops: echo ''...'' | sqlite3 development.db May the eunuchs peck out my eyes. jeremy
On Wed, 9 Mar 2005, Jeremy Kemper wrote:> Joe Van Dyk wrote: >> So, with a Rails application, is there any reason why I would want to >> learn/use another DB besides mysql? Any pragmatic benefits? > > If you''re in the mood to explore, I''d go a different track entirely and > try SQLite. It''s a full-featured embedded database which eliminates the > need for a RDBMS server. It''s faster and has a smaller memory footprint > than MySQL. Jamis Buck wrote some great bindings and we have solid > Rails support. Plus you may neatly skirt the irrelevant advocacy wars. > > 1. Install SQLite 3.1.3 on your platform of choice > http://sqlite.org/download.html > > 2. Install the Ruby bindings > gem install sqlite3-ruby > > 3. Create a database file > cd /my/rails/app/db > sqlite3 development.db < ''create table accounts (id integer primary > key, name varchar(255));'' > > 4. Configure config/database.yml > development: > adapter: sqlite3 > dbfile: db/development.db > > 5. Take it for a spin > $ ./script/console > > class Account < ActiveRecord::Base; end > > Account.create(''name'' => ''holy file-based db'') > > Account.find_all > > jeremyyes - this is all true. i use sqlite for 90% of my apps now. concurency can be an issue though on VERY heavily loaded sites. i use this where most people would use mysql - needing a simple, fast db. +1 -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 ===============================================================================
On Wed, 9 Mar 2005 14:33:06 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Wed, 09 Mar 2005 14:28:40 -0800, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote: > > Joe Van Dyk wrote: > > > So, with a Rails application, is there any reason why I would want to > > > learn/use another DB besides mysql? Any pragmatic benefits? > > > > If you''re in the mood to explore, I''d go a different track entirely and > > try SQLite. It''s a full-featured embedded database which eliminates the > > need for a RDBMS server. It''s faster and has a smaller memory footprint > > than MySQL. Jamis Buck wrote some great bindings and we have solid > > Rails support. Plus you may neatly skirt the irrelevant advocacy wars. > > > > 1. Install SQLite 3.1.3 on your platform of choice > > http://sqlite.org/download.html > > > > 2. Install the Ruby bindings > > gem install sqlite3-ruby > > > > 3. Create a database file > > cd /my/rails/app/db > > sqlite3 development.db < ''create table accounts (id integer primary > > key, name varchar(255));'' > > > > 4. Configure config/database.yml > > development: > > adapter: sqlite3 > > dbfile: db/development.db > > > > 5. Take it for a spin > > $ ./script/console > > > class Account < ActiveRecord::Base; end > > > Account.create(''name'' => ''holy file-based db'') > > > Account.find_all > > > > jeremy > > > > Not a bad idea. > > When would you probably "need" to have a full-fledged RDMBS with a > Rails app? (you''ll note I inserted that qualifier to keep this OT :-)2 or more web servers would probably be a PITA with sqlite, NFS sucks etc. etc. But if your app needs two servers, it''s probably already big enough to have hit some of the concurrency problems already. If you want other applications to read your data, sqlite could be a problem. On the whole though, you can do sqlite in development and switch to mysql once your app is reasonably complete.> > Joe > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
On Thu, 10 Mar 2005, Michael Koziarski wrote:>> Not a bad idea. >> >> When would you probably "need" to have a full-fledged RDMBS with a >> Rails app? (you''ll note I inserted that qualifier to keep this OT :-) > > 2 or more web servers would probably be a PITA with sqlite, NFS sucks etc. > etc. But if your app needs two servers, it''s probably already big enough > to have hit some of the concurrency problems already. > > If you want other applications to read your data, sqlite could be a problem. > > On the whole though, you can do sqlite in development and switch to mysql > once your app is reasonably complete.this is all true. however i should point out to that sqlite is quite capable with only a little bit of additional coding to acheive concurrency: as we speak i''m running tens of thousands of jobs through an nfs mounted sqlite ''queue'' with 50, or more, applications accessing the db. my point being that sqlite is quite useable in production environments if you understand it well. i my case working with some of it''s limitations was easier than using our mysql dbs. -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 ===============================================================================
On Wed, 09 Mar 2005 14:35:47 -0800, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:> > sqlite3 development.db < ''create table accounts (id integer primary > > key, name varchar(255));'' > > Oops: echo ''...'' | sqlite3 development.db > May the eunuchs peck out my eyes.For the sake of being completely pedantic, that would also have worked as: sqlite3 development.db < <(echo ''...'') Which is more similar to your original attempt. But your way is better as it doesn''t rely on bash... ;) -- One Guy With A Camera http://rbpark.ath.cx