Thread: Postgres v MySQL 5.0

How do you position Postgres against MySQL 5.0?

MySQL is more popular but I've always regarded it as a bit lightweight. I'm
told that this is no longer the case with 5.0. Why choose Postgres over
MySQL 5.0?

Thanks.

On Fri, 2006-10-13 at 10:35 +0100, Duncan Garland wrote:
> How do you position Postgres against MySQL 5.0?
>
> MySQL is more popular but I've always regarded it as a bit lightweight. I'm
> told that this is no longer the case with 5.0. Why choose Postgres over
> MySQL 5.0?
>

That's a very open-ended question. The databases have redically
different designs, philosophies, and development strategies.

The best thing you can do is download PostgreSQL, and spend a while
reading the documentation, mailing lists, IRC, and posting when you have
difficulty.

Regards,
    Jeff Davis

Duncan Garland wrote:
> How do you position Postgres against MySQL 5.0?
>
> MySQL is more popular but I've always regarded it as a bit lightweight. I'm
> told that this is no longer the case with 5.0. Why choose Postgres over
> MySQL 5.0?

One of the things you must realize is that they crossed off a lot of
features from the must have list. However these features are very
immature and incomplete at this point. For example subqueries are often
slow, prepared statements are not allowed for all types of queries,
their stored procedure implementation is severely lacking in
completeness. At the same time I would say that they have more trouble
with joining 5 or more tables than PostgreSQL

There are some areas where MySQL leads on the technical side. For
example they have more complete charset/collate support. Their partition
implementation in 5.1 will also be more complete than what PostgreSQL
has atm.

One of the key differences however is in the philosphie. MySQL wants to
prepackage everything to make things easy. PostgreSQL wants to provide a
flexible architecture, that people can extend with their exact needs.
You can see this differences especially with things like replication,
where MySQL bundles their solution while in the PostgreSQL ecosystem you
have a ton of choices depending on your needs.

regards,
Lukas

PS: I use MySQL for 90% of my projects.

Duncan Garland wrote:
> How do you position Postgres against MySQL 5.0?

We don't.  We produce useful database software, and people use it or
they don't.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Duncan Garland wrote:
>> How do you position Postgres against MySQL 5.0?
>
> We don't.  We produce useful database software, and people use it or
> they don't.
>

That really isn't a helpful answer Peter. On paper, MySQL 5.0 looks like
a very healthy competitor to PostgreSQL. We all know that MySQL 5.0 is
still very immature in the feature set that (on paper) compares very
well with PostgreSQL. This individual obviously does not.

Duncan, to answer your question; PostgreSQL is an extremely mature and
scalable database. The features that matter to an enterprise such as:
Table Spaces, Stored Procedures, Save Points, Sub Queries, Two Phase
Commit, Table Partitioning and extremely high scalability are much more
mature in PostgreSQL than MySQL 5.0.

In short, we really compare closer to an Oracle or DB2 then a MySQL.
MySQL just isn't there yet.

Sincerely,

Joshua D. Drake




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
> Peter Eisentraut wrote:
> > Duncan Garland wrote:
> >> How do you position Postgres against MySQL 5.0?
> >
> > We don't.  We produce useful database software, and people use it
> > or they don't.
>
> That really isn't a helpful answer Peter. On paper, MySQL 5.0 looks
> like a very healthy competitor to PostgreSQL. We all know that MySQL
> 5.0 is still very immature in the feature set that (on paper)
> compares very well with PostgreSQL. This individual obviously does
> not.

The question was how we position Postgres, not how it compares to MySQL.
We haven't ever positioned Postgres.  None of our development or
release decisions are concerned with positioning.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Joshua D. Drake wrote:
>> Peter Eisentraut wrote:
>>> Duncan Garland wrote:
>>>> How do you position Postgres against MySQL 5.0?
>>> We don't.  We produce useful database software, and people use it
>>> or they don't.
>> That really isn't a helpful answer Peter. On paper, MySQL 5.0 looks
>> like a very healthy competitor to PostgreSQL. We all know that MySQL
>> 5.0 is still very immature in the feature set that (on paper)
>> compares very well with PostgreSQL. This individual obviously does
>> not.
>
> The question was how we position Postgres, not how it compares to MySQL.
> We haven't ever positioned Postgres.  None of our development or
> release decisions are concerned with positioning.

I would disagree... I would bet many developers are very much looking at
the competition when they are determining what features to work on. It
may not be a conscious thought of, "We must have this because MySQL does
or Oracle does" but it definitely plays a part...

I would further argue that many of the features being pushed for 8.3,
recursive queries, bitmap indexes, rollup/group by, updateable queries
are the direct result of positioning either against Oracle/MSSQL or
MySQL pending feature sets. (Although more toward Oracle/MSSQL certainly).

Sincerely,

Joshua D. Drake




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Josh,

> I would further argue that many of the features being pushed for 8.3,
> recursive queries, bitmap indexes, rollup/group by, updateable queries
> are the direct result of positioning either against Oracle/MSSQL or
> MySQL pending feature sets. (Although more toward Oracle/MSSQL certainly).

Huh?  While "keeping up with the Ellisons" is a motivating factor, it's not
anywhere near the primary one.   The reason we want all that stuff is becuase
they're useful features.  When's the last time you saw us push something that
wasn't useful just because someone else had it?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Joshua D. Drake wrote:

> I would further argue that many of the features being pushed for 8.3,
> recursive queries, bitmap indexes, rollup/group by, updateable queries
> are the direct result of positioning either against Oracle/MSSQL or
> MySQL pending feature sets. (Although more toward Oracle/MSSQL certainly).

Wanting to implement features that postgresql users want and ask for
does not say we are positioning against db's that have implemented them
before us.

In the goal of creating a useful database (or any other software for
that matter) we will always have others that think up and implement
things before us, because we think their idea is good and would like
postgresql to do something similar doesn't mean we are trying to
position ourselves against them, we just want our product to do the
things we think it should be able to do.

We may hear about these features and start drooling and running polls to
see if we can convince every other postgresql user to want them included
in the next version, but that is because we want the feature and think
it would be useful not because we want to compete directly with
Oracle/MSSQL/MySQL.

It is thinking of and implementing features before the others (or just
doing them better) that gives us something to brag about and use when
comparing with the other db's.


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

Josh Berkus wrote:
> Josh,
>
>> I would further argue that many of the features being pushed for 8.3,
>> recursive queries, bitmap indexes, rollup/group by, updateable queries
>> are the direct result of positioning either against Oracle/MSSQL or
>> MySQL pending feature sets. (Although more toward Oracle/MSSQL certainly).
>
> Huh?  While "keeping up with the Ellisons" is a motivating factor, it's not

That is all I am saying.

Joshua D. Drake




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Oops! pgsql@007Marketing.com (Shane Ambler) was seen spray-painting on a wall:
> Joshua D. Drake wrote:
>
>> I would further argue that many of the features being pushed for 8.3,
>> recursive queries, bitmap indexes, rollup/group by, updateable queries
>> are the direct result of positioning either against Oracle/MSSQL or
>> MySQL pending feature sets. (Although more toward Oracle/MSSQL certainly).
>
> Wanting to implement features that postgresql users want and ask for
> does not say we are positioning against db's that have implemented
> them before us.

Indeed.  It seems to me that the features added to TheirSQL over the
last two years *do* amount to "positioning;" had they not suffered
from the problem of vital underlying technologies being bought out by
other vendors, they would have been in an excellent position to
position as "strong enough to be an SAP R/3 engine."

With stored procs and triggers as further extras, encouraged by other
use cases.

> We may hear about these features and start drooling and running polls
> to see if we can convince every other postgresql user to want them
> included in the next version, but that is because we want the feature
> and think it would be useful not because we want to compete directly
> with Oracle/MSSQL/MySQL.

It appears to me that the system most nearly "positioned against" is
more DB2 than anything else.  And I think that's accidental; it's just
that both PostgreSQL and DB2 have a habit of intentional conformance
with SQL standards.

> It is thinking of and implementing features before the others (or just
> doing them better) that gives us something to brag about and use when
> comparing with the other db's.

Ultimately, it's the Nifty Applications that give *real* bragging
rights.  What is nice is that PostgreSQL offers some ways of getting
substantial functionality very compactly, which can offer considerable
elegance.

This tends to be difficult with applications designed for another DB,
or for "DB agnosticism;" I have had the recent joy of some involvement
in generating new app code that doesn't have those legacy issues, and
there is considerable elegance to be had in writing a
PostgreSQL-specific application.  It feels rather good.
--
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/linuxdistributions.html
Rules of  the Evil Overlord #161.  "I will occasionally  vary my daily
routine and not live my life in  a rut. For example, I will not always
take  a swig of  wine or  ring a  giant gong  before finishing  off my
enemy." <http://www.eviloverlord.com/>

Peter Eisentraut wrote:
> Duncan Garland wrote:
>> How do you position Postgres against MySQL 5.0?
>
> We don't.  We produce useful database software, and people use it or
> they don't.
>
Positioning is all about making people consider what you do useful, no? What's useful in the
eyes of the producer is not always in alignment with the general perception of a potential
user-base. IMO, The PostgreSQL core team should be more humble to that fact. After all, you
do want your product to gain ground right? Why else are you doing this?

Kind Regards,
Thomas Hallgren

>> We don't.  We produce useful database software, and people use it or
>> they don't.
>>
> Positioning is all about making people consider what you do useful, no?
> What's useful in the eyes of the producer is not always in alignment
> with the general perception of a potential user-base.

100% agreed here :)

>IMO, The
> PostgreSQL core team should be more humble to that fact.
> After all, you do want your product to gain ground right? Why else are you doing this?

Well... I know many developers, develop to develop. They develop
PostgreSQL because they happen to like it, they could give a flying
donkey butt if people use the software.

Luckily most of those developers are being drowned out by the more sane
masses ;)

Sincerely,

Joshua D. Drake

Dawid,

> PS: And as for outsider's perception of PostgreSQL it is still common
> that people think PostgreSQL doesn't provide replication.  I think Slony
> really should be mentioned in "Server administration", along with
> other external tools like pg_pool, or pgreplicator.

I believe that Bruce has done this for 8.2.

> Oh, and it would be very, very good idea to put into the documentation
> more verbose information how to marry PostgreSQL with external
> tools.  Something like a simple use cases.  For instance, for "Backup
> and Restore" section it would be nice to put information how to use
> it together with AMANDA and with Tivoli Storage Manager.  Maybe
> a section about pg_pool and how can it be used to help with OLTP
> load.

Documentation contributions are always welcome.  If stuff is too
platform-specific to go into the general docs, we'll put it up on TechDocs.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:
> Dawid,
>
> > PS: And as for outsider's perception of PostgreSQL it is still common
> > that people think PostgreSQL doesn't provide replication.  I think Slony
> > really should be mentioned in "Server administration", along with
> > other external tools like pg_pool, or pgreplicator.
>
> I believe that Bruce has done this for 8.2.

Yes, I have a draft I will post this week.

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

  + If your life is a hard drive, Christ can be your backup. +

-----Original Message-----
From: pgsql-advocacy-owner@postgresql.org [mailto:pgsql-advocacy-owner@postgresql.org]On Behalf Of Harald Armin Massa
Sent: 21 October 2006 08:12
To: Duncan Garland
Cc: pgsql-advocacy@postgresql.org
Subject: Re: [pgsql-advocacy] Postgres v MySQL 5.0

Duncan,

MySQL is more popular but I've always regarded it as a bit lightweight. I'm
told that this is no longer the case with 5.0. Why choose Postgres over
MySQL 5.0?

There are a big bunch of arguments that I would present if on a paid consulting contract :)

But for free:
a) PostgreSQL has BSD licence, MySQL GPL or commercial. Please consult you legal advisor for differences.

b) PostreSQL is known for being correct, robust and trustworthy for years. "Users data is sacred" is the mantra of every postmaster process. Since 8.0, 8.1 and the upcoming 8.2, PostgreSQL is additionally known to be exceptionally fast.
MySQL is known to be fast for simple queries since years; and with 5.0 on some storage engines there is announced correctness as spelled with ACID.

My personal decision: To believe in correctness and to build trust I need time and history. To check speed I run benchmarks, which complete in minutes.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Duncan,

> I'm prototyping Postgres at the moment. I've always been an Oracle man
> in the past but the current project won't stand the licencing costs etc.
> I've picked Postgres because it grew out of a serious product, Ingres,
> and looks like a serious, heavyweight offering. I also like what I've
> seen so far.

Just FYI, Ingres and PostgreSQL share a common heritage (UCB, Stonebraker)
but no common code.

> I didn't expect anybody to do a lot of work, but I did hope that
> somebody would point me towards some authorative articles on the web.
> They must exist.

Hmmm ... I'm not sure how an article would be "authorative", in the absence
of published benchmarks or evaluations by standards bodies, neither of
which OSS DBs have yet.  Anything you got would just be someone's opinion.

> I do appreciate your replies, they made interesting reading. You all
> clearly believe in your product. However, it is a bit worrying that
> nobody on the Postgres advocacy mailing list could point me towards any
> authoratative material advocating Postgres over MySQL.

Let me see if I can dig anything up.   Generally we try to avoid the MySQL
comparison because it's not doing open source in general any good to
constantly have PostgreSQL and MySQL pitted against each other.  We'd
rather be compared with Oracle and DB2.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:

>> I didn't expect anybody to do a lot of work, but I did hope that
>> somebody would point me towards some authorative articles on the web.
>> They must exist.
>
> Hmmm ... I'm not sure how an article would be "authorative", in the absence
> of published benchmarks or evaluations by standards bodies, neither of
> which OSS DBs have yet.  Anything you got would just be someone's opinion.

This might help:
http://cis.cs.tu-berlin.de/Dokumente/Diplomarbeiten/2005/horstmann.pdf

regards,
Lukas

John Wang wrote:
> On 10/27/06, Josh Berkus <josh@agliodbs.com> wrote:
>>
>> Generally we try to avoid the MySQL
>> comparison because it's not doing open source in general any good to
>> constantly have PostgreSQL and MySQL pitted against each other.  We'd
>> rather be compared with Oracle and DB2.
>
>
> IMO, that's fine if you only want Oracle and DB2 users and want to let all
> the other sites run MySQL. Is that the case?

Well I typically look at it from a MySQL can't compare to PostgreSQL but
PostgreSQL can compare to Oracle or DB2. That is the argument I use when
comparing MySQL to PostgreSQL.

JoshB has a good point in that although we do want people to use
PostgreSQL, MySQL and PostgreSQL share a common goal and bond. It is
that we are both OSS. It may upset the general OSS momentum to fight
amongst the projects when the actual enemy is closed source database
software.

Sincerely,

Joshua D. Drake




--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

>>
>>
>>
>>
>>
> that is a self-serving stance, and I do believe it covers a deeper
> issue: why is mysql taking over the world and postgresql falling further
> and further behind?  Could it be that mysql has some marketing people
> and postgresql only has competent engineers?

What are you talking about? PostgreSQL isn't falling further behind by
any metric. I think you have been sitting entirely too long in a dark
closet with a candle and the latest copy of PHB magazine.

Sincerely,

Joshua D. Drake



--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

>
>
> Right now I typically look at the comparison as PostgreSQL isn't proven to
> scale as well as MySQL for Web 2.0 type community sites. It seems like all
> Alexa 100 sites that use a OSS database use MySQL (many of them on InnoDB).
> I've also seen an old eWeek performance test run where Oracle and MySQL
> compare well from a scalability perspective but MSSQL doesn't (Pg wasn't
> tested). In that case, MySQL, not Pg, was being compared against Oracle and
> MSSQL. I haven't found a single Alexa 100 site that runs PostgreSQL yet.

Uhmmm... you would be wrong :). I am looking at the Alexa 100 right now
and I see several that run PostgreSQL. Some of them are even my customers :P

Sincerely,

Joshua D. Drake


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

John Wang wrote:
> On 10/27/06, Joshua D. Drake <jd@commandprompt.com> wrote:
>>
>> Uhmmm... you would be wrong :). I am looking at the Alexa 100 right now
>> and I see several that run PostgreSQL. Some of them are even my customers
>> :P
>
>
> Can you say who they are? I hope I just overlooked them. Are they on either
> of the following pages?

I know I can tell you Sourceforge :). The others I probably can't.

Sincerely,

Joshua D. Drake


>
> http://www.postgresql.org/about/users
> http://www.postgresql.org/about/casestudies/
>


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Michael Dean wrote:
> Have any of you guys been in a situation where you are at a
> decision-making conference table where you are the only man?  We are
> talking here of overwhelming sufficating levels of estrogen, the
> anti-Mars perspectives are dominating, and you are chastised for using a
> common football term.  Well, right now, the postgresql community is
> similar, only its not a male-female thing, it is an engineer vs. anyone
> else thing!  You guys are denying the efficacy of scientific findings
> that could really put postgresql on the map!  You need to broaden your
> community to include people who can really help you! If you don't, you
> will perish, like CP/M.

O.k. fair enough. Let's say you are 100% right and I 100% agree that you
are 100% right.

What are *you* going to do about it?

I have never seen you at a show.
I have never seen you offer any expertise.
I have never seen you offer to help in any way.

I have seen, you complain, whine, moan, berate, and make insane comments
to the contrary of the community good.

So, please... Honestly, enlighten us, surprise us. Actually do something
productive.

Sincerely,

Joshua D. Drake





--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

John,

> It would be nice if more large scale Pg users came out of the closet.

How's your professional writing ability?  We currently have case
studies being held back by lack of community writing volunteers.


Josh Berkus
PostgreSQL @ Sun
San Francisco 415-752-2500

On Friday 27 October 2006 14:01, Josh Berkus wrote:
)> Duncan,

> > I didn't expect anybody to do a lot of work, but I did hope that
> > somebody would point me towards some authorative articles on the web.
> > They must exist.
>
> Hmmm ... I'm not sure how an article would be "authorative", in the absence
> of published benchmarks or evaluations by standards bodies, neither of
> which OSS DBs have yet.  Anything you got would just be someone's opinion.
>
> > I do appreciate your replies, they made interesting reading. You all
> > clearly believe in your product. However, it is a bit worrying that
> > nobody on the Postgres advocacy mailing list could point me towards any
> > authoratative material advocating Postgres over MySQL.
>
> Let me see if I can dig anything up.   Generally we try to avoid the MySQL
> comparison because it's not doing open source in general any good to
> constantly have PostgreSQL and MySQL pitted against each other.  We'd
> rather be compared with Oracle and DB2.

Generally I agree with  Josh's statements above, but I also keep a few links
around of what other people have written for cases like this:

http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/
http://www.sitepoint.com/article/site-mysql-postgresql-1
http://www.phpbuilder.com/columns/tim20001112.php3
http://www.oreillynet.com/onlamp/blog/2005/01/cd_baby_rewrite_in_postgres_an.html
http://www.leaveitbehind.com/home/2005/03/why_switch.html

And of course the following is usually worth pointing people to as well.
http://sql-info.de/mysql/gotchas.html

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

Duncan Garland wrote:

> I do appreciate your replies, they made interesting reading. You all clearly
> believe in your product. However, it is a bit worrying that nobody on the
> Postgres advocacy mailing list could point me towards any authoratative
> material advocating Postgres over MySQL.

May not be overly authoritative but - performance wise there are
benchmarks at tweakers.net - these are hardware performance testing that
use mysql and postgresql to measure what the hardware can do compared to
other hardware.

http://tweakers.net/reviews/646/10

I think a lot of comparisons/advocacy have basically been "I prefer x
because..." with some doing internal performance testing when first
making their choice.
I don't know of any that would compare db's feature to feature like you
might see in a pc mag comparing MS SQL with Oracle.


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

John Wang wrote:

> Also my statement "It seems like all Alexa 100 sites that use a OSS
> database use MySQL" isn't wrong b/c Sourceforge isn't mentioned on those
> two Pg site pages and the others are closet users. Now I can say it
> seems like only 1 Alexa 100 site uses OSS if I can find some
> confirmation from them. If Sourceforge really is using Pg in a big way,
> it would be great if we can get the Sourceforge folks to give a
> presentation on their Pg deployment and scaling at some conference. I'd
> be for supporting that.

The problem with many of the successful Web 2.0 companies is that they
lack a dedicated DBA at the very start. Actually I just claim this, I do
not have hard facts to back up that claim, but it sounded like that from
stuff I have read about the early days of many of these projects. If you
do not have a dedicated DBA you are much better off with MySQL in most
cases, because its simple .. there are few choices to make and you get
decent performance. Developers also often do not see the point in
letting the database manage the data integrity.

Now on projects where they do have a dedicated DBA, who does think that
data integrity is the job of the database, that does have the expertise
to figure out what replication method best fits the needs of the project
etc. there PostgreSQL makes absolute sense.

The problem is that MySQL has gotten to many case studies with press
coverage, that people start to think that MySQL is the better database
product regardless if you have a DBA or not. And this is something that
can only be countered with similar press coverage. Like every web
developer needs to know that SourceForge runs on PostgreSQL eventhough
they could be running on DB2 if they wanted to.

regards,
Lukas

On Saturday 28 October 2006 06:34, Lukas Kahwe Smith wrote:
> John Wang wrote:
> > Also my statement "It seems like all Alexa 100 sites that use a OSS
> > database use MySQL" isn't wrong b/c Sourceforge isn't mentioned on those
> > two Pg site pages and the others are closet users. Now I can say it
> > seems like only 1 Alexa 100 site uses OSS if I can find some
> > confirmation from them. If Sourceforge really is using Pg in a big way,
> > it would be great if we can get the Sourceforge folks to give a
> > presentation on their Pg deployment and scaling at some conference. I'd
> > be for supporting that.
>
> The problem with many of the successful Web 2.0 companies is that they
> lack a dedicated DBA at the very start. Actually I just claim this, I do
> not have hard facts to back up that claim, but it sounded like that from
> stuff I have read about the early days of many of these projects.

Tim O'Reilly did a series in his blog on some web 2.0 companies, and not only
do many of them not have a dba, many of them bypass the database entirely.

> If you
> do not have a dedicated DBA you are much better off with MySQL in most
> cases, because its simple .. there are few choices to make and you get
> decent performance. Developers also often do not see the point in
> letting the database manage the data integrity.
>

My experience is that application programmers like to solve problems in
application space... even when they shouldn't have to.

> Now on projects where they do have a dedicated DBA, who does think that
> data integrity is the job of the database, that does have the expertise
> to figure out what replication method best fits the needs of the project
> etc. there PostgreSQL makes absolute sense.
>

yep

> The problem is that MySQL has gotten to many case studies with press
> coverage, that people start to think that MySQL is the better database
> product regardless if you have a DBA or not. And this is something that
> can only be countered with similar press coverage. Like every web
> developer needs to know that SourceForge runs on PostgreSQL eventhough
> they could be running on DB2 if they wanted to.
>

We do have permission to add them to the users page, so I'll go ahead and
update that (must have slipped my mind), but my understanding is that
sourceforge is not interested in being vocal about thier postgresql usage.

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

Hi,

Yeah more case studies would be nice, but I guess PostgreSQL will need
to be driven from DBA's. Some of the derivatives will be able to use
marketing as a tool. But again PostgreSQL is a tool suited best for
DBA's. This should be made clear at every opportunity. This makes it
clear that PostgreSQL is not lagging behind MySQL in terms of scability
or features, just that its tailored to a smaller group of people than
the armies of PHP developers that use MySQL. This is not a sign of
weakness, just a sign of focus.

However, with the growth that PostgreSQL is putting down from the
already powerful base I am not all that worried. Like I said early on in
this thread, many of the features available in MySQL since the past
years simply lack polish. They are often slow and incomplete with the
notable exception of their charset support and the imminent release of
their partition support.

So to make my key point clear once again:
Its important to make people understand that PostgreSQL has no intention
on competing with MySQL over developers. PostgreSQL is competing for DBA's.

regards,
Lukas

Quoth johncwang@gmail.com ("John Wang"):
> On 10/27/06, Joshua D. Drake <jd@commandprompt.com> wrote:
>
>
>           John Wang wrote:
>      > On 10/27/06, Josh Berkus <josh@agliodbs.com> wrote:
>      >>
>      >> Generally we try to avoid the MySQL
>      >> comparison because it's not doing open source in general any good to
>      >> constantly have PostgreSQL and MySQL pitted against each other.  We'd
>      >> rather be compared with Oracle and DB2.
>      >
>      >
>      > IMO, that's fine if you only want Oracle and DB2 users and want to let all
>      > the other sites run MySQL. Is that the case?
>
>      Well I typically look at it from a MySQL can't compare to PostgreSQL but
>      PostgreSQL can compare to Oracle or DB2. That is the argument I use when
>      comparing MySQL to PostgreSQL.
>
>
> Right now I typically look at the comparison as PostgreSQL isn't
> proven to scale as well as MySQL for Web 2.0 type community
> sites. It seems like all Alexa 100 sites that use a OSS database use
> MySQL (many of them on InnoDB). I've also seen an old eWeek
> performance test run where Oracle and MySQL compare well from a
> scalability perspective but MSSQL doesn't (Pg wasn't tested). In
> that case, MySQL, not Pg, was being compared against Oracle and
> MSSQL. I haven't found a single Alexa 100 site that runs PostgreSQL
> yet.  If the Pg community doesn't want to compare itself against
> MySQL, is the implicit recommendation that community type sites
> planning for growth should use MySQL? That's fine if it is. I'm just
> curious.

That conclusion seems entirely opposite to the usual perspective that
comes out on scalability.

- MySQL(tm), with MyISAM, simply can't scale past ~ 10 heavily updating
  users, table locking being a prohibitively troublesome problem

- MySQL(tm) with InnoDB(tm) seems likely to be a performance loss, as
  compared to MyISAM, for small installations, but likely goes
  further, in terms of being able to scale to larger numbers of
  concurrent updates.

  It's probably comparable to older versions of PostgreSQL in that
  fashion.  Jan Wieck did some benchmarking on a TPC-W-like test that
  showed that there weren't material differences between InnoDB and
  PostgreSQL for moderate loads.

- It's on PostgreSQL where people are finding it worthwhile to head
  to much heavier levels of "scalability," where we're deploying
  many-CPU heavy iron boxes alongside disk array technology.

"Web 2.0" strikes me as being fairly much a nonsequitor in all of
this.  "Web 2.0" applications are ones whose purpose is to integrate
information from various web services.  So, if you get an address, you
send a request to Google Maps to get location information and such.
There's NO database work involved in that.

There is the fairly obvious implication that if your primary
bottlenecks are in sending web service requests off to remote places,
then database scalability becomes *less* of a factor in the
application.

That seems to fit with the notion that MySQL(tm) represents a
reasonable answer for "Web 2.0"; if database throughput isn't a
bottleneck, then you can use even *less scalable* databases for those
sorts of applications.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/emacs.html
Rules  of the  Evil Overlord  #181.  "I  will decree  that all  hay be
shipped in tightly-packed bales. Any wagonload of loose hay attempting
to pass through a checkpoint will be set on fire."
<http://www.eviloverlord.com/>

> That seems to fit with the notion that MySQL(tm) represents a
> reasonable answer for "Web 2.0"; if database throughput isn't a
> bottleneck, then you can use even *less scalable* databases for those
> sorts of applications.

Has anyone ever considered that the only reason we are even compared to
MySQL is that MySQL and PostgreSQL are the only FOSS databases with
traction and market recognition?

Every show I go to, when people ask how we compare to MySQL my response
is; we don't, we compare to Oracle and DB2.

We all need to stop worrying about the cute dolphin, and focus on our
strengths. If we focus on our strengths we won't even have to recognize
that MySQL exists.

So how about instead of spending all this time on this thread we start
working on more articles, techdocs, case studies and benchmarks that
show that we totally kick arse!

Sincerely,

Joshua D. Drake


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:

> Has anyone ever considered that the only reason we are even compared to
> MySQL is that MySQL and PostgreSQL are the only FOSS databases with
> traction and market recognition?
>
> Every show I go to, when people ask how we compare to MySQL my response
> is; we don't, we compare to Oracle and DB2.

Yes, the important point here is that Oracle and DB2 are run by DBA's,
which are the prime audience for PostgreSQL.

regards,
Lukas

Robert Treat wrote:

>And of course the following is usually worth pointing people to as well.
>http://sql-info.de/mysql/gotchas.html
>
>
>

OK, this link has to be the best dang reason to use Postgres instead
I've ever seen.  The combination of "Whoops, no InnoDB table support",
"Supported syntax, unsupported feature", and "Transactions on mixed
table types" strikes me as a recipie for utter diaster.  Especially
considering that you can not detect the mistake (forgetting to make a
table InnoDB) until after the damage is done, and long after the mistake
was made.  Transactions are like Yoda said- "Do or do not, there is no
try."  Having pseudo-transactions is worse than not having any
transactions at all- they lull me into a false sense of security.
Especially if all tables are supposed to be transaction-enabled, only by
accident some aren't.

This just cements my opinion that the only purpose of having more than
one engine in a database is so that you can pick the wrong one.

Brian

Lukas Kahwe Smith wrote:
> Hi,
>
> Yeah more case studies would be nice, but I guess PostgreSQL will need
> to be driven from DBA's. Some of the derivatives will be able to use
> marketing as a tool. But again PostgreSQL is a tool suited best for
> DBA's. This should be made clear at every opportunity. This makes it
> clear that PostgreSQL is not lagging behind MySQL in terms of
> scability or features, just that its tailored to a smaller group of
> people than the armies of PHP developers that use MySQL. This is not a
> sign of weakness, just a sign of focus.
>
> However, with the growth that PostgreSQL is putting down from the
> already powerful base I am not all that worried. Like I said early on
> in this thread, many of the features available in MySQL since the past
> years simply lack polish. They are often slow and incomplete with the
> notable exception of their charset support and the imminent release of
> their partition support.
>
> So to make my key point clear once again:
> Its important to make people understand that PostgreSQL has no
> intention on competing with MySQL over developers. PostgreSQL is
> competing for DBA's.
>
> regards,
> Lukas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
just a little side note.  We have had postgresql 8 up and running on
some of our websites now for 18 months or so.  we were able to fresh
install it with one of our php based template systems in a little over
an hour, with not a squeek of problem since.  works right out of the
box!  With that kind of reliability, why do we need a dba?

Michael Dean wrote:

> just a little side note.  We have had postgresql 8 up and running on
> some of our websites now for 18 months or so.  we were able to fresh
> install it with one of our php based template systems in a little over
> an hour, with not a squeek of problem since.  works right out of the
> box!  With that kind of reliability, why do we need a dba?

my last postgresql installs were a breeze. the point is more that if you
want replication you actually have a choice. some things need to be
pulled from contrib modules etc. in mysql most things come out of the
box and there are less choices to make.

that being said, your chances of getting a bad query plan are much
higher with mysql (for example mysql only supports nested loop joins ..
no hash or sort merge joins). but i guess a lot of these applications
stay below the in my experience magic mark of joining more than 5
tables. so they are not bitten by this problem all that often.

regards,
Lukas

Dear Joshua, List,

On 10/28/06, Joshua D. Drake <jd@commandprompt.com> wrote:
> So how about instead of spending all this time on this thread we start
> working on more articles, techdocs, case studies and benchmarks that
> show that we totally kick arse!

that sounds great. about 2 weeks ago i did a suggestion regarding the
lack of documentation regarding the disjunction operator ( ie: what is
it? ).

i thought it was a proper suggestion. i "submitted" the
google-soc-link to the pgfoundry page.

regards,

usleep

>
> Sincerely,
>
> Joshua D. Drake
>
>
> --
>
>       === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive  PostgreSQL solutions since 1997
>              http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

usleepless@gmail.com wrote:
> Dear Joshua, List,
>
> On 10/28/06, Joshua D. Drake <jd@commandprompt.com> wrote:
>> So how about instead of spending all this time on this thread we start
>> working on more articles, techdocs, case studies and benchmarks that
>> show that we totally kick arse!
>
> that sounds great. about 2 weeks ago i did a suggestion regarding the
> lack of documentation regarding the disjunction operator ( ie: what is
> it? ).
>
Let me make a tactical suggestion: Suppose we map out and delineate
various database ways of doing things, functions,etc. and classify these
as strengths or weaknesses of mysql and postgresql.  This gives us four
quadrants, what is strong for both, weak for both or strong for one and
weak for the other.  We then look at those things in that quadrant which
are weaknesses for mysql but strengths for postgresql.  We determine
that we are going to concentrate here on the most elegant and simple
solutions for postgresql which need more work for mysql.  Next, we take
a relatively large dataset and illustrate the contortions mysql needs to
do, as opposed to postgresql.  We time the actual dba costs for both, we
time the speed of execution, we record any problems.  WE do NOT limit
ourselves to "core" pg, but to the full panoply of utilities and
programs that surround it.

Next, we write up a small white paper (4-6 pages) with a title like: If
you are a Small Business and  You have to xxxx, here is how you could
easily do it with pg.  NO MENTION of mysql.  We are, after all,
promoting postgresql.by illustrating its unique strengths.  WHEN the
question of how mysql would solve that question comes up, and it surely
will, send the questioner over to the mysql booth for an answer.  This
way we are choosing our own battlefield.

Finally, once the selections are made, ground it with an actual
company.  write another 4-6 pager: Johnson & Johnson Saved $142,345.56
last year by adopting an elegant postgresql solution!  Now, once I have
the first parts, the code, the results, i will write the papers.  I am a
statistician, not a dba or programming genius.

> Finally, once the selections are made, ground it with an actual
> company.  write another 4-6 pager: Johnson & Johnson Saved $142,345.56
> last year by adopting an elegant postgresql solution!  Now, once I have
> the first parts, the code, the results, i will write the papers.  I am a
> statistician, not a dba or programming genius.

Now that sounds like a productive use of time.

Joshua D. Drake




--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Saturday 28 October 2006 18:23, Andy Astor wrote:
> Didn't I see recently that Elein had gotten permission to publicly
> mention Safeway, as well?
>

Yes, and they are listed under the users list.  What would be good would be
for folks to write up detailed case studies on these businesses.

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

On Sat, Oct 28, 2006 at 12:34:30PM +0200, Lukas Kahwe Smith wrote:
> stuff I have read about the early days of many of these projects. If you
> do not have a dedicated DBA you are much better off with MySQL in most
> cases, because its simple .. there are few choices to make and you get
> decent performance.

Oh, I wish this were true.  In fact, if you do not have a dedicated
DBA, you'd be much better off with Postgres, because the database
won't let you do stupid things that make you have to rewrite
everything later when it turns out your data model is broken.
Unfortunately, the developers who are often making those decisions
simply don't know that, and don't care.

But I thnk that's a rant for another day.

A
--
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
        --Damien Katz

Duncan Garland wrote:
> How do you position Postgres against MySQL 5.0?
>
> MySQL is more popular but I've always regarded it as a bit lightweight. I'm
> told that this is no longer the case with 5.0. Why choose Postgres over
> MySQL 5.0?
>
> Thanks.
>


What can i say, all is personal opinions.
However, MySQL has added a lot of missing features the last few years, version 4.1 and 5.0 are really quite capable.

However, PostgreSQL is more robust and has a long tradition of beeing so. In MySQL i have had at least two data
curruptionoccations, where innodb-tables suddenly looked like myisam-tables. 
all new inserts failed, unless we reused data from earlier queries. and no, the disks are not broken.
 From version 5.0 you have build in clustering in MySQL. MySQL is also comming strong on statistics.

PostgreSQL on the other hand is extreamly stable compared to MySQL. I have had no failuers the last two years on PG.
PG has better table partitioning of tables.
PG has better planner, and much better planner output (explain analyze).

However, currently i miss the following in PG:
SQL such as: GROUPING SETS, CUBE, ROLLUP. Also looking for WITH RECURSION for recursive SQL-queries.


a very handy thing in mysql is FEDERATED tables that allows one to open a channel from one MySQL-server to another
MySQL-server.
Does PG have anything similar?

/stig

Stig,

> However, currently i miss the following in PG:
> SQL such as: GROUPING SETS, CUBE, ROLLUP. Also looking for WITH RECURSION
> for recursive SQL-queries.

yep, you and everyone else.  Windowing aggregates are a big TODO.   WITH
RECURSION was supposed to be done by Jonah Harris for 8.2 but he bailed.
Take a look at the CUBE contrib module for 8.2, it's enhanced.

> a very handy thing in mysql is FEDERATED tables that allows one to open a
> channel from one MySQL-server to another MySQL-server. Does PG have
> anything similar?

Yes, and for far more than MySQL covers:

dblink:  /contrib/tablefunc in your PostgreSQL source
dbi-link: http://pgfoundry.org/projects/dbi-link
   (note that this allows connecting to *any* tabular data source)
oracle link: http://pgfoundry.org/projects/oralink
mssql link: http://pgfoundry.org/projects/dblink-tds/

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Fri, Oct 27, 2006 at 03:30:08PM -0700, Josh Berkus wrote:
> John,
>
> > It would be nice if more large scale Pg users came out of the closet.
>
> How's your professional writing ability?  We currently have case
> studies being held back by lack of community writing volunteers.

First I'd heard about that!

Please provide me more details, I might be able to scare up some time
from someone here at work. If nothing else, I'm a decent writer.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Sat, Oct 28, 2006 at 08:14:37AM -0700, John Wang wrote:
> On 10/28/06, Lukas Kahwe Smith <smith@pooteeweet.org> wrote:
> >
> >The problem is that MySQL has gotten to many case studies with press
> >coverage, that people start to think that MySQL is the better database
> >product regardless if you have a DBA or not. And this is something that
> >can only be countered with similar press coverage. Like every web
> >developer needs to know that SourceForge runs on PostgreSQL eventhough
> >they could be running on DB2 if they wanted to.
>
>
> Regardless of how large scale Web 2.0 sites started using databases, by the
> time they get to that scale and are giving presentations at OSCON, they are
> using InnoDB and recommending it. They also recommend MyISAM for specific
> cases where integrity isn't needed as much giving the impression MySQL is
> more flexible, offering you integrity when you need it and additional speed
> when you don't. Because these sites are talking about how they scaled, there
> is simply more information on using MySQL in those situations in the wild
> than Pg. For example, LiveJournal talks about how they use a multi-master
> MySQL configuration, composite primary keys, clustering user data based on
> composite PK and compsite key vs. GUID size. I think it would be great for
> Pg DBAs at high profile sites to discuss similar things they did to scale
> Pg, even if they didn't have to do anything to that extent (so people know
> it can scale w/o additional effort). For new sites just starting, MySQL may
> seem safer because there is simply more information available. The Apress
> "Pro MySQL" book also exists now for those that wish to tune their MySQL
> databases more.
>
> So perhaps the issue isn't comparing against MySQL directly but showing that
> Pg is used and how it is used in situations where only MySQL typically gets
> press. However, for a Sourceforge presentation, I think it would be nice if
> they at least briefly mentioned why they did not choose MySQL because both
> are OSS and they are an OSS shop.

It's interesting that you mention LJ, because I've done some
back-of-the-envelop calculations that showed that (at least as of about
a year ago), they could have run their entire operation off of a single
slony master and a few (2-3) slaves. At that time they were running over
a dozen clusters in what appeared to be a pretty kludgey setup that was
certainly prone to outages.

To be honest, I don't think most people that choose MySQL even think
about scalability; I think they just use it because it's what everyone
else uses.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Sat, Oct 28, 2006 at 06:22:36PM +0200, Lukas Kahwe Smith wrote:
> Joshua D. Drake wrote:
>
> >Has anyone ever considered that the only reason we are even compared to
> >MySQL is that MySQL and PostgreSQL are the only FOSS databases with
> >traction and market recognition?
> >
> >Every show I go to, when people ask how we compare to MySQL my response
> >is; we don't, we compare to Oracle and DB2.
>
> Yes, the important point here is that Oracle and DB2 are run by DBA's,
> which are the prime audience for PostgreSQL.

And it wasn't too long ago that Linux was just an oddity that wasn't run
by IT professionals.

The reality is that users who cut their teeth today on MySQL are likely
to carry it into their future jobs, even if PostgreSQL is a much better
choice. EnterpriseDB has many customers that have MySQL running around.
Right now, they're coming to us because they recognize that MySQL just
isn't enterprise-ready, but that won't be the case forever. It wasn't
long ago that we could easily dismiss MySQL because it didn't even
support sub-selects. And they're gunning to be an enterprise database.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Jim C. Nasby wrote:

> The reality is that users who cut their teeth today on MySQL are likely
> to carry it into their future jobs, even if PostgreSQL is a much better
> choice. EnterpriseDB has many customers that have MySQL running around.
> Right now, they're coming to us because they recognize that MySQL just
> isn't enterprise-ready, but that won't be the case forever. It wasn't
> long ago that we could easily dismiss MySQL because it didn't even
> support sub-selects. And they're gunning to be an enterprise database.

I do not think we will be able to get the n00b developer to start with
PostgreSQL. A much more feasible to reach audience is up and coming
DBA's. So for example making sure that University courses are done using
PostgreSQL instead of some closed source alternative. I sure hope that
none of them are using MySQL atm, as this would surely limit the scope
of what they can teach. So if you want to talk marketing, maybe there
should be (and maybe there already is) some site didacted for PostgreSQL
in the education sector.

At the Frankfurt PHP/OpenDB con earlier this week someone showed me a
PHP app written in PHP using PEAR::MDB2 as its abstraction layer which
supposedly is bundled with some education package from SUN. He was
atleast showing methe thing running on PostgreSQL, though it could of
course run on SQLite or MySQL just as well. But these are the kind of
places where we should hope to make PostgreSQL the default in both the
configuration and whatever tutorials are being send out. So that people
learn to make use of the entire scope of what RDBMS have to offer these
days right from the bat. And these are the people that actually care to
learn about them.

regards,
Lukas

> > a very handy thing in mysql is FEDERATED tables that allows one to
> > open a channel from one MySQL-server to another
> MySQL-server. Does PG
> > have anything similar?
>
> Yes, and for far more than MySQL covers:
>
> dblink:  /contrib/tablefunc in your PostgreSQL source
> dbi-link: http://pgfoundry.org/projects/dbi-link
>    (note that this allows connecting to *any* tabular data
> source) oracle link: http://pgfoundry.org/projects/oralink
> mssql link: http://pgfoundry.org/projects/dblink-tds/

<shameless plub>
dblink-ldap: http://pgfoundry.org/projects/dblink-ldap

//Magnus

On Thursday 9. November 2006 10:20, Lukas Kahwe Smith wrote:
>I do not think we will be able to get the n00b developer to start with
>PostgreSQL.

Maybe not the n00b, but at least amateurs like me who have been using
MySQL for some years, and are fed up with all the gotchas and coding
around all the inherent weaknesses of that DBMS.

MySQL is a lot like the dirty old BASIC of the eighties: It has a lot of
mindshare and an easy learning curve, but you're guaranteed to hit the
wall sooner or later. And then it takes quite some time of unlearning
all the bad things you had to do to make it do the things you wanted.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

> >I do not think we will be able to get the n00b developer to
> start with
> >PostgreSQL.
>
> Maybe not the n00b, but at least amateurs like me who have
> been using MySQL for some years, and are fed up with all the
> gotchas and coding around all the inherent weaknesses of that DBMS.

