Comparing PostgreSQL and Oracle stability

I am not terribly experienced on this matter, but we have implemented several
systems using Oracle 8.0.5, DB2 UDB 7.2 and using PostgreSQL 7.1.3. All of
them are running under Linux, and until this moment I have found that under
Linux, PostgreSQL is more stable than both Oracle 8.0.5 and DB2 7.2, is far
easier to administrate and the performance is very good. The most important
drawbacks we've encountered are:

1) The need to run VACUUM regularly (this is not much of an issue with
PostgreSQL 7.2.1 because it doesn't lock the tables), especially if you are
doing a lot of UPDATEs.

2) Postgres' query optimizer is not as smart as Oracle's or DB2's, so we
needed to write the queries in ways that would allow Postgres to optimize
them so that we could reach a performance similar to Oracle's. We found out
that Postgres does not handle SELECT ... IN ... queries very well, so we had
to rewrite them in ways that were not as straightforward.

On Tuesday 02 July 2002 09:59, Thomas Hyldgaard wrote:

Hi,

I'm considering porting code from Oracle to PostgreSql. I'm looking
for opinions from people that are experienced with both PostgreSQL and
Oracle:

1) Is the stability better, equal or worse when comparing Oracle 9i?
2) Are there any performance issues to be aware of when comparing
PostgreSql an d Oracle (on the same hardware configuration) - eg. size
of table colums, count of records in tables (and indexes).
3) And how about the work for DB administrators - are things simpler
or more complicated?

Best regards
Thomas

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Juan Jose Comellas
(juanjo@comellas.org)

Thomas-

My opinions are based on Oracle 8, which was the last version I used,
performance is a subjective guess-

-Performance is about the same.
-The only thing I've missed is point-in-time recovery.
-Stability is about the same.
-DBA work is about the same if you don't depend on a GUI.
-Support for postgreSQL is much faster. (A bit cheaper as well <grin>)

I haven't missed Oracle.

-Nick

3) And how about the work for DB administrators - are things simpler
or more complicated?

Simpler. I tried a trial version of Oracle a long time ago and couldn't figure out how to
get it installed. With Postgres I just read the docs and was up in minutes.

I'm considering porting code from Oracle to PostgreSql. I'm looking
for opinions from people that are experienced with both PostgreSQL and
Oracle:

1) Is the stability better, equal or worse when comparing Oracle 9i?
2) Are there any performance issues to be aware of when comparing
PostgreSql an d Oracle (on the same hardware configuration) - eg. size
of table colums, count of records in tables (and indexes).
3) And how about the work for DB administrators - are things simpler
or more complicated?

First off, I don't have much experience with Oracle, but I have done a couple
things with it, and one of my colleagues has used it much more extensively.

1) stability - I have not had stability problems with a recent release of
postgresql. The developers sometimes mention potential problems, but I
haven't encountered one (at least after 7.1, before that I seem to remember a
couple minor issues). I haven't heard of any problems with oracle.

2) I can't provide you with any meaningful info about performance. I didn't do
any scientific performance comparison. One thing I can say, is that my
colleague needed to spend a lot of time giving hints to oracle because
(according to him) the planner was really bad and frequently made planning
errors.

3) Much easier to administrate postgres. I gave up trying to get oracle
working myself.

Regards,
Jeff

Support PostgreSQL in some way hierarchical query like Oracle does?
Select ... start with ... connect by ... ???

JP

On Thursday 04 July 2002 11:18, you wrote:

I'm considering porting code from Oracle to PostgreSql. I'm looking
for opinions from people that are experienced with both PostgreSQL and
Oracle:

1) Is the stability better, equal or worse when comparing Oracle 9i?
2) Are there any performance issues to be aware of when comparing
PostgreSql an d Oracle (on the same hardware configuration) - eg. size
of table colums, count of records in tables (and indexes).
3) And how about the work for DB administrators - are things simpler
or more complicated?

First off, I don't have much experience with Oracle, but I have done a
couple things with it, and one of my colleagues has used it much more
extensively.

