As a DBA who has managed high transaction volume databases in Oracle and PostgreSQL, I have a bit of credibility to speak on this topic, but I hope that you will not find my answers too ambiguous. The largest problem with evaluating any set of technologies that claim to provide a particular functionality is that rarely are there apples to apples metrics to compare. One could focus on TPC benchmarks alone, but I know of no business that views transaction processing speed as the only value upon which to base their decision on where to store the lifeblood of an organization - Their data. You will find many religious wars between various technologies on the web, and few provide answers to your fundamental question: "Which technology should I use?" There are undoubtedly much smarter people than me who have devised a more comprehensive methodology for evaluating technologies which do not have easily mappable features to draw comparisons from. However, in my experience, wisdom comes at a premium and rarely do the powers within an IT organization allow their experts the time to evaluate business critical software using such a methodology. So what I hope I can do is provide an experience-based evaluation of the two RDBMS systems so that you will at least have a spring-board to do further analysis on this critical business decision.
Oracle vs. PostgreSQL - definitely not apples to apples...
I will leave the technology portion of this comparison to the end, because I don't want the primary focus to be on that comparison. The primary focus when comparing these two systems should be how your life as a DBA or IT manager will be served by the decision you make. The questions that matter to you will drive your decision. If you are asking questions like: What kind of support is available if we have a problem? What is their patching schedule to address exploits and known security defects? If there is a bug, how responsive is the organization to resolving the issue? These are questions that imply a need to have an organization behind your technology. In this case, clearly PostgreSQL is not an appropriate choice, because you won't get acceptable answers to these questions for any open source software. However, if your questions are: How can we get solid database technology for free? How can we leverage our expert team of developers who prefer working in the LAMP environment to manage more of our database operations? This would lean toward PostgreSQL. So in summary, what your organization values is going to drive the decision - not the technology itself.
That said, here are a few words on the technologies. PostgreSQL is essentially reverse-engineered Oracle at it's core. It has a default transaction isolation level of read-committed just like Oracle, and does provide for statement-level read consistency. It is capable of handling very high transaction volumes, like Oracle, and in my experience that is on the order of thousands of statements per second. As far as innovative features that you can't find in the other major RDBMS systems, you won't find it in PostgreSQL...but of course that's not why you are even considering it. You want a solid database for free. PostgreSQL does fit that bill. It's usability is just fine. It has a nice command line interface that is in my opinion far superior to SQL*Plus (I know, I'm not setting the bar very high) and if I remember correctly it includes things like up-arrow command history like bash. (Although this functionality can be achieved in SQL*Plus using a useful program called rlwrap introduced to me by James Nnaji.). Now on the Oracle side, clearly it's a Cadillac of features. The PostgreSQL core team of developers are clearly world-class programmers. I have to disclaim that before I say what I am about to say, as I have tremendous respect for the engineering prowess of that team...but Oracle as the second largest software company in the world obviously has resources available to it to not only build and maintain an astounding array of features built into their database, but also to keep their finger on the pulse of where IT is headed and to build the features that the market really needs. So if you are so fortunate to bask in the glory of such a feature-rich database it is easy to become jaded with a superiority complex shooing away those pesky "little" open source databases. Not a wise choice considering there is a lot of opportunity for saving your IT department money on all those Oracle licenses you are paying for on your small, ancillary databases.
In conclusion, as Stephen C. Meyer says "Beware of the sound of one hand clapping". There will always be noisy advocates of each technology. It's not a technology war. It's just trying to find the right tool to fit your particular organization and personnel.
