Обсуждение: PG -v- MySQL

Gauthier, Dave wrote:
> Hi:

> I’ve been invited to participate in a forum which discusses technical
> issues for relational DBs used in the corporation.  The 3 DBs they are
> discussing are Oracle, SQL-Server and MySQL.  I’d like to introduce PG,
> but want to be able to address the “Why not use MySQL” questions when
> they arise.  I know in the past there have been many comparisons between
> the 2 DBs but would like to know if there are any good, recent ones.
> Also, any “off-the-cuff” comments would be welcome, especially if you
> have used both DBs.

I suggest you take a look at:

http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project
http://sql-info.de/mysql/gotchas.html
http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql
http://www.commandprompt.com/files/mysql_learn.pdf
http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html
http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php

Sincerely,

Joshua D. Drake


P.S. In short, we rock.

On Tue, May 13, 2008 at 10:39 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
> > I've been invited to participate in a forum which discusses technical
> issues for relational DBs used in the corporation.  The 3 DBs they are
> discussing are Oracle, SQL-Server and MySQL.  I'd like to introduce PG, but
> want to be able to address the "Why not use MySQL" questions when they
> arise.  I know in the past there have been many comparisons between the 2
> DBs but would like to know if there are any good, recent ones.  Also, any
> "off-the-cuff" comments would be welcome, especially if you have used both
> DBs.
> >
>
>  I suggest you take a look at:
>
>  http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project
>  http://sql-info.de/mysql/gotchas.html
>
> http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql
>  http://www.commandprompt.com/files/mysql_learn.pdf
>  http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html
>  http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php

Here are some other things we have v. mysql:
*) Transactional DDL: much safer and easier to roll out changes to 24/7 systems
*) Better HA: PostgreSQL wal shipping/warm standby is much more robust
than mysql binary log shipping
*) MVCC: (I'm sure this is up there, but it's worth
repeating)...smarter locking/concurrency for high write activity
(important) applications.  There is more to databases than phpbb
backend.
*) Much better shell
*) Much smarter query planner (lame rule based planner is easily outsmarted)
*) Stored procedure support/SPI interface is years beyond anything
they offer...compare pl/pgsql to their lame psm implementation.
(procedure support is much more nuanced than "procedure support,
checkmark!"
*) A stronger, albeit smaller, community.

merlin

Le mardi 13 mai 2008, Joshua D. Drake a écrit :
> I suggest you take a look at:
>
> http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project
> http://sql-info.de/mysql/gotchas.html
> http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-p
>ostgresql http://www.commandprompt.com/files/mysql_learn.pdf
> http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html
> http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php

I'm suprised not to see following link in previous list:

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007

> P.S. In short, we rock.

I like to say that although using MySQL provides problems, using PostgreSQL
provides solutions.
--
dim

At 10:39a -0400 on Tue, 13 May 2008, Dave Gauthier wrote:
> I’d like to introduce PG,
> but want to be able to address the "Why not use MySQL" questions when
> they arise.  I know in the past there have been many comparisons between
> the 2 DBs but would like to know if there are any good, recent ones.

In addition to Josh's links, you may find Greg Smith's (et al) article
useful.  It is well-written, thorough, and recent (less than a year old).
 http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007

> Also, any "off-the-cuff" comments would be welcome, especially if you
> have used both DBs.

I use both on a daily basis, and as developer, my opinion is that
Postgres is much nicer:

* The error messages are much more informative: typo something in your
SQL and compare for yourself the error messages b/w the two DBs.

* If you work with the command line client, tab-completion is much nicer
in psql.  (I daresay, it's more complete. :-) )  You can complete almost
anything.  (It even understands the context of where the cursor is in
the statement you're writing.)

* I am a member of both MySQL and Postgres mailing lists.  In my
opinion, the community help from Postgres is (in general) much more ...
complete.  You will regularly get answers straight from the devs, with a
definitive answer on how to fix your problem.  And, the community is
more generally concerned with helping your larger problem, than just the
problem at hand.

* #postgres on irc.freenode.net follows the same path as the mailing lists.

* The documentation on the website for Postgres is by far the better of
the two DBs.  MySQL's is decent and very useful, but Postgres'
documentation just plain rocks.  In fact, the Postgres documentation is
one of the very best examples of documentation for any project with
which I've had to deal, OpenSource or otherwise.  Head and shoulders.

* The \* commands are much easier for me to work with, although that may
just be my preference.  \d a_table, for instance is easier for me than
typing SHOW CREATE TABLE a_table.  (And tab-completion works there too...)

As a proponent of data-correctness:

* The default for PG is ACID compliance.  As opposed to MySQL's default
table type of MyISAM, which, although fast for selects, is not ACID
compliant.  This is especially hazardous since MySQL's internal data
structure is all MyISAM.

* The community in general is oriented towards doing it "the right way"
as opposed to just getting it done.

As a proponent of speed and concurrency:

* Postgres may not be as fast for certain boundary cases like
   SELECT COUNT(*) FROM a_table; -- (read link above)
but it far outstrips MySQL if you have much concurrent access.  Someone
else will hopefully serve out some links on this soon since I don't have
them handy at the moment.

As a DBA for different clients, I /really/ like the fact that Postgres
has transactional DDL statements.  Even Oracle doesn't have
transactional DDL statements.  Just DML.

HTH,

Kevin

On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> Here are some other things we have v. mysql:
> *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems

It's also useful for lots and lots of other things.  For instance,
let's say you had an index on a good sized table, and that index took
three hours to build.  You look at the stats and it's not getting used
much.  You want to see if you can get rid of it, but don't want to
drop it and then have to spend three hours rebuilding it.  You have
other indexes you want to still be used while testing, so set
enable_indexscan off is out of the question.  You can do this:

begin;
drop humongous index;
explain analyze complexquerygoeshere;
-- run it a few more times to see how caching affects it
explain analyze complexquerygoeshere;
explain analyze complexquerygoeshere;
-- all done, got my numbers...
rollback;

While it's not safe to do in production 24/7 servers because of
locking issues, it's quite a sane to do on test or reporting servers.

> *) Better HA: PostgreSQL wal shipping/warm standby is much more robust
> than mysql binary log shipping

This can't be stressed enough.  MySQL's replication is of the "gee, I
sure hope it's working" variety, and there are LOTS of horror stories
of it just quitting with no notice.

> *) MVCC: (I'm sure this is up there, but it's worth
> repeating)...smarter locking/concurrency for high write activity
> (important) applications.  There is more to databases than phpbb
> backend.