1) stability - I have not had stability problems with a recent release of
postgresql. The developers sometimes mention potential problems, but I
haven't encountered one (at least after 7.1, before that I seem to remember
a couple minor issues). I haven't heard of any problems with oracle.

2) I can't provide you with any meaningful info about performance. I didn't
do any scientific performance comparison. One thing I can say, is that my
colleague needed to spend a lot of time giving hints to oracle because
(according to him) the planner was really bad and frequently made planning
errors.

3) Much easier to administrate postgres. I gave up trying to get oracle
working myself.

Regards,
Jeff

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Pruner Jan
jan@pruner.cz
http://jan.pruner.cz/
-----------------------------
Only Robinson Crusoe had all his work done by Friday

Support PostgreSQL in some way hierarchical query like Oracle does?
Select ... start with ... connect by ... ???

JP

On Thursday 04 July 2002 11:18, you wrote:

I'm considering porting code from Oracle to PostgreSql. I'm looking
for opinions from people that are experienced with both PostgreSQL and
Oracle:

1) Is the stability better, equal or worse when comparing Oracle 9i?
2) Are there any performance issues to be aware of when comparing
PostgreSql an d Oracle (on the same hardware configuration) - eg. size
of table colums, count of records in tables (and indexes).
3) And how about the work for DB administrators - are things simpler
or more complicated?

First off, I don't have much experience with Oracle, but I have done a
couple things with it, and one of my colleagues has used it much more
extensively.

1) stability - I have not had stability problems with a recent release of
postgresql. The developers sometimes mention potential problems, but I
haven't encountered one (at least after 7.1, before that I seem to remember
a couple minor issues). I haven't heard of any problems with oracle.

2) I can't provide you with any meaningful info about performance. I didn't
do any scientific performance comparison. One thing I can say, is that my
colleague needed to spend a lot of time giving hints to oracle because
(according to him) the planner was really bad and frequently made planning
errors.

3) Much easier to administrate postgres. I gave up trying to get oracle
working myself.

Regards,
Jeff

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Pruner Jan
jan@pruner.cz
http://jan.pruner.cz/
-----------------------------
Only Robinson Crusoe had all his work done by Friday

-------------------------------------------------------

Support PostgreSQL in some way hierarchical query like Oracle does?
Select ... start with ... connect by ... ???

Haven't found any support of that in postgresql.

This is really a functionallity I miss (or have missed ;).

I solve the problem by sorting posts in my application (JSP). Works pretty
well, however - I would like the db to do the work.

2) I can't provide you with any meaningful info about performance. I didn't
do any scientific performance comparison. One thing I can say, is that my
colleague needed to spend a lot of time giving hints to oracle because
(according to him) the planner was really bad and frequently made planning
errors.

I agree with your colleague. Writing you're queries "right" is
really important in Oracle. :)

--
/Nils H�glund, Naqua KB

E-mail: nils@naqua.se
Web: http://www.naqua.se/
Home Phone: +46 (0)18 30 09 19
Cellular Phone: +46 (0)709 91 97 65
Address: Nya Vals�trav. 26 B
SE-756 46
Uppsala, Sweden

I am porting code from Oracle to Postgresql, too.
I have missed :
- distributed database (select,delete,insert,update)
- tools for development screen (like forms in oracle) (for report I like php)

regards
haris peco

Once it was here, so I paste to you:)
Oracle:
with variables &TypNiv = 0 et &Niv = 0

select
sum(t01_caf) SCAF,
sum(t01_itm_cnt) SART
from T01_&DateData
start with T01_upr_lvl_typ = &TypNiv and T01_upr_lvl_nbr = &Niv
connect by prior T01_lvl_typ = T01_upr_lvl_typ and prior T01_lvl_nbr =
T01_upr_lvl_nbr

Postgres:
\set TypNiv 0
\set Niv 0

select
sum(t01_caf) as SCAF,
sum(t01_itm_cnt) as SCAF
from t01_20011231
where
strpos(t01_tree_sortkey,(select t01_tree_sortkey
from t01_20011231
where t01_upr_lvl_typ = :TypNiv
and t01_upr_lvl_nbr = :Niv))=1;

C.

Jan Pruner wrote: