Humor me: Postgresql vs. MySql (esp. licensing)

Yes, I know you've seen the above subject before, so please be gentle with
the flamethrowers.

I'm preparing to enter a discussion with management at my company
regarding going forward as either a MySql shop or a Postgresql shop.

It's my opinion that we should be using PG, because of the full ACID
support, and the license involved. A consultant my company hired before
bringing me in is pushing hard for MySql, citing speed and community
support, as well as ACID support.

My biggest concern with MySQL is licensing. We need to keep costs low,
and last I remember the parent company was being pretty strict on "fair
use" under the GPL. If I recall, they even said a company would have to
license the commercial version if it were simply used operationally within
the company.

Also, I was under the impression that Postgresql had pretty much caught up
with MySql in the speed category...is this not the case?

Finally, ACID support in mysql always seemed kind of a hack....perhaps
this has changed?

Thanks for any input (armament ;) ) you can provide.

John

Sorry for the repost again. I emailed the Admin asking to cancel it (I
originally posted from a non-subscribed address), but perhaps he missed
it.

John

John Wells said:
> Yes, I know you've seen the above subject before, so please be gentle with
> the flamethrowers.
>
> I'm preparing to enter a discussion with management at my company
> regarding going forward as either a MySql shop or a Postgresql shop.
>
> It's my opinion that we should be using PG, because of the full ACID
> support, and the license involved. A consultant my company hired before
> bringing me in is pushing hard for MySql, citing speed and community
> support, as well as ACID support.
>
> My biggest concern with MySQL is licensing. We need to keep costs low,
> and last I remember the parent company was being pretty strict on "fair
> use" under the GPL. If I recall, they even said a company would have to
> license the commercial version if it were simply used operationally within
> the company.
>
> Also, I was under the impression that Postgresql had pretty much caught up
> with MySql in the speed category...is this not the case?
>
> Finally, ACID support in mysql always seemed kind of a hack....perhaps
> this has changed?
>
> Thanks for any input (armament ;) ) you can provide.
>
> John
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

John Wells wrote:

>Yes, I know you've seen the above subject before, so please be gentle with
>the flamethrowers.
>
>I'm preparing to enter a discussion with management at my company
>regarding going forward as either a MySql shop or a Postgresql shop.
>
>It's my opinion that we should be using PG, because of the full ACID
>support, and the license involved. A consultant my company hired before
>bringing me in is pushing hard for MySql, citing speed and community
>support, as well as ACID support.
>
>My biggest concern with MySQL is licensing. We need to keep costs low,
>and last I remember the parent company was being pretty strict on "fair
>use" under the GPL. If I recall, they even said a company would have to
>license the commercial version if it were simply used operationally within
>the company.
>
>Also, I was under the impression that Postgresql had pretty much caught up
>with MySql in the speed category...is this not the case?
>
>Finally, ACID support in mysql always seemed kind of a hack....perhaps
>this has changed?
>
>Thanks for any input (armament ;) ) you can provide.
>
>
Take a look at this document: http://sql-info.de/mysql/gotchas.html
You will find that there are some "features" that are realy undesirable
in a serious SGBD.

--
Diogo Biazus
diogo(at)ikono(dot)com(dot)br
http://www.ikono.com.br

> I'm preparing to enter a discussion with management at my company
> regarding going forward as either a MySql shop or a Postgresql shop.

- PostgreSQL supports constraints. MySQL doesn't; programmers need to
take care of that from the client side

- Define a 32-bit field in MySQL. Insert a 64-bit number instead. Common
sense tells you the value would be rejected. Yet MySQL happily folds it
in and carries on its merry way.

- Triggers: PostgreSQL yes, MySQL no. Translates into more work for your
MySQL developers in both creating your app and moving it forward with
each rev.

- Transactions: We've been here before. Suffice to say, MySQL+InnoDB is
almost there. Plain ol' MySQL doesn't have it, which tells you something
about their philosophy towards database design.

- Speed: mHz for mHz, MySQL has PostgreSQL beat for simple searches.
Once you start getting complex, PostgreSQL is competitive. I think this
speed issue is overrated: over time, PostgreSQL has sped up and MySQL
has slowed down which is pretty impressive, considering both have added
features from their early versions.

- Scalability: MySQL dies before PostgreSQL does. PostgreSQL under
extreme load may slow down, but it'll finish. MySQL simply gives up.

If the project is for slapping dynamic html on a page with data not
crucial for business, MySQL is probably fine. But if we're talking
business processes, data you care dearly about, MySQL is out. Lack of
constraints is the deal-breaker for me.

