Thread: 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@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 > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Thomas Hyldgaard > Sent: Tuesday, July 02, 2002 8:00 AM > To: pgsql-general@postgresql.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@postgresql.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@postgresql.org -- Pruner Jan jan@pruner.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@postgresql.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@postgresql.org > > > --=20 > Pruner Jan > jan@pruner.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 > >