Why not n00bs? If we can point at why they chose My, perhaps we can do
something about it? (Sure, some things we'll never be able to do,
because it would break things for advanced developers, but we should
always be interested in lowering the bar for entry as long as it doesn't
have sideeffects)

//Magnus

On Thursday 9. November 2006 10:40, Magnus Hagander wrote:
>Why not n00bs? If we can point at why they chose My, perhaps we can do
>something about it? (Sure, some things we'll never be able to do,
>because it would break things for advanced developers, but we should
>always be interested in lowering the bar for entry as long as it
> doesn't have sideeffects)

When you're a n00b, you don't have a clue about what the advanced
features of PostgreSQL can do for you. Basically, you choose MySQL
because that's what everybody else is using.

It's hardly more difficult to start using PostgreSQL than MySQL. The
installation part is mostly the same. Regarding the query language
itself, the differences are small enough that it doesn't matter much
from a learning point of view.

The big difference between the two is when you start doing "serious
work" that demands complex queries, ACID transactions, and custom
business logic. Then it actually becomes a whole lot easier to use
PostgreSQL than MySQL.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

Leif B. Kristensen wrote:

> It's hardly more difficult to start using PostgreSQL than MySQL. The
> installation part is mostly the same. Regarding the query language
> itself, the differences are small enough that it doesn't matter much
> from a learning point of view.

The difference is that PostgreSQL does not bundle as much functionality
as MySQL does. Also the simple fact that you have plenty of choices in
PostgreSQL makes it harder as you grow. This is why developers recommend
other developers to use MySQL. Its not only easy to setup, but it will
do what most of them expect even if they grow bigger. Like replication
is in there by default etc.

regards,
Lukas

Couple of links from the Web 2.0 conference which I thought folks may
find interesting.
On many levels.

http://www.networkworld.com/news/2006/110806-web-20-bezos-cant-get.html

Amazon Simple Storage Service:
http://www.amazon.com/gp/browse.html?node=16427261

which can work in concert with Amazon Elastic Compute Cloud
http://www.amazon.com/gp/browse.html?node=201590011

Marten Mickos
The Great Structured Database in the Sky
http://blogs.zdnet.com/BTL/index.php?p=3918

Thanks,
Mike Ellsworth

Lukas Kahwe Smith <smith@pooteeweet.org> writes:
> Leif B. Kristensen wrote:
>
>> It's hardly more difficult to start using PostgreSQL than MySQL. The
>> installation part is mostly the same. Regarding the query language
>> itself, the differences are small enough that it doesn't matter much
>> from a learning point of view.
>
> The difference is that PostgreSQL does not bundle as much
> functionality as MySQL does. Also the simple fact that you have
> plenty of choices in PostgreSQL makes it harder as you grow. This is
> why developers recommend other developers to use MySQL. Its not only
> easy to setup, but it will do what most of them expect even if they
> grow bigger. Like replication is in there by default etc.

Actually, can you be more specific about "does not bundle as much
functionality?"

That MySQL(tm) may have an "included with the server by default"
replication system where things are not identical with PostgreSQL is a
reasonable example.

But it seems to me that PostgreSQL otherwise offers a lot more, such
as having a bunch of stored procedure languages, by default, as well
as mature support for things like triggers, views, rules,
notifications, an extensible type system, 2PC, custom aggregates, and
such.

Perhaps I am looking wrongly at "bundled functionality;" what sizable
list of "bundled MySQL functionality" am I missing?
--
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://linuxdatabases.info/info/finances.html
"Sponges grow in  the ocean. I wonder how much  deeper the ocean would
be if that didn't happen." -- Steven Wright

> But it seems to me that PostgreSQL otherwise offers a lot more, such
> as having a bunch of stored procedure languages, by default, as well
> as mature support for things like triggers, views, rules,
> notifications, an extensible type system, 2PC, custom aggregates, and
> such.
>
> Perhaps I am looking wrongly at "bundled functionality;" what sizable
> list of "bundled MySQL functionality" am I missing?

Replication would be the biggest one. However, you are looking at this
the wrong way... which is to say the honest and correct way.

Most people read the glossy cover, see the hot lingerie model and sign
up for the credit card. The same goes for reading the press about what
MySQL supposedly supports.

Joshua D. Drake


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

nhrcommu@rochester.rr.com (Mike Ellsworth) writes:
> Couple of links from the Web 2.0 conference which I thought folks may
> find interesting. On many levels.
>
> http://www.networkworld.com/news/2006/110806-web-20-bezos-cant-get.html
>
> Amazon Simple Storage Service:
> http://www.amazon.com/gp/browse.html?node=16427261
>
> which can work in concert with Amazon Elastic Compute Cloud
> http://www.amazon.com/gp/browse.html?node=201590011

Very interesting.  That offers a way of doing "virtual shared hosting"
at $0.10/h per VM, where you can build your own customized VM (based
on Linux).  I'm not sure it's an excellent way of hosting databases,
but it would definitely be a nice way to get around web hosting
providers' tendancies to only offer tightly chosen toolsets (often
that *don't* include PostgreSQL).

And it means that you can evade the need to buy your own server in
order to have a customized hosting environment.

> Marten Mickos
> The Great Structured Database in the Sky
> http://blogs.zdnet.com/BTL/index.php?p=3918

So "pie in the sky"...

He's not grasping in the slightest (or expressing) the general
aversion people have assortedly over:
 - Sharing MY PROPRIETARY DATA
 - The privacy problems that fall out of linking diverse sets of
   databases.

I spent yesterday at an RFID conference where those were Very
Prominent Issues.
--
(format nil "~S@~S" "cbbrowne" "linuxdatabases.info")
http://www3.sympatico.ca/cbbrowne/oses.html
We're Thinking Machines, so we don't have to.

nhrcommu@rochester.rr.com (Mike Ellsworth) writes: 

Couple of links from the Web 2.0 conference which I thought folks may
find interesting. On many levels.

http://www.networkworld.com/news/2006/110806-web-20-bezos-cant-get.html

Amazon Simple Storage Service:
http://www.amazon.com/gp/browse.html?node=16427261

which can work in concert with Amazon Elastic Compute Cloud
http://www.amazon.com/gp/browse.html?node=201590011   

Very interesting.  That offers a way of doing "virtual shared hosting"
at $0.10/h per VM, where you can build your own customized VM (based
on Linux).  I'm not sure it's an excellent way of hosting databases,
but it would definitely be a nice way to get around web hosting
providers' tendancies to only offer tightly chosen toolsets (often
that *don't* include PostgreSQL).

And it means that you can evade the need to buy your own server in
order to have a customized hosting environment.

Marten Mickos
The Great Structured Database in the Sky
http://blogs.zdnet.com/BTL/index.php?p=3918   

So "pie in the sky"...

He's not grasping in the slightest (or expressing) the general
aversion people have assortedly over:- Sharing MY PROPRIETARY DATA- The privacy problems that fall out of linking diverse sets of  databases.

I spent yesterday at an RFID conference where those were Very
Prominent Issues.

Mike,

> Would be nice if one of you PG heavyweights spoke at the Web 2.0
> conference.  Hopefully this suggestion won't reopen that Travel thread.

Unfortunately, Web 2.0 is O'Reilly's "see and be seen" conference.  If you're
not a Friend Of O'Reilly, it costs $$$$ to get in and there aren't automatic
passes for OSS luminaries.  It's also a pretty useless conference from a
technical perspective; one of us would be going just to schmooze, like OSBC.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Thu, 2006-11-09 at 11:42 +0100, Lukas Kahwe Smith wrote:
> Leif B. Kristensen wrote:
>
> > It's hardly more difficult to start using PostgreSQL than MySQL. The
> > installation part is mostly the same. Regarding the query language
> > itself, the differences are small enough that it doesn't matter much
> > from a learning point of view.
>
> The difference is that PostgreSQL does not bundle as much functionality
> as MySQL does. Also the simple fact that you have plenty of choices in
> PostgreSQL makes it harder as you grow. This is why developers recommend
> other developers to use MySQL. Its not only easy to setup, but it will
> do what most of them expect even if they grow bigger. Like replication
> is in there by default etc.

Actually, I think the biggest barrier to winning over this crowd is
performance out of the box.  MySQL just sort of "works" with the default
settings, and is quite fast.  The default Postgres install, well, if you
don't tune the parameters, analyze your data, ect, the performance will
be poor compared to MySQL.

I was chatting with a developer the other day who uses MySQL, and he
explained how he chose MySQL over Postgres.  He loaded a fairly large
data set into both, did some querying on it, and MySQL was way faster.
I'm sure he didn't tune the conf file, or analyze the data, or some
combination of the things you need to do.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Brad Nicholson wrote:
> On Thu, 2006-11-09 at 11:42 +0100, Lukas Kahwe Smith wrote:
>
>> Leif B. Kristensen wrote:
>>
>>
>>> It's hardly more difficult to start using PostgreSQL than MySQL. The
>>> installation part is mostly the same. Regarding the query language
>>> itself, the differences are small enough that it doesn't matter much
>>> from a learning point of view.
>>>
>> The difference is that PostgreSQL does not bundle as much functionality
>> as MySQL does. Also the simple fact that you have plenty of choices in
>> PostgreSQL makes it harder as you grow. This is why developers recommend
>> other developers to use MySQL. Its not only easy to setup, but it will
>> do what most of them expect even if they grow bigger. Like replication
>> is in there by default etc.
>>
>
> Actually, I think the biggest barrier to winning over this crowd is
> performance out of the box.  MySQL just sort of "works" with the default
> settings, and is quite fast.  The default Postgres install, well, if you
> don't tune the parameters, analyze your data, ect, the performance will
> be poor compared to MySQL.
>
> I was chatting with a developer the other day who uses MySQL, and he
> explained how he chose MySQL over Postgres.  He loaded a fairly large
> data set into both, did some querying on it, and MySQL was way faster.
> I'm sure he didn't tune the conf file, or analyze the data, or some
> combination of the things you need to do.
>
>
That's hard to compete with.  By default tables created in MySQL are
non-transactional...and can thus operate with much less overhead .  In a
"query only" - or when you are only inserting rows in a table -
situation, this is quite fast.  As a transactional database, I'm not
sure how well PostgreSQL could compete with that...

--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

> I was chatting with a developer the other day who uses MySQL, and he
> explained how he chose MySQL over Postgres.  He loaded a fairly large
> data set into both, did some querying on it, and MySQL was way faster.
> I'm sure he didn't tune the conf file, or analyze the data, or some
> combination of the things you need to do.

He probably also didn't use Innodb :)

Joshua D. Drake


>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Brad Nicholson wrote:

>Actually, I think the biggest barrier to winning over this crowd is
>performance out of the box.  MySQL just sort of "works" with the default
>settings, and is quite fast.  The default Postgres install, well, if you
>don't tune the parameters, analyze your data, ect, the performance will
>be poor compared to MySQL.
>
>I was chatting with a developer the other day who uses MySQL, and he
>explained how he chose MySQL over Postgres.  He loaded a fairly large
>data set into both, did some querying on it, and MySQL was way faster.
>I'm sure he didn't tune the conf file, or analyze the data, or some
>combination of the things you need to do.
>
>
>

The problem with this is that there is no "one size fits all"
configuration I can think of.  Using 4G of memory on a machine with 8G
of memory and the machine is dedicated to postgres is maybe about right,
if not underutilizing the machine.  Using 4G of memory on a machine with
256M of memory which is mainly doing other things is bad.

What might not be a bad idea is a configuration generator- a simple
program that you can give small amount of information to (how much
memory to use, how many concurrent connections, etc) and produces a
reasonable configuration file.  This wouldn't necessarily be an optimal
configuration file, and real admins would probably still want to hand
edit their configuration file.  For example, I would have it just
generate more or less acceptable values for autovacuuming.  This would
be newbie oriented program- newbies don't know anything about vacuuming,
let alone autovacuuming.

I don't think this would be that hard to write.  Thoughts?

Brian

On Fri, 2006-11-10 at 14:53 -0500, Chander Ganesan wrote:
> Brad Nicholson wrote:
> > On Thu, 2006-11-09 at 11:42 +0100, Lukas Kahwe Smith wrote:
> >
> >> Leif B. Kristensen wrote:
> >>
> >>
> >>> It's hardly more difficult to start using PostgreSQL than MySQL. The
> >>> installation part is mostly the same. Regarding the query language
> >>> itself, the differences are small enough that it doesn't matter much
> >>> from a learning point of view.
> >>>
> >> The difference is that PostgreSQL does not bundle as much functionality
> >> as MySQL does. Also the simple fact that you have plenty of choices in
> >> PostgreSQL makes it harder as you grow. This is why developers recommend
> >> other developers to use MySQL. Its not only easy to setup, but it will
> >> do what most of them expect even if they grow bigger. Like replication
> >> is in there by default etc.
> >>
> >
> > Actually, I think the biggest barrier to winning over this crowd is
> > performance out of the box.  MySQL just sort of "works" with the default
> > settings, and is quite fast.  The default Postgres install, well, if you
> > don't tune the parameters, analyze your data, ect, the performance will
> > be poor compared to MySQL.
> >
> > I was chatting with a developer the other day who uses MySQL, and he
> > explained how he chose MySQL over Postgres.  He loaded a fairly large
> > data set into both, did some querying on it, and MySQL was way faster.
> > I'm sure he didn't tune the conf file, or analyze the data, or some
> > combination of the things you need to do.
> >
> >
> That's hard to compete with.  By default tables created in MySQL are
> non-transactional...and can thus operate with much less overhead .  In a
> "query only" - or when you are only inserting rows in a table -
> situation, this is quite fast.  As a transactional database, I'm not
> sure how well PostgreSQL could compete with that...

I certainly agree.  But if that's the market people want to compete with
(I'm not implying that it is), that's and issue that needs to be
addressed.  There are simple ways, like shipping with alternate
postgresql.conf files.  If I remember the MySQL install procedure, I
seem to recall that you had a choice of three default conf files to
chose from.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

On Fri, 2006-11-10 at 11:58 -0800, Joshua D. Drake wrote:
> > I was chatting with a developer the other day who uses MySQL, and he
> > explained how he chose MySQL over Postgres.  He loaded a fairly large
> > data set into both, did some querying on it, and MySQL was way faster.
> > I'm sure he didn't tune the conf file, or analyze the data, or some
> > combination of the things you need to do.
>
> He probably also didn't use Innodb :)

He also mentioned that a product he rolled out on MySQL recently had
some pretty serious performance problems almost immediately after it
launched and had more than 5 clients connecting :-)
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Hi All,

On 11/10/06, Brian Hurt <bhurt@janestcapital.com> wrote:
> The problem with this is that there is no "one size fits all"
> configuration I can think of.  Using 4G of memory on a machine with 8G
> of memory and the machine is dedicated to postgres is maybe about right,
> if not underutilizing the machine.  Using 4G of memory on a machine with
> 256M of memory which is mainly doing other things is bad.
>
> What might not be a bad idea is a configuration generator- a simple
> program that you can give small amount of information to (how much
> memory to use, how many concurrent connections, etc) and produces a
> reasonable configuration file.  This wouldn't necessarily be an optimal
> configuration file, and real admins would probably still want to hand
> edit their configuration file.  For example, I would have it just
> generate more or less acceptable values for autovacuuming.  This would
> be newbie oriented program- newbies don't know anything about vacuuming,
> let alone autovacuuming.
>
> I don't think this would be that hard to write.  Thoughts?

i agree. lots of times postgresql is perceived as slow, because of
out-of-the-box configuration. most importantly, the memory
configuration.

perhaps a "make tune" or "make tune 50%" or "make tune 75%".

and it should be mentioned in the README, along with the make-instructions.

there is also another funny thing, about an article mentioned before:
http://tweakers.net/reviews/649/7

this is a dutch slashdot-alike site, which runs on mysql. the article
is about a back-to-back test between pgsql-mysql on opteron and
ultrasparc hardware. very thorough and well tuned.

they clearly show that postgresql scales much better.

the funny thing is: they don't consider switching to postgresql
themselves, even though they suffered a slashdotting a couple of
months back. i can't imagine they never had corrupted tables. would it
be worthwhile to have a postgresql-advocacy officer to contact them?

regards,

usleep

usleepless@gmail.com writes:
> i agree. lots of times postgresql is perceived as slow, because of
> out-of-the-box configuration. most importantly, the memory
> configuration.

The defaults are a lot higher than they used to be, so I suspect that
with 8.2, this may be less of an issue than it has traditionally been,
particularly in that 8.2 uses various resources more effectively than
has been true in the past.

To wit...  I did a "CVS HEAD" install the other day, to do some
Slony-I testing.  The *ONLY* config change I made was to change the
port number.  (Because the box already has 9 other production PG
instances running on it! ;-))

The defaults for various interesting things were...
max_connections = 100
shared_buffers = 32MB
max_fsm_pages = 204800

The default work_mem was 1MB, which is arguably low, on a machine with
15GB of RAM :-).

But frankly, I don't see any particular problem with the defaults.
32MB is *not* an over-tiny amount of shared memory; it's enough to be
meaningful for more than merely a trivial workload.

It looks like initdb is doing a better job of detecting system sizing
than it used to, and the default numbers are no longer embarrassingly
low.  The 524K of yesteryear *was* embarrassing, but 32MB seems enough
larger to me.
--
(format nil "~S@~S" "cbbrowne" "linuxfinances.info")
http://linuxdatabases.info/info/postgresql.html
Rules of the  Evil Overlord #207. "Employees will  have conjugal visit
trailers which  they may use provided  they call in  a replacement and
sign out on  the timesheet. Given this, anyone caught  making out in a
closet  while  leaving  their   station  unmonitored  will  be  shot."
<http://www.eviloverlord.com/>

Hi,

if you guys are really serious about wanting to win over people who are
mysql users or are the people currently served by them .. then you have
to put in some serious PR efforts to pound it into peoples brains that
PostgreSQL is just as easy to use. MySQL throws around the 15 minute
rule for installation. My last PostgreSQL install definitely made this
mark without any extra tuning. And yes the MySQL install gives you a few
choices about the type of work load you expect. And yes MySQL has
replication build in. It also supports charsets etc. And from a checkbox
perspective it pretty much supports everything else there is.

So again you have to make a concerted PR effort with a stream of
published articles in magazines, conferences, blogs and news items where
you highlight the ease of installation (this include some sort of
replication as MySQL has established that benchmark).

Also people need to hand hold middleware language and framework
projects. So that they feel cool about supporting this superior
database. It should make them all fuzzy inside. People give PostgreSQL
credit for having all the enterprise features in a more complete
fashion. Now you need to make it clear that its supported in all sorts
of applications.

However, before the fun can really begin you need to make it easy for
hosters to setup PostgreSQL. Make sure hosters can put in solid quota's
for disc space, memory usage, CPU usage or whatever so that they can
create nicely distinct packages for their users.

Now the question is, do you guys really want to put in that much effort?
I am all for making as much things as easy as sensible. But imho
PostgreSQL is best served if you guys focus on what has distingished it
from MySQL. And that is providing a platform with superior SQL standards
completeness and compliance. And extensible framework that promotes user
choice over one size fits all defaults.

regards,
Lukas

With respect to quotas for cpu, etc, Mark Kirkwood has recently implemented workload management that allows role-based
administrationof workload limits on a per-statement basis.  Currently this allows prescribed concurrency and cost-based
limits,and will in future allow for specification of priority, memory and othwr resources. 

We will have more with respect to the design and proposal for inclusion into 8.3 later, but expect a superior workload
managementcapability to be the outcome. 

- Luke

Msg is shrt cuz m on ma treo

 -----Original Message-----
From:     Lukas Kahwe Smith [mailto:smith@pooteeweet.org]
Sent:    Friday, November 10, 2006 03:55 PM Eastern Standard Time
To:    usleepless@gmail.com; pgsql-advocacy@postgresql.org
Subject:    Re: [pgsql-advocacy] Postgres v MySQL 5.0

Hi,

if you guys are really serious about wanting to win over people who are
mysql users or are the people currently served by them .. then you have
to put in some serious PR efforts to pound it into peoples brains that
PostgreSQL is just as easy to use. MySQL throws around the 15 minute
rule for installation. My last PostgreSQL install definitely made this
mark without any extra tuning. And yes the MySQL install gives you a few
choices about the type of work load you expect. And yes MySQL has
replication build in. It also supports charsets etc. And from a checkbox
perspective it pretty much supports everything else there is.

So again you have to make a concerted PR effort with a stream of
published articles in magazines, conferences, blogs and news items where
you highlight the ease of installation (this include some sort of
replication as MySQL has established that benchmark).

Also people need to hand hold middleware language and framework
projects. So that they feel cool about supporting this superior
database. It should make them all fuzzy inside. People give PostgreSQL
credit for having all the enterprise features in a more complete
fashion. Now you need to make it clear that its supported in all sorts
of applications.

However, before the fun can really begin you need to make it easy for
hosters to setup PostgreSQL. Make sure hosters can put in solid quota's
for disc space, memory usage, CPU usage or whatever so that they can
create nicely distinct packages for their users.

Now the question is, do you guys really want to put in that much effort?
I am all for making as much things as easy as sensible. But imho
PostgreSQL is best served if you guys focus on what has distingished it
from MySQL. And that is providing a platform with superior SQL standards
completeness and compliance. And extensible framework that promotes user
choice over one size fits all defaults.

regards,
Lukas

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Hi List and Chris,