PostgreSQL is more comparable to Oracle. MySQL is more like Access --
quick and dirty.

But boy, they sure are good at marketing. Probably because MySQL is
developed by a single company with venture cap and a public relations
company whereas PostgreSQL is developed out in the open by a close-knit
community.

--
Best,
Al Hulaton | Sr. Account Engineer | Command Prompt, Inc.
503.222.2783 | ahulaton(at)commandprompt(dot)com
Home of Mammoth PostgreSQL and 'Practical PostgreSQL'
Managed PostgreSQL, Linux services and consulting
Read and Search O'Reilly's 'Practical PostgreSQL' at
http://www.commandprompt.com

> It's my opinion that we should be using PG, because of the full ACID
> support, and the license involved. A consultant my company hired before
> bringing me in is pushing hard for MySql, citing speed and community
> support, as well as ACID support.

Does the consultant push "speed AND ACID" or "speed OR ACID"? My point
is that PostgreSQL is said to be harder to install/maintain/tune than
MySQL. I have been reading some MySQL mailing list and for what I see
there, using InnoDB tables (the only way to have foreign keys,
transactions, and row level locking for MySQL) makes MySQL slower and
adds complexity to tuning the database. See this thread for example
http://lists.mysql.com/mysql/148832 . So when someone says that
PostgreSQL without tuning is 5 times slower than MySQL retrieving the
same query, it is quite right to also say that MySQL InnoDB without
tuning is 5 times slower than MySQL MyISAM.

In my opinion you might consider MySQL only when you don't need the
features provided by PostgreSQL (and even then data consistency and
durability issues favor PostgreSQL) because if you need them, your
developers need to implement them and do extra work, spending more time
and money. It was already mentioned but I'll post this link again
http://sql-info.de/mysql/ . IMHO if you are not aware of these gotchas
they can also increase development time because some things are too
different from regular/logical behavior (or common sense if you will) of
any other database.

Kaarel

> Does the consultant push "speed AND ACID" or "speed OR ACID"? My point
> is that PostgreSQL is said to be harder to install/maintain/tune than
> MySQL.

Erhm, I kind of missed half of my sentence or smth. Anyway the point is
that with MySQL you get *either* (speed) or (foreign keys and
transactions) not both as what seems the consultant is pushing.

Kaarel

Thanks for this information, it's very helpful. I've included some
additional comments to further demonstrate how a qualified business planner
may look at this...

>> I'm preparing to enter a discussion with management at my company
>> regarding going forward as either a MySql shop or a Postgresql shop.
>
> - PostgreSQL supports constraints. MySQL doesn't; programmers need to
> take care of that from the client side

Wow. That's so rediculous that I don't want to believe it because
this feature is just so basic.

> - Define a 32-bit field in MySQL. Insert a 64-bit number instead. Common
> sense tells you the value would be rejected. Yet MySQL happily folds it
> in and carries on its merry way.

That's unacceptable. To me, this is a complete show-stopper because I
simply won't tolerate data loss due to an idiotic design flaw.

> - Triggers: PostgreSQL yes, MySQL no. Translates into more work for your
> MySQL developers in both creating your app and moving it forward with
> each rev.

There are also circumstances where PostgreSQL will create implicit
triggers, which means to me that MySQL must be lacking in some other
features as a result of not supporting this.

> - Transactions: We've been here before. Suffice to say, MySQL+InnoDB is
> almost there. Plain ol' MySQL doesn't have it, which tells you something
> about their philosophy towards database design.

It also indicates that Transactions are new to this product, and so
one may be better off with a more experienced group of developers who've
already earned their "battle scars" as is obviously the case with
PostgreSQL.

> - Speed: mHz for mHz, MySQL has PostgreSQL beat for simple searches.
> Once you start getting complex, PostgreSQL is competitive. I think this
> speed issue is overrated: over time, PostgreSQL has sped up and MySQL
> has slowed down which is pretty impressive, considering both have added
> features from their early versions.

Do you know of any published benchmarks for this? I need to convince
some people who are hell-bent on MySQL being fast for everything that
they're mis-informed, and they refuse to take anyone's word for it.

> - Scalability: MySQL dies before PostgreSQL does. PostgreSQL under
> extreme load may slow down, but it'll finish. MySQL simply gives up.
[sNip]

I've experienced this very problem with MySQL actually. It seems that
Apache James (an open source Java-based SMTP/POP3 mail server) running on
FreeBSD will trigger this problem very quickly as soon as multiple users
attempt to send large (greater than 10 MBs) file attachments -- perhaps
JDBC is part of the problem, but in the Apache James error logs there is
indication of MySQL connectivity problems (also during busy times on
systems sending approximately 500,000 eMails per day).

