Thread: Need Some Recent Information on the Differences between Postgres and MySql

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...

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:

> unless there was a specific reason to migrate

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:

>  The built in MyISAM
> solution is the best known

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:

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

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

John Gage, 25.06.2010 11:50:
> 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.

Getting really off-topic now: but MySQL does support Regex

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Disabused.

On Jun 25, 2010, at 11:59 AM, Thomas Kellerer wrote:

>
> Getting really off-topic now: but MySQL does support Regex
>
> http://dev.mysql.com/doc/refman/5.1/en/regexp.html
>

On Fri, Jun 25, 2010 at 4:22 AM, John Gage <jsmgage@numericable.fr> wrote:
> There are features, are there not, that Postgres has that MySQL does not
> have?

My favorite pgsql feature is partial and functional indexes.  For
instance, let's say you have a work queue, and in it you have a boole
called processed.  it is 99.999% true, and you need an index for quick
lookup on the ones that are false.

create index yada on table blah (id) where processed is false.

Now any query that does select id from table blah where processed is
false can use that index for quick lookups.

Now supposed you want to lookup a table based on the first two
characters of some field.

create index yada on table blah (substring(textfield,1,2));

Next up: PostgreSQL stores its system catalogs in transaction safe
table types, like everything else it stores.  MySQL stores its table
defs in myisam, even if the whole of the db you create is innodb and
innodb is the default.  System crash in the middle of DDL?  Might lose
a table or two.

Next up: MySQL has optimizations made without proper testing.  For
example, see this bug:

http://bugs.mysql.com/bug.php?id=28591

This "optimization" made MySQL ignore the DESC keywork in innodb
tables.  It was pushed into live, production ready MySQL code
midstream in version 5.0.28 on 2007-08-02.  MySQL GA (i.e. production
ready) release had been made two years previously in March of 2005.

Fix was pushed out on 2007-09-24, nearly 60 days later, to version
5.0.48.  Problem solved right?  Well, not really, according to
http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed.  The
actual fix gets pushed out on 2008-09-12.

This shows several things about the MySQL release philosophy, at least
at the time.  1: Introducing performance enhancments without thorough
testing in a production release is A-OK.  2: The fix may or may not
actually work when it does get applied. and 3: It can take about a
year to get that fix in place.

Things may be a LOT better by now.  I'd certainly hope so.  But I have
no real confidence or evidence of such an internal change.

Now compare that to pgsql bugs introduced that may clobber your data
in some way.  It's easy to do, look for a production release, followed
by another production release within a day or two.  It's happened
about two times I can remember off the top of my head with pgsql.  The
new version comes out, a horrific bug is found, fix is made, new
version release, old one pulled.  For 8.1 it looks like there was a
two day window where overly restrictive type length in functions and
constraints snuck out.  Two. Days.  There was another release that was
never actually released, might have had a show stopper in it too.

Biggest difference between MySQL and PostgreSQL? The developers.

On Fri, Jun 25, 2010 at 7:58 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> This shows several things about the MySQL release philosophy, at least
> at the time.  1: Introducing performance enhancments without thorough
> testing in a production release is A-OK.  2: The fix may or may not
> actually work when it does get applied. and 3: It can take about a
> year to get that fix in place.
>
> Things may be a LOT better by now.  I'd certainly hope so.  But I have
> no real confidence or evidence of such an internal change.

Note that I have a lot of respect for the Drizzle team and what
they're doing with MySQL's code base.  It seems like they "get it" as
a team, while MySQL never did.

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

My favorite features in Postgres that MySQL doesn't have

- deferrable constraints
- sequences
- check constraints
- windowing functions
- recursive common table expressions
- the absence of a program to check the consistency of the data
- the ability to use a subselect in a DML statement that references the table to be updated
- generate_series()
- array handling


Thomas

Thomas Kellerer, 25.06.2010 14:32:
> 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
>
> My favorite features in Postgres that MySQL doesn't have
>
> - deferrable constraints
> - sequences
> - check constraints
> - windowing functions
> - recursive common table expressions
> - the absence of a program to check the consistency of the data
> - the ability to use a subselect in a DML statement that references the
> table to be updated
> - generate_series()
> - array handling
>

And another thing:

The following works in Postgres (and Oracle, DB2, SQL Server, Derby) but not in MySQL (using InnoDB):

create table fktest (
    id        integer primary key not null,
    name      varchar(20),
    parent_id integer
  );
