Rails - Sep 2010 - Thinking of going back to MySql from PgSql. Your thoughts?

Just thinking out loud. about switching from pgsql to mysql. So far,
haven''t experienced any differences between the two databases.
I''ve
been using mysql for the past few years. I like it more because am a
mac interface junkie and mysql has sequel pro. Not that this is a
determining factor. But pgsql also has navicat (in my opinion, not as
good as sequel pro).

The more important thing is, web hosts that I have spoken to have said
that pgsql takes up a considerable amount of RAM than mysql... More
RAM on a web host means more $$$.

Moreover, since I''ve switched from Symfony PHP. Most of my other web
projects will be hosted with a mysql db.

I am sure there may be other more significant differences. But for my
every day use as a web developer, I don''t really notice the internal
features that pgsql has over mysql.

What are you thoughts

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

They make Navicat for MySQL too. I love it, couldn''t do without it.

That being said, if you really can''t tell the difference between PGSQL
and MySQL then you''re probably better off with MySQL :)

Most developers make the switch to PG because they realize they need
more advanced DBA features that MySQL doesn''t offer. If you''re
not
missing these features, why bother?

My $0.02.

*Cue PG fan trashing MySQL based on technical merits here*

--------------------
http://subimage.com
http://twitter.com/subimage



On Tue, Aug 31, 2010 at 5:43 PM, Christian Fazzini
<christian.fazzini-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Just thinking out loud. about switching from pgsql to mysql. So far,
> haven''t experienced any differences between the two databases.
I''ve
> been using mysql for the past few years. I like it more because am a
> mac interface junkie and mysql has sequel pro. Not that this is a
> determining factor. But pgsql also has navicat (in my opinion, not as
> good as sequel pro).
>
> The more important thing is, web hosts that I have spoken to have said
> that pgsql takes up a considerable amount of RAM than mysql... More
> RAM on a web host means more $$$.
>
> Moreover, since I''ve switched from Symfony PHP. Most of my other
web
> projects will be hosted with a mysql db.
>
> I am sure there may be other more significant differences. But for my
> every day use as a web developer, I don''t really notice the
internal
> features that pgsql has over mysql.
>
> What are you thoughts
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

One has said that he/she had to switch to pgsql because of scaling
problems with mysql. Is this true? Just thinking of the future

On Sep 1, 9:03 am, seth b
<subim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> They make Navicat for MySQL too. I love it, couldn''t do without
it.
>
> That being said, if you really can''t tell the difference between
PGSQL
> and MySQL then you''re probably better off with MySQL :)
>
> Most developers make the switch to PG because they realize they need
> more advanced DBA features that MySQL doesn''t offer. If
you''re not
> missing these features, why bother?
>
> My $0.02.
>
> *Cue PG fan trashing MySQL based on technical merits here*
>
> --------------------http://subimage.comhttp://twitter.com/subimage
>
> On Tue, Aug 31, 2010 at 5:43 PM, Christian Fazzini
>
> <christian.fazz...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Just thinking out loud. about switching from pgsql to mysql. So far,
> > haven''t experienced any differences between the two
databases. I''ve
> > been using mysql for the past few years. I like it more because am a
> > mac interface junkie and mysql has sequel pro. Not that this is a
> > determining factor. But pgsql also has navicat (in my opinion, not as
> > good as sequel pro).
>
> > The more important thing is, web hosts that I have spoken to have said
> > that pgsql takes up a considerable amount of RAM than mysql... More
> > RAM on a web host means more $$$.
>
> > Moreover, since I''ve switched from Symfony PHP. Most of my
other web
> > projects will be hosted with a mysql db.
>
> > I am sure there may be other more significant differences. But for my
> > every day use as a web developer, I don''t really notice the
internal
> > features that pgsql has over mysql.
>
> > What are you thoughts
>
> > --
> > You received this message because you are subscribed to the Google
Groups "Ruby on Rails: Talk" group.
> > To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> > To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> > For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Christian Fazzini wrote:
> Just thinking out loud. about switching from pgsql to mysql.
You would be making a huge mistake.
> So far,
> haven''t experienced any differences between the two databases.
That''s nearly unbelievable unless you haven''t been using
either much, or
unless you''ve been using the most absolutely basic features.
> I''ve
> been using mysql for the past few years. I like it more because am a
> mac interface junkie and mysql has sequel pro. Not that this is a
> determining factor. But pgsql also has navicat (in my opinion, not as
> good as sequel pro).
Don''t choose your DB server based on pretty graphical clients.  MySQL
is
far inferior to Postgres in virtually every respect.  Postgres performs 
better, lets you have referential integrity along with a fast storage 
engine, and is more extensible (PostGIS would not be possible in MySQL, 
for example).  I think MySQL is *still* too brain-dead to scan table 
indices backwards, and its procedural language (if you ever need it) is 
so bad as to be nearly useless.  Also, Postgres'' syntax adheres more 
closely to the SQL standard.

MySQL''s advantages over Postgres include ease of setup and (so
I''ve
heard) clustering...and that''s it.  For most projects, it is a mistake 
to choose MySQL over Postgres.
> 
> The more important thing is, web hosts that I have spoken to have said
> that pgsql takes up a considerable amount of RAM than mysql... 
Never heard this.
> More
> RAM on a web host means more $$$.
So configure your Postgres install to use less RAM.  Or use Heroku, 
which has Postgres ready configured.
> 
> Moreover, since I''ve switched from Symfony PHP. Most of my other
web
> projects will be hosted with a mysql db.
That''s just compounding the mistake. :)
> 
> I am sure there may be other more significant differences. But for my
> every day use as a web developer, I don''t really notice the
internal
> features that pgsql has over mysql.
> 
> What are you thoughts
You''ll notice the difference as soon as you try to do something 
non-trivial.  Stick with Postgres.  It''s by far the better product. 
The
fact that the differences are under the hood doesn''t make them less 
important.  There are also no commercial entities causing uncertainty as 
to its fate.

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Aug 31, 2010, at 5:43 PM, Christian Fazzini wrote:
> Just thinking out loud. about switching from pgsql to mysql. So far,
I wouldn''t.
> haven''t experienced any differences between the two databases.
I''ve
Try inserting an invalid date into each database.  See what it does.  Unless
it''s changed mysql will insert it as 0000-00-00.  PostgreSQL will give
you an error.  I think mysql will set a warning, but it will still do the
insert.

Create a VARCHAR(100) field.  Now stuff 101 characters into it.  MySQL will
truncate the data.  PostgreSQL will give you an error.

You might be lucky and never run into these but they are a PITA when you do.  
> I am sure there may be other more significant differences. But for my
> every day use as a web developer, I don''t really notice the
internal
> features that pgsql has over mysql.
Not sure how old these are, but might be interesting...

http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html

Awhile back I had the exact same Rails site running on MySQL and PostgreSQL. 
Database created via Rails migrations.  Data inserted via the same load scripts.
Identical in every way.  A query that joined two tables of about 30K and 100K
rows respectively took 30 seconds in MySQL.  It took <1 on PostgreSQL.  I was
floored.  The reason?  The conditions were complex enough that MySQL
wouldn''t use all the available indexes.  This is a fairly well known
problem.  And it can be a killer.  What good are creating indexes if it
won''t use them?

(for the record I may have some of my numbers wrong, but it was an incredible
difference and the explain/analyze output clearly showed mysql wasn''t
picking up indexes that were available).

> What are you thoughts
Don''t change.  Write a letter to Sequel Pro and tell them
you''d like them to support PostgreSQL :)

-philip

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Thanks for the feedback.

@Philip, @Marnen, I will definitely review these points. Thanks for
passing the information.

@marnen I got an email from one of the web hosts I am communicating
with. They have said this "Yes, PostgreSQL can be installed with our
Managed VPS''s. Many use with cPanel but keep in mind it''s
resource
intensive so RAM usage will definitely be higher then MySQL."

@seth, the only thing I dont like about navicat is that it has to open
up a new window for various tasks. i.e. open the table or query view.

On Sep 1, 11:11 am, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org>
wrote:
> On Aug 31, 2010, at 5:43 PM, Christian Fazzini wrote:
>
> > Just thinking out loud. about switching from pgsql to mysql. So far,
>
> I wouldn''t.
>
> > haven''t experienced any differences between the two
databases. I''ve
>
> Try inserting an invalid date into each database.  See what it does.
 Unless it''s changed mysql will insert it as 0000-00-00.  PostgreSQL
will give you an error.  I think mysql will set a warning, but it will still do
the insert.  
>
> Create a VARCHAR(100) field.  Now stuff 101 characters into it.  MySQL will
truncate the data.  PostgreSQL will give you an error.
>
> You might be lucky and never run into these but they are a PITA when you
do.  
>
> > I am sure there may be other more significant differences. But for my
> > every day use as a web developer, I don''t really notice the
internal
> > features that pgsql has over mysql.
>
> Not sure how old these are, but might be interesting...
>
>
http://sql-info.de/mysql/gotchas.htmlhttp://sql-info.de/postgresql/postgres-gotchas.html
>
> Awhile back I had the exact same Rails site running on MySQL and
PostgreSQL.  Database created via Rails migrations.  Data inserted via the same
load scripts.  Identical in every way.  A query that joined two tables of about
30K and 100K rows respectively took 30 seconds in MySQL.  It took <1 on
PostgreSQL.  I was floored.  The reason?  The conditions were complex enough
that MySQL wouldn''t use all the available indexes.  This is a fairly
well known problem.  And it can be a killer.  What good are creating indexes if
it won''t use them?  
>
> (for the record I may have some of my numbers wrong, but it was an
incredible difference and the explain/analyze output clearly showed mysql
wasn''t picking up indexes that were available).
>
> > What are you thoughts
>
> Don''t change.  Write a letter to Sequel Pro and tell them
you''d like them to support PostgreSQL :)
>
> -philip
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Wednesday 01 September 2010, Christian Fazzini wrote:
> Just thinking out loud. about switching from pgsql to mysql. So far,
> haven''t experienced any differences between the two databases.
[...]
As for the databases themselves: Add or delete a column to/from an 
existing large table, say > 1M rows. Or add/remove an index. In 
PostgreSQL these operations will all be well below a minute. In MySQL 
I''ve experienced them to take so long as to become prohibitive.

Also, if anything goes wrong during your migration, PostgreSQL behaves 
properly transactional. MySQL will leave you with a partially applied 
migration where you have to sort out the mess manually.

Possibly it is a matter of taste or familiarity, but I think the 
PostgreSQL reference docs are much better and particularly much better 
organized. As a bonus, on Debian Linux I have all the command and SQL 
language references as man pages.

The psql commandline tool, in my opinion, is far superior to mysql. For 
one thing, auto-completion works.
> The more important thing is, web hosts that I have spoken to have
> said that pgsql takes up a considerable amount of RAM than mysql...
> More RAM on a web host means more $$$.
I haven''t been there myself, as I mostly work on small-scale apps, but
I
take it that properly configured for a high load, both DBMS take lots 
and lots of RAM -- and use it to improve performance. If you are 
concerned about the RAM use, you ought to look into how these systems 
are configured. Their default configurations are meant to work, but are 
in no ways optimized for what you might be doing with your databases.

Michael

-- 
Michael Schuerig
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org
http://www.schuerig.de/michael/

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.