Forum OpenACS Q&A: If money grew on trees, which would you choose Oracle or PG?

Hey guys,

Out of curiosity and with an eye on developing a better sales pitch,
I was wondering if the community could answer some questions:

a) If money and hardware were no object, which would you prefer,
Oracle or Postgres?

b) Which feature(s) are most attractive in Oracle? Which in PG?

c) Which feature(s) are least attractive in Oracle? PG?

c) What are the conditions in which you would prefer Oracle? PG?

d) If systems administration were no issue, which yould you prefer
Oracle or PG?

e) Is there a third DB you would prefer to either Oracle or PG?

Those are the questions that I currently have. I invite anyone to add
more questions in this vein if they can think of them.

Thanks for your answers in advance.


a) Depends on the application. But money doesn't grow on trees.

b) Oracle: Replication, backup, support from application vendors, tools, tablespaces, looks great on resume.
PG: Ease of installation, ease of administration, porwerful feature set with small footprint, speed, psql; smart, committed team of core developers (in bright contrast to the MySQL team).

c) Oracle: Installation, administration, SQL*Plus sucks, huge footprint. PG: The oid problem with functions, VACUUM is pretty annoying (although this will be a lot better in 7.2), tablespaces; replication is here but not feature-complete yet.

d) Oracle: Very demanding system, for a very large installation. PostgreSQL: everything from very small to medium-large applications.

e) It is an issue, a very big one. You can't pretend it doesn't exist.

f) I haven't looked at Interbase or SAPdb, but they look like good options.

If money grew on trees, I'd plant trees, rather than worry about which database to use :)
Some thoughts on living with the two as a developer: I've used Oracle more than Postgres at this point, and I've always found tracking down syntax errors in functions or queries to be a lot easier with Oracle. Postgres error messages are just not specific enough. And while pl/pgsql is good enough to be worth using, PL/SQL is clearly more refined at this stage in the game. Also, there's lots of decent documentation for Oracle. This becomes less of an issue if you work with Postgres a lot, I suppose, but Oracle has proven more user (er, programmer) friendly for me. Postgres seems to be improving so quickly though, who knows...

Roberto's right about sql plus: it's awful.

Postgresql is more standards compliant in some areas, which is a plus.  Especially date handling.  Oracle date handling is a royal pain in the butt.
I agree with Roberto.  I'd add that Oracle's data dictionary is very cool for when you do need to tune, but by then you've probably outgrown postgres anyway.  Oracle's SQL is slightly more featureful than Postgres's, too, but postgres has all the basics down.  Once in a while I think, "this query would be nicer if i could use xxy feature of Oracle," but not often.

I guess if "money and hardware being no object" meant being able to afford hiring a DBA to keep Oracle happy without blinking at the expense, I would go with Oracle.  But in the real world I am happy with postgres and am likely to stay with it for the forseeable future.

a) I will join up with Don and build a farm.  That way we can mass produce money growing trees.


For me use Postgres unless you will use something that only Oracle offers.  This features are are more or less covered by the earlier posts.

I think right now PG is at the point that it is really a DB, not an alternative or something.  Just wished more and more opensource project will use PG, since some good projects runs on mysql.  I dont want to port them to PG.  Also I dont want to start a flame war, MySQL is ok but I need a real RDBS like PG.

If money and resources are no object then (IMHO) the client is almost certainly one who will feel most comfortable with Oracle.  So if it's a sales pitch you're after, it's a no brainer.  They may not *need* Oracle, but they'll feel better about that choice.

The only exception is if it happens to be a company that values OpenSource-ness;  then the technical questions of which is actually more appropriate come into play.  I think others have covered this well enough;  the only thing I would add is that AFAIK there are more and better add-on tools for Oracle to make programmer's lives easier, which in the long run is better for the client too.

a) if money and hardware were truly no object, I would prefer Postgres. With large amounts of 'no-object-money' I could hire enough talented people to add whatever I wanted to Postgres. This would be *far* cheaper than a mirrored action on Oracle.

b) In Oracle: partitions, tablespaces, CONNECT BY, more visible tuning parameters.
In PG: rules, backups/exports have less options than Oracle but are less likely to fuck me over from small version differences. So I'd say PG is ahead in backups.

c) Oracle: administration, stupid tuning defaults, partition stupidity.
PG: no partitions or tablespaces. CONNECT BY would be nice, or MERGE (iirc) -- the new keyword IBM will be bringing to the table to combine the 'update or insert' two-statement approach we're all familiar with into one DML.

d) dance, admins, dance!!

e) As soon as I destroy the unbelievers in comp.databases.theory I'll pick through the remainder and see what's left.

All I really want right now in Postgres is partitions.

I want tablespaces - symlinks don't hack it as a table management tool.    Partioning would be nice, too, of course!  I think we'll get tablespaces in PG 7.3...
One feature I would really like to see PG use from Oracle is Index Only Tables. They tend to speed up searches on existance for certain keys by a large factor. In the current PG structure an index node may not be valid and you must check the corresponding table to see if an index node does exist. In a heavily volatile system many index may indeed be no longer valid. Remember that vacuum does not shrink indexes either.

There was a patch that kinda started adding it but it doesnt seem to have been picked up and developed more. For more info check out pgsql is 75 times faster with my new index scan thread on hackers list.
12: PG Index size (response to 1)
Posted by David Walker on
Remember that vacuum does not shrink indexes either.

Does this mean that I should periodically drop and re-add large indexes to help manage their size?
It definitly can help performance/storage if you periodically add the drop/create index approach to your db maintenance program. The Create Index will grab a lock on the table during operation allowing only reads during duration.  So keep that in mind for when you may schedule this since large tables make take awhile.
PG doesn't shrink the index files nor does vacuum reclaim them, but space in index files is scavenged, I believe.

To do what Carl's suggestion would require a new index type, I think.  I don't think the PG folks would give up their existing index structure.  While the fact that there's no MVCC information kept with individual index entries does indeed mean that visibility/validity information must be retrieved from the table, it also means that the indexes are smaller.  Since PG stores integers as real binary integers (32 bits) the size overhead required to support MVCC information in the index table could be quite high.

That's been the major argument against making indexes transaction-aware.

Carl's actually talking about something different I just realized - index-organized tables, rather than making the general btree indexes transaction-aware.  Sort of a best-of-both-worlds in cases where it would work (a worst-of-both-worlds if you don't know what you're doing!).

One of the reasons the PG developer group is less-than-enthusiastic about some of these interesting ideas/proposals is that they're still working hard at improving some important nuts-and-bolts issues.  For instance, "lazy vacuum" in PG 7.2 (I think it's there?) that will do space reclaiming in the background, which will help sites that do a lot of delete/update/inserting of data avoid the need for frequent vaccums.  In other words triage ...

Oracle does have its uses....

It keeps probably half the tech team at your average Mobile Operator in work....No I'm not kidding....

It is also a tangible entity that large companies can sue if something goes drastically wrong...

I guess what I'm saying is that things like choice of database, particualryl in large companies, are rarely goverend by technical suitability and moreoften for purely commercial/political reasons.

It all goes to show however what a smart move supporting both databases is...Oracle may make me wince, but it may also just swing the big contract, based on OpenACS.