alter table fktest add constraint fktest_parent foreign key (parent_id) references  fktest(id);

insert into fktest (id,name,parent_id) values (1,'Root', null);
insert into fktest (id,name,parent_id) values (2,'Sub1', 1);
insert into fktest (id,name,parent_id) values (3,'Subsub', 2);
insert into fktest (id,name,parent_id) values (4,'Sub2', 1);
commit;

delete from fktest where id in (1,2,3,4);
commit;

MySQL complains that it cannot delete the rows"Cannot delete or update a parent row: a foreign key constraint fails"


Regards
Thomas

Dave Page <dpage@pgadmin.org> writes:
> 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.

There actually were two smgr storage modules in the code we inherited
from Berkeley, and I think there were probably more at one time.  But
the smgr interface is *way* lower level than mysql's storage engines;
there is not that much that you can do to affect the behavior of the DB
by replacing an smgr module.  I believe what they had in mind originally
was to be able to drive different physical storage devices, using raw
access instead of going through the filesystem.  That decision was taken
before everything of interest got unified under the Unix filesystem API.
These days, if you needed to do what they had in mind, you'd be writing
a kernel device driver instead.  So smgr is pretty vestigial, and we've
largely broken its API abstraction anyway by doing filesystem access
directly in so many other places.

            regards, tom lane

On Fri, Jun 25, 2010 at 2:02 PM, Jim Montgomery <monty1967@hotmail.com> wrote:
> Remove me from your email chain.

You need to unsubscribe from the mailing list. Until now your weren't
explicitly included on any of the messages that I can see.

http://www.postgresql.org/community/lists/

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

Remove me from your email chain.
 
> Date: Fri, 25 Jun 2010 10:44:34 +0100
> Subject: Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
> From: dpage@pgadmin.org
> To: andreas.kretschmer@schollglas.com
> CC: pgsql-general@postgresql.org
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Get a free e-mail account with Hotmail. Sign-up now.

Remove me from your email chain!
 
> Date: Fri, 25 Jun 2010 08:13:36 -0400
> Subject: Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
> From: scott.marlowe@gmail.com
> To: jsmgage@numericable.fr
> CC: wultsch@gmail.com; mary.y.wang@boeing.com; pgsql-general@postgresql.org
>
> On Fri, Jun 25, 2010 at 7:58 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > This shows several things about the MySQL release philosophy, at least
> > at the time.  1: Introducing performance enhancments without thorough
> > testing in a production release is A-OK.  2: The fix may or may not
> > actually work when it does get applied. and 3: It can take about a
> > year to get that fix in place.
> >
> > Things may be a LOT better by now.  I'd certainly hope so.  But I have
> > no real confidence or evidence of such an internal change.
>
> Note that I have a lot of respect for the Drizzle team and what
> they're doing with MySQL's code base. It seems like they "get it" as
> a team, while MySQL never did.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Get a free e-mail account with Hotmail. Sign-up 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

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

---

Get a new e-mail account with Hotmail - Free. Sign-up now.

Jim Montgomery <monty1967@hotmail.com> writes:
> Remove me from your email chain.

Jim, are you trying to win the Jerk of the Month award?  There's an
unsubscribe link on every PG list message.  All you're accomplishing
with this is to annoy other list members who cannot unsubscribe you.

            regards, tom lane

On Fri, Jun 25, 2010 at 1:44 AM, 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.
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise Postgres Company
>

In many cases this criticism would be correct with the current
interface. Drizzle has already changed the interface and is looking to
allow more knowledge to be passed back to planner. In a few years
MySQL like systems may have as much knowledge as PG does.

The freedom of the storage engine interface allows for much more
varied backend characteristics. Some examples:
- NDB: A GPL'ed distributed highly redundant transactional storage
engine for MySQL that can non-impactfully survive the lose of servers.
I know of no PG equivilant.
- TokuDB and Infobright: Data warehousing backends. The alternatives
data warehousing forks based on PG that I know of are true forks which
end up diverging significantly from the mainline. With MySQL the
esoteric backends can stay current with mainline easily. IIRC both
have incorporated new planner'ish features.
- Sphinx : Fulltext indexing in MySQL done right.
- CSV : A SQL interface to CRUD CSV. I know of no comparable in PG.
- Blitzdb :An interesting new non-transactional engine that has
recently been merged into Drizzle.
- Innodb : The primary transactional storage engine for MySQL. It does
not have all the features of PG (like check contraints), but it has
some features (like Compression!!!) which are *exceptionally* useful.
The backend being seperate from the core has in the last few years
allowed significant features additions/changes (thank you Oracle).
PostGIS is somewhat similar in how it is decoupled from core.
- Blackhole : A storage engine that does not actually store data. I
have used this for a variety of purposes including making ORM happy
and allowing the dropping of parts of an application backend without
breaking the application completely.

