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

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.

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.

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.

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.

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...

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?

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 ...