Why PostgreSQL is not that popular as MySQL?

Raymond Chui <raymond.chui@noaa.gov> writes:

I am just start look at PostgreSQL for our Redhat Linux.
I am wonder why most of people choose MySQL in Linux
world rather than PostgreSQL? PostgreSQL has 15 years
history (I never know that before) which is much longer
than MySQL. Also PostgreSQL supports a lot of things
which MySQL has not support yet.

Postgres, yes. PostgreSQL, no. PostgreSQL was a new project with
Postgres95 as a starting point. Postgres95 was an attempt to put an
SQL front-end on Postgres. AFAIK, most all of the Postgres code was
jettisoned early on for performance reasons. That makes PostgreSQL
roughly five years old, code-wise.

I still have a Postgres95 tree in CVS before the PostgreSQL fork to
prove it, too! ;)

--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

Raymond Chui <raymond.chui@noaa.gov> writes:

I am just start look at PostgreSQL for our Redhat Linux.
I am wonder why most of people choose MySQL in Linux
world rather than PostgreSQL? PostgreSQL has 15 years
history (I never know that before) which is much longer
than MySQL. Also PostgreSQL supports a lot of things
which MySQL has not support yet.

This is just my personal opinion (of course). Several years back when folks
were starting to use SQL databases as the back end for web sites on Linux,
PostgreSQL was a bit of a dog -- slow and prone to memory leaks. MySQL on
the other hand was very fast and although it lacked many of the features of
PostgreSQL it was more than adequate for many web site needs. It was in the
right place at the right time.

Things seemed to have changed now, PostgreSQL has improved tremendously and
on some benchmarks is considerably faster than MySQL ...

--
Mike
-----------------------------------------------------------------------------
Mike Kenney
Software Engineer
UW Applied Physics Lab
mikek@apl.washington.edu (206) 543-1300 / (206) 543-6785 FAX
-----------------------------------------------------------------------------

On 29 Nov 2000, Ronald Cole wrote:

Raymond Chui <raymond.chui@noaa.gov> writes:

I am just start look at PostgreSQL for our Redhat Linux.
I am wonder why most of people choose MySQL in Linux
world rather than PostgreSQL? PostgreSQL has 15 years
history (I never know that before) which is much longer
than MySQL. Also PostgreSQL supports a lot of things
which MySQL has not support yet.

Postgres, yes. PostgreSQL, no. PostgreSQL was a new project with
Postgres95 as a starting point. Postgres95 was an attempt to put an
SQL front-end on Postgres. AFAIK, most all of the Postgres code was
jettisoned early on for performance reasons. That makes PostgreSQL
roughly five years old, code-wise.

I still have a Postgres95 tree in CVS before the PostgreSQL fork to
prove it, too! ;)

so do we :) way way back when:

RCS file: /home/projects/pgsql/cvsroot/pgsql/HISTORY,v
Working file: HISTORY
head: 1.79
branch:
locks: strict
access list:
symbolic names:
REL7_0_PATCHES: 1.70.0.2
REL7_0: 1.70
REL6_5_PATCHES: 1.52.0.2
REL6_5: 1.52
REL6_4: 1.44.0.2
release-6-3: 1.33
SUPPORT: 1.1.1.1
PG95-DIST: 1.1.1

---------------
Postgres95 1.02 Thu Aug 1 18:00:00 EDT 1996
-------------------------------------------------------------

Source code maintainenance and development
* worldwide team of volunteers
* the source tree now in CVS at ftp.ki.net
* developers mailing list - pg95-dev@ki.net
---------------

yeesh ... now *that* is old ... 4.5 years and growing ...

Ronald Cole <ronald@forte-intl.com> writes:

Postgres, yes. PostgreSQL, no. PostgreSQL was a new project with
Postgres95 as a starting point. Postgres95 was an attempt to put an
SQL front-end on Postgres.

Right; original Postgres used a query language called "POSTQUEL",
which was sort of like SQL but not compatible.

AFAIK, most all of the Postgres code was jettisoned early on for
performance reasons. That makes PostgreSQL roughly five years old,
code-wise.

