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."

If you need Oracle, you need it.

90% of the people who use it don't need it. 100% of that 90% are/have been convinced they need it.

Re:I'm not convinced...

I'm very happy that our company is using Oracle - it's expensive, that's why! That high expense reflects back on me, in a good way. "The software is valueable, so the people who work with it are valueable". I'm better paid because they chose Oracle over MySQL.

Another thing is the large selection of Oracle training available. The more expensive a thing is, the more training is "worth it" -- even if it is insanely expensive. When I get this training, it is because "I am worth it" -- making me worth more in the process.

And yet another thig is the high level of professionalism surrounding Oracle. Our Oracle DBA is fantastic, he really preaches the right practice, and management listens to him. Because he is an professional Oracle DBA, not some MySQL tweaker.

Personally I would use PostgreSQL, but I'm happy we are using Oracle. Who needs all the features above and beyond ACID compliance? Perversly, it's Oracles high price tag that makes it better for me - personally - at work. I'm not footing the bill, and a bigger budget translates to higher saleries in the field.

I's perverse, but that's how it is.

Re:I'm not convinced...

I hate this doublethink. I both 100% agree with you and 100% hate the fact that your right.

Why do people associate the cost of the tool with the cost of the engineer? Surely a man who can create a masterpiece with a brush and an oil solute is worth more than some monkey with a digital camera and photoshop. I guess its just an easier metric for managers to deal with.

What annoys me the most is that this is why big companies, that make lousy solutions, are making a killing. The project I'm working on put out a tender for its platform technologies. Unsuprisingly, the technologies that won were BEA Weblogic (Container), Sun (Servers), Cisco (Networks) and Oracle (Database).

I know that the same product could be built using Tomcat (Container), Debian (Servers), OpenBSD (Networks) and PostgreSQL (Database) and work as well or better (the budget doesn't reflect the complexity in this case), and I know that they weren't even concidered because, as OSS solutions, they don't have a consultancy team running around making promises Dev can't keep. I used to believe that it was important that enterprise solutions came with enterprise support, however, I have yet to experience enterprise grade support from anyone, at least not in any form that was better than an OSS product.

But who am I trying to kid. If PHBs had a clue about technology they wouldn't be PHBs. The big corporations that can afford big iron software soultions exist because of pervasive ignorance and metric crunching abilities of middle management, and the zeal of their marketing dept, not becuase they know what their doing.

Re:If you need Oracle, you need it.

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.

Works for me

We're using PostgreSQL as the indi [getindi.com] backend; it handles requests both from a Jabber server and a Ruby on Rails web site and web service using the native extension (i.e., written in C) driver.

It's working great so far, and since ejabberd [blogs.com] has native integration with PostgreSQL, we'll be able to switch to that pretty easily.

Corrections to the article

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 :-)

Oracle's rich feature?

others said that Oracle's rich feature cannot be ignored

Oracle's rich feature? Oracle only has one feature?

If that's the case, it would have to be a pretty mighty feature to beat any other database...well, except maybe Access.

So does anyone know what this feature is?

Re:Oracle's rich feature?

Oracle's rich feature?

You have to be rich to buy Oracle. That's the feature.

There are other options....

I've been working with Ingres recently. It's GPL and has a great enterprise-proven track record. Best of both worlds.

http://ingres.com/ [ingres.com]

Re:postGRES

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

postgresql...ease of use?

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 could use /etc/passwd for authentication of users/passwords, I hate that it has to use its own internal user/pass database.

Re:postgresql...ease of use?

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.

Who Ya Gonna Call?

I like how I can call Oracle and get the best developers/DBAs/integrators/troubleshooters to solve my problem, and it requires only money. I like how I can look at the Postgres source code, so I don't have to call anyone to solve my problem - or I can choose who I call.

Re:Who Ya Gonna Call?

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.

Postgres tcp/ip too difficult to configure

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.

Uhh...is editing a config file really that difficult a process? It's like two lines.

Re:Postgres tcp/ip too difficult to configure

For a non-trivial application, it is very often impossible to create a configuration GUI that is as clear, capable, and useable as the text config file it is meant to replace/hide.

So what do you do? You end up making assumptions about how the app is "likely" to be used. This makes the GUI usable for people who's needs and desires match your assumptions, but you've essentially reduced the functionality of your application to match those assumptions. People whose needs don't match those assumptions now find your application to be difficult or impossible to use.

On a side note, it has been my experience that people who rely on GUIs to configure non-trivial apps never seem to have a good idea what's actually going on. They simply try something and if that doesn't work, they try something else. People who've actually invested the effort to learn how to modify the config file generally know exactly what change is required to get the desired change in behavior. Those are the people I hire. I don't want someone who is inclined to make changes without understanding their effects first.

Oh, and in the future, if you're going to call someone an elitist (or whatever), at least have the courage to use your own account.

A little of both?

Why does everything have to be all or nothing? There's nothing stopping an Oracle shop from using PostgreSQL here and there. Plus you've got EnterpriseDB, [enterprisedb.com] which bolts Oracle compatibility onto PostgreSQL for a little bit of the best of both worlds. Go ahead and pay Oracle for the top end of what their feature set lets you do and use PostgreSQL for the rest.

Oracle is a great database

as is MySQL/InnoDB and Postgres.

MySQL and Postgres have the features that most projects need. Most databases are relatively small (in the 50 gigabyte range), and do fine with a standard database with triggers, views and stored procedures.

Oracle has features that are absolutely essential to some projects. And MySQL and Postgres are slowly (or more quickly in the case of MySQL) adding features, turning Oracle into a niche product.

Long gone are the days where, to paraphrase, "No one was ever fired for implementing Oracle".

MySQL 5.1 (beta) has data-partitioning, row-based replication (and statement based), event scheduler (like Oracle jobs), and the ability to replication between non-clustered databases and clustered databases (clustered databases don't support foreign keys in MySQL 5.0 and 5.1, so this is a good thing).

The advantage of Open Source

To me, the important advantage of Open Source on the server side is that my data is in an Open Format - because I have the source. I can clean up corruptions or load old backups because I know exactly how the server reads the data.

Also, I can use the same database version forever. I have to get someone to patch the code to run on Vista or Windows Server 2025 or whatever in the future, but the core of the database server remains the same. Database servers just keep running on some server and are forgotten until suddenly someone makes the decision to upgrade those old NT 3.51 servers ASAP. If you run an ancient version of Oracle, you are stuffed. No support for the old version, your proprietary front end application doesn't support the Oracle versions that run on Win2003 - so what do you do? Run your business critical RDBMS at an unsupported version on NT on VMWare on Win2003? With Open Source, you can patch the layer that needs fixing, without changing the rest of the product or include the feature bloat the Oracle Sales keep getting added into their products.

DBA Comparisions - Oracle vs. PostgreSQL

If they want something that plays nice with Oracle, they should take a look at http://www.enterprisedb.com/ [enterprisedb.com] .

One of the goals of the company is aimed specifically at making life easier for Oracle people on PostgreSQL.

Company I work for runs both PostgreSQL and Oracle. Years ago we were a PostgreSQL only shop. Along comes a Sr. Developer who touts Oracle to management, and they listened to him.

Now we have 2 Sr. Oracle DBAs, 1 Jr., and 2 PL/SQL programmers.

Oh yeah, we don't have any PostgreSQL DBAs. But we have just as many PostgreSQL servers.

Now we are moving some of our applications back to PostgreSQL, which of course scares the Oracle DBAs.

Our servers are heavy-hit. Thousands of queries per-second on both systems. PostgreSQL can keep up with Oracle, and Oracle can keep up with PostgreSQL.

One thing I've noticed about the market that is both good and bad for PostgreSQL - You can put out an Ad for an Oracle DBA and get hundreds of responces. Put one up for PostgreSQL and you get almost none. Almost a year we've had an Ad out for a PostgreSQL, there just arn't any.

And I don't think its because there arn't any full-time DBAs. The reality is PostgreSQL just doesn't need the same amount of staff that an equal amount of Oracle databases need. The good side, it just works and requires so little maintenence. The bad side? Its hard to sell to companies when they can't have someone full-time on it.

I'm curious with other companies experiences. How many full-time DBAs do you have for Oracle? How many for PostgreSQL?

Oracle Database 10g Express Edition for free

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.com]

    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.