--
Randolf Richardson - rr(at)8x(dot)ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

Randolf Richardson, DevNet SysOp 29 wrote:

>>- Define a 32-bit field in MySQL. Insert a 64-bit number instead. Common
>>sense tells you the value would be rejected. Yet MySQL happily folds it
>>in and carries on its merry way.
> That's unacceptable. To me, this is a complete show-stopper because I
> simply won't tolerate data loss due to an idiotic design flaw.

Worse. It is no data loss. It is loss of data integrity. If I know I have lost
two hours of work, I will crib but redo it. If I know around 5% of records are
messed up by database in last 5 years but don't know which, just think where do
I stand.

>>- Speed: mHz for mHz, MySQL has PostgreSQL beat for simple searches.
>>Once you start getting complex, PostgreSQL is competitive. I think this
>>speed issue is overrated: over time, PostgreSQL has sped up and MySQL
>>has slowed down which is pretty impressive, considering both have added
>>features from their early versions.
> Do you know of any published benchmarks for this? I need to convince
> some people who are hell-bent on MySQL being fast for everything that
> they're mis-informed, and they refuse to take anyone's word for it.

Good benchmarks are hard to come by for two reasons

1. It is very difficult not to be blamed biased.
2. Featuer compensation. What if you run a postgresql benchmark with triggers
and views, how do you test it with mysql anyways?

I would suggest you to try OSDB becnhmarks

http://osdb.sourceforge.net/

Your results will be great contribution to the community.

Or try porting pgbench to mysql innodb.

Actually I would like to see what the this benchmark does. Any prior knowledge
of the results?

>>- Scalability: MySQL dies before PostgreSQL does. PostgreSQL under
>>extreme load may slow down, but it'll finish. MySQL simply gives up.
>
> [sNip]
>
> I've experienced this very problem with MySQL actually. It seems that
> Apache James (an open source Java-based SMTP/POP3 mail server) running on
> FreeBSD will trigger this problem very quickly as soon as multiple users
> attempt to send large (greater than 10 MBs) file attachments -- perhaps
> JDBC is part of the problem, but in the Apache James error logs there is
> indication of MySQL connectivity problems (also during busy times on
> systems sending approximately 500,000 eMails per day).

Try dbmail. I am no mail admin but that is a mail server which works off
postgresql/mysql. http://www.dbmail.org

Shridhar
>

>>>- Define a 32-bit field in MySQL. Insert a 64-bit number instead.
>>>Common sense tells you the value would be rejected. Yet MySQL happily
>>>folds it in and carries on its merry way.
>>
>> That's unacceptable. To me, this is a complete show-stopper because I
>> simply won't tolerate data loss due to an idiotic design flaw.
>
> Worse. It is no data loss. It is loss of data integrity. If I know I
> have lost two hours of work, I will crib but redo it. If I know around
> 5% of records are messed up by database in last 5 years but don't know
> which, just think where do I stand.

That is worse, thanks for your clarity. I stopped before thinking it
through this far because any kind of screw-up like this just isn't worth
the risk in my opinion -- they really need to fix that and I think they
should make it a high priority.

[sNip]
>> Do you know of any published benchmarks for this? I need to convince
>> some people who are hell-bent on MySQL being fast for everything that
>> they're mis-informed, and they refuse to take anyone's word for it.
>
> Good benchmarks are hard to come by for two reasons
>
> 1. It is very difficult not to be blamed biased.
> 2. Featuer compensation. What if you run a postgresql benchmark with
> triggers and views, how do you test it with mysql anyways?
>
> I would suggest you to try OSDB becnhmarks
>
> http://osdb.sourceforge.net/

Thanks, I'll take a look at that.

> Your results will be great contribution to the community.

If I get enough spare time, I'll consider doing this.

> Or try porting pgbench to mysql innodb.
>
> Actually I would like to see what the this benchmark does. Any prior
> knowledge of the results?

I have no idea.

[sNip]
>> I've experienced this very problem with MySQL actually. It seems that
>> Apache James (an open source Java-based SMTP/POP3 mail server) running
>> on FreeBSD will trigger this problem very quickly as soon as multiple
>> users attempt to send large (greater than 10 MBs) file attachments --
>> perhaps JDBC is part of the problem, but in the Apache James error logs
>> there is indication of MySQL connectivity problems (also during busy
>> times on systems sending approximately 500,000 eMails per day).
>
> Try dbmail. I am no mail admin but that is a mail server which works off
> postgresql/mysql. http://www.dbmail.org

