Forum OpenACS Q&A: Postgres vs. Oracle for OpenACS

Collapse
Posted by Jade Rubick on
Hi all,

A couple of years ago, back in the ACS 3.4 and OACS 3.2 days, I decided to use Oracle vs. Postgres. Postgres just didn't seem mature enough for a mission-critical Intranet.

We're considering porting our ACS 3.4 system to OpenACS 4.6, but are unsure whether to port it to Postgres at the same time, or stay with Oracle.

This is a mission-critical Intranet. The company absolutely depends on it running, not 24/7, but at least during business hours.

We could lose about a days data, and that would be a huge inconvience, but not the end of the world. However, if we lost all the information in the database, I'd have to start looking for other work 😟

However, running on Postgres does seem attractive, given that it will save thousands of dollars, and it seems simpler to administrate.

I know there are a lot of heavy-duty production sites out there using Postgres. Would you trust your data to Postgres, or stay with Oracle?

Collapse
Posted by Don Baccus on
birdnotes.net has been running on Postgres for, oh, three years now?  If I lost everyone's data they'd kill me.  Maybe even literally.  On the other hand I only promise that I can recover to last night's backup.

This site (openacs.org) has been running on Postgres since its inception.

To my mind the question isn't whether or not Postgres is reliable enough to trust with your data.

Rather the issue's scalability.  Postgres has a per-row space penalty that means for huge systems with tons of data, this size penalty can be significant.  4GB RAM costs less than an Oracle license for a decent sized server, though, so the space penalty should really only be a consideration for truly HUGE amounts of data.

The Postgres optimizer isn't as good in some cases, and that's affected scalability, too.  However some of the work being done by me (permissions) and Jeff Davis (proper index on child tables that use foreign keys) are going to significantly boost performance for both the PG and Oracle versions.  Both will be noticably more scalable than the current 4.6 release.

Also PG's handling of in-database pseudo-LOBs for storing of binary files is less efficient than Oracle BLOBS.  You can get around this if necessary by having the content repository store such data in the host filesystem rather in the database.  And Postgres wins for large text because with Oracle you need to use CLOBs for any varchar longer than 4KB.

Frankly though I can't imagine an intranet raising scalability issues unless you're talking about a major corporation.

Collapse
Posted by Roberto Mello on
I built http://bookexchange.usu.edu/ and it still runs on OpenACS 3.2.5 (with some mods) and has ran for the past 2.5 years.

At the beginning and ending of semesters the 20,000 USU students pound on that site like there's no tomorrow, trying to sell their books or buy books for less from other students. We get around 8,000 unique visitors per day on those periods of the year.

Almost all of the pages hit the database, and for the 2.5 years the site has run, it has run almost unnatended and without almost no maintenance. We keep daily backups, but thanfully never needed them. The machine was a simple K6-2 300 machine with 256 MiB of RAM.

-Roberto

Collapse
Posted by Janine Ohmer on
This may not be a popular point of view, but my recommendation to clients is still to go with Oracle if they can afford it, and Postgres if they can't or if Open Source is important to them. I have several reasons for this:
  • There are more 3rd party tools available for Oracle than for Postgres (though PG is slowly catching up)
  • There is more reference material out there for Oracle to help when things go wrong
  • I've had decent luck getting help from Metalink and Ask Tom, but not so much getting help from the PG mailing lists
  • Although Oracle's tech support is rumored to be pretty bad, at least it exists
Ok, so my reasons are centered around things going wrong, and perhaps that is more likely to happen with Oracle than Postgres. But as someone who is responsible for a lot of other people's data, having resources to turn to is important to me.
Collapse
Posted by Roberto Mello on
Janine,

Where have you been looking for help for PG? I have found the PostgreSQL mailing lists extremely helpful. You often get responses straight from Tom Lane, Bruce Momjian, Peter Eisentreut, etc. and these are the people who _write_ PostgreSQL. Usually I get a response to a question in less than 2 hours.

Tom Lane's responses in particular, are usually very detailed and very accurate. A recent example of that was the experience of Jeff Davis and the GEQO optimization question. http://xarg.net/blog/one-entry?entry%5fid=19950

-Roberto

Collapse
Posted by Janine Ohmer on
It has been a while since I last tried, but at one time I posted several questions to the PG mailing lists (don't recall which one) and didn't get any responses at all, or at least nothing helpful.  It's been long enough that I don't recall exactly what happened.

I'm willing to believe the situation is better now, but I've got my resources lined up for Oracle so that's my comfort zone.  Your mileage obviously varies. :)

Collapse
Posted by Bruno Mattarollo on
Hello Jade,

Well, the answer to that question really depends on the functionality that you really need for that intranet. For example, we at Greenpeace, decided to go Oracle because of interMedia, despites the nightmare that it might be, it still provided us (two years ago, when the decision was made) with full text search within the DB in multiple languages and some other features that we don't really use... Some others (features) we do use, as mentionned, interMedia, online-backups, integrates nicely with the backup services at our provider, knowlege in-house and at the provider, etc ...

Our mid-term to long-term was to slowly migrate to PG when some of the most critical functionality would start to be available. Still not the moment but if you are migrating, asking some of these questions might help :)

Just my 2 cents.

Collapse
Posted by Talli Somekh on
We have yet to work on a project where Oracle was *critical* for some feature. PostgreSQL is rock solid for most applications, particularly intranet applications, and the price can't be beat. In addition, when considering this issue over a year ago, I asked this question in a thread, " If money grew on trees, which would you choose Oracle or PG?". The discussion may be a bit dated since the PG community adds features at an amazing rate and with such quality, but even then the community held PG in high esteem.

I've also had this discussion with Janine who made very good points in favor of Oracle, that she's relayed above. There's something to be said for using an industry standard application (unless, of course, it's a buggy, security-challenged POS whose vulnerabilities threaten the world's network infrastructure.) If your boss is afraid of open source software and he is willing to spend money to allay any potential fear, Oracle can't be beat.

There are an increasing number of companies that provide support for PG, including Red Hat. So this issue is slightly less extreme. But those companies, like most open source companies, are usually small shops because they tend or can't charge ridiculous "enterprise" rates. And the numbers of massive and truly mission critical PG installations are growing daily, from Afilias system that manages the .org and .info registries to some very mission critical apps that Musea has built for nonprofit service agencies.

So from my experience, going with PG is a safe choice but not without needing to do some "selling" to management. Oracle is a great and "can't lose" DB from a technical perspective, but it's tough and expensive to have and maintain. PG is a relative breeze in both of these regards, but it doesn't have the industry support or give the trade-mag-reading CTO the warm and fuzzies.

Either way, since you're in Portland, if anything goes wrong you can call up Super-Don to rescue your data. All you have to do is have a couple of pitchers of good beer and he'll manually recover the bits in the db.

talli

Collapse
Posted by Jeff Davis on
I would probably go with postgres if your database is not too large (and to me too large is probably something like 5gb), didn't need replication or other high availability features of oracle, and could get along without the permissioning, table partitioning, and other fancy oraclisms (all of which are not really used by acs/openacs anyway, unless you have added them).

That said, I know people at redhat are deploying postgres as HA solutions (though not with replication as far as I know) and postgres seems to be gaining ground quickly.

I have been really beating on postgres to see what breaks and it has held up really well. I have been writing up the stuff I did specifically on forums tuning if you are interested.

Collapse
Posted by Mark Aufflick on
Just to second the praise for the postgres mailing lists - my firstexperiences with it (in the past few weeks) have been amazing - quick replies from Tom Lane that had me in the right direction.

"The right direction" being the key - opensource guys love to help out - but they are not paid to hold your hand.

Some clients need their hand holding - so either you plan to do that yourself, or tell them to buy Oracle and hire a dba ;)

On Oracle vs. Postgres - I just like developing in Oracle, for some reason it's api(s) "feel" nicer. Having said that, I use postgres for all my projects now, because for the very small amount of less niceness - it's a heck of a lot cheaper! It's less tuneable, which is both good and bad.

(It's also worth noting that I use Apple Mac's, so filter my tastes accordingly!)