Replication on PG is no good

Slony I requires a primary key on all tables in order to be able to do anything. I have tables that don't have primary keys and I don't want to ever have them. I've normalized my DB and it's the best way to keep track of multiple items for a single person. OIDs are a waste of time in this situation and a cop-out. I don't want to rely on some level of replication that runs on top of the database server, I want it to be part of the database server so everything that works with the DB is aware of replication needs.

Postgres really needs some replication or mirroring mechanism built-in in order to even begin to attract people away from Oracle. The Slony II project will certainly require this level of integration, and I hope it succeeds, even it it takes until PostgeSQL 10.0.

Re:Replication on PG is no good

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.

oracle's rich feature

So is oracle's rich feature the one the one where you pay through the nose for their software and they get rich?

Only one conclusion one can draw

Anyone who has done enterprise level web-enabled applications can easily tell you the faults with all of the major players in the database field. Oracle is simply 'ok', but for most tasks, it's -to be brutally honest- over kill. Do you really need the replication features of SQL when all you are doing is cacheing emails and collecting messages from your users? At this juncture, most people are relieved because they believe that they settle for a second-teir solution such as MYsql and save the licensing fees to boot.

This might be ideal if all that you were doing was a ruby program that indexed your record collection, say for a student project in your CS class; but in the real world if you have to interface with serious e-commerce applications [getindi.com] you will find that not only does MySqL lack even moderately advanced SQL features, but that you will be facing rising support costs for this "free" platform.

So, this brings us to PostGRESL. Now, I don't have a lot of experience with it, myself, given that I've mostly stuck to following the major database players instead of the fringe ones, but since this article addresses it, I've asked some of my friends their opinions. While it's featureful and scalable enough to meet the demands of your average medium sized shop, they've noticed that it tends to not be a viable solution for larger projects. In particular the latest industry benchmarks show PostGREsqL performing poorly compared to more mainstream vendor such as ingres [ingres.com].

Again, like MysQl, POSTgres demonstrates that in order to get enterprise level performance out of hobbyist level software, you're going to have to pay enterprise level fees for support as well as licenseing.

So, in conclusion, after seeing the way in which the other industry standard database solutions fail, there's only one choice a sane IT manager can make: When you need a datacenter solution which both high performant and scalable, is eoconomically viable and contains more support for the current standards the only real contender is SQL Server.

Availability of Source Code? Does it Matter?

"I like how I can look at the Postgres source code, so I don't have to call anyone to solve my problem - or I can choose who I call."

In discussions like this, availability of source code always comes up.

I want to know who has a job where they have so much extra time on their hands that they can debug the source code of their database product.

No, seriously. I REALLY want to know. I can't imagine things operating at a pace where this kind of thing is even an option.

The only conclusion is that people who actually do this are either (a) the top .001% of the elite programmers who can do this on the fly, (b) ex-developers from the PostgreSQL team, or (c) nerds in their basement with no time constraints because all they're doing is running their Star Trek fansites with it.

THIS is how source code availability matters

I want to know who has a job where they have so much extra time on their hands that they can debug the source code of their database product.

Nobody except the active contributers to the RDBMS I'm guessing. Certainly not be. But I'll tell you my personal experience with PostgreSQL and how it being open source directly benefited me:

I was doing a project involving PgSQL many years ago (v6.2 I think) to manage a small inventory database. There was a problem that looked like a bug in PgSQL rather than a configuration issue (I think it was causing VACUUM to fail among other things but my memory fails me). What I clearly remember was how I resolved the issue, and it is the first time that the benefits of open source directly affected me and when I becane clearly sold on open source.

I had given up and since there wasn't a company to turn to I looked for contact emails in what passed for the docs at the time (they are MUCH better now) and on the website. I emailed one of the core developers and described my problem. He emailed me back the next day and thanked me for my feedback and said he had a few other reports of problems somewhat similar to mine. He also ATTACHED THE SOURCE CODE OF THE PATCH he had been working on that was not yet in the release on the website! I applied the patch and recompiled and bingo...it was back to normal!

