PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?

Stephen Livesey <ste@exact3ex.co.uk> wrote:

Please could someone explain what are the major differences between
PostgreSQL, Oracle 8i, DB2 and MySQL.

Of these, MySQL lacks a number of features associated with "serious"
database development; see e.g.
http://openacs.org/philosophy/why-not-mysql.html

The major difference between PostgreSQL and Oracle 8i or DB2 is that
PostgreSQL is free software. All of them are deployed for serious
application development.

HTH,
Ray
--
"The software `wizard' is the single greatest obstacle to computer literacy
since the Mac."
http://www.osopinion.com/Opinions/MichaelKellen/MichaelKellen1.html

Oracle and DB2 also have a lot more kitchen sink features
sorta like the developers were bored and the marketing department
thought it would sound good to be able to say that the database
had a built in message queue and java vm etc....

If you work at a company that spends a lot of money and uses a lot
of third party aplications on top of the database it's hard to
avoid using one of the mainstream ones.

If you work at a company that tends to use things like linux or freebsd
and you have developers who write in things like C, perl, php, even java
etc.. then postgres is almost a no brainer.

Alex.

On Mon, 12 Feb 2001, J.H.M. Dassen (Ray) wrote:

On Sat, 10 Feb 2001 02:39, you wrote:

Please could someone explain what are the major differences between
PostgreSQL,

Pro:-

It's free in all senses of the word. ( BSD Licence )
Thus no new licence required if you migrate to more powerful hardware.
It works well enough to run a set of Ledgers.
( Since version 7, I would trust my own payroll data to it. )
It has transaction support.
It has an enormous number of builtin extensions and interface apis in many
languages.
It just goes slower under very heavy loads.

It is supported by a very competent team of developers who are not only
determined to stay at the front of open source database race, but also
quite patently give the impression that they actually _care_ about their
users' problems.

The development releases available from the tip of the CVS tree are
considerably more stable than the run-of-the-mill CVS tips in other
projects.

There are several open source projects at various stages of development
doing what you intend to do already.

Con:-

Documentation is not as up-to-date or comprehensive as perhaps it should
be.
Probably not quite as fast as the commercial products.
The point and click interfaces and support program generators are all
rather rudimentary when compared to the commercial offerings.
All disk access is via the ( slower? ) host file system; no raw disk read
or write.
Neither the replication nor hot backup facilities have had time to mature.

=====

Oracle 8i, DB2

Closed, expensive, & secret commercial offerings.
Both have good reputations.
Very good point and click interfaces.
DB2 is very well respected as a mature and solid product.

You forgot to mention Informix which has a distant common ancestor with
PostgreSQL. It works well. Its extensive documentation is well written,
but totally chaotic. You need 6 books open at the same time to get the
Dynamic Server installed!

=====

and MySQL

In a word - Don't. Definately not in an financial accounting capacity.

No transaction support. MySQL is intended as a very fast, mostly read,
data store. The designers have sacrificed data integrity for speed.
It is reported to fall over catistrophically under heavy load.

This URL will lead through to several papers which explain in more detail.

http://www.google.com/search?q=Philip+Greenspun+Why+not+MySQL&amp;btnG=Google+Search

We are looking to start re-developing our accounts/order processing
system using a major database (we currently use a 4GL). We would
appreciate any constructive advise to help us make the decision on which
database to use.

--
Sincerely etc.,

NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--

On Tue, Feb 13, 2001 at 11:55:18AM +1300, Christopher Sawtell wrote:

<snip>

It is supported by a very competent team of developers who are not only
determined to stay at the front of open source database race, but also
quite patently give the impression that they actually _care_ about their
users' problems.

Let me echo this. I have _never_ been on a list where so many of the
developers of a product were subscribed as well. And these folks
provide _very_ considerate and clear assistance. Hats off to them.

Paul

On Tue, 13 Feb 2001, Paul M Foster wrote:

Let me echo this. I have _never_ been on a list where so many of the
developers of a product were subscribed as well. And these folks
provide _very_ considerate and clear assistance. Hats off to them.

Yah. What I find is the developers set the general tone/culture of the list.
This affects the type of responses/support you get even from the other list
subscribers. So it's quite good here where you have kind and helpful developers.

As for the original question. I find in a corporate environment it boils down to
who you want blame to fall to - blame tends to flow down the payee channels.

For accounts and order processing it's probably Postgresql/Oracle/DB2.

If you have a resident DBA, get the DBA to pick the database. If the DBA is you
well then if you have lots of money you may wish to pick Oracle/DB2 - because if
you're new to DB stuff and doing major stuff, you'll probably need to blame
someone else ;). If there's very small budget then it's Postgresql, but make
sure your bosses know that they're getting a lot more than what they paid for
;).