I see that it doesn't support file-based mail directories for storing
messages. That's too bad, because it just won't be able to meet the
performance of well-programmed mail servers such as Mercury (uses Novell
Directory Services for the user database) or qmail (can use PostgreSQL, and
other database engines, for the user database).

--
Randolf Richardson - rr(at)8x(dot)ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

It would be helpful to both communities if there was a factual
feature-by-feature grid comparing the relative capabilities of Postgresql
7.4 and MySQL 4.x. It would probably be best to limit it to objective
measures such as "Supports sub-selects in UPDATE queries". Even features
that are supported by both should be listed, just so the evaluator can
make it as a known issue. A separate supplemental section offering
subjective items such as scalability anecdotes would be useful, too.

An associate starting a project expecting to use MySQL asked me about it.
I had formed my own unscientific impressions that PostgreSQL was more
appropriate for his app, but I googled in vain for an updated PostgreSQL
vs. MySQL table as described above. I only found one from 2001, and many
of the distinctions no longer applied to current versions.

[sNip]
>> I would suggest you to try OSDB becnhmarks
>>
>> http://osdb.sourceforge.net/
>
> Thanks, I'll take a look at that.
>
>> Your results will be great contribution to the community.
>
> If I get enough spare time, I'll consider doing this.
[sNip]

According to their home page, tests are "underway" for both PostgreSQL
and MySQL. I guess I won't have to worry about this since someone else is
already working on it.

--
Randolf Richardson - rr(at)8x(dot)ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

> It would be helpful to both communities if there was a factual
> feature-by-feature grid comparing the relative capabilities of Postgresql
> 7.4 and MySQL 4.x. It would probably be best to limit it to objective
> measures such as "Supports sub-selects in UPDATE queries". Even features
> that are supported by both should be listed, just so the evaluator can
> make it as a known issue. A separate supplemental section offering
> subjective items such as scalability anecdotes would be useful, too.
>
> An associate starting a project expecting to use MySQL asked me about it.
> I had formed my own unscientific impressions that PostgreSQL was more
> appropriate for his app, but I googled in vain for an updated PostgreSQL
> vs. MySQL table as described above. I only found one from 2001, and many
> of the distinctions no longer applied to current versions.

How does this one look?

Database Server Feature Comparisons
http://www.mysql.com/information/crash-me.php

--
Randolf Richardson - rr(at)8x(dot)ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

If you'd like to head up this list, I'm sure people will answer any
questions you might have. Getting the two communities to agree will be
tricky, for example, you would have to break down the features into
minute detail: support subselects in SELECT clause, supports subselects
in FROM clause, supports subselects in WHERE clause.

then theres also the areas where the two project simply disagree... one
area is (something like) supporting 1 -- 1 = 0 i think, which mysql
supports but we dont. (course neither does the spec...)