Now I was (still am) far from a guru C programmer but as with a lot of people I can stumble my way around makefiles and GCC and patches and so forth, and I did have time to recompile PgSQL. I can also (at the instruction of one of the developers) to traces and such and send in the results and THEY can do the debugging with my help. If I was using Microsoft SQL Server and had a similar problem I'd be screwed: I'd have to call clueless tech support, or wander around the KB articles and hope to find the solution, and in this case I'd probably find a useledd KB article along the lines of "Microsoft has acknowledged this to be an issue and will provide a solution in the next available hotfix" telling me to do some kludgy, unacceptable workaround in the meantime, which could be days, or weeks...or maybe even never. I certainly would NEVER have the ear of a Microsoft programmer who wrote or reviewed the code as a lowly intern-type doing a small experimental project.

So there you go...I'm (a) not an "elite programmer", (b) never been part of the PostgreSQL team beyond exchanging emails with a team member, and (c) though some may say I am a nerd I moved out of my parents' home when I was 17 and never lived in their basement. Despite that I have indeed directly benefited from source code availability for software that I did not write.

Re:Availability of Source Code? Does it Matter?

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...

In other news...

Next week on Slashdot:

Movers: 18 Wheelers and Pickup Trucks Debate
Grave Diggers: 360 Degree Excavators and Shovels Debate
Firefighters: C-130s and Hoses Debate

Anyone tried clustering postgres w/ slony?

I want to do this, anyone tried this yet?

Clustering

Lack of pure built in clustering or proven replication support keeps me away from Postgres. Any idea when it will be part of the actual DBMS?

Many DBAs miss the point

Like any profession, database administration is rife with the mediocre and downright incompetent. For those, Oracle and the like provide an awesome service. A DBMS that works half the time, and the half the time it isn't working, there are documents, online knowledge bases, and expensive tech support personnel who can read to you from their CDROMs.

If you really want to know if PostgreSQL (or MySQL) can handle it, look at the best and brightest tech corps in the world. I'll pick two for you: Google and Yahoo!. They use MySQL extensively. IMO PostgreSQL can do whatever MySQL can (though, honestly, I'm not sure, I've only ever seen MySQL in high volume environments like Digg, where I'm currently working).

If your org *NEEDs* Oracle or Sybase or whatever because MySQL and PostgreSQL aren't supported by some software you bought, I feel sorry for you, and recommend you either accept your company's mediocrity or get out.

If you think MySQL/PostgreSQL just don't have what it takes on a fundamental level, I humbly suggest you rethink your competence in the field.

Backups backups backups!

Just to plug my favourite itch..

PostgreSQL needs a reliable, well documented method for performing live incrememental backups. As in:

1) dump the whole database once a day
2) dump the transaction log every 5 minutes

You can then recover to any point give or take 5 minutes by loading the last full dump and each of the incrementals up to the point you need.

PostgreSQL ALMOST has this in the form of "Point In Time Recovery" but..

1) the documentation is incomplete - key details (like a definitive method for identifying the current log file) are missing. Check out the threads in the postgres-admin mailing list. It needs to be easier and users need to be 100% confident that they have the right set of files.

2) you can only backup and restore the whole server instance - ie ALL the databases at once. In practice this means you need a second server somewhere to do recovery on, then need to perform a complicated migration back to the primary server.

If backups don't really matter to you (or you're not running a transactional system) then PostgreSQL is fantastic. But if it's getting many updates a day and you care about recovery (so you're not boned when someone forgets the WHERE clause in a DELETE/UPDATE command) then it doesn't quite cut the mustard yet.

An official reference implementation backup & restore script would be a good start.

Funny quote

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.

If you've ever had to bail out an inept Oracle DBA, this quote is rather ironic. Managing Oracle through it's GUI looks simple, but a few false steps you fall into a terrifying shadow world of arcane SQLPlus incantations and mysterious and evil grimoire config files.

Relevant discussion topic, but keep in mind bias

TechTarget is paid by Oracle and others to write articles like this, so keep in mind the bias when reading the linked article.

Neither supports SQL99 WITH RECURSIVE syntax

Although Oracle has the CONNECT BY clause which can accomplish the same thing.

Postgresql has a patch that enables CONNECT BY support, but it's not standard.

Hardware overwhelms 99% of problems

