Comparing PostgreSQL and Oracle stability

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

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(at)postgresql(dot)org

--
Juan Jose Comellas
(juanjo(at)comellas(dot)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

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Thomas Hyldgaard
> Sent: Tuesday, July 02, 2002 8:00 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Comparing PostgreSQL and Oracle stability
>
>
> 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(at)postgresql(dot)org
>
>

> 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(at)postgresql(dot)org

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

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
On Wednesday 03 July 2002 06:46 pm, Juan Jose Comellas wrote:
> 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(at)postgresql(dot)org

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:
> 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 rememb=
>
> er
>
>>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(at)postgresql(dot)org
>
>
> --=20
> Pruner Jan
> jan(at)pruner(dot)cz
> http://jan.pruner.cz/
> -----------------------------
> Only Robinson Crusoe had all his work done by Friday
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>