Обсуждение: Why is PGSQL better than MySQL

On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote:
> But does anyone know if there is a quasi-categorical comparison of the
> two so that I could speak with more conviction other than "I know it's
> better".

I've been involved in that comparison at work, and after some boring
theoretical discussions, i once turned to my CTO and said:

"Do a test. On the same machine, do a script that executes 50.000
inserts, OR any other 50.000 SQL operations you want, and benchmark the
results".

He did, and Postgres immediately got another die-hard fan.
The facts spoke more clearly than i could ever do, with just words.
Nowadays, he is even more "defensive" of PGSQL over MySQL than i am.

\\pb

Agreed. You can't argue with a well executed benchmark. All I'd say is
be careful not just the same simple select statement over and over
again - Do a variety of both simple and complex queries. And always
use InnoDB as compared to MyISAM (otherwise you're not comparing
apples with apples). The biggest pro (imho) that PostgreSQL has over
MySQL is its locking mechanism. PostgreSQL uses MVCC, which greatly
improves concurrency.

For a list of "Gotchas" take a look at these:
http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html

Also stumbled across this:
http://www.vitavoom.com/postgresql.html

On 12/13/05, Pedro B. <pedro.borracha@netcabo.pt> wrote:
> On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote:
> > But does anyone know if there is a quasi-categorical comparison of the
> > two so that I could speak with more conviction other than "I know it's
> > better".
>
> I've been involved in that comparison at work, and after some boring
> theoretical discussions, i once turned to my CTO and said:
>
> "Do a test. On the same machine, do a script that executes 50.000
> inserts, OR any other 50.000 SQL operations you want, and benchmark the
> results".
>
> He did, and Postgres immediately got another die-hard fan.
> The facts spoke more clearly than i could ever do, with just words.
> Nowadays, he is even more "defensive" of PGSQL over MySQL than i am.
>
> \\pb
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote:
> I've been reading the "AP2" thread and take it as a matter of faith
> that PGSQL is better than MySQL. It seems more complete for one thing.
> I have tried to explain my reasoning to several people when convincing
> them to use one versus the other. But does anyone know if there is a
> quasi-categorical comparison of the two so that I could speak with
> more conviction other than "I know it's better".

I have been a serious MySQL user for several years and was looking
forward to the 5.0 release so that I could employ triggers and stored
procedures in some of our applications. While attempting to convert from
MySQL 4.1 to 5.0 this past summer we uncovered numerous serious bugs in
MySQL all of which were reported. We subsequently discovered many of
these same bugs in version 4.1. None of the reported bugs have yet been
fixed in any version. Additionally the implementation of stored
procedures and triggers is so useless that it is laughable that they
even bothered to mention it as a feature.

Needless to say, this fall we began the conversion to PostgreSQL 8 and
we couldn't be more pleased. As a part of the conversion I found several
new bugs in MySQL due to PostgreSQL complaining about some of my old
queries. The most egregious of these are MySQL silently truncating var
chars that are too long for the column and silently allowing one to
insert a quoted string into an integer column. Ouch! We won't be using
MySQL for any new work here and will eventually migrate all of our
applications to PostgreSQL.

My heartiest congratulations to the entire PostgreSQL team for an
absolutely outstanding product!

dave


--
David Rogers
vnet 777-6522
MCI Network Security Operations Center

--- John Mohr <jtmohr@healthconresources.com> wrote:

> I've been reading the "AP2" thread and take it as a
> matter of faith that
> PGSQL is better than MySQL. It seems more complete
> for one thing. I have
> tried to explain my reasoning to several people when
> convincing them to use
> one versus the other. But does anyone know if there
> is a quasi-categorical
> comparison of the two so that I could speak with
> more conviction other than
> "I know it's better".
>

John,  when i first started developing in open source,
i did a lot of research on dbs.  mysql had a HUGE,
HUGE, HUGE advantage in documentation at the time -
and that was a big factor since i'd be starting from
scratch.

i chose postgrsql inspite of this.  native foreign key
constraints was a big reason.  i came to like
transactions and it is more sql compliant than mysql.
it is also less buggy.