This I dispute. A lot of the core functionality has a very traceable
lineage back to original Postgres; even though some details of the code
may have been revised pretty heavily, the algorithms and design
decisions remain. This has good points and bad points ;-) ... but
it's absolutely not true that Postgres95 threw away the existing code
and started over. As you said yourself, it was more of a question of
sticking a new frontend (ie, parser) on the existing database engine.

regards, tom lane

Raymond Chui writes:

I am wonder why most of people choose MySQL in Linux
world rather than PostgreSQL?

I normally stay out of the advocacy wars, but as a recent convert I
figure I'll pipe up.

I switched to PostgreSQL as the back-end to my services at
http://www.flutterby.com/ from MySQL when I was collaborating on a
project with a "data warehousing" person who came from a larger
systems background, where I came from a microcomputer background.

I've been seriously programming microcomputers for a decade and a
half. One of the great accomplishments of my career was shoehorning a
big database application into a 4.77 MHz PC XT under that 640k limit,
including the bigger versions of DOS and the Novell drivers. 30 meg
was a *huge* database. Assembly language and special case hacks to
CTREE (which we only used part of because the record management was
more inefficient than we could tolerate) were the norm.

When we were struggling over linker paging issues and 500 bytes here
and there the idea of wastefully having the database functionality
uncoupled from the application just wasn't an option. So referential
integrity and update triggers and transactions all those other things
that a real database system provides had to be special-cased in the
application code.

Since then I've been a games programmer, and then on to a rendering
system where we were extremely careful about space in data structures
because we were bumping up against 2 gig physical RAM limits, and the
application was running on farms of a few thousand processors.

When you're shipping a hundred thousand copies of software, hardware
costs are leveraged very hard. Saving 10 dollars in minimum hardware
requirements gives you a million bucks to spend on programming.

MySQL blends that philosophy (provide the basic services as fast and
lightweight as possible, offload the thinking on to the programmer)
with a few extremely useful data types (ENUM, longer strings and
BLOBs). It's not really a database manager, more a loosely coupled
system of tables.

However, good programmers are hard to find, the world is heading
towards a much more client-server way of thinking, and hardware is
cheaper than sand and getting more so. Now that I code for the web
it's much more reasonable to suggest that someone spend an extra $10k
on a server than that they employ a $100k+/year coder.

PostgreSQL comes from the latter camp.

The benchmarks that show PostgreSQL outperforming MySQL generally talk
about joins between 10 or more tables. This is not something that the
microcomputing side of me would even consider. When first implemented
for MySQL, my simple comment system for my personal web page tried to
intelligently cache the sort of lookups that would usually be done
with joins because that's the way I'm used to seeing things.

My impression of the PostgreSQL philosophy is that ENUM is irrelevant
because you just throw that definition in another table.