On 11/10/06, Chris Browne <cbbrowne@acm.org> wrote:
> The defaults for various interesting things were...
> max_connections = 100
> shared_buffers = 32MB
> max_fsm_pages = 204800
>
> The default work_mem was 1MB, which is arguably low, on a machine with
> 15GB of RAM :-).
>
> But frankly, I don't see any particular problem with the defaults.
> 32MB is *not* an over-tiny amount of shared memory; it's enough to be
> meaningful for more than merely a trivial workload.

"trivial", that's the point. there are people who are loading huge
tables into postgresql the same way they do into mysql. and then they
compare some queries. and then go back to mysql.

i think we need a make tune command which can configure at least up to
50% of the machine's physical hardware.

just my thoughts,

regards,

usleep

On Fri, 2006-11-10 at 14:39 -0500, Brad Nicholson wrote:
> On Thu, 2006-11-09 at 11:42 +0100, Lukas Kahwe Smith wrote:
> > Leif B. Kristensen wrote:
> >
> > > It's hardly more difficult to start using PostgreSQL than MySQL. The
> > > installation part is mostly the same. Regarding the query language
> > > itself, the differences are small enough that it doesn't matter much
> > > from a learning point of view.
> >
> > The difference is that PostgreSQL does not bundle as much functionality
> > as MySQL does. Also the simple fact that you have plenty of choices in
> > PostgreSQL makes it harder as you grow. This is why developers recommend
> > other developers to use MySQL. Its not only easy to setup, but it will
> > do what most of them expect even if they grow bigger. Like replication
> > is in there by default etc.
>
> Actually, I think the biggest barrier to winning over this crowd is
> performance out of the box.  MySQL just sort of "works" with the default

If I were to choose between "performance out of the box" and "safety out
of the box" I would choose safety every time.

The fact that MySQL chooses performance instead of safety by default is
purely for marketing reasons, and is irresponsible in my opinion. If all
PostgreSQL cared about was attracting those types of new users, they
could ship with fsync=off.

It's hard to outdo the MySQL marketing, because marketing influences
every aspect of their product.

Regards,
    Jeff Davis

> Now the question is, do you guys really want to put in that much effort?
> I am all for making as much things as easy as sensible. But imho
> PostgreSQL is best served if you guys focus on what has distingished it
> from MySQL. And that is providing a platform with superior SQL standards
> completeness and compliance. And extensible framework that promotes user
> choice over one size fits all defaults.
>

I am still wondering why the PostgreSQL *community* is trying to compete
with the MySQL *Corporation*.

Joshua D. Drake



> regards,
> Lukas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Friday 10. November 2006 21:00, Brian Hurt wrote:
>What might not be a bad idea is a configuration generator- a simple
>program that you can give small amount of information to (how much
>memory to use, how many concurrent connections, etc) and produces a
>reasonable configuration file.  This wouldn't necessarily be an
> optimal configuration file, and real admins would probably still want
> to hand edit their configuration file.

I think that's a great idea. After three years, I still haven't got
around to tune my pg installation. But for my rather smallish dataset
(~4MB), it runs okay right out of the box on a 3GHz prescott with 1 GB
RAM. And for somewhat complex queries, it actually runs faster than a
denormalized mirror database running on MySQL with MyISAM.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

On Friday 10 November 2006 17:39, Joshua D. Drake wrote:
> > Now the question is, do you guys really want to put in that much effort?
> > I am all for making as much things as easy as sensible. But imho
> > PostgreSQL is best served if you guys focus on what has distingished it
> > from MySQL. And that is providing a platform with superior SQL standards
> > completeness and compliance. And extensible framework that promotes user
> > choice over one size fits all defaults.
>
> I am still wondering why the PostgreSQL *community* is trying to compete
> with the MySQL *Corporation*.
>

Because the postresql *corporations* don't provide as many resources as the
mysql *community* ?

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

On Fri, 2006-11-10 at 19:26 -0500, Robert Treat wrote:
> On Friday 10 November 2006 17:39, Joshua D. Drake wrote:
> > > Now the question is, do you guys really want to put in that much effort?
> > > I am all for making as much things as easy as sensible. But imho
> > > PostgreSQL is best served if you guys focus on what has distingished it
> > > from MySQL. And that is providing a platform with superior SQL standards
> > > completeness and compliance. And extensible framework that promotes user
> > > choice over one size fits all defaults.
> >
> > I am still wondering why the PostgreSQL *community* is trying to compete
> > with the MySQL *Corporation*.
> >
>
> Because the postresql *corporations* don't provide as many resources as the
> mysql *community* ?

Could that be because the postgresql corporations don't run into people
actually using MySQL over PostgreSQL?

I mean really... let's look at the majority of MySQL users out there.
Now lets look at the majority of PostgreSQL users out there.

If you are a developer and you say:

Well I loaded PostgreSQL and I loaded MySQL and MySQL was faster... well
you are a pretty stupid developer and I wouldn't hire you and I feel
sorry for the person who did.

A smart developer, will review his options. A smart developer knows
there is more to it than just plugging it in and watching the flashing
lights. There are times when MySQL *is* faster and more approporiate to
use than PostgreSQL and vice versa.. but the plug-n-play argument is
false.

It reminds me of the conversation I had with a java developer on
#postgresql . They guy logs on and says,

Dev: PostgreSQL is slow
Me: What seems to be the problem
Dev: I am using Hibernate and my queries are slow
Me: Do you vacuum? Did you analyze? What does the explain say?
Dev: parse error
Me: ??maintenance
Dev: You mean there is stuff you have to do to the database beside
Hibernate config?
Me: /quit

Sincerely,

Joshua D. Drake







--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
> On Fri, 2006-11-10 at 19:26 -0500, Robert Treat wrote:
>> On Friday 10 November 2006 17:39, Joshua D. Drake wrote:
>>>> Now the question is, do you guys really want to put in that much effort?
>>>> I am all for making as much things as easy as sensible. But imho
>>>> PostgreSQL is best served if you guys focus on what has distingished it
>>>> from MySQL. And that is providing a platform with superior SQL standards
>>>> completeness and compliance. And extensible framework that promotes user
>>>> choice over one size fits all defaults.
>>> I am still wondering why the PostgreSQL *community* is trying to compete
>>> with the MySQL *Corporation*.
>>>
>> Because the postresql *corporations* don't provide as many resources as the
>> mysql *community* ?
>
> Could that be because the postgresql corporations don't run into people
> actually using MySQL over PostgreSQL?
>
> I mean really... let's look at the majority of MySQL users out there.
> Now lets look at the majority of PostgreSQL users out there.
>
> If you are a developer and you say:
>
> Well I loaded PostgreSQL and I loaded MySQL and MySQL was faster... well
> you are a pretty stupid developer and I wouldn't hire you and I feel
> sorry for the person who did.
>
> A smart developer, will review his options. A smart developer knows
> there is more to it than just plugging it in and watching the flashing
> lights. There are times when MySQL *is* faster and more approporiate to
> use than PostgreSQL and vice versa.. but the plug-n-play argument is
> false.
>
> It reminds me of the conversation I had with a java developer on
> #postgresql . They guy logs on and says,
>
> Dev: PostgreSQL is slow
> Me: What seems to be the problem
> Dev: I am using Hibernate and my queries are slow
> Me: Do you vacuum? Did you analyze? What does the explain say?
> Dev: parse error
> Me: ??maintenance
> Dev: You mean there is stuff you have to do to the database beside
> Hibernate config?
> Me: /quit

Well I think you fail to understand here that a developer != DBA. So a
developer without a DBA is smart if he chooses an RDBMS that requires
little manual maintenance. MaxDB in theory tries to fill that niche, but
its too hard to setup initially and not integrated into enough
applications. Firebird is also big in this area. Much easier to setup,
but also lacks wide adoption. So the point is MySQL gives decent enough
performance initially. It allows you to get up and running. And once you
have invested this initial time, you are more likely to spend the
additional time/money to tweaks things once you hit a wall. But at that
point you can actually afford to do so!

So the point is: MySQL is a perfectly valid choice for most developers,
even if they are aware of its limitations.

My point is: Cater to the DBA's of the world, that are very willing to
learn how to manually tune PostgreSQL.

regards,
Lukas

> > A smart developer, will review his options. A smart developer knows
> > there is more to it than just plugging it in and watching the flashing
> > lights. There are times when MySQL *is* faster and more approporiate to
> > use than PostgreSQL and vice versa.. but the plug-n-play argument is
> > false.
> >
> > It reminds me of the conversation I had with a java developer on
> > #postgresql . They guy logs on and says,
> >
> > Dev: PostgreSQL is slow
> > Me: What seems to be the problem
> > Dev: I am using Hibernate and my queries are slow
> > Me: Do you vacuum? Did you analyze? What does the explain say?
> > Dev: parse error
> > Me: ??maintenance
> > Dev: You mean there is stuff you have to do to the database beside
> > Hibernate config?
> > Me: /quit
>
> Well I think you fail to understand here that a developer != DBA. So a
> developer without a DBA is smart if he chooses an RDBMS that requires
> little manual maintenance.

No, I understand it perfectly and I stand by my assertion. That
developer, should have either:

A. Worked with a DBA
B. Learned to be a DBA
C. Spent one day just getting the at least the basics down

If he is too lazy or dumb to do that, I don't want him near my
application and I do feel sorry the person that hired him. The person
that hired him is going to spend a great deal more fixing all of the
mistakes he makes because he didn't do his homework than he would of if
he actually paid a professional.

A professional would not take the same approach as the developer you
mention or the developer I spoke with. A professional does what needs to
be done to make sure that the application is done right.

> have invested this initial time, you are more likely to spend the
> additional time/money to tweaks things once you hit a wall. But at that
> point you can actually afford to do so!
>

Can you?

/me points at drupal
/me points at slashdot
/me points at (a bunch of customers I have that have spent entirely too
much money fixing mysql problems by porting to postgresql than they
would have if they just did it right the first time)

Sincerely,

Joshua D. Drake


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

> > have invested this initial time, you are more likely to spend the
> > additional time/money to tweaks things once you hit a wall. But at that
> > point you can actually afford to do so!
> >
>
> Can you?
>
> /me points at drupal
> /me points at slashdot
> /me points at (a bunch of customers I have that have spent entirely too
> much money fixing mysql problems by porting to postgresql than they
> would have if they just did it right the first time)

To be fair to the MySQL comment, most of those problems are caused by
inept programmers, not MySQL itself. MySQL becomes a sympton at that
point.

Joshua D. Drake


>
> Sincerely,
>
> Joshua D. Drake
>
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Well I loaded PostgreSQL and I loaded MySQL and MySQL was faster... well
> you are a pretty stupid developer and I wouldn't hire you and I feel
> sorry for the person who did.
> ...
> It reminds me of the conversation I had with a java developer on
> #postgresql . They guy logs on and says,
>
> Dev: PostgreSQL is slow
> Me: What seems to be the problem
> Dev: I am using Hibernate and my queries are slow
> Me: Do you vacuum? Did you analyze? What does the explain say?
> Dev: parse error
> Me: ??maintenance
> Dev: You mean there is stuff you have to do to the database beside
> Hibernate config?
> Me: /quit