MySLQ has MVCC, if you're willing to give up full text indexing, and
use an engine now owned by Oracle, which may or may not be available
in the future.  (i.e. InnoDB).  And there are a few things that innodb
has that postgresql doesn't, like predicate locking, which can be
really useful (allows you to refer to things like count(id) in the net
query and be guaranteed that it's right), and really aweful (to do
that you lock EVERY row referenced in the previous count(id)),
depending on your perspective.

> *) Much better shell

I'd disagree.  I'd say it's the best damned shell period.  :)  Having
used a lot of command line db interfaces, nothing touches psql.
Learning it is easy, it's self documenting, and starting with 8.2 the
line buffer goes by query not by line.

And I totally agree with everything you said Merlin.  Especially about
the community.

Scott Marlowe wrote:
> On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> Here are some other things we have v. mysql:
>> *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems

> And I totally agree with everything you said Merlin.  Especially about
> the community.
>

In short, we rock. :P

Joshua D. Drake

On Tuesday 13 May 2008 12:09:28 Kevin Hunter wrote:
> * The documentation on the website for Postgres is by far the better of
> the two DBs.  MySQL's is decent and very useful, but Postgres'
> documentation just plain rocks.  In fact, the Postgres documentation is
> one of the very best examples of documentation for any project with
> which I've had to deal, OpenSource or otherwise.  Head and shoulders.
>

I've always thought that the biggest problem with the MySQL documentation is
that it has a very hard time organizing all the little caveats that come into
play between the different storage engines. Sometime an item might be
documented in a storage specific area rather than a specific command (so it
gets overlooked), other times I find that the command pages themselves are
too cluttered with storage specific issues (making it hard to find
information specific to your environment).  It's an understandably difficult
issue to work around, since ever storage engine you use means that you're
basically learning the intricacies of a separate database, so it doesn't
surprise me that things end up a little schizophrenic at times.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

jd@commandprompt.com ("Joshua D. Drake") writes:
> Gauthier, Dave wrote:
>> I�ve been invited to participate in a forum which discusses
>> technical issues for relational DBs used in the corporation.  The 3
>> DBs they are discussing are Oracle, SQL-Server and MySQL.  I�d like
>> to introduce PG, but want to be able to address the �Why not use
>> MySQL� questions when they arise.  I know in the past there have
>> been many comparisons between the 2 DBs but would like to know if
>> there are any good, recent ones.  Also, any �off-the-cuff� comments
>> would be welcome, especially if you have used both DBs.
>
> I suggest you take a look at:
>
> http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project
> http://sql-info.de/mysql/gotchas.html
> http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql
> http://www.commandprompt.com/files/mysql_learn.pdf
> http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html
> http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php

I would add to that document list the following...

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007
--
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/internet.html
If anyone ever  markets  a really  well-documented Unix that   doesn't
require  babysitting by a phalanx of  provincial Unix clones, there'll
be a  lot of unemployable,  twinky-braindamaged misfits out deservedly
pounding the pavements.

It surely rocks.  We switched from MySql to PostgreSQL about a year ago
and have never looked back, we are happy as Larry.  A bit more
information on our web site.

http://www.alerce.com.au

Regards,

Mario



Joshua D. Drake wrote:
> Scott Marlowe wrote:
>> On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com>
>> wrote:
>>> Here are some other things we have v. mysql:
>>> *) Transactional DDL: much safer and easier to roll out changes to
>>> 24/7 systems
>
>> And I totally agree with everything you said Merlin.  Especially about
>> the community.
>>
>
> In short, we rock. :P
>
> Joshua D. Drake
>
>
>

On May 13, 2008, at 11:42 AM, Merlin Moncure wrote:
> Here are some other things we have v. mysql:
> ....
> *) Much better shell

I tend to agree based on my limited experience.

However, being a GUI-oriented person I haven't noticed management
tools comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac).
Perhaps someone can enlighten me?

(Yes, I've tried pgAdmin, but it's not quite ... right. I can't say
why at the moment, I should probably take another look at it.)

-- Andy

On May 13, 2008, at 11:42 AM, Merlin Moncure wrote:

Here are some other things we have v. mysql:
....
*) Much better shell

I tend to agree based on my limited experience.

However, being a GUI-oriented person I haven't noticed management tools comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac). Perhaps someone can enlighten me?

(Yes, I've tried pgAdmin, but it's not quite ... right. I can't say why at the moment, I should probably take another look at it.)

-- Andy

Andy Anderson wrote:
> However, being a GUI-oriented person I haven't noticed management tools
> comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac). Perhaps
> someone can enlighten me?
>
> (Yes, I've tried pgAdmin, but it's not quite ... right. I can't say why
> at the moment, I should probably take another look at it.)

There is phpPGAdmin which I believe was forked from phpMyAdmin several
years ago, but still maintained.

Also, pgAdminIII is a great tool, I think you should take a second look.

Matt