Forum OpenACS Q&A: Postgres vs. Oracle for OpenACS
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?
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.
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.
- 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
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
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. :)
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.
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.
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.
"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!)