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