the license is also much more application developer
friendly - and this is HUGE if you ever want to
develop a product and market it.

having said that, mysql is an INCREDIBLY popular
database.  those people that know it inside and out
will not be inclined to change easily - the devil you
know is better than the devil you don't know...

while i'd choose pgsql 10/10 times, a 5 year veteran
of mysql will likely choose mysql 10/10 times - and
that developer may well be an excellent developer and
that may be the right choice for him/her.

now, if you are going to be developing apps as part of
a team of folks who want to use mysql, that would be
tough.  i'd want to use postgresql, too.  do your
research regarding sql compliance, bug issues and
hacked "features" in mysql that were designed into
postgresql natively.

this isn't a run down on mysql, either.  it is a fact
they hacked in features after the fact.  a good mysql
developer may be able to tweak the system so it works
fine, but i prefer to avoid the jumping and the hoops.

mysql has a reputation for being blazing fast...  but
i have to say my postgresql db on our lan is blink of
the eye fast, too - although my records are currently
in the 100s, not 100,000s.

best of luck.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On 12/12/05, John Mohr <jtmohr@healthconresources.com> wrote:
>
> I've been reading the "AP2" thread and take it as a matter of faith that
> PGSQL is better than MySQL. It seems more complete for one thing. I have
> tried to explain my reasoning to several people when convincing them to use
> one versus the other. But does anyone know if there is a quasi-categorical
> comparison of the two so that I could speak with more conviction other than
> "I know it's better".

Here is my response: In the beginning, PostgreSQL focused on
implementing features and being as standards-compliant as possible.
MySQL focused on raw speed, and keeping the codebase to the smallest
footprint possible.

The end result is that the PostgreSQL folks implemented a ton of
features, and adhered to a lot of standards, and have now had plenty
of time to refactor and refine the software to provide significant
advances in speed and stability. MySQL is only now at the point where
they are starting to implement features and consider standards.

To me, this puts MySQL at the same point of PostgreSQL, just three or
four years ago. Why wait? ;-)

--
Mitch Pirtle
Joomla! Core Developer
Open Source Matters

On Tuesday 13 December 2005 12:03, Mitch Pirtle wrote:
> Here is my response: In the beginning, PostgreSQL focused on
> implementing features and being as standards-compliant as possible.
> MySQL focused on raw speed, and keeping the codebase to the smallest
> footprint possible.

If PostgreSQL is like that old saying "Make it right, then make it fast", I
guess my$ql is "Make it fast, then make it right".  Seems like a no brainer
to me  :-)

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

On Tue, Dec 13, 2005 at 08:36:12AM -0600, David Rogers wrote:
> Needless to say, this fall we began the conversion to PostgreSQL 8 and
> we couldn't be more pleased. As a part of the conversion I found several
> new bugs in MySQL due to PostgreSQL complaining about some of my old
> queries. The most egregious of these are MySQL silently truncating var
> chars that are too long for the column and silently allowing one to
> insert a quoted string into an integer column. Ouch! We won't be using
> MySQL for any new work here and will eventually migrate all of our
> applications to PostgreSQL.

You should make sure all that stuff is reported in the gotchas:
http://sql-info.de/mysql/gotchas.html
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Tue, Dec 13, 2005 at 02:24:48AM +0000, Pedro B. wrote:
> On Mon, 2005-12-12 at 20:02 -0600, John Mohr wrote:
> > But does anyone know if there is a quasi-categorical comparison of the
> > two so that I could speak with more conviction other than "I know it's
> > better".
>
> I've been involved in that comparison at work, and after some boring
> theoretical discussions, i once turned to my CTO and said:
>
> "Do a test. On the same machine, do a script that executes 50.000
> inserts, OR any other 50.000 SQL operations you want, and benchmark the
> results".

My personal favorite:

CREATE TABLE s(s smallint);
INSERT INTO s VALUES(99999);
SELECT * FROM t;

Seeing what those 3 lines produce is MySQL is enough to convert 50% of
people on the spot (after you tell them that *every* type in non-strict
MySQL operates that way).
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461