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

Oracle Database 10g Express Edition for free

[Anonymous Coward]

For those cost-conscious users, you may want to explore the free Oracle Database 10g Express Edition.

http://www.oracle.com/technology/products/database /xe/index.html

    Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level,
    small-footprint database based on the Oracle Database 10g Release 2 code base
    that's free to develop, deploy, and distribute; fast to download; and simple
    to administer.

It is absolutely free. It has certain size-restrictions but they should be enough for a lot of usages.

Re:Postgres tcp/ip too difficult to configure

[allanw]

They aren't. You configure postgres to listen on a TCP/IP port on postgresql.conf, and allow specific IP's to connect remotely/locally in pg_hba.

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.

Re:Who Ya Gonna Call?

[einhverfr]

Companies that employ core PostgreSQL programmers and offer tech support include:

1) Command Prompt, Inc.
2) PostgreSQL, Inc.
3) Software Research Associates

If you want to pay for software licenses, I would suggest doing buisness with EnterpriseDB.

Other potential vendors include Fujitsu (in Australia at least) and Green Plum in CA.

Sun is also looking at offering support for PostgreSQL when it is bundled with Solaris.

Want more? My firm offers DBA-level support. If you want highly technical support, use the email lists, or call Command Prompt.

Re:postgresql...ease of use?

[einhverfr]

I just wish mysql could use /etc/passwd for authentication of users/passwords, I hate that it has to use its own internal user/pass database.

PostgreSQL can do this. Read up on the pam authentication method.


obviously they've never tried to dump and restore a database when upgrading to a new major release. Never goes according to the documentation. thats why I love mysql, just install the new rpms and keep on truckin'.

I just wish MySQL had transactional full text indexing, Java stored procedures, and nestable database roles (which makes administering a database with many users easy). MySQL has both technical limits and ease of use limits once you start doing anything moderately complex with it.

Of course, compared to Oracle, anything is easy to use.

Re:postGRES

[WindBourne]

Except that it was not a fork. It started on its own, but at the same lab.

Re:Availability of Source Code? Does it Matter?

[cr0sh]

It isn't that you can do it, or that you can hire someone else to do it (mangle/compile the code) - but the fact that you can do it at all.

Say, for example, Microsoft or Oracle go "belly up". It can happen, quicker than you think, for a variety of reasons. There have been many examples throughout history of this happenning, either due to external or internal reasons. So in theory, if your company relies on MSSQL or Oracle's DB product, and the vendor goes belly up, what then?

Well, your company can probably continue with the software, as is (as long as there isn't any "call home" licensing checks). Hopefully, if your company is smart, they immediately begin a crash course to migrate to a new database product and/or vendor. However, let's say they don't, because they can't convince their clients to buy an all new DB backend or whatever, or the DB software being used has a feature not available anywhere else, or something like that. Time passes, then one day, a very nasty bug in the software is found, something that could possibly take down the business, leaving all the clients in the lurch - what then?

Since your company doesn't have the source to the DB software, you can't fix it. You better pray you can find a workaround. If not, it may be curtains for the business (and maybe some of your clients, who may have went with thier own version of "proprietary software" when they went with your company, unless your code is open source). Had you instead gone with an open-source DB solution (and/or rolled your own code to bring those "needed-features" the other proprietary guy had and gave them back to the community as a note of "thanks"), and had that open-source solution gone "belly-up", and had events transpired the same way (bugs found, etc)...

In theory, at that "darkest moment", you could "save yourself", either by hunting down the offending code and fixing it (and distributing the patch to clients), or hiring someone else to do the same. THAT is the power of open source, and why it is a good thing, even if you never touch it yourself. Frankly, having been in a variety of vertical-market software development jobs over the past 15 years, the above situation happens more often than you think (although in most cases it is with other software than databases), causing companies to almost grind to a halt as they look for yet another proprietary vertical market solution in their domain (most vertical market solutions are proprietary due to the nature and size of the business domains they serve - think insurance, medical, warehousing, distribution, etc) - paying huge amounts of money in their contracts to have the lucky winner "convert them over" to the new system...