To some in the MySQL community much of the most interesting
development has happened outside of core. I guess the crux of my point
is that storage engine interface allows for many features that are not
found in PG.




--
Rob Wultsch
wultsch@gmail.com

On Fri, Jun 25, 2010 at 11:48 AM, Rob Wultsch <wultsch@gmail.com> wrote:
> - Innodb : The primary transactional storage engine for MySQL. It does
> not have all the features of PG (like check contraints), but it has
> some features (like Compression!!!) which are *exceptionally* useful.

You do know that pg has compression for text types built in already,
right?  I'm sure there are subtle differences in the way compression
is done in each engine, just pointing that out.

On Fri, Jun 25, 2010 at 4:58 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Next up: PostgreSQL stores its system catalogs in transaction safe
> table types, like everything else it stores.  MySQL stores its table
> defs in myisam, even if the whole of the db you create is innodb and
> innodb is the default.  System crash in the middle of DDL?  Might lose
> a table or two.

This is not true. MySQL stores users, acl, etc in MyISAM tables. In
almost all setups users and acl do not change often so the crash
sensitivity is not a big issue. I have dealt with (and still do deal
with) horribly abused MySQL instances and I very very rarely run into
issues with corruption on the system schema.

MyISAM in not involved in the storage of data about Innodb. MySQL
table definitions are stored in .frm files. Alterations to table
definitions in MySQL (in general) are done by building a temporary
table with the new definitions and the existing data and then shell
gaming the files in. It is possible to have issues from a crash, but
it is very rare.


>
> Next up: MySQL has optimizations made without proper testing.  For
> example, see this bug:
>
> http://bugs.mysql.com/bug.php?id=28591
>
> This "optimization" made MySQL ignore the DESC keywork in innodb
> tables.  It was pushed into live, production ready MySQL code
> midstream in version 5.0.28 on 2007-08-02.  MySQL GA (i.e. production
> ready) release had been made two years previously in March of 2005.
>
> Fix was pushed out on 2007-09-24, nearly 60 days later, to version
> 5.0.48.  Problem solved right?  Well, not really, according to
> http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed.  The
> actual fix gets pushed out on 2008-09-12.
>
> This shows several things about the MySQL release philosophy, at least
> at the time.  1: Introducing performance enhancments without thorough
> testing in a production release is A-OK.  2: The fix may or may not
> actually work when it does get applied. and 3: It can take about a
> year to get that fix in place.
>
> Things may be a LOT better by now.  I'd certainly hope so.  But I have
> no real confidence or evidence of such an internal change.

Sun/Oracle has improved things a lot. Long standing bugs are being
closed and it feels like more care is being put into releases.


--
Rob Wultsch
wultsch@gmail.com

On Fri, Jun 25, 2010 at 8:56 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Jun 25, 2010 at 11:48 AM, Rob Wultsch <wultsch@gmail.com> wrote:
>> - Innodb : The primary transactional storage engine for MySQL. It does
>> not have all the features of PG (like check contraints), but it has
>> some features (like Compression!!!) which are *exceptionally* useful.
>
> You do know that pg has compression for text types built in already,
> right?  I'm sure there are subtle differences in the way compression
> is done in each engine, just pointing that out.
>

I do, but TOAST is a very different animal than compression at the
page level. The innodb buffer pool is also effectively compressed
which allows for far greater use of memory.

--
Rob Wultsch
wultsch@gmail.com

On Fri, Jun 25, 2010 at 09:44:04AM +0100, Dave Page wrote:
> 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.

Having it divided off at the place where it's divided in MySQL is
certainly such a barrier.  Having a storage API, as PostgreSQL used to
have, and will have again with SQL/MED, doesn't necessarily present
such a barrier.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, 25 Jun 2010 08:48:11 -0700
Rob Wultsch <wultsch@gmail.com> wrote:

> The freedom of the storage engine interface allows for much more
> varied backend characteristics. Some examples:

This is *really* fascinating but pg transactional engine is very
mature and solid.
Before any of the should-be-really-transactional-engine of MySQL
will be as mature as pg's one, pg will have a lot more
feature/speed/development sugar.

If I didn't need a transactional engine I'd be happy to go (well not
really happy[1]) with MySQL and any of its engines.

It is nice to reuse and mix and match different tools.


[1] I find some inconsistency of mysql a bit painful to live with...
as I find some inconsistency in PHP equally irritating but they are
there, quite diffused and still for certain domains the best
compromise available.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On 6/25/2010 4:22 AM, John Gage wrote:
> There are features, are there not, that Postgres has that MySQL does not
> have?

Yes, a big one would be data integrity.  Most people would not consider
data integrity an optional feature in a DBMS, but apparently MySQL does.
  Try this in MySQL:

create table t1 (f1 varchar(10))
insert into t1 values('this is a long string')
select * from t1

When I do this on a version 5.0.44 MySQL DB (the latest I have
conveniently available), MySQL processes the insert without complaint,
silently truncating the string to "this is a".  Why people aren't
bothered by this is beyond me.

--
Guy Rouillier

Tom Lane wrote:
> Dave Page <dpage@pgadmin.org> writes:
> > 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.
>
> There actually were two smgr storage modules in the code we inherited
> from Berkeley, and I think there were probably more at one time.  But
> the smgr interface is *way* lower level than mysql's storage engines;
> there is not that much that you can do to affect the behavior of the DB
> by replacing an smgr module.  I believe what they had in mind originally
> was to be able to drive different physical storage devices, using raw
> access instead of going through the filesystem.  That decision was taken
> before everything of interest got unified under the Unix filesystem API.
> These days, if you needed to do what they had in mind, you'd be writing
> a kernel device driver instead.  So smgr is pretty vestigial, and we've
> largely broken its API abstraction anyway by doing filesystem access
> directly in so many other places.

Yes, the second storage manager we had was for WORM drives, or more
accurately, stubs were left in our code for WORM drives.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> There actually were two smgr storage modules in the code we inherited
>> from Berkeley, and I think there were probably more at one time.

> Yes, the second storage manager we had was for WORM drives, or more
> accurately, stubs were left in our code for WORM drives.

No, the other storage manager in the Berkeley sources was for keeping
stuff in "hopefully stable" main memory:
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/smgr/Attic/mm.c?rev=1.1;content-type=text%2Fplain

Of course that was dead as a doornail after people stopped using
magnetic-core main memory.  There are references to a WORM storage
manager in the Berkeley Postgres papers, but it's not clear to me
if they ever wrote one or merely theorized about it.  The hint-bit
stuff, to take just one example, would never have worked on WORM
storage, and yet it's implemented above the smgr API.  So it seems
kinda unlikely that there was ever a working WORM smgr.

            regards, tom lane

> John Gage, 25.06.2010 11:50:
>> I am astonished to discover that MySQL does not support
>> regular expressions much less something like tsvector.
>
> Getting really off-topic now: but MySQL does support Regex
>
> http://dev.mysql.com/doc/refman/5.1/en/regexp.html

I have done an extensive comparison between MySQL's support for regexp
and Postresql's and, frankly, there is no comparison.  The support in
Postgresql is far greater than in MySQL.  This is not a flame.  It is
intended to help anyone choosing between the two programs.

The best example I can present is the regexp_split_to_table function
in Postgres.  I use it all the time.  It is enormously convenient.
Anyone analyzing text is ecstatic to have such a powerful function
readily available.

This is a qualitative difference.

John

On Sun, Jul 18, 2010 at 2:13 PM, John Gage <jsmgage@numericable.fr> wrote:
>> John Gage, 25.06.2010 11:50:
>>>
>>> I am astonished to discover that MySQL does not support
>>> regular expressions much less something like tsvector.
>>
>> Getting really off-topic now: but MySQL does support Regex
>>
>> http://dev.mysql.com/doc/refman/5.1/en/regexp.html
>
> I have done an extensive comparison between MySQL's support for regexp and
> Postresql's and, frankly, there is no comparison.  The support in Postgresql
> is far greater than in MySQL.  This is not a flame.  It is intended to help
> anyone choosing between the two programs.
>
> The best example I can present is the regexp_split_to_table function in
> Postgres.  I use it all the time.  It is enormously convenient.  Anyone
> analyzing text is ecstatic to have such a powerful function readily
> available.
>
> This is a qualitative difference.

That and array_accum (in the docs in 8.3 I think it's built in now)
make life a breaze for making reports.  No need for post query data
mangling, I get it out just the way I need it.