MySQL vs. PostgreSQL

On 10 Jul 2002, builder wrote:

Which is better, PostgreSQL or MySQL? Both are excellent products with
unique strengths, and the choice is often a matter of personal
preference. Read on for a useful comparison of these two open source
database systems.
http://builder.com.com/article.jhtml?id=u00320020624gcn01.htm

For some definition of "useful." You say in your table that postgres
is slower. How did you measure this?

Not many Web developers use PostgreSQL because they feel that
the additional features degrade performance.

Why developers should feel that features such as MVCC will degrade
performance, I have no idea.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

...

Not many Web developers use PostgreSQL because they feel that
the additional features degrade performance.

"Not many web developers" is a completely subjective statement which has
no basis in fact. How many is "not many"? Is that two? One hundred? Two
million? Twenty million?

How many is "many web developers"? Must be more than "not many", eh?
Probably can't narrow it down much beyond that :/

Hmm. Given the proven performance scaling benefits from Postgres, one
could also say "not many web developers feel that their applications
need to scale beyond 5 simultaneous users". Or "not many web developers
need transaction-enabled databases", or ???

- Thomas

This web developer for both internet and intranet applications, who does
both connectionless and simulated connection oriented applications, uses
PostgreSQL WHERE EVER POSSIBLE. If I ever have issues with load, there are
much better ways of getting performance then choosing a less capable
database for its trimness (eg horsepower and memory are cheap, dedicated
servers are cheap, and better table design/layout/indexes usually will fix
ANY databases slowness where mass volume is not the issue).

I train all my programmers on ONE db architecture, I have to maintain only
ONE db architecture on all my servers, and the db I have chosen does
everything that even my most complex job needs to do. (Now that 7.2.1
supports OUTER JOINS).

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

Yes, there are points to quibble on, but overall, I thought it was a
decent article that exposed people to the idea of using PostgreSQL.

After all, it could've been "MySQL vs MS SQL" or "MySQL vs MS Access".

Sometimes Perfect is the enemy of Good.

On Fri, 12 Jul 2002, Elaine Lindelef wrote:

Yes, there are points to quibble on, but overall, I thought it was a
decent article that exposed people to the idea of using PostgreSQL.

Well, yeah, but it's really time to kill, properly, this idea that
postgres is always slower than mysql. I'm reasonably convinced that
under fairly heavy OLTP loads with some large queries going, MySQL would
grind to a halt at loads much less than postgres can handle.

The only area I know of where MySQL is noticably faster is when
dealing with large amounts of data in tables with very short rows;
MySQL has much less row overhead, so of course it's faster. Though
people are working on reducing postgres' row overhead as well,
which will help....

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

I did notice a considerable difference in time, with a
enquete-result-page (in PHP) using this query:
SELECT
answers.answertext,
answers.answerid,
count(answered_question.answerid) AS answercount
FROM
answers
LEFT JOIN answered_question ON answers.answerid =
answered_question.answerid
WHERE
answers.questionid = '{$this->questionid}'
GROUP BY answers.answerid
ORDER BY answers.answerorder

Which is, I presume, a quite common query...
It runs on exactly the same dataset with exactly the same indices and
table layouts (as far as possible) on exactly the same machine, with
both postgres and mysql optimised 'a bit'.
The query would take 29.45, 55.22 and 17.74 ms on postgresql (for
different questions) takes on mysql respectively: 36.74, 81.14 and 26.37 ms.

Resulting in some simple stats at the end of the page creation:
Database stats Time (s)
Total Execution time: 2.212 s
Total Query time: 2.130 s
Total Number of Queries: 230
Average Time per Query: 0.009 s

vs

Database stats Time (s)
Total Execution time: 3.061 s
Total Query time: 3.002 s
Total Number of Queries: 230
Average Time per Query: 0.013 s

About 150 of these 230 queries are the one showed above. The rest are
simple selects which run 'much' faster on mysql.
The only 'hack' for postgresql I had to make was to set the
ENABLE_MERGEJOIN to OFF, otherwise in some cases it would use the
mergejoin rather than the nested loops on the index scans of both
answers and answered_question, taking over 300ms in stead of 60ms... (a
optimiser problem perhaps?).

Anyway, this shows you that it is not even necessary to have huge
databases, heavy loads or very complicated queries to find postgresql
performing better than mysql.

Regards,

Arjen

Curt Sampson wrote:

I try both and find that mysql can "order by" a Chinese
language field when I use a "select" SQL, but postgresql
can not do this. Therefore I choose mysql as my
Chinese-Language -Database-System. I am a Chinese.
Best regards,
George.

On 7/13/02 5:11 AM, "Curt Sampson" <cjs@cynic.net> wrote:

On Fri, 12 Jul 2002, Elaine Lindelef wrote:

Yes, there are points to quibble on, but overall, I thought it was a
decent article that exposed people to the idea of using PostgreSQL.

Well, yeah, but it's really time to kill, properly, this idea that
postgres is always slower than mysql. I'm reasonably convinced that
under fairly heavy OLTP loads with some large queries going, MySQL would
grind to a halt at loads much less than postgres can handle.

The only area I know of where MySQL is noticably faster is when
dealing with large amounts of data in tables with very short rows;
MySQL has much less row overhead, so of course it's faster. Though
people are working on reducing postgres' row overhead as well,
which will help....

Are there any current benchmarks showing postgres' performance against other
major dbs? I've only seen one, and I think it's a couple of years old. Eweek
profiled major commercial dbs and had mySQL as the open source
representative. Not even a whisper about PgSQL. I'd love to see some current
numbers, just to fend off with a stick the people who keep asking me why I
don't use mySQL.

-- sgl

=======================================================
Steve Lane

Vice President
Chris Moyer Consulting, Inc.
833 West Chicago Ave Suite 203

Voice: (312) 433-2421 Email: slane@fmpro.com
Fax: (312) 850-3930 Web: http://www.fmpro.com
=======================================================

Steve Lane wrote:

On 7/13/02 5:11 AM, "Curt Sampson" <cjs@cynic.net> wrote:

On Fri, 12 Jul 2002, Elaine Lindelef wrote:

Yes, there are points to quibble on, but overall, I thought it was a
decent article that exposed people to the idea of using PostgreSQL.

Well, yeah, but it's really time to kill, properly, this idea that
postgres is always slower than mysql. I'm reasonably convinced that
under fairly heavy OLTP loads with some large queries going, MySQL would
grind to a halt at loads much less than postgres can handle.

The only area I know of where MySQL is noticably faster is when
dealing with large amounts of data in tables with very short rows;
MySQL has much less row overhead, so of course it's faster. Though
people are working on reducing postgres' row overhead as well,
which will help....

Are there any current benchmarks showing postgres' performance against other
major dbs? I've only seen one, and I think it's a couple of years old. Eweek
profiled major commercial dbs and had mySQL as the open source
representative. Not even a whisper about PgSQL. I'd love to see some current
numbers, just to fend off with a stick the people who keep asking me why I
don't use mySQL.

I have heard we are faster for multi-user queries than MySQL and haven't
heard anyone dispute that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Two years ago on phpbuilder.com, there was a performance comparison between
MySQL and PostgreSQL. Tim Perdue simulated load on various
sourceforge.net pages. See:

http://phpbuilder.com/columns/tim20001112.php3?print_mode=1

George Essig

------------------------------

Date: Sun, 14 Jul 2002 20:54:51 -0500
From: Steve Lane <slane@fmpro.com>
To: <pgsql-general@postgresql.org>
Subject: Re: MySQL vs. PostgreSQL
Message-ID: <B957959B.10424%slane@fmpro.com>

Are there any current benchmarks showing postgres' performance against

other

major dbs? I've only seen one, and I think it's a couple of years old.

Eweek

profiled major commercial dbs and had mySQL as the open source
representative. Not even a whisper about PgSQL. I'd love to see some

current

numbers, just to fend off with a stick the people who keep asking me why

I

An interesting talk back was posted to this article that parallels the
experience of many folks with postgresql and mysql.

http://phpbuilder.com/annotate/message.php3?id=1010522