That said, installing, configuring and maintaining Postgresql is a lot easier
than Oracle/DB2. For instance there's a lot more "backward compatibility"
ugliness in Oracle. So in a less "corporate" environment I'd say go with
postgresql.

Performancewise with these three the main factor is probably going to be
how the DBA organises the data and forms the queries. The DB engines of all 3
are quite decent once you know about their various quirks[1]I've come to a conclusion that if it doesn't have strange quirks it's not an RDBMS..

Cheerio,
Link.

[1]: I've come to a conclusion that if it doesn't have strange quirks it's not an RDBMS.
an RDBMS.

Yah. What I find is the developers set the general tone/culture of the

list.

This affects the type of responses/support you get even from the other

list

subscribers. So it's quite good here where you have kind and helpful

developers.

Helpful developers doesn't go near far enough..

I've seen (and still do see) commercial support that isn't up to the grade
of support I have gotten from the -general and -hackers lists. I can ask any
question and I *always* get a response within minutes from one of the core
developers.. I have yet to have a question go un-answered and I've been on
the list for a pretty long time! It just doesn't get much better than that
to me.

Thanks to all the people that have put up with my strange (and sometimes
amusing) questions!

-Mitch

On Fri, 16 Feb 2001, Mitch wrote:

Helpful developers doesn't go near far enough..

I've seen (and still do see) commercial support that isn't up to the grade
of support I have gotten from the -general and -hackers lists. I can ask any
question and I *always* get a response within minutes from one of the core
developers.. I have yet to have a question go un-answered and I've been on
the list for a pretty long time! It just doesn't get much better than that
to me.

What I really like is the truth. The developers will tell you straight out
that a feature is broken and they may even say they don't know how to fix it
properly yet.

Whereas for some commercial products (not all, some have excellent support),
I've had cases where they deny that there's anything wrong.

Cheerio,
Link.

I have tried the function gethostbyname(text), which should return an inet value
or reference. However, it gave me something like <unprintable>

sql#= select gethostbyname('www.postgres.org');

Anyone got any clue?

Phillip Pan
-----------

On Thu, Feb 15, 2001 at 08:49:36PM -0500, Mitch Vincent wrote:

Yah. What I find is the developers set the general tone/culture of the

list.

This affects the type of responses/support you get even from the other

list

subscribers. So it's quite good here where you have kind and helpful

developers.

Helpful developers doesn't go near far enough..

I've seen (and still do see) commercial support that isn't up to the grade
of support I have gotten from the -general and -hackers lists. I can ask any
question and I *always* get a response within minutes from one of the core
developers.. I have yet to have a question go un-answered and I've been on
the list for a pretty long time! It just doesn't get much better than that
to me.

I agree. The support from the Postgres people is outstanding. There
seems to be a catch though. It was pretty much the same with PHP about 3
years ago: I story I keep telling is that my very first question on the
list there was answered in less than 30 minutes by Rasmus Lerdorf, the
inventor of PHP himself (imagine posting a question to M$ and within 30
mins, the founder of the company . . . ). He's still very active on the
list but the sheer volume of postings has reached a limit, due to the
popularity and success of PHP, where a _lot_ is getting lost and
unanswered in php-general. I wonder whether this is an inbuilt,
unavoidable problem with free software projects once they reach a certain
level of popularity.

Regards, Frank

On Fri, 16 Feb 2001 21:12, Frank Joerdens wrote:

Helpful developers doesn't go near far enough..

I've seen (and still do see) commercial support that isn't up to the
grade of support I have gotten from the -general and -hackers lists. I
can ask any question and I *always* get a response within minutes from
one of the core developers.. I have yet to have a question go
un-answered and I've been on the list for a pretty long time! It just
doesn't get much better than that to me.

I agree. The support from the Postgres people is outstanding. There
seems to be a catch though. It was pretty much the same with PHP about 3
years ago: I story I keep telling is that my very first question on the
list there was answered in less than 30 minutes by Rasmus Lerdorf, the
inventor of PHP himself (imagine posting a question to M$ and within 30
mins, the founder of the company . . . ). He's still very active on the
list but the sheer volume of postings has reached a limit, due to the
popularity and success of PHP, where a _lot_ is getting lost and
unanswered in php-general. I wonder whether this is an inbuilt,
unavoidable problem with free software projects once they reach a
certain level of popularity.

Yes it is.

This is a universal problem. It is that once a particular package reaches
that critical mass it is completely impossible for a small team of
developers to both help the user community _and_ to develop anything at
all. I've seen this in both the free and the comercial software worlds.

