PostgreSQL vs. Oracle: Users speak out
By Mark Brunelli, News Editor
06 Apr 2006 | SearchOracle.com
A recent interview has DBAs talking about the merits of the open source PostgreSQL database management system (DBMS) as compared to Oracle – and their opinions truly run the gamut.
In that interview, Robert Treat and Jason Gilmore, co-authors of "Beginning PHP and PostgreSQL 8: From Novice to Professional," said that PostgreSQL 8.0 is much more than just a back end for Web sites. In many situations, the authors say, PostgreSQL can be used instead of or as a complement to Oracle and other DBMSs.
DBAs responding to the interview said they liked the low cost of the open source database, while others said that Oracle's rich feature set is second to none.
More about Oracle and DBMS
Listen to MongoDB's CEO discuss NoSQL database technology in this video
Learn about Oracle's efforts to get noticed in the NoSQL technology world
Read about the importance of the requirements stage in relational database design
Jim Allen, a longtime Oracle professional and an independent technology consultant, says he has had considerable experience with PostgreSQL 7.4, but not the newest version, 8.1.
Allen believes that PostgreSQL is much more suitable for the casual database developer, such as Java developers who need a back end for [Java Database Connectivity] access.
"PostgreSQL has a solid set of features now that includes most if not all of what these developers would ever use," Allen said. "Oracle has a feature set several orders of magnitude more rich, but few if any of these features would ever be used by this group."
Another thing Allen likes about PostgreSQL is the fact that the stored procedure compilation is transactional.
"You can recompile a stored procedure on a live system, and only transactions starting after that compilation will see the changes," he said. "Transactions in process can complete with the old version. Oracle just blocks on the busy procedure."
Matt S., a DBA, said that he has successfully used PostgreSQL 8 in conjunction with Microsoft SQL Server 2000 and 2005, as well as with Oracle 10g.
"I was impressed thoroughly with the ease of implementation, as well as compatibility and installation of PostgreSQL, considering its open source nature," he said. "Using it in enterprise applications [and] Web site situations was relatively painless [and] a simplified security structure made it very appealing."
Another user, who did not want to be identified, said that it makes more sense to compare PostgreSQL to OracleXE, a slimmed down and free version of the Oracle DBMS. The user said that like PostgreSQL, OracleXE is easy to install and use. He added that OracleXE includes Apex/HTMLDB to help developers quickly build and deploy Web applications.
"I've used PostgreSQL in the past [and] it is fine," the user wrote. "However, Oracle is stepping up and making a pretty good product and making it easier for organizations to upgrade their database systems down the road from the free version to their other versions of their product."
Richard Goulet, a senior Oracle DBA with a New England-area power components manufacturer, said that he uses Oracle and PostgreSQL side-by-side for numerous tasks.
Goulet agrees that PostgreSQL is easy to use, and he says it complies with the SQL standard nicely. He adds that there is plenty of support readily available for PostgreSQL through numerous mailing lists. But that's where Goulet's fondness for the open source software ends.
PostgreSQL doesn't behave as nicely as Oracle when the system fills up, Goulet said. In those instances, the system tends to crash quickly.
Goulet said that setting up a TCP/IP connection capability with PostgreSQL is hardly an intuitive process. To do it, he says, one needs to modify the postgres.conf and pg_hba.conf files manually.
"The last big thing between PostgreSQL and Oracle that's really missing is a gateway product from Oracle. These two don't talk to each other except by externally built and most times [highly customized] connectors," Goulet said. "An Oracle gateway to PostgreSQL would mean a lot to those who use both products happily."
Josh Berkus, who works with the PostgreSQL Project Core Team, said there are in fact third party tools available on the Web which help integrate Oracle and PostgreSQL data. They include Ora2pg and DBI-Link, he said.