On Mon, 15 Jul 2002 George.T.Essig@stls.frb.org wrote:

Just curious,

Is anyone keeping all of these links as an archive
possibly for the redesigned website ?

I know pgsql inc has a section with "news clippings" on it.

Jeff.

On Monday, July 15, 2002, at 01:30 PM, scott.marlowe wrote:

An interesting talk back was posted to this article that parallels the
experience of many folks with postgresql and mysql.

http://phpbuilder.com/annotate/message.php3?id=1010522

OK, reading this article makes me want to throw in my .02c.

- "most web developers" have no clue about transactions, referential
integrity, triggers, stored procedures.
- "most web developers" are dealing with small applications (think
product catalogs with a couple of tables, with less than 1K rows).
- Therefore, "most web developers" happen to be in the "sweet spot" for
mysql's performance vs. it's set of features.

That's why all these web development industry zines keep saying "mysql
is best". I'm making these statements based on my experience since '96,
working in the Internet Service Provider, and web development industries.

For this to change, somehow, the average web developer needs to be
educated about the world beyond MySQL. I really really hope this
happens, somehow. Just thinking out loud...

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alex_rice@arc.to
alrice@swcp.com

An interesting talk back was posted to this article that parallels the
experience of many folks with postgresql and mysql.

http://phpbuilder.com/annotate/message.php3?id=1010522

OK, reading this article makes me want to throw in my .02c.

- "most web developers" have no clue about transactions, referential
integrity, triggers, stored procedures.
- "most web developers" are dealing with small applications (think
product catalogs with a couple of tables, with less than 1K rows).
- Therefore, "most web developers" happen to be in the "sweet spot" for
mysql's performance vs. it's set of features.

That's why all these web development industry zines keep saying "mysql
is best". I'm making these statements based on my experience since '96,
working in the Internet Service Provider, and web development industries.

For this to change, somehow, the average web developer needs to be
educated about the world beyond MySQL. I really really hope this
happens, somehow. Just thinking out loud...

Why does this need to change? I don't mean to start a war (I love
postgres), but if mysql works for these people why should they learn
something else? Take other technologies...

... if notepad works for HTML, why make them learn FrontPage/Dreamweaver?
... if a P400 running linux works for their webserver, why should they buy
a Sun ES1000?

In both cases they don't need it and won't use the added features.

I'm not saying that we shouldn't education people, but I think for the
"web developers" mentioned above postgres will lose that battle against
mysql. Mysql does what they need it to do and it's everywhere. Just
look at the number of ISP's that have mysql support vs postgres support.
And yes, I know hub.org has it, but I think it's like $300/mo compared to
say $10/mo and I know why that's the case, but for the people mentioned
above they don't and they don't need to.

I think what people need educating on is that *once* you've grown beyond
mysql there is another open source (ie. free) database you can move to
instead of shelling out $$$ for oracle and friends.

That is postgresql's market.. at least in my mind.

just my 2 cents...

and again, I don't mean this to start a war, I like postgres just fine :)

-philip

On Mon, 2002-07-15 at 17:29, Philip Hallstrom wrote:

An interesting talk back was posted to this article that parallels the
experience of many folks with postgresql and mysql.

http://phpbuilder.com/annotate/message.php3?id=1010522

OK, reading this article makes me want to throw in my .02c.

- "most web developers" have no clue about transactions, referential
integrity, triggers, stored procedures.
- "most web developers" are dealing with small applications (think
product catalogs with a couple of tables, with less than 1K rows).
- Therefore, "most web developers" happen to be in the "sweet spot" for
mysql's performance vs. it's set of features.

That's why all these web development industry zines keep saying "mysql
is best". I'm making these statements based on my experience since '96,
working in the Internet Service Provider, and web development industries.

For this to change, somehow, the average web developer needs to be
educated about the world beyond MySQL. I really really hope this
happens, somehow. Just thinking out loud...

Why does this need to change? I don't mean to start a war (I love
postgres), but if mysql works for these people why should they learn
something else? Take other technologies...

... if notepad works for HTML, why make them learn FrontPage/Dreamweaver?
... if a P400 running linux works for their webserver, why should they buy
a Sun ES1000?