and finally you need to work out how your going to define supporting
something. In postgresql we have basically two levels of supported
functionality... coded but not released, and released code. in mysql
they have alpha, beta, gamma... maybe more? and they have "plugins" that
might be needed in some functionality (think innodb for transactions,
and let's gloss over whether their implementation is acid compliant)

so just be prepared if your going to do it. also, if you do do it, i'd
like to see a feature comparison against some more interesting
candidates, like oracle or informix

Robert Treat

On Thu, 2003-11-20 at 11:12, Jeff Kowalczyk wrote:
> It would be helpful to both communities if there was a factual
> feature-by-feature grid comparing the relative capabilities of Postgresql
> 7.4 and MySQL 4.x. It would probably be best to limit it to objective
> measures such as "Supports sub-selects in UPDATE queries". Even features
> that are supported by both should be listed, just so the evaluator can
> make it as a known issue. A separate supplemental section offering
> subjective items such as scalability anecdotes would be useful, too.
>
> An associate starting a project expecting to use MySQL asked me about it.
> I had formed my own unscientific impressions that PostgreSQL was more
> appropriate for his app, but I googled in vain for an updated PostgreSQL
> vs. MySQL table as described above. I only found one from 2001, and many
> of the distinctions no longer applied to current versions.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Shridhar Daithankar wrote:

> Good benchmarks are hard to come by for two reasons
>
> 1. It is very difficult not to be blamed biased.
> 2. Featuer compensation. What if you run a postgresql benchmark with triggers
> and views, how do you test it with mysql anyways?

Good benchmark implementations you mean.

If you look for example at the TPC-W definitions, they define an
artificial bookstore web interface, from what communications have to be
encrypted down to what consitency constraints have to be guaranteed.
They do not force you to implement these constraints in any particular
way or tell you what part of the application logic has to run where. If
you're using PostgreSQL, you are absolutely fine implementing whatever
makes sense as a view, a stored procedure, whatever comes handy. Of
course, for the comparision to that other databaze, you have to
implement all these requirements in your middleware ... and then you get
into this biased blaming and so on and so forth.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

"Randolf Richardson, DevNet SysOp 29" <rr(at)8x(dot)ca> writes:
>> - Speed: mHz for mHz, MySQL has PostgreSQL beat for simple
>> searches. Once you start getting complex, PostgreSQL is
>> competitive. I think this speed issue is overrated: over time,
>> PostgreSQL has sped up and MySQL has slowed down which is pretty
>> impressive, considering both have added features from their early
>> versions.
>
> Do you know of any published benchmarks for this? I need to
> convince some people who are hell-bent on MySQL being fast for
> everything that they're mis-informed, and they refuse to take
> anyone's word for it.

Publishing benchmarks is more than a little troublesome.

The sorts of ways to make PostgreSQL _really shine_, performance-wise,
involves making use of features that MySQL simply doesn't have. Fancy
footwork with stored procedures, for instance.

And "they" would (somewhat rightly) object that this would represent a
test that is "intended to make MySQL fail."

And if you start publishing results, MySQL AB might be able to send
"attack lawyers" at you, irrespective of whether there's a case with
merit. Virtually all of the commercial databases have a licensing
clause that forbids publishing benchmarks without the express consent
of the vendor, and a decent case can be made that this is a legitimate
'right' for the vendor to have.

In any case, I would think it doubtful that there is anything
reasonable to present to people that are so religiously "hell-bent,"
in any case. If they don't want to believe you, they will find
excuses to disbelieve whatever results you may give them.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

[sNip]
>> Do you know of any published benchmarks for this? I need to
>> convince some people who are hell-bent on MySQL being fast for
>> everything that they're mis-informed, and they refuse to take
>> anyone's word for it.
>
> Publishing benchmarks is more than a little troublesome.
>
> The sorts of ways to make PostgreSQL _really shine_, performance-wise,
> involves making use of features that MySQL simply doesn't have. Fancy
> footwork with stored procedures, for instance.

Then a different approach is needed. One idea I can think of is to
come up with a set of both simple and complex objectives, and then see
which databases yield the best results. I believe this type of testing
would reveal more useful results for someone who's faced with the task of
choosing the right database engine for the job.

> And "they" would (somewhat rightly) object that this would represent a
> test that is "intended to make MySQL fail."

Unfortunately this is what happens whenever we compare Apples to
Oranges. Let's refrain from bringing Bananas into this. ;-D

> And if you start publishing results, MySQL AB might be able to send
> "attack lawyers" at you, irrespective of whether there's a case with

They can do this regardless of what statements are made. In fact,
they could even take issue with something completely irrelevant and,
although the courts may laugh it out of the courtroom, it could be very
much like a DoS attack, but I doubt MySQL would be so bold since it would
likely be portrayed as bad publicity from the anti-corporation types who
seem to be quite easy to find in just about every open source community.

> merit. Virtually all of the commercial databases have a licensing
> clause that forbids publishing benchmarks without the express consent
> of the vendor, and a decent case can be made that this is a legitimate
> 'right' for the vendor to have.

In that case MySQL could be in a lot of legal trouble in the future
given this web page where they include comparisons with database engine
products from various vendors (e.g., Microsoft, Oracle, SyBase, etc.):

MySQL Benchmarks
http://www.mysql.com/information/benchmarks-old.html

> In any case, I would think it doubtful that there is anything
> reasonable to present to people that are so religiously "hell-bent,"
> in any case. If they don't want to believe you, they will find
> excuses to disbelieve whatever results you may give them.

Not everyone is hell-bent on brand names when it comes to determining
which products best fit the job at hand.

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

Randolf Richardson wrote:

>> And if you start publishing results, MySQL AB might be able to send
>> "attack lawyers" at you, irrespective of whether there's a case with
>
> They can do this regardless of what statements are made. In fact,
> they could even take issue with something completely irrelevant and,
> although the courts may laugh it out of the courtroom, it could be very
> much like a DoS attack, but I doubt MySQL would be so bold since it would
> likely be portrayed as bad publicity from the anti-corporation types who
> seem to be quite easy to find in just about every open source community.

You didn't follow the MySQL vs. NuSphere (aka PeerDirect) clash? It's
been even on our mailing lists, search the archives.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #