Need Some Recent Information on the Differences between Postgres and MySql

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wang, Mary Y
Sent: Thursday, June 24, 2010 4:05 PM
To: pgsql-general
Subject: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql

Hi,

I'm trying to find some write-ups about the differences between Postgres and MySql. A lot of stuff showed up on Google, but most of them are old.
I saw this wiki over here http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by EnterpriseDB.

Are there any other most recent summaries on the differences between Postgres and MySql?

An extremely important difference is the license structure:

MySQL is either GPL or commercial. If your project is GPL open source then you can use the GPL version but if not and it is a commercial project then you must purchase a commercial license.

PostgreSQL is Berkeley licensed and so you can use it with impunity for any sort of project without the need to purchase a license.

They are both nice, mature software tools and either would be suitable for doing something like setting up a web site.
<<

On Thu, Jun 24, 2010 at 7:04 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:

Hi,

I'm trying to find some write-ups about the differences between Postgres and
MySql.  A lot of stuff showed up on Google, but most of them are old.
I saw this wiki over here
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and
plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by
EnterpriseDB.

Are there any other most recent summaries on the differences between
Postgres and MySql?

The philosophical difference tends to be that MySQL tends to lean
towards being easier to use, and when it comes to a choice between
absolutely "correct" operation and "just do it" MySQL will just do it.
PostgreSQL tends to throw errors more often if what you're asking it
to do is not explicitly correct.

For instant, by default, this will work in mysql:

create table test (i int);
insert into test (i) values ('');

with a warning, but will produce an error in most modern versions of pgsql.

Slowly, MySQL becomes more standards compliant while pgsql gets easier
to use, but generally the differences like this remain pretty common.

On Thu, Jun 24, 2010 at 6:13 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

For instant, by default, this will work in mysql:

create table test (i int);
insert into test (i) values ('');

with a warning, but will produce an error in most modern versions of pgsql.