In many web apps transactions just don't matter, generally you've got
to order inserts so that other things can link to the new sequence
numbers anyway, and disk space is cheaper than testing out delete
routines (Oracle users I've talked to, and the (in)famous Philip
Greenspun in one of his talks in Berkeley, have said "Set a flag in
the record and make your queries check DELETED='N', don't ever delete,
that'll make your tables unstable"). In the sequence of things, if
that final INSERT that makes the user's message visible fails the user
will try again.

It takes a while to make the conceptual shift to thinking about how to
implement the structure of the database completely inside the database
manager rather than letting the application manage integrity.

Finally, version 7 is fairly recent, and it's the first version of
PostgreSQL that's really rich enough to be usable for quick and dirty
projects. Even at that I'm hoping that none of my users post comments
longer than 8k and holding off on converting a few other bits of my
website over to SQL 'til 7.1.

And in the process of changing my thinking I've almost ripped out
PostgreSQL and gone back to MySQL several times. Changing my code to
use different syntax for "INSERT" and "UPDATE" ("SET" versus "VALUES")
was a pain in the tail. The huge latency on connections meant that I
had to get mod_perl installed and tuned, which broke some legacy
CGI. Having to write TIMESTAMP functionality that MySQL included from
scratch meant yet another stumble in the learning curve, and I still
need to rework some of the gross places I used the wrong ENUM
workarounds while I was learning.

While I'm now convinced that PostgreSQL is the way for me to go, this
was a long uphill battle driven by my respect for people who had
different backgrounds than I, and my xenophilia for different ways of
thinking.

That momentum in the coder populace at large is going to take a while
to overcome.

Dan

On Mon, Dec 04, 2000 at 03:05:23PM -0800, Dan Lyke wrote:

I've been seriously programming microcomputers for a decade and a
half. One of the great accomplishments of my career was shoehorning a
big database application into a 4.77 MHz PC XT under that 640k limit,
including the bigger versions of DOS and the Novell drivers. 30 meg
was a *huge* database. Assembly language and special case hacks to
CTREE (which we only used part of because the record management was
more inefficient than we could tolerate) were the norm.

This is interesting.

I too come from a background of using CTREE (I'm assuming you mean the
product from www.faircom.com).

And this exposure was precisely why I choose Postgres over MySQL.
Maybe because I switched over later and started using transactions once
CTREE supported them (prior to that, we literally made backup copies of
the files to simulate transactions!).

I too am used to putting all of the integrity issues into the application.
And many many times I'd come close to writing a layer on top of ctree that
did exactly that! But having started using PostgresQL, I am hooked on
having the database handle it. Constantly reinventing the wheel inside the
application is annoying! And not very exciting.

mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen

MySQL simply did a better job of marketing themselves to an audience that
typically doesn't know SQL or understand relational databases. MySQL
focuses on speed as it's primary asset. Integration with quick growing
languages (like PHP) helped a lot too. MySQL is also the product of an
existing software company that employed basic marketing principles early in
the game.

Most of the web based projects I have found were obviously written by (good)
programmers that just didn't have a grasp of relational data models. They
just needed a quick & easy way to store large tables of information.
(Berkley DBM's only go so far) Given that this "less DB savvy" audience
didn't understand relational databases, things like triggers and decent SQL
support don't strike them as important as speed.

MySQL is a good migration path for xBase, and the multitude of DBM based
scripts that were dominate a few years ago. PostgreSQL is the only path for
someone that intends to use relational design in their applications. MySQL
doesn't even come close.

The bottom line is that the marketing of PostgreSQL is not even close to
that of MySQL. Thank god the code, community and support doesn't follow
suit!

my 2 cents

--rob

----- Original Message -----
From: "The Hermit Hacker" <scrappy@hub.org>
To: "Ronald Cole" <ronald@forte-intl.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2000 3:08 PM
Subject: Re: Re: Why PostgreSQL is not that popular as MySQL?

Raymond Chui writes:

I am wonder why most of people choose MySQL in Linux
world rather than PostgreSQL?

I've bounced between a couple of different DBMS over
the last year, and I have some pretty straightforward
ideas about the relative strengths of the ones I've
worked with:

Oracle:
Plus: complete, fast, reliable
Minus: expensive and a PITA to install and administer

MySQL:
Plus: lightweight, easy to administer, very fast
with simple queries/updates
Minus: slow with a mix of complex queries and updates,
incomplete SQL support.

PostgreSQL:
Plus: more complete than MySQL, good performance
under a variety of situations
Minus: no BLOBs (soon to be rectified, I understand),
version upgrades require dump/reload.

For most basic web applications, each page needs to be
generated very quickly, which rules out complex queries
and/or updates under almost any engine. That puts
PostgreSQL, MySQL, and Oracle on a pretty even footing
feature-wise. MySQL's speed, PostgreSQL's row size limits,
and Oracle's expense make MySQL the best choice for a lot
of simple web applications.

As a previous poster pointed out, a couple of years
ago, a lot of folks were using Berkeley DB as their
storage mechanism; watching those people move to
_any_ relational database is a good sign. As the
larger population of people working on server-side
systems learns more about relational databases,
they'll outgrow MySQL at some point as well.
Of course, the MySQL developers aren't sitting still;
if they continue to improve MySQL to meet their
user's needs, then in a few years, MySQL could be
just as complete as PostgreSQL.

- Tim

Ive read the docs through and the examples in the source directory
(searched archives etc)- and was wondering if there was a tutorial - or
more examples out there on plpgsql.
Im getting to understand it, and have some things working but some
simple examples would help a lot.

I know this has been asked many times before - but some simple tutorials
on it might encouarge the less knowledgeable of us to take up
postgresql.

As for me, when I was trying to decide and not as aware of the significance
of certain functionality's, I almost went with MySQL for a simple reason.
90% of the code examples I found involved MySQL as oppsoed to Postgresql.
That also implies it will be easier to find general help for MySQL than
Postgresql.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Mike Castle" <dalgoda@ix.netcom.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2000 6:41 PM
Subject: Re: [GENERAL] Why PostgreSQL is not that popular as MySQL?

On Mon, Dec 04, 2000 at 03:05:23PM -0800, Dan Lyke wrote:

I've been seriously programming microcomputers for a decade and a
half. One of the great accomplishments of my career was shoehorning a
big database application into a 4.77 MHz PC XT under that 640k limit,
including the bigger versions of DOS and the Novell drivers. 30 meg
was a *huge* database. Assembly language and special case hacks to
CTREE (which we only used part of because the record management was
more inefficient than we could tolerate) were the norm.

This is interesting.

I too come from a background of using CTREE (I'm assuming you mean the
product from www.faircom.com).

And this exposure was precisely why I choose Postgres over MySQL.
Maybe because I switched over later and started using transactions once
CTREE supported them (prior to that, we literally made backup copies of
the files to simulate transactions!).

I too am used to putting all of the integrity issues into the application.
And many many times I'd come close to writing a layer on top of ctree that
did exactly that! But having started using PostgresQL, I am hooked on
having the database handle it. Constantly reinventing the wheel inside

the

On Mon, Dec 04, 2000 at 08:26:12PM -0800, some SMTP stream spewed forth:

Raymond Chui writes:

I am wonder why most of people choose MySQL in Linux
world rather than PostgreSQL?

I've bounced between a couple of different DBMS over
the last year, and I have some pretty straightforward
ideas about the relative strengths of the ones I've
worked with:

Oracle:
Plus: complete, fast, reliable
Minus: expensive and a PITA to install and administer

MySQL:
Plus: lightweight, easy to administer, very fast
with simple queries/updates
Minus: slow with a mix of complex queries and updates,
incomplete SQL support.

PostgreSQL:
Plus: more complete than MySQL, good performance
under a variety of situations
Minus: no BLOBs (soon to be rectified, I understand),

*snip*

Of course, the MySQL developers aren't sitting still;
if they continue to improve MySQL to meet their
user's needs, then in a few years, MySQL could be
just as complete as PostgreSQL.

- Tim

...assuming that PostgreSQL development halts for that time. ;-)

gh

The bottom line is that the marketing of PostgreSQL is not even close to
that of MySQL. Thank god the code, community and support doesn't follow
suit!

I think MySQL got a big start by migrating mSQL users years ago and
having a compatibility module for mSQL.

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

Bruce Momjian wrote:

The bottom line is that the marketing of PostgreSQL is not even close to
that of MySQL. Thank god the code, community and support doesn't follow
suit!

I think MySQL got a big start by migrating mSQL users years ago and
having a compatibility module for mSQL.

One great advantage of mySQL (and mSQL) ist the low
connection-overhead. Especially in mSQL this was an explicit
design-goal.

In CGI-based WEB-environments it is very important that the time spent
for connecting the database is as low as possible. Time wasted there
cannot be autweight by fast SELECTS since many CGI do only very few
SQL-statements.

Using later WEB-techniques (as e.g. Servlets or Fast-CGI) this part
becomes less important since an single DB-connection can be kept open
across multiple WWW-requests. In applications where the connection is
kept open for a long time the connection overhead becomes
insignificant.

Elmar