Обсуждение: Why choose PostreSQL and not MySQL or Oracle!!

Yeah - what kinda comparing..

Oracle isn't free  -  I know that.. PostreSQL has for me always been
something that was just there when starting up a linux-server! I've
never heard anything good about it.

I've asked on the Experts-exchange.com why I should use Oracle - and
nothing seemes to pull me over there.. All their reasons given:

"1. For application developers:

   (1) Oracle supports stored programs such as stored procedurs,
triggers, packages,...
This feature enables you to centralize your application logic in the
database regardless whatever clients are.
You can also get better performance,managability, as well as integrity.
   (2) Oracle SQL and functions are powerful.
Oracle provides powerfule functions and subqueries in its SQL statements.
This feature saves much cost for developing complex queries.
   (3) Oracle supports distributed databases.
You can develop distributed systems by database links, materialized
views, and distributed queries.
If your application creates distributed transactions, Oracle handles
two-phase commit for you automatically.
This feature saves much cost for developing distributed systems and the
applications get lower complexcity.
   (4) Oracle supports partitioned tables/indexes.
This feature is important for developing Datawarehousing/DSS systems.
The disk I/O workload can be well balanced and thus get better
performance/recoveribility.
   (5) Oracle provides many build-in utilities for developers,
such as File I/O , TCP , SMTP , HTTP, Dynamic SQL, Job scheduling ...
For example, you can send e-mail in a stored procedure by calling the
UTL_SMTP package.
These build-in utilities can save much development cost.
   (6) Oracle supports Java in the databse.
Java is a popular language, and you can get many external
resources/libraries for your application.
In fact, the SMTP, HTTP, TCP build-in utilities are implemented by Java.
   (7) Oracle database is well integrated with its middle tier(iAS) and
developer products.


2. For DBAs:

   (1) In addition to full backup, Oracle supports many other backup
options.
These advanced backup options do not require database shutdown for
backup operations.
This feature is important for 24*7 systems.
   (2) For a well managed database,in most cases, a disk crash do not
require databse shutdown for restore/recovery.
Only damaged tablespaces/datafiles are not available for the users.
The other parts of the database can be accessed normally.
This feature provides much higher availibility and is important for many
critical systems.
   (3) Oracle supports many advanced recovery options.
For example, by performing an incomlete recovery, you can recover your
database state preior to the
running of a batch job which causes an accidental mass error deletion of
customer records.
   (4) Oracle supports many performance tuning utilities and statistics,
which is useful
to locate the system bottlenecks and then tuning."


F.x. StoredProcedures - is that supported by PostgreSQL?
"And actually mySQL has transaction safe tables using the innoDB or BDB
table type" - does the PostreSQL support that too!??


I've seen all the awards at the web-site - but why doesn't we hear
anything about PostgreSQL - it's always MySQL (in the world of free
alternatives)... PostgreSQL - is it a slow database (compared to MySQL)?

What pros and cons are there choosing PostgreSQL (compared to MySQL)?


Please tell me
Preben Holm

Preben Holm wrote:
>
> Yeah - what kinda comparing..

And what a whole lot of unordered information snippets about Oracle
later on. The way you threw that together tells me one thing only. For
"you", MySQL is the better choice because all you have to learn is how
to use all the different table types. It has no other features.

If you're really interested, type "mysql postgresql" into some search
engines and read for the next 2 years or so.


Jan

--

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

Thanks Jan - That's really nice to know :(

Well - the unordered information snippets about Oracle was "copy-pasted"
from some guys at the experts-exchange.com  -  I asked them why I should
  use Oracle instead of any other DB - and that was their answers -
sorry to tell you that those guys actually claim to be professionals.
I know that I've only worked with SQL for a year know - but I want to
choose the best free solution to begin with!!!