You're entitled to your opinion, but from an advocacy perspective, that's
a terrible response and a terrible attitude. Our goal should be to educate
and encourage newbies, not laugh at them and drive them off. Unfortunately,
I see too much of the latter, and not enough of the former, including on
this very list.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200611121950
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFV8FfvJuQZxSWSsgRAlp4AJ96DEzbKzzfGYGglmNJkkHsFhK6TACghLOJ
VRN1EsmVOIjYE1d6r9Ti2Zo=
=ohnO
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I am still wondering why the PostgreSQL *community* is trying to compete
> with the MySQL *Corporation*.

Well, because PostgreSQL does not really have a Corporation, and MySQL
does not really have a Community.

However, they both want to see their products do well and gain mind and
market share. I think our advocacy efforts are severely lacking at the
moment, and I'd certainly not be adverse to seeing more effort by both
the PG community and the PG corporations to fix this. And yes, MySQL
is certainly one of our competitors: saying otherwise is wishful thinking
at best.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200611121954
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFV8KhvJuQZxSWSsgRAt6EAKDblaGn4csG9+fEAU5emIMdwcKfLgCgnC3p
KpiwqvKLkCg5u1pKjyFG0bQ=
=9E4N
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> But frankly, I don't see any particular problem with the defaults.

random_page_cost anyone?

> It looks like initdb is doing a better job of detecting system sizing
> than it used to, and the default numbers are no longer embarrassingly
> low.  The 524K of yesteryear *was* embarrassing, but 32MB seems enough
> larger to me.

Yes, it's gotten better, but we're not quite there yet. I think the new
ability to specify values in common terms like "MB" will help us out a
lot too (thanks Peter!)

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200611122003
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFV8SPvJuQZxSWSsgRArJzAJ4pSZhk2OL52yy7MOHGvZCmPLSztQCffSoI
ZeQ7LR+A+LnvO+hQUKrS8uU=
=zIV3
-----END PGP SIGNATURE-----

>> Dev: PostgreSQL is slow
>> Me: What seems to be the problem
>> Dev: I am using Hibernate and my queries are slow
>> Me: Do you vacuum? Did you analyze? What does the explain say?
>> Dev: parse error
>> Me: ??maintenance
>> Dev: You mean there is stuff you have to do to the database beside
>> Hibernate config?
>> Me: /quit
>>
>
> You're entitled to your opinion, but from an advocacy perspective, that's
> a terrible response and a terrible attitude. Our goal should be to educate
> and encourage newbies, not laugh at them and drive them off. Unfortunately,
> I see too much of the latter, and not enough of the former, including on
> this very list.
>
I have spent more time than is probably healthy helping newbies on these
lists and various support channels.
This isn't about helping newbies, I am 100% happy to help newbies. My
point in this thread is not about
newbies. My point is about irresponsibility of developers, specifically
professional developers  and lack of
willingness to actually understand your platform.

If you say, I am a developer... you are not a newbie. You may be new to
the platform, but if you are actually
a developer you are more than smart enough and you have a
*responsibility* to make sure you understand
that platform.

Sincerely,

Joshua D. Drake



> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200611121950
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iD8DBQFFV8FfvJuQZxSWSsgRAlp4AJ96DEzbKzzfGYGglmNJkkHsFhK6TACghLOJ
> VRN1EsmVOIjYE1d6r9Ti2Zo=
> =ohnO
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>
>

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
>> I am still wondering why the PostgreSQL *community* is trying to compete
>> with the MySQL *Corporation*.
>>
>
> Well, because PostgreSQL does not really have a Corporation, and MySQL
> does not really have a Community.
>
> However, they both want to see their products do well and gain mind and
> market share. I think our advocacy efforts are severely lacking at the
> moment, and I'd certainly not be adverse to seeing more effort by both
> the PG community and the PG corporations to fix this. And yes, MySQL
> is certainly one of our competitors: saying otherwise is wishful thinking
> at best.
>
I believe you know, that you don't have to convince me that we need
better advocacy. My point is...
MySQL in general isn't the target. We don't have to care if you are a
MySQL or Oracle developer.
We just need to positively advocate *our* database. The rest will fall
into place on its own.

Sincerely,

Joshua D. Drake


> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200611121954
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iD8DBQFFV8KhvJuQZxSWSsgRAt6EAKDblaGn4csG9+fEAU5emIMdwcKfLgCgnC3p
> KpiwqvKLkCg5u1pKjyFG0bQ=
> =9E4N
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

usleepless@gmail.com writes:
> Hi List and Chris,
>
> On 11/10/06, Chris Browne <cbbrowne@acm.org> wrote:
>> The defaults for various interesting things were...
>> max_connections = 100
>> shared_buffers = 32MB
>> max_fsm_pages = 204800
>>
>> The default work_mem was 1MB, which is arguably low, on a machine with
>> 15GB of RAM :-).
>>
>> But frankly, I don't see any particular problem with the defaults.
>> 32MB is *not* an over-tiny amount of shared memory; it's enough to be
>> meaningful for more than merely a trivial workload.
>
> "trivial", that's the point. there are people who are loading huge
> tables into postgresql the same way they do into mysql. and then they
> compare some queries. and then go back to mysql.
>
> i think we need a make tune command which can configure at least up to
> 50% of the machine's physical hardware.

I disagree.

With the improvements in the quality of buffer usage, if the buffer
sizing has improved by a factor of 64, I do NOT see there being a
similar kind of improvement to be had by trying to get exceedingly
automated about going further.

To *in theory* get another 64-fold improvement requires that people be
prepared to devote 2GB of RAM to shared memory, which seems very
unreasonable.

The fact is that the changes that have been made are, *RIGHT NOW*, an
enormous improvement.  Increasing buffer sizes further will NOT have
the same "return on investment."
--
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/linux.html
'There  is  no substitute  for  good  manners,  except, perhaps,  fast
reflexes.' -- random unix fortune

On Mon, Nov 13, 2006 at 04:22:11AM -0800, Joshua D. Drake wrote:
>
> >>Dev: PostgreSQL is slow
> >>Me: What seems to be the problem
> >>Dev: I am using Hibernate and my queries are slow
> >>Me: Do you vacuum? Did you analyze? What does the explain say?
> >>Dev: parse error
> >>Me: ??maintenance
> >>Dev: You mean there is stuff you have to do to the database beside
> >>Hibernate config?
> >>Me: /quit
> >>
> >
> >You're entitled to your opinion, but from an advocacy perspective, that's
> >a terrible response and a terrible attitude. Our goal should be to educate
> >and encourage newbies, not laugh at them and drive them off. Unfortunately,
> >I see too much of the latter, and not enough of the former, including on
> >this very list.
> >
> I have spent more time than is probably healthy helping newbies on these
> lists and various support channels.
> This isn't about helping newbies, I am 100% happy to help newbies. My
> point in this thread is not about
> newbies. My point is about irresponsibility of developers, specifically
> professional developers  and lack of
> willingness to actually understand your platform.
>
> If you say, I am a developer... you are not a newbie. You may be new to
> the platform, but if you are actually
> a developer you are more than smart enough and you have a
> *responsibility* to make sure you understand
> that platform.

That's making the (in my experience false) assumption that your average
app developer has any clue about databases. Most app developers can
string together simple DML and DDL, and *maybe* have some concept of
normalization. This is simply because they have no desire to become
database people; they just want to get their app written.

One of the best things the community could do would be to help educate
these people on basic database concepts and pitfalls, such as why
concurrency is almost always more important than single-connection
performance. Or why you want to keep the number of round-trips to the
database to a minimum. Or why ACID is important for apps other than
banking. Etc., etc.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Mon, Nov 13, 2006 at 04:28:40AM -0800, Joshua D. Drake wrote:
> Greg Sabino Mullane wrote:
> >However, they both want to see their products do well and gain mind and
> >market share. I think our advocacy efforts are severely lacking at the
> >moment, and I'd certainly not be adverse to seeing more effort by both
> >the PG community and the PG corporations to fix this. And yes, MySQL
> >is certainly one of our competitors: saying otherwise is wishful thinking
> >at best.
> >
> I believe you know, that you don't have to convince me that we need
> better advocacy. My point is...
> MySQL in general isn't the target. We don't have to care if you are a
> MySQL or Oracle developer.
> We just need to positively advocate *our* database. The rest will fall
> into place on its own.

Sure, but part of that positioning is educating people on how we're
different than MySQL, since a huge number of developers don't understand
that.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

>> If you say, I am a developer... you are not a newbie. You may be new to
>> the platform, but if you are actually
>> a developer you are more than smart enough and you have a
>> *responsibility* to make sure you understand
>> that platform.
>
> That's making the (in my experience false) assumption that your average
> app developer has any clue about databases. Most app developers can

No. I am not making an assumption that an average app  at 04:22:11AM
-0800, Joshua D. Drake wrote:
>>
>> >>Dev: PostgreSQL is slow
>> >>Me: What seems to be the problem
>> >>Dev: I am using Hibernate and my queries are slow
>> >>Me: Do you vacuum? Did you analyze? What does the explain say?
>> >>Dev: parse error
>> >>Me: ??maintenance
>> >>Dev: You mean there is stuff you have to do to the database beside
>> >>Hibernate config?
>> >>Me: /quit
>> >>
>> >
>> >You're entitled to your opinion, but from an advocacy perspective,
>> that's
>> >a terrible response and a terrible attitude. Our goal should be to
>> educate
>> >and encourage newbies, not laugh at them and drive them off.
>> Unfortunately,
>> >I see too much of the latter, and not enough of the former, including
>> on
>> >this very list.
>> >
>> I have spent more time than is probably healthy helping newbies on these
>> lists and various support channels.
>> This isn't about helping newbies, I am 100% happy to help newbies. My
>> point in this thread is not about
>> newbies. My point is about irresponsibility of developers, specifically
>> professional developers  and lack of
>> willingness to actually understand your platform.
>> developer has any clue about databases. I am declaring that it is their
*responsibility* to make sure they do (if they are going to use the
platform).

I don't care if an app developer does not have experience. I care that he
is too lazy to do his homework or get help from someone who has the
experience.

> One of the best things the community could do would be to help educate
> these people on basic database concepts and pitfalls, such as why
> concurrency is almost always more important than single-connection
> performance. Or why you want to keep the number of round-trips to the
> database to a minimum. Or why ACID is important for apps other than
> banking. Etc., etc.

I agree 100% with this.

Sincerely,

Joshua D. Drake



> --
> Jim Nasby                                            jim@nasby.net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

Good comparison (for Postgres, anyways) from digg:

http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html

On 1/19/2007 12:32 PM, Brian Hurt wrote:
> Good comparison (for Postgres, anyways) from digg:
>
> http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html

I liked particularly this user comment:

"Now, the MySQL performance using the new techniques beats Postgres
performance using the old techniques."

So optimizing a crappy database for MySQL makes it faster than running
the crappy database on Postgres ... I never expected that one.


Jan


>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match


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