However it is easy to get mostly sane behavior from MySQL:

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test (i) values ('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'i' at row 1

If it were me I would generally work with whichever system I knew
better unless there was a specific reason to migrate. Both systems
will be a bit of a pain as they are both complicated. C'est la vie.

All else being equal I would start a new project with PG.

Full disclosure: I am a MySQL DBA.

Best,

Rob Wultsch
wultsch@gmail.com

On Thu, Jun 24, 2010 at 9:46 PM, Rob Wultsch <wultsch@gmail.com> wrote:

On Thu, Jun 24, 2010 at 6:13 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

For instant, by default, this will work in mysql:

create table test (i int);
insert into test (i) values ('');

with a warning, but will produce an error in most modern versions of pgsql.

However it is easy to get mostly sane behavior from MySQL:

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test (i) values ('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'i' at row 1

Now if there were just a way to turn it on and not let the user turn it off...

If it were me I would generally work with whichever system I knew
better unless there was a specific reason to migrate. Both systems
will be a bit of a pain as they are both complicated. C'est la vie.

All else being equal I would start a new project with PG.

Agreed. I find that PostgreSQL tends to teach you fewer bad habits and
MySQL does.

Full disclosure: I am a MySQL DBA.

I'm a pgsql DBA...

Remove me from your email chain.

Date: Thu, 24 Jun 2010 21:57:15 -0400
Subject: Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
From: scott.marlowe@gmail.com
To: wultsch@gmail.com
CC: mary.y.wang@boeing.com; pgsql-general@postgresql.org

On Thu, Jun 24, 2010 at 9:46 PM, Rob Wultsch <wultsch@gmail.com> wrote:

On Thu, Jun 24, 2010 at 6:13 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

For instant, by default, this will work in mysql:

create table test (i int);
insert into test (i) values ('');

with a warning, but will produce an error in most modern versions of pgsql.

However it is easy to get mostly sane behavior from MySQL:

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test (i) values ('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'i' at row 1

Now if there were just a way to turn it on and not let the user turn it off...

If it were me I would generally work with whichever system I knew
better unless there was a specific reason to migrate. Both systems
will be a bit of a pain as they are both complicated. C'est la vie.

All else being equal I would start a new project with PG.

Agreed. I find that PostgreSQL tends to teach you fewer bad habits and
MySQL does.

Full disclosure: I am a MySQL DBA.

I'm a pgsql DBA...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
http://clk.atdmt.com/UKM/go/195013117/direct/01/
We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now

On Thu, Jun 24, 2010 at 10:03 PM, Jim Montgomery <monty1967@hotmail.com> wrote:

Remove me from your email chain.

Remove yourself.
--
Rob Wultsch
wultsch@gmail.com

Wang, Mary Y, 25.06.2010 01:04:

Hi,
I'm trying to find some write-ups about the differences between Postgres
and MySql. A lot of stuff showed up on Google, but most of them are old.
I saw this wiki over here
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and
plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered
by EnterpriseDB.
Are there any other most recent summaries on the differences between
Postgres and MySql?
Thanks in advance
Mary

You might be interested in these postings (from a MySQL developer?)

http://krow.livejournal.com/692692.html
http://marksverbiage.blogspot.com/2010/05/mysql-what-are-you-smoking.html

Thomas

On Fri, Jun 25, 2010 at 12:04 AM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:

Hi,

I'm trying to find some write-ups about the differences between Postgres and
MySql.  A lot of stuff showed up on Google, but most of them are old.
I saw this wiki over here
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and
plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by
EnterpriseDB.

If you mean the one from last week, then that would be a good choice.
It's got our chief architect discussing the topic with our newest
member of the EDB team, Robin Schumacher, who spent many years working
in a senior product management position at MySQL.

http://www.enterprisedb.com/learning/postgresql_vs_mysql.do

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

There are features, are there not, that Postgres has that MySQL does
not have?

I refer in particular to things like tsvector.

Am I mistaken in this?

John

On Jun 25, 2010, at 3:46 AM, Rob Wultsch wrote:

On Fri, Jun 25, 2010 at 1:22 AM, John Gage <jsmgage@numericable.fr> wrote:

There are features, are there not, that Postgres has that MySQL does not
have?

I refer in particular to things like tsvector.

Am I mistaken in this?

John

On Jun 25, 2010, at 3:46 AM, Rob Wultsch wrote:

unless there was a specific reason to migrate

MySQL has several full text search solutions. The built in MyISAM
solution is the best known, but there is also an engine for using
sphinx.

...

And there are features that MySQL has that PG does not. Index only
queries is a massive feature. Pluggable backend storage engines are
another. MySQL is also somewhat simpler to tune.

Both systems can work well. Both have advantages. Both can suck.

As has been just demonstrated, both have communities that suck at
mingling with the other major open source rdms.

--
Rob Wultsch
wultsch@gmail.com

On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch <wultsch@gmail.com> wrote:

MySQL has several full text search solutions. The built in MyISAM
solution is the best known, but there is also an engine for using
sphinx.

...

And there are features that MySQL has that PG does not. Index only
queries is a massive feature. Pluggable backend storage engines are
another.

Some might argue that is not a feature. Sure, it means you can have
different types of storage, but it means the feature set gets
fragmented - for example, if you want text search, you use MyISAM, but
if you want relational integrity you have to use InnoDB or some other
backend. You want both? Oh. Hmmm.

It could also be argued that having a storage engine API means that
the query planner/optimiser cannot have nearly as much knowledge about
how the data is stored and what access characteristics it may have
thus preventing it from being as well optimised as Postgres.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On 25 June 2010 09:44, Dave Page <dpage@pgadmin.org> wrote:

On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch <wultsch@gmail.com> wrote:

MySQL has several full text search solutions. The built in MyISAM
solution is the best known, but there is also an engine for using
sphinx.

...

And there are features that MySQL has that PG does not. Index only
queries is a massive feature. Pluggable backend storage engines are
another.

Some might argue that is not a feature. Sure, it means you can have
different types of storage, but it means the feature set gets
fragmented - for example, if you want text search, you use MyISAM, but
if you want relational integrity you have to use InnoDB or some other
backend. You want both? Oh. Hmmm.

It could also be argued that having a storage engine API means that
the query planner/optimiser cannot have nearly as much knowledge about
how the data is stored and what access characteristics it may have
thus preventing it from being as well optimised as Postgres.

Didn't PostgreSQL used to have more than 1 storage engine in the past?
I thought I read somewhere it did, but it was decided it was a
compromise on stability and/or quality, so ended up using a single
kick-ass engine?

Thom

On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown <thombrown@gmail.com> wrote:

Didn't PostgreSQL used to have more than 1 storage engine in the past?
 I thought I read somewhere it did, but it was decided it was a
compromise on stability and/or quality, so ended up using a single
kick-ass engine?

Yes, many, many moons ago.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

Forgive me for being somewhat stupid, but is MyISAM a text search
engine? The Wikipedia article doesn't make it sound like one.

Could you be more specific as to how, for example, MySQL implements
regular expressions or the tsvector funcitionality?

John

On Jun 25, 2010, at 10:33 AM, Rob Wultsch wrote:

In response to Dave Page :

On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown <thombrown@gmail.com> wrote:

Didn't PostgreSQL used to have more than 1 storage engine in the past?
�I thought I read somewhere it did, but it was decided it was a
compromise on stability and/or quality, so ended up using a single
kick-ass engine?

Yes, many, many moons ago.

Really? Do you have a link?

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

In response to John Gage :

Forgive me for being somewhat stupid, but is MyISAM a text search
engine? The Wikipedia article doesn't make it sound like one.

MyISAM provides textsearch and other features, but no referential
integrity. It's just one of many storage engines.

Could you be more specific as to how, for example, MySQL implements
regular expressions or the tsvector funcitionality?

I think, this is the wrong place to explain mysql-features...

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

On Fri, Jun 25, 2010 at 10:22 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

In response to Dave Page :

On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown <thombrown@gmail.com> wrote:

Didn't PostgreSQL used to have more than 1 storage engine in the past?
 I thought I read somewhere it did, but it was decided it was a
compromise on stability and/or quality, so ended up using a single
kick-ass engine?

Yes, many, many moons ago.

Really? Do you have a link?

Hmm, I think I misread Thom's question. The smgr API used to be far
more rigidly designed as I understand it, to allow the possibility of
having different storage engines (for example, maybe one that used raw
devices). I don't know that any other storage engines were ever
actually written though.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

In the words of Dwight Eisenhower, I couldn't fail to disagree with
you less. That said...

Replying to my own post, and on further examination of the MySQL
documentation, I am astonished to discover that MySQL does not support
regular expressions much less something like tsvector. Please
disabuse me of this idea if I am mistaken.

To me, this turns MySQL into a toy. Regular expressions are an
extraordinarily powerful tool rooted in science that make manipulating
text data infinitely easier. To leave them out of a system (recall
that the Macintosh is based on Unix and supports egrep, for example,
out of the box) is unbelievably backward.

Why extirpate part of your brain if you don't have to? MySQL thus
becomes part of Gödel's inferred conspiracy to make men stupid.

John

P.S. I am aware that MySQL has its own, roll your own, text search
capability...which adds insult to injury.

P. P. S. I realize that there is an element of flame here. However,
the facts are the facts and anyone wanting to judge between Postgres
and MySQL has to deal in facts.

On Jun 25, 2010, at 11:37 AM, A. Kretschmer wrote:

On 25 June 2010 10:50, John Gage <jsmgage@numericable.fr> wrote:

In the words of Dwight Eisenhower, I couldn't fail to disagree with you
less.  That said...

Replying to my own post, and on further examination of the MySQL
documentation, I am astonished to discover that MySQL does not support
regular expressions much less something like tsvector.  Please disabuse me
of this idea if I am mistaken.

To me, this turns MySQL into a toy.  Regular expressions are an
extraordinarily powerful tool rooted in science that make manipulating text
data infinitely easier.  To leave them out of a system (recall that the
Macintosh is based on Unix and supports egrep, for example, out of the box)
is unbelievably backward.

I still find it frustrating that I can't use Perl-style regular
expressions in PostgreSQL though... although it might be the case that
it does, and that I just don't know how to use it.

Thom