Actually this was the only thing I asked about:

 > F.x. StoredProcedures - is that supported by PostgreSQL?
 > "And actually mySQL has transaction safe tables using the innoDB or
 > BDB table type" - does the PostreSQL support that too!??


 > I've seen all the awards at the web-site - but why doesn't we hear
 > anything about PostgreSQL - it's always MySQL (in the world of free
 > alternatives)... PostgreSQL - is it a slow database (compared to
 > MySQL)?

 > What pros and cons are there choosing PostgreSQL (compared to MySQL)?


I know that I'm not the best writer whem it comes to english (I'm danish
and not that good at english).
All the facts about Oracle came from other guys (in that order (from
experts-exchange))!!

Thanks Hans

That was a really nice answer - I wont say I understood all the things
the PostgreSQL database was capable of doing - but I think I've found my
database :)

I don't actually know what a SQL preprocessor is?
But I did read something about the mySQL system. When using tranactional
  safe tables it saves the query in the buffer and then afterwards
perform the action after having written a log - in case something went
wrong?

"locking: row level locking instead of table locking"
Locking a row - what does it mean?
I've also read something about the Oracle system. In case of a Disk I/O
error the database would still be accessible. It would just be the rows
that was damaged, that would be inaccessible? Has that something to do
with row-locking?

I am a newbie - I know but everybody needs to learn something all the
time :(

Subselects? What's that actually!

Triggers! Has that something to do with Stored Procedures? Some Oracle
guy told me that - I don't know if that's true!!

Kerberos? That has something to do with mail - or can be used by
mail-clients? But what exactly can I use it for!

If I should write an application for the PostgreSQL DB - How should I do
that? So it would be easy for the end user to use!! Is there some
programming features inside PostgreSQL that can perform client-work or
should I write (fx.) a java-client that connects to the server and
performs some queries?


Thanks for all your replying Hans

Are you a german? Having names like Jürgen is often german names!!! Just
curious!! My german teacher's name was Jürgen!
Btw. I'm from Denmark...

What PostgreSQL can or cannot do...

> "1. For application developers:
>
>    (1) Oracle supports stored programs such as stored procedurs,
> triggers,

yes

> packages,...

don't know what this is

>    (2) Oracle SQL and functions are powerful.
> Oracle provides powerfule functions and subqueries in its SQL statements.
> This feature saves much cost for developing complex queries.

yes

>    (3) Oracle supports distributed databases.

no

>    (4) Oracle supports partitioned tables/indexes.

no; however you can always use a combination of RAID, logical volumes, or
move database files by hand (very tricky though)

>    (5) Oracle provides many build-in utilities for developers,
> such as File I/O

yes

> TCP , SMTP , HTTP

why should this belong in a database?

> Dynamic SQL

no

> Job scheduling ...

use cron/at instead

> For example, you can send e-mail in a stored procedure by calling the
> UTL_SMTP package.

can be done by executing shell commands from the database

>    (6) Oracle supports Java in the databse.

yes and no -- there once was a separate package providing Java support in
the backend, but I don't know what has become of it; besides at least the
following languages are supported inside the backend: plpgsql, perl,
python, tcl, C

>    (7) Oracle database is well integrated with its middle tier(iAS) and
> developer products.

does not apply

> 2. For DBAs:
>
>    (1) In addition to full backup, Oracle supports many other backup
> options.

no (or at least you will have to write your own backup scripts then)

> These advanced backup options do not require database shutdown for
> backup operations.

yes

>    (2) For a well managed database,in most cases, a disk crash do not
> require databse shutdown for restore/recovery.

no; use a sane RAID array instead

>    (3) Oracle supports many advanced recovery options.
> For example, by performing an incomlete recovery, you can recover your
> database state preior to the
> running of a batch job which causes an accidental mass error deletion of
> customer records.

no (unless you manage to group all operations inside a transaction)

>    (4) Oracle supports many performance tuning utilities and statistics,
> which is useful
> to locate the system bottlenecks and then tuning."

available tools are less intuitive, but very helpful

> F.x. StoredProcedures - is that supported by PostgreSQL?

yes

> "And actually mySQL has transaction safe tables using the innoDB or BDB
> table type" - does the PostreSQL support that too!??

yes

>
>
> I've seen all the awards at the web-site - but why doesn't we hear
> anything about PostgreSQL - it's always MySQL (in the world of free
> alternatives)... PostgreSQL - is it a slow database (compared to MySQL)?