The commercial world tries to solve it by having "Knowledge Base"
machinery of some kind or another. My own exp. is that it simply does not
work.

It might help to install ht://dig so that the online documentation can be
searched easily. If people think that that would be a good idea then I'd
be happy to make that contribution.

Another point is that PostgreSQL is widely used by people who have learnt
English at school. English is a proper horror of a language & it must be
extremely difficult to understand the docs. if you didn't learn English on
your Mother's knee. I learnt French at school, but I would really _hate_
to have to understand PostgreSQL from French docs. I'm suggesting that now
that the critical mass of users is nearly upon us that a serious
translation effort be made. Unfortunately I am not sufficiently able in
any foreign language to help with translation, but could perhaps attempt
to make the language of the documentation somewhat easier to understand.

--
Sincerely etc.,

NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--

I agree. The support from the Postgres people is outstanding. There
seems to be a catch though. It was pretty much the same with PHP about 3
years ago: I story I keep telling is that my very first question on the
list there was answered in less than 30 minutes by Rasmus Lerdorf, the
inventor of PHP himself (imagine posting a question to M$ and within 30
mins, the founder of the company . . . ). He's still very active on the
list but the sheer volume of postings has reached a limit, due to the
popularity and success of PHP, where a _lot_ is getting lost and
unanswered in php-general. I wonder whether this is an inbuilt,
unavoidable problem with free software projects once they reach a certain
level of popularity.

Funny you should mention PHP. I talked to Rasmus about the email volume
when I first met him in the fall. He said the volume of email is so
great that just reading the subject lines takes a long time.

We aren't there yet, but we are heading in that direction. One thing
some of us have done are to take full-time jobs with PostgreSQL so we
can handle the increased load. Second, I have started to skip emails
with subjects that contain obvious questions, relying on other users to
answer these. When several people post on the easy question, I start to
suspect there is some issue there and start reading.

Tom Lane has been very valuable in reading emails and responding to
questions.

One major thing I have done is to start reading the email in threaded
mode, so all subjects appear together. The ordering is based on the
first posting of the question, with followups appearing right after it.
Elm handles this, so I assume other mail readers to as well. This is
very helpful when I wake up in the morning and have >100 emails to read.
I can quickly scan through emails because they are all groups on the
same topic.

I suppose the major issue is not to get an answer from a major developer
each time, but to get a quick answer to your quesions, and have tough
queries properly addressed.

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

I agree. The support from the Postgres people is outstanding. There
seems to be a catch though. It was pretty much the same with PHP about 3
years ago: I story I keep telling is that my very first question on the
list there was answered in less than 30 minutes by Rasmus Lerdorf, the
inventor of PHP himself (imagine posting a question to M$ and within 30
mins, the founder of the company . . . ). He's still very active on the
list but the sheer volume of postings has reached a limit, due to the
popularity and success of PHP, where a _lot_ is getting lost and
unanswered in php-general. I wonder whether this is an inbuilt,
unavoidable problem with free software projects once they reach a certain
level of popularity.

It is a problem. A human being just can't answer ten trillion emails a day
and do anything else productive.

I use PHP a lot (with PostgreSQL ) and when I first got into PHP I was on
the PHP mailing lists, they were high volume even a few years ago so I
couldn't imagine what they are now..

The good thing about PostgreSQL is that you don't have many people
installing it to just play around. I foung that probably %80 of the
questions around the PHP mailing lists were from people that didn't have a
clue how to get started and wanted to install and code PHP because they
heard it was neat. I have nothing against that at all, it's a good thing
(sort of ), I just don't think we see that kind of attitude with PostgreSQL
(or any RDBMS really).. Generally I see people using MySQL first (why, I
don't know) and then moving to PostgreSQL for it's much more rich feature
set. In the process there people tend to learn the little stuff with MySQL
like basic SQL syntax and at least a general understanding of what's going
on. In addition to all that the people that are using PostgreSQL (or and
RDBMS) are generally using it for a specific purpose (writing an application
to use it, for instance) and programmers tend to be able to figure a lot out
for themselves.. I think that all the above contributes to the fact that
where there is a substantial amount, it's not to 100+ a day yet :-)

Anyway.. I've been on the list for almost two years now and I have seen an
increase in traffic for sure but I haven't seen a decrease in support.
Support always has and continues to be excellent. I don't think it will
become much of a problem in the near future but only time will tell!

-Mitch

... I wonder whether this is an inbuilt,
unavoidable problem with free software projects once they reach a certain
level of popularity.

Funny you should mention PHP. I talked to Rasmus about the email volume
when I first met him in the fall. He said the volume of email is so
great that just reading the subject lines takes a long time.

We aren't there yet, but we are heading in that direction.

Yes, the shape of the curve is pretty clear --- it's already not
possible for the key developers to respond to everything, and that'll
get worse. We have to start thinking about ways to spread out the load
better.

Second, I have started to skip emails
with subjects that contain obvious questions, relying on other users to
answer these.

I still try to read everything, but I'm trying to train myself not to
answer the easy questions ;-). Other folks should step up and answer
if they know the answer.