I've been a oracle dba for a long time, and recently turned my back on oracle to seek other career challenges. I think the short explaination for my decision, is that the speed of hardware has grown to overwhelm most any real world performance problem, with just "good practices" as a dba as opposed to having to be some kind of hero dba. So there is no future there as far as $$.

I built some very large, very high thruput databases under difficult conditions (torrid growth at paypal) and I don't think it could have been done with that hardware and even todays mysql or postgres: only oracle had the features then, and it's features then exceed what pg and mysql have *in the category of scalability and serviceability*. This mostly stemmed from the difficulty of doing transaction safe database calls across multiple machines forcing us to scale a single machine for a very long time. And before anyone says pg and mysql have transactions, I"m talking about billions of dollars of transactions, where if you loose or fail to recover even a few you risk goign out of business because customers stop trusting you.

But moores law and whatever law applies to disk densities just crush any classic RDBMS problems. Number of spindles can still be sort of costly, but way cheaper than even 5 years ago. memory and cpu are practically free, making it unecessary and wasteful of time and money to obsess over tuning, inall but the most exterme cases.

If you're writing a custom new application, fuck oracle, except in those cases fall along these few lines:

- you want to do truly massive scans of multi terrabyte tables: Oracle RAC will do 2-4 gigabytes /sec on a few xeon servers facing a few FC disk arrays. PG or MySQL will never do that even on the same hardware, since they don't parallelize and they don't do directIO and asyncIO in a pervasive or big way. This is the classic datawarehouse design and is probably a bastion for oracle (oracle is basically trying to eat terradata from the lower end)
- you want > 99.9 uptime: You will still need to work 10x harder in the application to get this or better, but you'll also need the serviceability of oracle e.g. index rebuilds corruption fixes without reboot. Also, the visibility and monitoring of oracle is lightyears ahead of mysql and pg.
- you want to build an empire as a middle manager
- you are writing a turnkey application you plan to sell to corporate america or govt.

One bozo's review of Oracle, MSQLServer, Postgres,