it is very fast; it is said to be slower than MySQL for read-only
databases

Regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de>             /| \__
The past: Smart users in front of dumb terminals       /_|____\
                                                     _/\ |   __)
$ ./configure                                        \\ \|__/__|
checking whether build environment is sane... yes     \\/___/ |
checking for AIX... no (we already did this)            |

Thanks for replying

 >>   (3) Oracle supports many advanced recovery options.
 >>For example, by performing an incomlete recovery, you can recover your
 >>database state preior to the
 >>running of a batch job which causes an accidental mass error deletion of
 >>customer records.
 >
 >
 > no (unless you manage to group all operations inside a transaction)


That answer I would like to get specified a little bit. Maybe it's
because the definition of a transaction could need to be specified a
little bit more (cause I'm a real newbie)!!


Thanks
Preben Holm (Denmark)

On Thu, Aug 15, 2002 at 11:33:57AM +0200, Preben Holm wrote:
>
> F.x. StoredProcedures - is that supported by PostgreSQL?

Yes, but these mean different things (practically, it seems, for
every RDBMS).  The big thing with stred procedures in PostgreSQL is
that they cannot (currently) return record sets, exactly.  For more
detail, you can read the docs and the archives.

> "And actually mySQL has transaction safe tables using the innoDB or BDB
> table type" - does the PostreSQL support that too!??

PostgreSQL has always had transactions.  You need do nothing special.

> I've seen all the awards at the web-site - but why doesn't we hear
> anything about PostgreSQL - it's always MySQL (in the world of free
> alternatives)... PostgreSQL - is it a slow database (compared to MySQL)?

MySQL had an early head start.  It was small and fast (although just
brutally non-standard, and something of a toy).  Now it has
transactions (but not subselects), and so it's a more reasonable
choice, although the transaction support is bought at the cost of
speed.  For more details on this, just have a look at the archives.
There are regular MySQL/PostgreSQL flame-fests, but sometimes they
have some useful information in them.

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Hi Helge,

Helge Bahmann wrote:
>
> What PostgreSQL can or cannot do...
<snip>
> > Dynamic SQL
>
> no

In the more recent versions of PostgreSQL, there is a sql command called
EXECUTE which lets you dynamically create and run queries through
PL/pgSQL.

http://www.postgresql.org/idocs/index.php?plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN-QUERIES

Hope this helps.

:-)

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

I suggest that you try Postgresql out for a project, and see if you like
it .
This is more or less how I came to be using it.

Of course you can test drive Mysql similarly...then make up your own
mind.

Due to the strong feelings of the various communities, you will probably
become buried in opinions if you read too much about comparing Mysql and
Postgresql. Be warned that you will become buried in marketing drool if
you read about comparing Oracle to anything at all!

best wishes

Mark

On Thu, 2002-08-15 at 17:29, Andrew Sullivan wrote:
> On Thu, Aug 15, 2002 at 11:33:57AM +0200, Preben Holm wrote:
> >
> > F.x. StoredProcedures - is that supported by PostgreSQL?
>
> Yes, but these mean different things (practically, it seems, for
> every RDBMS).  The big thing with stred procedures in PostgreSQL is
> that they cannot (currently) return record sets, exactly.  For more
> detail, you can read the docs and the archives.

One important detail: this will be corrected in 7.3 with some non-zero
possibility. 7.3 will enter beta testing quite soon.

(It's on the -hackers mailing list, Jan Wieck in a 'Open 7.3 issues'
thread.)

cheers
-- vbi

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