Aside from persuading more people to spend time answering email
questions, I agree we need to work harder on making answers findable
outside the mailing lists. Improving the docs, making the mail archives
more easily searchable, etc etc. I dunno if an "annotated manual" would
help --- I've never used one --- but if people want to try one, it can't
hurt. The main problem is to get the work done. We need volunteers to
actually do some of these things, not just suggest them ...

regards, tom lane

Yes, the shape of the curve is pretty clear --- it's already not
possible for the key developers to respond to everything, and that'll
get worse. We have to start thinking about ways to spread out the load
better.

I used to keep stuff in emails in mailbox until I saw someone reply,
then I would delete it from my mailbox. Now, in the morning, when I
have lots of emails, I can easily see if no one has replied and try to
answer it.

Aside from persuading more people to spend time answering email
questions, I agree we need to work harder on making answers findable
outside the mailing lists. Improving the docs, making the mail archives
more easily searchable, etc etc. I dunno if an "annotated manual" would
help --- I've never used one --- but if people want to try one, it can't
hurt. The main problem is to get the work done. We need volunteers to
actually do some of these things, not just suggest them ...

I will say that the FAQ and my book have visibly reduced the number of
questions. When I put something on the FAQ, the questions about that
topic just magically go away. I know lots of lists have the RTFM reply,
but in our case, it seems they do read the FAQ pretty thoroughly, so it
really cuts things down. One goal of my book was to explain how all the
PostgreSQL features fit together, and having it online allows people to
get that information right away.

That means we are pretty much left with _good_ questions that don't have
easy answers. Mutlibyte support, Java, can't compile, stuff like that.
The easy stuff is pretty much gone because people are really using the
resources we provide.

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

It is a problem. A human being just can't answer ten trillion emails a day
and do anything else productive.

I use PHP a lot (with PostgreSQL ) and when I first got into PHP I was on
the PHP mailing lists, they were high volume even a few years ago so I
couldn't imagine what they are now..

The good thing about PostgreSQL is that you don't have many people
installing it to just play around. I foung that probably %80 of the
questions around the PHP mailing lists were from people that didn't have a
clue how to get started and wanted to install and code PHP because they
heard it was neat. I have nothing against that at all, it's a good thing
(sort of ), I just don't think we see that kind of attitude with PostgreSQL

We will get those folks someday. Right now, they are down in
MySQL-land, but they could come soon. In the old days, Linux served
that purpose, and BSD sat more in the experienced camp.

The big question is whether we can provide resources for those new
people so they can get started without flooding the mail lists. I
actually find that IRC is a good avenue for them because they can
interact with people and get clarifications that are hard to do in
email. And, our IRC channel is getting huge. We have 23 people on the
IRC channel right now, and one sarcasm-bot who I am growing fond of. :-)