I am NOT a DBA, nor do I want to become one. Doing a tiny scrap of self-taught database programming (i.e., learning SQL from a book and Google), and trying all three databases (free and trial versions), I ended up using Postgres. Oracle was the loser; with a database that "dumped" into a couple of megabytes of SQL that would recreate it, on a machine with 1.5 GiB of memory, Oracle decided to fail some of my transactions because (it's claim) it could not find 4 MiB for some table or another. A friend of mine who knew more about databases said, "well sure, if you use Oracle, you need a DBA. You need MSSQLServer." (why am I not surprised that DBAs like Oracle?)

That was great, till the demo timed out, and the development was not done yet. So then I learned about Postgres. So far, so good. Yes, I need to edit those stupid scripts. I am a notably grumpy and impatient person, and this was not hard. MSSQLServer also doesn't run very well on my Mac.

I consider MySQL, but back when I was considering, MySQL did not do subqueries, and it turned out that the SQL I was writing (not knowing any "better", just reading the book and believing it) used subqueries. So no dice there, either. That failing, together with all the hype, as since led me to pretty much ignore MySQL -- why should I believe today's hype, when yesterday's hype was so wrong?

So, in my book, postgres wins. It hasn't done me wrong yet, and the other guys have.

All I know is...

All our work (bioinformatics for a small biotech) is done on postgres (of course, the payroll for 75 people is in Oracle), over the last 6 years I've installed and configured probably over 100 instances of postgres and maybe 5 or 6 of Oracle - and I've spent a lot more total time configuring Oracle.

Oracle has a bug that prevents PostgreSQL ...

There is a known bug in Oracle's HSODBC layer that prevents Oracle integration with PostgreSQL (or any other non-oracle database probably). Any VARCHAR fields in Postgres with UNICODE encoding will not be readable in Oracle.
Oracle know about it. And the answer is no, they won't fix it.

True life story follows...

Now imagne you're Oracle customer and paid 50,000 for database. And you can't integrate it with other systems because of this.

- Oracle is quite arrogant to say the least. They know about this major problem for years and the answer is - won't be fixed. Maybe they do it on purpose. You have other non-oracle DBs and they force you into submission, so you either migrate everything to Oracle or drop Oracle for Postgres.
And of course if you paid 50,000 you won't be dropping it., You'll buy anouther 50,000 license to justify the first one.
- You're pissed off because you paid big money and you can't even fix it yourself because source code is not available. You can go with Postgres, for free and it will work with any database. Turns out that Postgres is more supported and more standarts compliant than Oracle. And even if you have a problem you may fix it in source.

So morale is quite simple, don't buy Oracle product.

Solaris Optimized PostgreSQL

Wow all these posts and there are only a handfull of Sun/Solaris mentions. Sun are making great improvements to PostgreSQL, they're currently working on a Solaris optimized version which will be a big bonus.

Reference: See the coming soon section. [sun.com]

Hard to learn

I like to think of myself as a very technically minded person. But I recently installed Oracle 8i on my Linux (debian) server and found it very hard to use. Administration was very hard and I followed instructions in my huge Oracle 8i book but it never worked. when asking for help on the Oracle forums I did get the right answers, but just could never control it myself, with the use of the book. Simple things like shutting down and starting the Oracle server was hard. A complex and big DBMS, should be used for big and complex operations.

Oracle vs PostgreSQL: Security

I'm an Oracle Administrator & Developer. I worked with both PostgreSQL (7.4 and 8.1) and Oracle (7.3, 8i, 9i and 10g) and, from the developer perspective I prefer Oracle. From the Administrator perspective..., Oracle is more easy to administrate but her police in fixing security vulnerabilities makes me very paranoid when I need to protect one Oracle Database.

This problem doesn't appear with PostgreSQL (or any other Open Source Database), simply, because I can modify the source code of the product if I found a problem.

There is no such option with an Oracle database so, you can try using a workaround (if possible and available) or you will need to wait for a year (or more).

When the Oracle guys change her policy about security problems I will say that is my prefered database in the world. At the moment I can only say that it's the most powerfull database (among IBM DB2) and, surely, one of the most insecure databases in the world.

Why use Oracle

Oracle costs a fair bit. Their pricing has finally come more in line for mid-size installations, but still it ain't cheap. So why spend that cash?

1. You can find experienced developers and DBAs who live and breathe best-practices.
2. DBAs and developers cost money too. A developer is so much more productive in PL/SQL (Oracle's stored procedure language is a real language, not a shell script running in the db) that once you have 6 or so full time database developers Oracle pays for itself in developer productivity. It really is comparable paying licensing fees to code in Java or C# vs. coding in ksh for free.
3. There is an enormous, in-depth Oracle knowledge base to draw on, both on-line and as authors. I can get any question resolved frighteningly quickly at asktom.oracle.com or http://www.quest-pipelines.com/pipelines/dba/index .asp [quest-pipelines.com] or forums.oracle.com or www.dizwell.com/forum or ...

In short, I have much better tools, much better support and a much deeper knowledge base to draw on.

On the plus side of PostgreSQL, recently I had to resolve a SQL Server issue that simply brought the db to its knees. The solution was to not use large (10k to 100k rows) transactions. PostgreSQL has an entirely sensible concurrency/transaction model and does not have the same aweful issues.

Cheers,
Scott

Re:Comparisons

How about a site that shows me objective views Oracle, PostgreSQL, MySQL, etc. have to offer?

Just go to that site that has the unbiased comparison of emacs vs vi (can't rembember the url), then click on the "Perl vs Ruby vs Python: An Ojective Analysis" link. On that page, there is a link to exactly what you are looking for (It's just under all the "Linux vs Microsoft: TCO" whitepapers.)

Re:Comparisons

http://www-css.fnal.gov/dsg/external/freeware/mysq l-vs-pgsql.html [fnal.gov]

Re:What did I miss?

I might be missing something but I think that David Fetter's project, DBI-Link might simplify your life. It allows you to use Perl/DBI to present any data source you can reach through DBI as a PostgreSQL view. It is basically a partial implimentation of the SQL/MED standard.

Re:No oracle for FreeBSD

PostgreSQL does run on FreeBSD however. Better than that, PostgreSQL runs on FreeBSD on old 64 bit Sun kit.

Now that's a cheap and reliable database system.