In both cases they don't need it and won't use the added features.

I'm not saying that we shouldn't education people, but I think for the
"web developers" mentioned above postgres will lose that battle against
mysql. Mysql does what they need it to do and it's everywhere. Just
look at the number of ISP's that have mysql support vs postgres support.
And yes, I know hub.org has it, but I think it's like $300/mo compared to
say $10/mo and I know why that's the case, but for the people mentioned
above they don't and they don't need to.

I think what people need educating on is that *once* you've grown beyond
mysql there is another open source (ie. free) database you can move to
instead of shelling out $$$ for oracle and friends.

That is postgresql's market.. at least in my mind.

just my 2 cents...

and again, I don't mean this to start a war, I like postgres just fine :)

-philip

I think the education that needs to take place is the answer to "at what
point do I outgrow mysql?" While I suppose ACIDity is not required for
every application (though some would argue that point I'm sure) it
probably should be used in many more applications than it is. A good
example of this would be something like phpauction, which is an online
auction (think ebay) software package based on php and mysql. While I
have never heard of anyone having an issue, to me it is scary to think
there are live auction sites running that need to reliably track things
like bid times and monetary amounts that are doing it without any of the
"data insurance" that postgres brings to the table. And I'm not picking
on phpauction (I refer to it because I have submitted code for it),
there are a lot of other projects that probably should be using postgres
but the developers don't realize it.

Robert Treat
xzilla@users.sourceforge.net

On Monday, July 15, 2002, at 03:29 PM, Philip Hallstrom wrote:

I think what people need educating on is that *once* you've grown beyond
mysql there is another open source (ie. free) database you can move to
instead of shelling out $$$ for oracle and friends.

That is postgresql's market.. at least in my mind.

I'm not suggesting they should not use MySQL at all. I've used MySQL a
lot in the past, and don't regret it. But how will they know when they
have grown beyond Mysql? ...

I just noticed Robert Treat's followup on exactly this. I think we are
all agreeing.

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alex_rice@arc.to
alrice@swcp.com

On Fri, Jul 12, 2002 at 04:52:11PM +0900, Curt Sampson wrote:

For some definition of "useful." You say in your table that postgres
is slower. How did you measure this?

In my personal experiance PostgreSQL runs circles around MySQL.
Specifically MySQL makes lousy choices about which indexes to
use and requires me to give it hints. PostgreSQL just works.

On Tue, 2002-07-16 at 00:33, Christopher Weimann wrote:

On Fri, Jul 12, 2002 at 04:52:11PM +0900, Curt Sampson wrote:

For some definition of "useful." You say in your table that postgres
is slower. How did you measure this?

In my personal experiance PostgreSQL runs circles around MySQL.
Specifically MySQL makes lousy choices about which indexes to
use and requires me to give it hints. PostgreSQL just works.

Weeeellll. While I'd never use mysql, I find postgres does not 'just
work' - it needs quite a bit attention:

- running vacuum by hand (there was a recent debate on auto-running
vacuum when it's needed. I'll be happy to see this)
- the default set up needs some tuning if your database grows to a
certain size (I've not come to that point in my tiny projects, I'm just
reading -general and -hackers cause I'm interested).

But if you assume a proper installation and administration of the
database, then I agree with you.

cheers
-- vbi

--
secure email with gpg http://fortytwo.ch/gpg

Christopher Weimann wrote:

On Fri, Jul 12, 2002 at 04:52:11PM +0900, Curt Sampson wrote:

For some definition of "useful." You say in your table that postgres
is slower. How did you measure this?

In my personal experiance PostgreSQL runs circles around MySQL.
Specifically MySQL makes lousy choices about which indexes to
use and requires me to give it hints. PostgreSQL just works.

I use both databases depending on the project requirements. But
I find that mysql is more willing to use your indexes than
postgres is. I also like that fact that I can tell mysql to use
specific indexes if I feel like it. Plus the ability to add ,delete
and modify the name and type of any field , table or database is a
fat fingered admin's dream. I love postgres, but sometimes the mysql
bashing goes way out hand on the list.
No war please.

--
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com