(or any RDBMS really).. Generally I see people using MySQL first (why, I
don't know) and then moving to PostgreSQL for it's much more rich feature
set. In the process there people tend to learn the little stuff with MySQL
like basic SQL syntax and at least a general understanding of what's going
on. In addition to all that the people that are using PostgreSQL (or and
RDBMS) are generally using it for a specific purpose (writing an application
to use it, for instance) and programmers tend to be able to figure a lot out
for themselves.. I think that all the above contributes to the fact that
where there is a substantial amount, it's not to 100+ a day yet :-)

Anyway.. I've been on the list for almost two years now and I have seen an
increase in traffic for sure but I haven't seen a decrease in support.
Support always has and continues to be excellent. I don't think it will
become much of a problem in the near future but only time will tell!

I think there is no decrease because people are getting answers
off-list, and saving the good ones for us. In fact, I think the most
frequently asked questions are about bugs in previous releases that we
already have fixed in current, and we need to give them workarounds.

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

Tom Lane writes:

Aside from persuading more people to spend time answering email
questions, I agree we need to work harder on making answers findable
outside the mailing lists. Improving the docs, making the mail archives
more easily searchable, etc etc. I dunno if an "annotated manual" would
help --- I've never used one --- but if people want to try one, it can't
hurt. The main problem is to get the work done. We need volunteers to
actually do some of these things, not just suggest them ...

One thing we should try to do in the future (i.e., the next big attack I
have on you) is to maintain a human-edited concept index for the docs,
like every good non-fiction book has at the end. The technical details
for this are mostly worked out, it just needs someone to compose a list of
all "concepts" and find all the places where they're discussed.

This might even be something to stick in for the 7.1.1 release, because
otherwise there will be another 8 month lag before it becomes useful.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Bruce Momjian <pgman@candle.pha.pa.us> writes:

It is a problem. A human being just can't answer ten trillion emails a day
and do anything else productive.

[...]

The good thing about PostgreSQL is that you don't have many people
installing it to just play around. I foung that probably %80 of the
questions around the PHP mailing lists were from people that didn't have a
clue how to get started and wanted to install and code PHP because they
heard it was neat. I have nothing against that at all, it's a good thing
(sort of ), I just don't think we see that kind of attitude with PostgreSQL

We will get those folks someday. Right now, they are down in
MySQL-land, but they could come soon. In the old days, Linux served
that purpose, and BSD sat more in the experienced camp.

Speaking of which...

At NetBSD, we're dealing with these problems through (ISTM) good
organization of volunteer efforts. We have a www mailing list with
volunteers rotating on a weekly basis to answer questions. We have a
netbsd-help list with a certain number of knowledgeable individuals
responding to the (sometimes very basic) questions that get posted
there. Ideally, there would be some kind of a volunteer rotation on
the netbsd-help list, too; right now, one person handles probably 50%
of the questions there. There are also people on most of the other
lists who are willing to redirect requests to netbsd-help when the
question isn't appropriate to the list at hand.

Ideally, there would be some kind of a rotation for answering
questions on the netbsd-help list, but that may not be very practical
-- questions posted there require different areas of expertise on the
part of the answerers.

Granted, NetBSD doesn't have the same volume of questions as a FreeBSD
or a Linux distro, but we don't have the same volume of experienced
volunteers, either.

I guess what I'm trying to say is that, as your demand for support
grows, and as your volunteers increase in number, you may not be able
to get them to match up with each other *unless* you throw some
organization at the problem.

Chris

--
chris@mt.sri.com -----------------------------------------------------
Chris Jones SRI International, Inc.
www.sri.com

Peter Eisentraut <peter_e@gmx.net> writes:

One thing we should try to do in the future (i.e., the next big attack I
have on you) is to maintain a human-edited concept index for the docs,
like every good non-fiction book has at the end. The technical details
for this are mostly worked out, it just needs someone to compose a list of
all "concepts" and find all the places where they're discussed.

The large documents I've done in the past (product manuals and such)
used automatic index generation in LaTeX. You add a tag to text that
needs an index entry:

To fix this problem, frobnitz the foobar<index>foobar</index>.

and then the index will have an entry for "foobar" that references this
page, along with any other pages where <index>foobar</index> appears.
<index>foobar</index> doesn't affect the visible text on the page
however.

Assuming that our SGML tools can do something similar, this would seem
like the way to go. Getting the docs marked up initially would be a
painful task, but once it's done it'd be relatively easy for doco
contributors to include appropriate index entries in new text.

I think an index that's maintained separately from the text proper would
be doomed to failure ...

regards, tom lane

(This was: "Re: [GENERAL] Re: PostgreSQL vs Oracle vs
DB2 vs MySQL - Which should I use?", but I think a new
thread has spun off...)

[Tom Lane said ...]

I agree we need to work harder on making answers

findable

outside the mailing lists. Improving the docs,

making the mail archives

more easily searchable, etc etc. I dunno if an

"annotated manual" would

help --- I've never used one --- but if people want

to try one, it can't hurt.

[... to which Bruce Momjian added ...]

I will say that the FAQ and my book have visibly

reduced the number of

questions. When I put something on the FAQ, the

questions about that

topic just magically go away.

I have not used an annotated document either.
However, I could see such a beast being used in the
development of the documentation in a way that is not
dissimilar to the development of the product itself.
That is, I see an annotatable set of documentation as
similar in nature to the developmental version of the
product. After some period of development, some lucky
editor(s) would fold the annotations into the document
proper, periodically releasing the "stable" version of
the docs.

Would this lighten the work load on the folk that are
currently maintaining the docs? Would it lighten the
work load for the core developers? Would it stimulate
the development of richer documetation? Would it draw
some of the load off the mailing lists?

A pilot project may be enlightening. Anyone have
experience with setting up/maintaining annotatable
on-line documentation?

Cheers,
Richard Blackwell
Programmer/Analyst
Simon Fraser University
Burnaby, BC Canada

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/