Oracle and PostgreSQL Debate

Mark Brunelli writes DBAs are talking about the merits of the open source PostgreSQL database management system (DBMS) as compared to Oracle - and their opinions truly run the gamut. DBAs responding to the interview said they liked the low cost and ease of use of the open source database, while others said that Oracle's rich feature cannot be ignored. Still others talked about how well the two systems play together. According to one DBA, a gateway product from Oracle would be a welcome offering."

Comments

Re:Replication on PG is no good

[bloodnok]

I have used slony-I, Mammoth Replicator, Oracle Advanced Replication, an early version of Oracle Streams in 10g (don't know if it's still called that), and an Oracle third-party replication scheme that I can not currently remember the name of.

Of them all, I would choose slony almost every time. Yes, you have to have a data design with PKs. As a fan or the relational model I think that's generally a good thing. But for those cases where you don't have a PK, slony lets you add one. Painlessly.

I have found building a slony replicated cluster to be way easier than with any other system. I have used slony's switchover in a live environment to upgrade the database, the server and the hardware, with only a 6 minute outage. I administer a 24*7 web-based site and hardly ever have to touch the database or slony.

It's way better than you make out. And if your database design really requires you not to have PKs, then you don't understand relational modelling.

Slony-I does not support multi-master, or synchronous replication. It is not designed to do so. It would be great to have this capability for Postgres but its lack should not be cause to criticise slony-I.

Corrections to the article

[einhverfr]

I too have been using PostgreSQL since 6.5. I have experience with every version from 6.5 to the current 8.1....

The article made a number of mistakes or maybe the interviewees were not that knowledgable:

Jim Allen, a longtime Oracle professional and an independent technology consultant, says he has had considerable experience with PostgreSQL 7.4 and 7.5, but not the newest version, 8.0.

7.5 never existed. It was renamed to 8.0 shortly before entering beta. Goes to show how little he knows-- like those people who used to call and ask for tech support for "Windows 97" except a DBA should know better....

On the other hand, Allen was unimpressed by the fact that in PostgreSQL, stored procedure parameters are not typed.

"Everything is passed as strings, even integer arrays," he said.

Huh??? This is plainly incorrect and has been since I have been working on stored procs in it (at least 7.0, maybe 6.5 or earlier). All parameters are typed. They may, however, be presented as text depending on the function and how it is called.

PostgreSQL doesn't behave as nicely as Oracle when the system fills up, Goulet said. In those instances, the system tends to crash quickly.

I assume he is talking about oid/xid wraparound issues. Oid wraparounds fail pretty gracefully. In 8.1, you will get plenty of warnings before the xid wraparound forces a crash. However the crash is there as a safety measure to protect your data-- if the xid was allowed to wraparound, previously committed transactions would become invisible.

The solution to the xid wraparound is simply to do regular mainetnance. With 8.1 the autovacuum capability is integrated into the database backend and so this should never be a problem.

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.

Prior to 7.4 I think this was the case. With 8.0 and 8.1, only the pg_hba.conf needs to be enabled though you *might* also want to allow the system to listen on addresses other than localhost. In this case, you might need to alter both files.

But then there are webmin plugins etc. that allow you to modify the pg_hba entries from a web interface :-)

Re:If you need Oracle, you need it.

[morgan_greywolf]

There's plenty of misinformation going around. For instance

PostgreSQL doesn't behave as nicely as Oracle when the system fills up, Goulet said. In those instances, the system tends to crash quickly.

I'm, among other things, an Oracle administrator. When the filesystem that holds the databse files fills up on Oracle 9i 9.2.0.4 on both Solaris and Linux, I can tell you for sure that the Oracle instance will crash suddenly, with nothing more than a notation in the log that the disk was full trying to write to file such-and-such.

That's not any different from what they describe with PostgreSQL.