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

If you need Oracle, you need it.

[Anonymous Coward]

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

Re:If you need Oracle, you need it.

[ObsessiveMathsFreak]

100% of that 90% are/have been convinced they need it.

Well, what's their alternative? SQL Server? You can only get by on that for so long.

The usual transition goes like this; Access->SQL Server->(Something Better)

List out the current list of products that qualify as "Something Better" than SQL Server.

Re:If you need Oracle, you need it.

[pbaehr]

50% of this message was convoluted by unnecessary use of percentages. 100% of that 50% was difficult to read as a result.

Re:If you need Oracle, you need it.

[Doctor Memory]

Oracle and DB2. A.k.a., "what the big boys use".

Re:If you need Oracle, you need it.

[ajs]

Isn't it more complex than that? In my experience almost no one NEEDS Oracle, but in addition to having bought into the idea that they do need it, most cannot move away from it, because they've allowed their applications to become locked into proprietary features, even where open versions of those features exist.

Re:If you need Oracle, you need it.

[RaymondRuptime]

List out the current list of products that qualify as "Something Better" than SQL Server.

For one, Progress OpenEdge. My experience from working with both is that Progress is better, faster, cheaper (lowest TCO of the major RDMBS products), and is multi-platform (who is running SQL Server on Linux?). It has a very powerful toolset with the option of using a rich and intuitive 4GL or SQL. It takes next to nothing to maintain--just throw it over the wall and let it hum. And it has good connectors to Oracle, SQL Server, et al, so you can easily have a multi-product shop (which, in the age of acquisitions, is inevitable).

I'd pick Progress over SQL Server every day of the week and twice on Sunday, and I would pick Progress over Oracle for any project except extremely large (>1 TB) databases.

Re:If you need Oracle, you need it.

[hey!]

Well, what's their alternative? SQL Server?

I think the point is that Postgres might be a better solution for them, now that it's supported on NT.

List out the current list of products that qualify as "Something Better" than SQL Server.

All the products? Do I have to include encoding information on knotted cords and dispatching it over mountain footpaths via barefoot runners?

Better depends on what for of course. SQL server is fair as a database engine, but so is Postgres. Transact SQL is utter crap, both by design (it's the most unorthagonal SQL I've ever seen) and in implementation (buggy if you aren't doing vanilla SQL); given that I'd say Postgres is better if you rely upon SQL. But SQL Server is a good choice if you're going 100% Microsoft end to end, including IDEs that generate all the SQL for you.

Re:If you need Oracle, you need it.

[chris_mahan]

Ooh, nice troll...

Re:If you need Oracle, you need it.

[hey!]

100% of that 90% are/have been convinced they need it.

That's a bit of Captain Obvious point, isn't it?

That said, Oracle has a lot of really good and unusual features that even relatively mundane appliations can use. They aren't necessary of course, and they are highly non-standard, which is why they are seldom used.

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:If you need Oracle, you need it.

[erotic piebald]

Hmmm, I've been an Oracle DBA for almost as long as I can remember (not that that's pertinent to your situation :) ), but I've never seen this behavior.

There are an awful lot of "ORA-nnnnn Unable to extend ..." errors (depending on what you're allowing to autoextend) that are trapped and reported to the process that encountered the error as well as some(/most/all?) being reported in the alert.log. I've seen nearly every variation of those (table/index/other segment, tablespace, file, and yes, disk).

Behavior that you're describing is a bug and you should report it. Since you're at 9.2.0.4, maybe it's been reported before and there's a patch available. I think the current version is 9.2.0.7 (maybe .6).

Re:If you need Oracle, you need it.

[Anonymous Coward]

I have become increasingly frustrated with Progress for about 2 versions now. The 4GL is clunky and limited, and the implementation of SQL is poor. Interoperability with free software tools, languages and databases is practically nonexistent, so you get tied into an all-progress solution. That just grinds my gears.

But at least empty string isn't a null. WTF were Oracle thinking?

Anyway, this is so offtopic. Postgres is entirely adequate for anything you would do with Progress, and it's relatively unencumbered with bullshit.

Re:If you need Oracle, you need it.

[SimuAndy]

THe current version of oracle 9iR2 released on linux is 9.2.0.8, windows 9.2.0.8 is in beta and is scheduled for release once regression testing is done. 10gR2 is the current recommended version, 10.2.0.1 has most of the bugs that are being back ported to 9.2.0.8 fixed or non-existent.

Re:If you need Oracle, you need it.

[andrewzx1]

I totally disagree. I was using Oracle for an enterprise telephony project. We accidentally set the file system as read-only. Oracle started complaining but kept running. We were stunned and amazed at its resilience. This was grace under pressure.

Re:If you need Oracle, you need it.

[rutledjw]

List out the current list of products that qualify as "Something Better" than SQL Server.

This drives me nuts. I'm NOT arguing with you here, but so often this is the debate, better-best-whatever vs. getting something that matches the needs of the business and (almost more importantly) the skillset of your DBAs and developers.

A highly sophisticated DB2 or Oracle 10g RAC system in the hands of junior developers is like putting a large firearm in the hands of a child. It's a bad idea.

That being said, we're one of those business units that "needs" Oracle. We use RAC in rather large configurations, need worldwide 24x7 support, 99.99 and blah-blah-blah. That being said, we're always looking for ways to simplify certian functions and absolutely reduce cost. Using a PostGRES or MySQL for reporting / long-term data retention, etc, seems a logical step.

I don't know how well these databases will deal with large amounts of data (2+ TB), BUT, it's reporting database. I think (and could be wrong) that to reduce cost to near zero the business would accept a slowdown on the reporting function for "older" data.

For us, SQLServer is out simply because I'm trying to reduce platforms and general complexity. I'm willing to make some exceptions, as long as they're reasonable, but we're UNIX, and I really don't want to add an entirely new platform...

Re:If you need Oracle, you need it.

[Anonymous Coward]

And no one has answered yet. Interesting. Perhaps there is no difference?

Re:If you need Oracle, you need it.

[Anonymous Coward]

"...65% of the time, it works every time..."

Re:If you need Oracle, you need it.

[Anonymous Coward]

Obviously troll since PostgreSQL has one of the most compliant SQL implementations available. Most other vendors steer toward vendor specific implementations rather than SQL standard. You're either a troll and have no idea what you're talking about.

Re:If you need Oracle, you need it.

[Anonymous Coward]

Bah, it just typical Oracle DBA nonsense. Oracle DBAs are that rare bread of ignorant non-engineers, with no real programming experience, but help up high on a lofty purchase sure in the self reighteousness. Thinking that what they do is some amazing dark art, when all it really ends up being is manipulating n-dimension arrays. Something any first year computer science student can do.

DBAs are a bullshit profession. Thank goodness its stating to show. Maybe we reinvest all that money wasted on DBAs into software development.

Re:If you need Oracle, you need it.

[Achromatic1978]

You forgot the third option - you can't read.

Progress != Postgres.

Re:If you need Oracle, you need it.

[snero3]

I am Oracle DBA (RAC and single instance) and I don't know what kind of screwy setup you have going on there but there is no it should crash if the file system fills up. The worse I have seen is not be able to log into (as anyone other than sys) a database that is in archive logging mode because it can't write anymore archive log files, this is expect behaviour not crashing.

Whem my datafiles out grow their disk I just get a warning similar to "Can't extend tablespace by 8k etc...." it _doesn't_ crash.

Re:If you need Oracle, you need it.

[Anonymous Coward]

buggy if you aren't doing vanilla SQL

For example...?

Re:If you need Oracle, you need it.

[pamar]

I had the "pleasure" to work on eveolutive maintenance of a large Progress project for 4 years.

Progress, despite its name, is really a collection of relic, non-standard concepts and technologies, with bizzarre and arbitrary ideas like implicit transaction scope, rollback on memory data structures, a 4GL that grown in a sort of Frankestein monster of a language, pathetical error checking, inflexible data model and convoluted syntax to replicate stuff, like cursors, that other DBMS had sported for decades.

Add horrible performance under ODBC/JDBC ("but we have solved that now, and we needed only 3 major revisions of the platform to get it right!"), grudgily, non-integrated support for SQL (SQL-89 to be precise) a bolted-on-the-side OO (released this February, so untested in the field, apart from the obvious fact that they added another cartload of statements to a bloated language) and the fact that anything invented in the last 30 years (from sockets to XML) is either impossible to do or extremely clunky, and you have "Progress".

My opinion is that it survived because it was quite successfully in the 80s, and created a niche industry centered around specific vertical solutions.
Imagine Powerbuilder without the OO or the ability to interface with diverse RDBMS and you will have a vague idea of what Progress is.

Progress - the company - actually controls an umbrella of diverse technologies, some even very interesting like Sonic, but Progress - the product, is something that should have died a long ago, or be revamped instead of adding more and more stuff without ever deprecating language features from 5+ versions ago.

Re:If you need Oracle, you need it.

[Anonymous Coward]

Dammit im one of them! :)

Re:If you need Oracle, you need it.

[Anne Thwacks]

If you neeed Oracle, you should switch to DB2 instead. Its better in every possible way.

If you could get by with something else, Postgres IS that something else.

Re:If you need Oracle, you need it.

[ultranova]

Using a PostGRES or MySQL for reporting / long-term data retention, etc, seems a logical step.

Using Postgres for long term data retention is a bad idea. The database format chances often, and the migration tools are unreliable - pgdump has a nasty tendency to produce dumps that can't be read back in even to the same version, much less next version.

Got me bad when I reinstalled Debian (stable) after a hard drive failure - Postgres had been updated, and it turned out to be impossible to read the database dumps made with the previous version. A pity - Postgres is otherwise pretty good, but such data loss is simply not acceptable.

Re:If you need Oracle, you need it.

[Kaemaril]

In every way? That's a little harsh. And also, imho, inaccurate.

Amongst others (many others) : I much prefer Oracle's method of implementing table partitioning. I've yet to find a way to "Create OR REPLACE view", you can't change a nullable column to NOT NULL without recreating the table. I've yet to find a way to move an index from one tablespace to another. I much prefer Oracle's REDO/UNDO to db2's log method. The data dictionary and dynamic views seem better in Oracle than DB2.

I'm sure much of this is because I'm used to doing things the Oracle way, and somebody coming from DB2 to Oracle would probably mutter the same things about "They do things that way? I can't believe it..." but, for me at least, "every possible way" is by no means the case.

Re:If you need Oracle, you need it.

[hey!]

bound parameters in a subquery.

Re:If you need Oracle, you need it.

[mw]

Which version are you using? I've done this (restoring to different database versions, with other tools that made the dump) quite often (for sure 50 times or more for testing purposes). I never had such a problem, I'm pretty sure you're making a mistake in your restore process.

A little bit offtopic here: 3 months ago I had troubles restoring a MySQL 5.0.18 dump to another MySQL 5.0.18 database. Now that's what I call unreliable.

Re:If you need Oracle, you need it.

[pocketstheclown]

(who is running SQL Server on Linux?)
They do, its called Sybase

Re:If you need Oracle, you need it.

[RangerElf]

Actually, it's YOU who can't read:

Anyway, this is so offtopic. Postgres is entirely adequate for anything you would do with Progress, and it's relatively unencumbered with bullshit.

I wholeheartedly agree, I've suffered through Progress' lack of compatability with *any* kind of open-source or third-party tools.

-gus

Re:If you need Oracle, you need it.

[jedidiah]

It sounds like he's complaining about archiver hangs.

If you get into a situation where you can't archive to one of your manditory archive destination, oracle STOPS. This can be a connectivity issue with a failover database or a disk filling up.

What repectable admin lets any of his disks fill up, ever?

Re:If you need Oracle, you need it.

[morgan_greywolf]

All I can tell you is that my ora_smon, pmon, etc. processes die. The tables themselves are fine, but after I clear up the space, I have to reissue a 'startup' from sqlplus.

Re:If you need Oracle, you need it.

[erotic piebald]

But even archiver hangs are just that: hangs. It doesn't "crash" which what my parent poster claimed.

Your last point is well taken.

Re:If you need Oracle, you need it.

[ultranova]

Which version are you using?

7.4.7-6sarge1 now, dunno what it was previously.

The database contained large objects, so I wonder if that might have been the problem. Too late now, since the backups have been long since recycled.

Re:If you need Oracle, you need it.

[theshowmecanuck]

67.8% of all statistics are made up on the spot.

Re:If you need Oracle, you need it.

[Achromatic1978]

Alright, I'll grant that and act suitably chastened. ;)

Re:If you need Oracle, you need it.

[snero3]

hmm sounds like a bug to me, I would check it out on metalink etc... if necessary get oracle to release a bug fix for you.

Re:If you need Oracle, you need it.

[ckaminski]

From my stint playing with Progress, I'd have to agree with you. 4GL makes data manipulation easy enough my father could get away with it. The troublewith Progress is the dearth of 3rd-party tools that would make it a true contender in the RDBMS space. As it is, it's fairly well consigned to integrated/embedded db space (somewhat like my favorite dbms, ObjectStore), and it's SQL support, last I checked, was both horribly slow and not nearly SQL-92 compliant. On the upside, the Progress purchase of Merant/Data Direct just prior to my layoff was one of the best strategic moves I think Progress had made to date (certainly better than the eXcelon purchase which brought me to Progress in the first place). Smart objects (adm2) seemed like a poor attempt at checking off "object oriented" on a marketing checklist, something I think the PRGS management hopes Datadirect can fix with .Net.

Keep an eye on them long term. IIRC, PRGS was one of only a handful of Boston based tech firms that made money nearly every quarter since the dot-bomb.

Re:If you need Oracle, you need it.

[morgan_greywolf]

Do you run with archive logs turned on or off? I run with them turned on (not my idea), and it's when Oracle tries to create an archive log that it crashes. If I turn off archive logging, the problem doesn't exist, and, you're right, it won't crash -- it'll just sit there in a somewhat disabled state, not being able to create records, etc., until some space is added or freed up.

Re:If you need Oracle, you need it.

[snero3]

I run with archive logs on, as this is necessary for any sort of production database. This it allows oracle to recover from block corruptions, it also allows for "restore to point in time" type recover.

What is happening to you is that in archive logging mode on you are filling up you archive log dest and thus oracle can't create another archive log file. In archive log mode it is 100% necessary for oracle to be able to create archive logs. What it is doing is copying off the redo logs as they cycle out, if it can't create an archive log it doesn't allow that redo log file to be reused and this no DML or DDL can occur. In oracle DML happens just by login in which is why it doesn't allow anyone in other that sys (this is the only user that has the privilege to login without creating records of the login).

What you should be doing is backing up the archive logs and then delete them on regular basis. It is highly dangerous to be running a production oracle DB without archive logging mode on. One block corruption in 1 tables space and you will be heading for the tapes to get back all the data files that make up that table space.

Re:If you need Oracle, you need it.

[DoctorNetwork]

It is a valid question. I've been using MS SQL Server for going on 10 years now and recently picked up Oracle duties. I definitely see some advantages for Oracle but SQL Server has advantages as well. I'm amazed at the "brittleness" of new Oracle features. Over and over again in my Oracle 10G class the instructors would say "this feature still doesn't work as advertised", even on 10G2 - IOW, they hadn't worked out the bugs before going GA. Yes, MS SQL Server has bugs but not in features just not working on ship (don't get me started about how long "ship" takes for MS). PL/SQL is years ahead of MS's T-SQL implementation in terms of programmability, at least for SQL 2000. MS catches up greatly with the implementation of the .NET CLR in the middle of the MS SQL Server engine, but it is a 1.0 implementation without the maturity of PL/SQL. One thing that gets me about Oracle, though, is that for a update of a table on top of a joined record set, I have to cursor through in PL/SQL doing one row at a time. MS SQL Server's T-SQL implementation just does this in one relatively simple update statement. I was amazed at the concept that Oracle DBA's (at least as taught in an Oracle DBA class) concentrate on security, system, and space management. Programmability issues of SQL and PL/SQL coding efficiency are left to developers according to the Oracle paradigm (as it was taught by my experienced Oracle instructor). Maybe this is because the Oracle paradigm of managing every little jot and tittle of the DB environment was so complex. Since MS SQL Server is nowhere near as complex (generally), much more emphasis devolves back to the MS DBA looking at coding efficiency - arguably the greatest place to recognize performance gains. Now with SQL 2000 and SQL 2005, a well designed MS SQL Server application can exist in the TB range quite well. Those who disagree are just bigotted. With that in mind, since MS SQL Server is cheaper and MS SQL Server DBA's are cheaper, more companies will startup and live comfortably for the life of their company in the totally-MS sphere. It devolves back to money and convenience. MS SQL Server is cheaper than Oracle and when you run in a total-MS environment (MS Clients OS's, MS Server OS's, MS DBMS's, MS Middle Tier), it is just more convenient than mixing technologies from different companies and dealing with finger pointing when something goes wrong. Support is everything and MS has improved dramatically in the 10 years that I've been working with their products.

Works for me

[tcopeland]

We're using PostgreSQL as the indi 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 has native integration with PostgreSQL, we'll be able to switch to that pretty easily.

Re:Works for me

[Anonymous Coward]

Practically every comment you post links to one of your projects or your book. Please stop spamming. Just because you use PostgreSQL, it doesn't make your comment on-topic.

Oracle's rich feature?

[Anonymous Coward]

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?

[Gorshkov]

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?

For ANY application, ANY database just needs one feature ......

It does the job.

Everything else is just noise.

Re:Oracle's rich feature?

[bakes]

Oracle's rich feature?

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

There are other options....

[jarich]

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/

Re:There are other options....

[rduke15]

I didn't know that Ingres was GPL now. So I went to have a look, but all the Ingres documentation seems to be in PDF only! No thanks...

Re:There are other options....

[G3ckoG33k]

I tried to find out something more about Ingres, which I had never heard of before. Google spits out strange remarks like: "The original RDBMS, but the current version of its QUEL language has been corrupted to acommodate SQL."

Is there a sad and bitter ex-employee somewhere? Or what does that mean?!

Re:There are other options....

[jarich]

Ingres has been around for a long time. I'm a newbie to it, but so far it's nice. I think it will fill a nice niche.

Check out Wikipedia http://en.wikipedia.org/wiki/Ingres

and to the other poster, I don't like the PDF docs either! :(

Re:There are other options....

[einhverfr]

I also noticed that the client libraries are licensed under the GPL, which effectively causes the same sorts of licensing headaches that make MySQL money.

PostgreSQL doesn't have this problem. Nor would MySQL or Ingres if the client libs were LGPL instead of GPL.

Re:There are other options....

[Doctor Memory]

QUAL was a pre-SQL relational language, which had much better conformance with relational algebra. In order to use it to its limits, though, you had to really know relational theory. I vaguely remember something about being able to specify an ordering within a subselect that was different than the ordering in the outer query, but I don't really remember. I do remember that QUEL "made sense", more so than SQL did when I learned it. Then again, my first RDBMS was University INGRES, so I learned QUEL first, so maybe it's no surprise that I prefer it. Used to, anyway -- I haven't used QUEL since....hmmm, over a decade, anyway.

Re:There are other options....

[killjoe]

Do you have a blog or something with your experiences. It's not a widely used system I for one would very much appreciate some real world knowledge about it.

Is it easy to install, is it easy to mananage? Does the replication work? Does the replication work? Does the replication work?

Re:There are other options....

[jarich]

I'm currently writing a Ruby wrapper for it and then I'll add a Rails adapter. When I'm done I plan on writing it up, but at the moment I don't have the time.

My blog is on my sig if you want to keep an eye on it.

Re:There are other options....

[killjoe]

Thank you. I have your blog bookmarked. I am especially curious about the replication.

Re:There are other options....

[jarich]

heh... I got that. :)

You might also want to ask about the replication in the newsgroup comp.databases.ingres You can find it Google Groups.

There are a lot of very experienced Ingres users who hang out there.

postgresql...ease of use?

[Squeezer]

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?

[Just Some Guy]

obviously they've never tried to dump and restore a database when upgrading to a new major release. Never goes according to the documentation.

Since this is the first time I've ever heard of that problem, and have never seen it myself during my own upgrades, care to back that up with evidence?

Re:postgresql...ease of use?

[Squeezer]

docs at http://www.postgresql.org/docs/8.1/interactive/bac kup.html

I do just like they say, the pg_dumpall, then upgrade the database to the new major release, then do the restore. when I restore with psql -f infile postgres. and it always errors with things like the users not existing, the database not existing, etc. so i have to go in and create the users by hand, and then create an empty database, etc. I always just have to play around until I can make it work. upgrading between postgresql major releases is a pain, always has been, probably always will be.

like i said, thats why I love mysql, install the new rpms and just keep on going.

Re:postgresql...ease of use?

[Anonymous Coward]

If you don't know what your doing with it, this can easily happen.

I view MySQL, PostgreSQL, and Oracle as such -

MySQL - Designed for beginners. They work first and foremost on ease-of-use features. This is not to say its not a good database, it is. But its the easiest database to get into.

PostgreSQL - Initially designed for people who had a bit more experience. To get the full potential out of the system, you truley have to tweak with the configurations. More recent versions have made it easier for entry level users to get involved in it.

Oracle - Grand daddy. Designed to do everything for everyone, but it takes a full-time staff to make it happen.

MySQL and PostgreSQL were designed with a different mentallity. MySQL was designed to be left alone from the start.

From the start MySQL would just run, leave it alone and almost never worry about it. Two years later its still running.

PostgreSQL was designed to be powerful, but you had to look at it once in a while. Specifically the VACUUM command. More recent versions have made this easier with auto-VACUUMing.

Oracle - Don't leave this sucker alone. You have to keep an eye on it. Or, if you have a really good Sr. DBA on staff, they can configure it to run smoothly with little need for maintenance. But don't leave it alone for to long.

MySQL - Focused goals, less interaction needed.
PostgreSQL - Less focused, tries to do more for more people, more attention needed.
Oracle - Least focused, tries to do everything for everyone, needs the most attention.

The more complex and rich the system, the more work its going to need, but if you know how to take advantage of them, you can get 10x more out of them then you put in.
Compare the amount of tunable options between the three and you'll see what I'm talking about. MySQL you can configure in a few hours. Oracle? Good lord.

My company uses all three.

Re:postgresql...ease of use?

[HappyDrgn]

"I just wish mysql could use /etc/passwd for authentication"
 
You can use MySQL for UNIX user authentication. You'll want to replicate MySQL's users table to another DB for the local host's authentication. The method might not be exactly what you where looking for, as it replaces /etc/passwd, however this completes the goal of central authentication for UNIX and MySQL users. Many other services can authenticate using a MySQL table too.

Re:postgresql...ease of use?

[Anonymous Coward]

Wow. Sounds like you really know what you're talking about. Not.

Re:postgresql...ease of use?

[Anonymous Coward]

well sure, given you don't have any data integrity anyway, i guess you would never get complaints from mysql :-D

Re:postgresql...ease of use?

[rduke15]

care to back that up with evidence?

Well, I don't remember the details, but when I moved from 6.x to 7.x a couple of years ago, it wasn't easy for me either.

Re:postgresql...ease of use?

[Ian Wolf]

I tend to disagree as my mileage has varied significantly.

MySQL is my database of choice for small web applications that demand simplicity and speed. The database is remarkably simple to install and configure, but I've personally run in to data corruption issues more often than with any other database. That being said, recovery is pretty simple and I'm usually back up and running in no time.

PostgreSQL is not a database I'm real strong in, but so far its OK by me. I find its feature set more complete than MySQL and would happily use it more often in place of Oracle if I only had the time to get more familiar with it.

SQL Server despite what most Oracle zealots will tell you has come a long way. Overall, its a fantastic database that is remarkably easy to manage.

Oracle is an unwieldly beast sometimes. In fact, I'm wrestling with setting up a four node RAC cluster on CentOS right now and frankly, I'm getting my ass kicked. That being said, I personally believe that its damn near bulletproof. Of all the databases I've worked with the Oracle databases are generally the most stable, most advanced, and the most powerful. The clustering, replication, and data security features have no equal. However, there are some serious drawbacks:

• The price is obscene.
• PHB's think they have to have it even if a spreadsheet would do the trick.
• The price is obscene.
• It can be a real bitch to fix when its broken.
• The price is obscene.

I've been playing around with the new free version "Express Edition" and must say that I'm pretty impressed with it so far. It's crippled to a single CPU, 1GB SGA, and a total database size of 4GB, but a great solution for a small application database that needs to talk to a mothership database running Oracle.

Re:postgresql...ease of use?

[jocknerd]

Good god! PostgreSQL 7.x was out 5 years ago! 6.x was out almost 10 years ago. A lot has happened since then.

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:postgresql...ease of use?

[mrnobo1024]

Even if MySQL used /etc/passwd for usernames and passwords, it would still need the internal database for permissions, what hosts each user can connect from, and stuff like that.

Re:postgresql...ease of use?

[Anonymous Coward]

shhhh, the grown ups are talking now, go play with your friend access.

Re:postgresql...ease of use?

[GnuDiff]

I recently migrated my data from 7.2 to 8.1.
Zero glitches for the pg_dump/restore.

Re:postgresql...ease of use?

[VGPowerlord]

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

Let me repeat what the grandparent said, with emphasis.

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?

[Anonymous Coward]

MySQL is my database of choice for small web applications that demand simplicity and speed. .. but don't really know either.

MySQL is the database of choice for people who don't understand SQL.

Re:postgresql...ease of use?

[Anonymous Coward]

Or don't mind if their data isnt' intact.

Recently fixed 'features' (not bugs, oh no) that MySQL users lived with for years....

The database silently changing data entered out of range, rather than throwing an error is a 'feature', not a bug.

February 31st was a valid date.

You have to love how the MySQL developers talk about features though....
The put out version after version saying "Working foreign keys are evil, they aren't needed"
The next version they put out uses (semi)-real foreing keys and they say"We have forign keys! We are a *real* database". Ditto with views, stored procedures, and every other feature they've 'recently' added that has been the part of all REAL databases for years.

Re:postgresql...ease of use?

[BlueQuark]

Run it on Solaris with VCS 10g Cluster. Much nicer.

What kind of interconnect do you plan on using? If you are thinking Ethernet of any kind, except maybe 10gE, you might want to reconsider something else, unless you have tested. GigE with jumbo frames enabled, might be okay, but still I feel Ethernet's latency is a problem for a cluster interconnect.

YOu can try myrinet, SCI or Infiniband.

I've setup a few 10g clusters under both Linux and Solaris. And I have to tell you, no matter what Oracle says, Linux is kind of wonky for this sort of thing...

The Solaris clusters are so much nicer and much more stable. Or course they cost a bit more, but NOT that much more...

I agree with all your points though regarding MySQL, PostgreSQL, which I run as well.

Re:postgresql...ease of use?

[icklemichael]

We had a small amount of pain going from 7.1 to 8, the problem was to do with invalid unicode characters, I thought the databases were created with encoding UNICODE on both versions, but it was a while back and they might have been SQLASCII or something...

The problem was definitely with the data, and it was trivially solved with iconv...

Re:postgresql...ease of use?

[Discrete_infinity]

Ditto , I went from postgresql 7.3 -> 8.1, trouble free. Just my 2 cents.

-Upgrade or not, there is no patch!

Who Ya Gonna Call?

[Doc Ruby]

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?

[garyrich]

"I like how I can call Oracle and get the best developers/DBAs/integrators/troubleshooters to solve my problem"

Not true for most of their "customers" since they are not Oracle's cusomters. They are customers of some vertical marker supplier that they bought their MRP/CRM/SOX/LIMS/whatever software from that just happens to use Oracle. They have zero support through Oracle. Usually if the VAR can't fix the problem you end up playing semaphore with the VAR sitting in the middle trying to mediate. This does not always work well.

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:Who Ya Gonna Call?

[Anonymous Coward]

Bigger companies than Oracle provide postgresql support (hint, google for "fujitsu supported postgresql"). If you want a > $40B company supporting your database, your only choices are DB2, POstgresql and SQLServer.

Re:Who Ya Gonna Call?

[Doc Ruby]

I can buy a support contract directly from Oracle without having bought the Oracle product directly from Oracle.

That is also one of the reasons VARs/ISVs design Oracle into their products: they can offer direct Oracle support to customers without being Oracle.

Re:Who Ya Gonna Call?

[Doc Ruby]

Have you any reason to believe that Fujitsu support for Postgres is as good as Oracle support for Oracle? I've got lots of reasons to believe it's not, starting with Fujitsu's lack of brand equity dependence on Postgres quality.

Re:Who Ya Gonna Call?

[Doc Ruby]

Those are all potentially good options. Certainly better than nothing. None of them have the reputation Oracle has in supporting its own core product. The "email lists" option is exactly the part that I want to call Oracle with: a "guaranteed" solution to my problem, whenever/wherever/whatever.

Re:Who Ya Gonna Call?

[RidiculousPie]

Ghostbusters!

Re:Who Ya Gonna Call?

[jbolden]

Then the VAR is abusing the embedded license. If the customer knows they are running Oracle they can't offer that level of support.

Re:Who Ya Gonna Call?

[darilon]

I just /join #mysql and get access to many highly qualified professionals as well as a bunch of slackers with too much time on their hands. Best part is when my problem turns out to be user error, there's no additional charge.

Re:Who Ya Gonna Call?

[bout]

And regarding Solaris and PostgreSQL, here's a blog post that I wrote a while back that points to some resource on that topic.

Re:Who Ya Gonna Call?

[farble1670]

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

hmmm. not bloody likely. sun has been releasing press release after press release lately about how they are doing this and bundling that with oracle. i'd include a link, but there's so much out there, just google for "sun oracle" and take a look at the first ten hits.

Postgres tcp/ip too difficult to configure

[zfalcon]

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

[Dan Ost]

Apparently, when you come from a point-and-click world, editing text is unintuitive.

Re:Postgres tcp/ip too difficult to configure

[RelliK]

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

Yes! Unless it comes with a wizard and that funny animated paperclip, it's too hard!

Re:Postgres tcp/ip too difficult to configure

[Anonymous Coward]

Would you like fries with that elitism?

Seriously, are the developers of this product too lazy to generate a tool to configure the application so that people who need to get on with their jobs don't need to look up really arcane config file settings? Least of all the fact that they have two separate config files for no apparently good reason.

wal_sync_method?
cpu_tuple_cost?

Why do I care? Set them to some sane defaults and give me a tool to easily find and change the values. For bonus points, include some freaking documentation that describes what changing those values mean in laymans terms.

I guess you're one of those crowd that can't understand why everyone doesn't use Linux?

Re:Postgres tcp/ip too difficult to configure

[Anonymous Coward]

Well, Oracle isn't exactly point and click world. Comparing psql and sqlplus, anyone will take psql without thinking.

Re:Postgres tcp/ip too difficult to configure

[Doctor Memory]

Compare that to trying to create a new database:

Oracle:
- Create new directories for bdump, cdump, udump and archivelog.
- Add new files for the new tablespace(s), control and redo logs.
- Add the new SID to TNSNAMES.DAT and listener.ora
- Create the new tablespace(s)

PostgreSQL:
- "createdb "

Oracle's got PosgreSQL beat in terms of features (which, as someone else already noted, many Oracle users don't need), but I wouldn't try whining that PostgreSQL is "hard to configure" Not compared to Oracle it isn't!

Re:Postgres tcp/ip too difficult to configure

[PitaBred]

Why should it be in two separate places, though? Even if it is multiple lines, there's no good reason for configurations relating to the same thing being found in two different files.

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:Postgres tcp/ip too difficult to configure

[svendog]

I don't think it's so much a case of elitism -- it's just that not every application needs to be overburdened by a complete GUI frontend. The documentation for PostgreSQL is quite rich actually: see http://www.postgresql.org/docs/8.1/interactive/ind ex.html for the latest sets. The amount of work that goes into detailing nearly every aspect of configuring, running, and working with this database system would seem to indicate that the developers are far from lazy. Not bothering to read said documentation on the other hand ... There is also an extremely helpful set of mailing lists, wikis, and related sites (such as the General Bits site: http://www.varlena.com/varlena/GeneralBits/ ) such that keys to the "mysteries" of various parameters and how to tweak one's server for best performance are quite readily available.

Re:Postgres tcp/ip too difficult to configure

[Dan Ost]

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.

Re:Postgres tcp/ip too difficult to configure

[prell]

Every time I've tried to alter network access and authentication for PostgreSQL, it has broken. The format of the files, at least the last time I did it, possibly a year or so ago, is unclear, and altering it feels very fragile.

Re:Postgres tcp/ip too difficult to configure

[MagicM]

Oracle:
- "dbca"
- choose "Create a database"
- keep on clickin'

Re:Postgres tcp/ip too difficult to configure

[einhverfr]

PostgreSQL still has a few rough edges, but these are being worked out pretty quickly.

My main comment was....

"Right.... And Oracle is the paradigm of intuitive behavior!"

Re:Postgres tcp/ip too difficult to configure

[Anonymous Coward]

Are you really saying that having a configuration GUI that said "Allow remote access from these IPs: ____________________" would be a bad idea?

Re:Postgres tcp/ip too difficult to configure

[einhverfr]

When I first started using Samba, I found the configuration file hard to follow. I relied on tools like Swat. Then I discovered that these tools were far from transparent. It was often easier to edit the config files with a man page open as reference. I then discovered how badly Swat mutilated the files (among other things, deleting all comemnts). At that point I gave up on GUI editors for config files, as I found that what time I gained in not learning how things were working, I wasted attempting find obscure options in the GUI or Web UI.

Same thing for PostgreSQL. Web UI tools are available. Would I use them? No.

Text based config files are more transparent. It is not a matter of being elitist, but those who use them develop a clearer understanding of what a program is capable of than those who rely on GUI's and web UI's. Transparency leads to knowledge.

As for this comment:

Least of all the fact that they have two separate config files for no apparently good reason.

Except that one of the config files is just a table of hostnames/addresses, users, databases, and authentication mechanisms. Since on a large/complex system with many users and complex access policies, this table can get fairly large, I don't want it to be mixed with the main configuration file, so I am happy with the current setup.

Re:Postgres tcp/ip too difficult to configure

[budgenator]

It is according to the AOL tech support person as he started to talk me through hand editing a Hosts file using notepad.

Re:Postgres tcp/ip too difficult to configure

[grantsucceeded]

And I wonder, has he ever tried to do anything non default with oracle's equivalint: tnsnames.ora, listener.ora swlnet.ora etc.
The defaults have bad security holes, and changing things is pretty bad. They had a daemon in there who's *purpose* was to allow remote systems to execute programs without authentication for gods sake. You had to pay for encryption and it took highpriced dba to set it up (contrast with pretty simple SSL for opensource dbs)

SQLNet is *not* simple and out of the box is pretty freaking insecure.

Re:Postgres tcp/ip too difficult to configure

[Ayanami Rei]

Ahhh yes, dbca
How I loathe thee...

--which, incidentally, doesn't run on linux-x64 because of its reliance on an ancient java implementation ... ugh

Re:Postgres tcp/ip too difficult to configure

[Anonymous Coward]

I generally agree with you about GUI tools being kinda crappy, but I think that anything that can be in a GUI should be. You could have a textarea for parts that demand freeform.

I think one problem about Slashdot is that we always go to these higher-level philosopical arguments -- perhaps this way everyone can comment and contribute. GUI config vs Text config is much the same as GUI apps vs Command line. GUIs can offer hints and multichoice -- whereas text files are much more free form. The problem when I talk about it this way is that in this instance we're talking about a GUI tool for databases.. security, tables, rows, query builders... and competing databases have already shown interfaces that work well.

This isn't a theoretical problem -- the question isn't about GUI vs text files. It's about "how do we make a good interface for security?" or "Is a listbox right for this option?". These are questions that can be addressed, rather than philosphical differences.

I don't see why Slashdot has taken this conversion off into a higher realm so everyone can argue these points. It's dissapointing and it'll happen again and again.

There is PGAdmin3 (at least it was v3 last I had a look) and it does much of this. Postgres think GUI and Text is good... I don't see why slashdot wants to argue this stupid topic. Can't we please move on from simple philosophical discussions to something more relevant and useful to the topic at hand?

Re:Postgres tcp/ip too difficult to configure

[Anonymous Coward]

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

Some people can't deal with anything more complex than point and clicky. They are probably the same people that have never edited a tnsnames.ora file for Oracle either. I agree that editing a text file is not a big deal.

Re:Postgres tcp/ip too difficult to configure

[Dan Ost]

It's not a good idea unless all it does is modify the config file for you while preserving the organization and formatting of the config file. If it just appends the correct settings at the bottom of the config file, or doesn't follow currently used conventions for how the config file is organized, then it makes it makes further configuration a nightmare.

Re:Postgres tcp/ip too difficult to configure

[einhverfr]

The basic issue is this...

GUI tools are great for providing rich information to the user but not great at accepting rich information from the user. Therefore, GUI's are great for reporting tools, etc. but actually slow you down when you are trying to do anything moderately complex simply because you are limited in how quickly you can provide the information to the computer. GUI's therefore lead to less productivity and since they are less transparent (though they don't have to be), they often lead to less understanding of how a system works or what it is capable of. The transparency issue isn't really fundamental and could be solved, but the productivity one cannot be.

This is a judgement against GUI's for certain tools, not against those who use them.

Re:Postgres tcp/ip too difficult to configure

[Anonymous Coward]

I think you've missed my point.

You're taking to a higher issue of GUI philosophy and although I agree with you I think that's an unhelpful place to take this discussion.

We are still talking about a GUI for a database, and in particular whether we could have a GUI tool for adding remote access to postgres (among other security/admin tasks).

This is not a complex task and it doesn't require freeform text. I hope we can agree that this particular task does not require freeform input. It's IP addresses so it's limited to number input with IPv4 or IPv6 syntax, perhaps a subnet.

Config files have lots of boolean choices and configurations that point to particular directories (these could be as a folder chooser dialog).

We're getting specific here and it shows how silly this thread has gotten. Philosphy doesn't matter here -- a GUI can do these things. And talking about text vs form widgets ignores the task at hand which is to provide things like users, groups, and passwords which are easily represented in a GUI.

The difficulty is in breaking down the requirements of a piece of configuration and seeing whether form widgets can represent the options. No one's doing this. No one's come up with anything a GUI couldn't do they've just taken this discussion to a higher level principle so we can settle in two camps and have ridiculous arguments about the obvious pros and cons between text and form widgets.

That's unhelpful. That's the point.

Re:Postgres tcp/ip too difficult to configure

[ttfkam]

I believe the word you were looking for was paragon, not paradigm.

Re:Postgres tcp/ip too difficult to configure

[einhverfr]

We are still talking about a GUI for a database, and in particular whether we could have a GUI tool for adding remote access to postgres (among other security/admin tasks).

There are GUI's and web UI's for doing this. I don't have a problem with them. I am not as productive using them however.

This is not a complex task and it doesn't require freeform text. I hope we can agree that this particular task does not require freeform input. It's IP addresses so it's limited to number input with IPv4 or IPv6 syntax, perhaps a subnet.

It is more complex than it might initially appear because different authentication mechanisms may allow for different or even freeform options (identd being the most notable). However, for most deployments, a GUI or Web UI is sufficient for a Jr admit to be reasonably productive. For more complex deployments, I am still comfortable with using the text files.

The difficulty is in breaking down the requirements of a piece of configuration and seeing whether form widgets can represent the options. No one's doing this. No one's come up with anything a GUI couldn't do they've just taken this discussion to a higher level principle so we can settle in two camps and have ridiculous arguments about the obvious pros and cons between text and form widgets.

No, it is not. The difficulty is in being able to efficiently represent a large number of configuration options in a couple of reasonably-sized screens. For a more complex piece of software, this is not a trivial process.

Re:Postgres tcp/ip too difficult to configure

[einhverfr]

Fingers typed the wrong word. I was thinking of "paragon" :-(

Re:Postgres tcp/ip too difficult to configure

[I Like Pudding]

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.

Bullshit. All you're doing is mapping screens to a data structure, same as any gui app. The complexity of the gui will stem from how complex and machine-parsable the config file is. Thing is, writing a 100% complete GUI is just time consuming as hell compared to adding an option to a config file. This is why GUIs that only cover 75% are so common. That, and engineers generally suck at gui design.

The only config file that can't have a gui is sendmail.cf, because sendmail.cf looks like an ancient Babylonian dialect of perl encoded in utf32 viewed with cat. The gui would end up looking like a Hieronymus Bosch triptych.

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.

GUIs lower the bar because being easier and, ideally, quicker than modifying a config file are their raison d'être. Your point is a tautology: "The people who know how to configure the app are better than the people who do not". Thank you, Socrates, for pointing that one out. Guess what! The people who don't know what they are doing gravitate towards the gui because less prequisite knowledge is required to get things going. The people who do know what they are doing can use either the config file or the gui, and it is just a matter of preference at that point.

Me? I gravitate towards the config files due to ssh being a fantastic low-calorie way to get at a remote server.

Re:Postgres tcp/ip too difficult to configure

[geminidomino]

Why should it be in two separate places, though? Even if it is multiple lines, there's no good reason for configurations relating to the same thing being found in two different files.

Seperate files for seperate settings. One is configuration, the other is access control.

The same reason freebsd has /etc/rc.conf and /etc/passwd in different files.

Re:Postgres tcp/ip too difficult to configure

[ckaminski]

Philosophically and pragmatically, I am not opposed to GUI configuration utilities. What I am opposed to are GUIs that turn my beautiful text files into unreadable, undebuggable crap.

That's all. Once people have GUIs in place, I've found they tend to like moving to serializable data, or something that reduces the amount of text processing and validation they have to do and ruin the simplicity of the text config file.

I have a USB install of a rescue system that can repair my Apache/Postgres/Mysql server. I cannot necessarily say the same of my Oracle system.

A little of both?

[PCM2]

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

Re:A little of both?

[Anonymous Coward]

Why does everything have to be all or nothing?

Darn good question. I am not sure of the answer but suspect vendor FUD has alot to do with it. Vendors hate competition as it makes them have to be competative in service and price. If they know your a closed shop it gives them margins on price and service not in the customers favor. The biggest discounts I have seen in this business clearly go to mixed shows. Walk a Microsoft or Oracle salesman by a rack of Linux MySQL/Postgres servers and they do notice...

Oracle is a great database

[puppetman]

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

Re:Oracle is a great database

[Dan Ost]

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.

You make it sound like MySQL is ahead of PostgreSQL in the features department. While it is true that MySQL is currently adding features faster than PostgreSQL, it's because most of those features that MySQL has been adding have been present in PostgreSQL for years.

MySQL is largely playing catchup.

Re:Oracle is a great database

[activewire]

> MySQL is largely playing catchup.

Mod parent up! I currently use all 3 (Oracle10,Postgres8,MySQL5) on several projects.
For basic (low-complexity, low-traffic) webapps, MySQL is perfectly fine.
For unlimited budgets or >5-nines uptime, Oracle please.
Everything else (most enterprise projects I have seen) would do
well on Postgres.

Re:Oracle is a great database

[puppetman]

Yes and no.

Postgres doesn't have built-in replication, non-commercial, yet MySQL has had it since 3.x and they are on 5.x now. Yes, there is SLONY, but it's an add on.

Postgres has had views, triggers and stored procedures for a while now, and MySQL just got them in 5.0.

MySQL has clusters since 4.1, and I don't believe Postgres has anything equivilent.

Postgres just beat MySQL to data partitioning (MySQL 5.1 is in beta, but 8.1 is production I think).

MySQL had 64-bit support (I love the Opteron) since 4.0; Postgres just got it in 8.1. The ability to utilize large amounts of memory is a big deal for databases.

Ya, MySQL was missing more of the "core" features, but for us, replication and large database buffers was more important two years ago than stored procedures, triggers or views.

Re:Oracle is a great database

[kpharmer]

> Ya, MySQL was missing more of the "core" features, but for us, replication and large database
> buffers was more important two years ago than stored procedures, triggers or views.

But core comes first:
    - views have been around since about 1983 or earlier in DB2 and probably earlier than that in Oracle
    - triggers, stored procs, etc have been around for 10-15 years in most commercial products
This is all stuff that commercial products have had for decades, postgresql has had in place for years. MySQl has a *.0 release supporting it. It's essential for portability, managability and flexibility.

As for the other stuff:
    - data partitioning - we're still not talking anything like DB2, Oracle, Informix. This is union-all views or merely spreading data across containers. This is where the commercial products were in 1992.
    - clusters - mysql didn't beat postgresql to clusters, it bought a completely separate product that supports it. In memory only. Not even remotely useful to 99% of the people out there, not even remotely comparable to Oracle RAC.
    - 64-bit support - ok, that's a good thing.
    - replication - typically a nasty work-around to performance or failover. In most large database architectures it's the first thing I rip out to replace with better solutions. There are situations in which you can get forced to use it, but that's the only time I'd use it.

Re:Oracle is a great database

[einhverfr]

Postgres doesn't have built-in replication, non-commercial, yet MySQL has had it since 3.x and they are on 5.x now. Yes, there is SLONY, but it's an add on.

Do you really want to tie your replication version to your database version? As long as it is not so tied, it makes it possible to replicate between different versions of PostgreSQL (say for a zero-downtime upgrade, or a phasein of a new version) without worrying so much about forward/backward compatibility issues on the replication side.

Slony may be an add-on, but it is hardly third-party. It was written with the help of at least one of the core PostgreSQL developers.

Finally, PostgreSQL *does* have at least one replication technology, dbmirror, as a part of its contrib package which is shipped with the source.

Postgres has had views, triggers and stored procedures for a while now, and MySQL just got them in 5.0.

PostgreSQL still has more features in this area than does MySQL.
* Multiple triggers per table
* Stored procs in arbitrary languages

PostgreSQL also has roles, better foreign key support (there are some ways of adding foreign keys that MySQL 5.0 w/innodb silently drops even in strict mode).

PostgreSQL has had data partitioning possibilities since 1995, but the new check-constraint-based optimizations make it more useful in data warehousing operations.

MySQL had 64-bit support (I love the Opteron) since 4.0; Postgres just got it in 8.1. The ability to utilize large amounts of memory is a big deal for databases.

Funny, I was not aware that the Opteron was the only 64-bit chip out there. I was aware of plenty of huge DB's running on 64-bit Alphas and the like and the recommendations of 64-bit chips on large DB's was documented in PostgreSQL at least as far back as 2000.

Also, I would point out that source builds for PostgreSQL on the Opteron were shown as effective at least as of 8.0. I would not be surprised if it was not working on the platform even earlier.

The biggest issue for me prior to 5.0 was the silent truncation of *numbers* by the database. The database is your last line of defense against bad data and it should not be changing the data in order to avoid providing errors to the program. Even in 5.0, strict mode can be turned off by the client so it provides very little protection at all.

The other issue I find is that if you have a large number of database users, mysql permissions become a pain. :-) This is why roles are so useful.

MySQL is a *perfect* database for content management. But for anything where the integrity of the data matters, I am not sure I would trust it.

Also, there is a new movement in PostgreSQL to move as much functionality out of the core distribution as possible and kernelize it. Thus, for example, pl/pgsql could be upgraded without upgrading PostgreSQL. Thus many of the community projects (such as PL/J) will become increasingly important. MySQL because of their licensing system will never be able to go this direction.

Re:Oracle is a great database

[LurkerXXX]

I think you forgot a few important things.

Postgres has realized for many many years that February 31st is not a real date. MySQL only recently realized that.

Postgres has thrown errors for years if you entered out of bounds data. Until extremely recently, MySQL would happily silently change your data to something it liked. No errors, just bad data. Yummy. And it will still do that if you don't run it in 'strict' mode (not the default, except on windows). Postrges doesn't have any such setting to 'accept bad data and silenty change it'. Real databases don't.

Re:Oracle is a great database

[Anonymous Coward]

You also should notice that MySQL 3.x/4.x replication scheme usually requires that your application be engineered to run on those setups (to avoid duplication of primary keys). You also should notice that in 5.0, replication had big issues with stored procedures and triggers (as in "not replicated"), to add to quite a few nasty bugs (for more information, check changelogs from 5.0.12 up).
I do some development with MySQL (a 5.0.17 cluster) and with Postgres 8.0, and MySQL looks like a child's toy compared to Postgres. Just try on and create stored procedures in java and embbed them on your database :)

Re:Oracle is a great database

[Gorshkov]

Ya, MySQL was missing more of the "core" features, but for us, replication and large database buffers was more important two years ago than stored procedures, triggers or views.

*ahem* ...... shouldn't one of the core features be DATA #$*#@&*#@$ INTEGRITY?

Sorry. Regardless of how long the laundry-list of features the zealosts come up with, I'll stop considering MySQL a toy when a) it actually does SQL properly, and b) the data I get out of my database is the same as the data I put INTO my database

Re:Oracle is a great database

[Anonymous Coward]

MySQL had 64-bit support (I love the Opteron) since 4.0; Postgres just got it in 8.1. The ability to utilize large amounts of memory is a big deal for databases.

What a troll! PostgreSQL has been 64-bit on Solaris longer than MySQL has had mind share. This is the usual BS crap from the MySQL clueless clans. Please, bother to learn *something* about the topic you post before you post. This one is so obvious, one can only reason you entire post is nothing but troll garbage! Shesh! This garbage sounds like most of the lies and misinformation that the MySQL groups constantly pump out, I would have to guess that you work for them. Every chance they get, they pump out completely fictional articles about features or performance. Sadly, lots of people believe their garbage because the don't know any better; ye tthe mindless go on to repeat it.

Please, if you are a MySQL user, take the safe bet and assume you are completely clueless about databases in general. As such, refrain from posting unless you actually have DBA experience with a sophisticated database solution (Sybase, Oracle, PostgreSQL, Informix, DB2, Ingress, MSSQL Server, heck, even firebird, etc.). I'm sorry, but MySQL is still a toy...sure, it's catching up with the low hanging fruit, which it should of had some three or fours years ago...but it's still a toy. Worse, you are clueless about what the competition actually provides. This is one more excellent example of how scared the MySQL developers and fan-boys are of projects like PostgreSQL. In a nut shell, if their user base realizes what a real RDBMS looks like, they'll loser their customer base...especially when a better, freebie exists right around the corner.

Re:Oracle is a great database

[Jakeypants]

That's all well and good, but MySQL isn't a good choice for large systems. A few reasons off the top of my head:

• Its processing of subqueries sucks. Most database systems can handle a SELECT * FROM X WHERE Y IN (SELECT Y FROM Z) gracefully. It'll execute the subquery once, then evaluate the... uh... supquery(?) using the subquery data. In MySQL's case, it'll evaluate SELECT Y FROM Z for every row that it evaluates the SELECT * FROM X part. I can't count how many times this has frozen our server, because we're used to databases that can handle this much better.
• It allows invalid data to be added. You can, for example, enter a date of 2006-02-31. Sure, there are people that will say your program logic should validate the data before it goes in to the DB, which is true, but the database SHOULD reject data that are absolutely invalid.
• MySQL's handling of foreign keys (at least in 4.1, I haven't tried 5.0) sucks. I was adding foreign keys to tables, which required me to change the table driver. Once I changed the table driver, though, I lost the ability to create fulltext indexes, which I needed because the tables were to be searchable. I shouldn't have to choose between referrential integrity or fulltext indexes, I should have both.

I'm signing my karma death warrant here, but MySQL isn't good for production systems yet.

Re:Oracle is a great database

[Anonymous Coward]

1: Never use a subquery when a plain old join will do. "SELECT DISTINCT x.* FROM z JOIN x ON z.y = x.y" does the same thing and a lot faster. (The DISTINCT is extraneous if y is unique in both tables.)

2: Not if you enable strict mode.

mysql> create table t (d date);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values ('2006-02-31');
ERROR 1292 (22007): Incorrect date value: '2006-02-31' for column 'd' at row 1


3: Fulltext indexes in InnoDB are being worked on.

mysql/postgres comparisons

[kz45]

http://www.whenpenguinsattack.com/2006/02/mysql-vs -postgres.html

and a speed test of various databases

http://www.whenpenguinsattack.com/2006/03/10-datab ase-speed-tests.html

The advantage of Open Source

[ksp]

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.

Re:The advantage of Open Source

[Anonymous Coward]

FUD, pure FUD.

Oracle is very backwardly compatible. You can take old clients that access a newer version of an Oracle database just fine. That is assuming you aren't using a datatype that the old version doesn't understand. (eg timestamp) But that would be silly to add a datatype to your system that makes it incompatible with what you are using currently. (Oracle is knwn for keeping old datatypes around for a long long time. In the past 20 years I can't think of a datatype they dropped.)

I have been places where they have moved the backend to different operating systems (to or from windows to or from netware or to or from Unix ) and the client didn't have to change anything. (other than your database is on this machine vs that machine)

I don't have a lot of experience with Postgres and my assumption is that what client and what OS server the Postgres db runs on isn't really relevant either. (I would expect that the functionality would be the same and the client wouldn't care.)

Clearly you haven't a clue about Oracle or you are just an uninformed troll.

Re:The advantage of Open Source

[beacher]

I really don't know where to begin on this one.

"I can clean up corruptions or load old backups because I know exactly how the server reads the data."
You need take a serious look at you backup and recovery plans. The last thing I would ever want to hear is that a DBA is using khexedit on raw database files.

"Database servers just keep running on some server and are forgotten"- Sure fire path to disaster and reinforces my first point.

"If you run an ancient version of Oracle, you are stuffed"
On a 9i to 10g migration, one of the options is to just upgrade the instance directly. Anyone that has ever done or heard of someone upgrading Windows versions will tell you that you should nuke the disk and do a clean install. In the case with Oracle this is handled through exp(ort)/imp(ort).

If your database is critical to your business, you should be taking much better care of it PERIOD. Open Source or Proprietary, it should be periodically maintained.

Re:The advantage of Open Source

[Roadmaster]

"The last thing I would ever want to hear is that a DBA is using khexedit on raw database files."

Wimps! real DBAs use cat!!

DBA Comparisions - Oracle vs. PostgreSQL

[Anonymous Coward]

If they want something that plays nice with Oracle, they should take a look at http://www.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?

Re:DBA Comparisions - Oracle vs. PostgreSQL

[rjstanford]

Its funny - we had the same experience with Informix. My last company was responsible for about 150 critical production Informix instances, plus about 25 in-house development ones, and handled the load with 2.5 DBAs who spent most of their time tweaking for peak performance, or doing upgrades, et cetera. When we brought on an Oracle product that ratio dropped to more like 1 DBA for every 10 instances.

Then again, Oracle is the same company that sells a multiDollar enterprise product without a hot backup script... If you were a database consulting services company, which would you recommend?

Re:DBA Comparisions - Oracle vs. PostgreSQL

[kkrause]

"without a hot backup script?"....ever heard of RMAN? bundled right in there for your added pleasure.

Re:DBA Comparisions - Oracle vs. PostgreSQL

[HappyDrgn]

"Then again, Oracle is the same company that sells a multiDollar enterprise product without a hot backup script"
 
Why would you need "hot backup" when you have RMAN backups with redo and archive logs? Simply rman restore and apply archive logs to the current time, and you have yourself an identical backup with out having to copy live datafiles that are being written to. Even on MySQL, why do those scripts even exist when you have backups and bin logs? This hot backup thing never made sense to me, it seems, well... hokey... and totally avoidable with a proper backup or replication policy.

Re:DBA Comparisions - Oracle vs. PostgreSQL

[tweek]

Interestingly enough, you'll have the same results when putting out an ad for a DB2 UDB dba. These people get somewhere and stay there or they're all consultants. We search 6 months for a full-time DB2 DBA and just hired one a month ago.

In our environment, we've basically broken it down to DB2 DBA and SQL DBA (which is the dumbest thing I've heard of).

The DB2 dba handles all the DB2 servers and the SQL dba handles EVERYTHING else (mssql, postgresql and mssql).

Re:DBA Comparisions - Oracle vs. PostgreSQL

[einhverfr]

If you were a database consulting services company, which would you recommend?

We are, and we recommend PostgreSQL. What good is it to us if our customers are paying lots of money to Oracle, Microsoft, or IBM? We can make up for a few missing features here or there for most deployments (those that don't depend on intraquery parallelism for performance) with some extra services. Customers save money, and more importantly, we make more money.

Some people are shortsighted and let their vertical compliments (like Oracle) bleed their customers dry....

Re:DBA Comparisions - Oracle vs. PostgreSQL

[rjstanford]

The last time that I used Oracle directly, which was admittedly a few years ago, RMAN was not considered a good solution. It was only bundled with Oracle starting with the 8i series, and was possibly not quite ready for prime time then.

Even now, the 10g RMAN page is less than optimistic about previous versions:

RMAN becomes more powerful with a redesigned incremental backup scheme, offline recovery of incremental backups, previewing restore, recovering through resetlogs, file compression, and much more.

Most people would agree that RMAN is the de facto tool of choice for Oracle database backup. But as powerful as they were, early versions of RMAN left something to be desired. Like many DBAs, I had pet peeves about the absence of what I consider to be must-have features. ...

So why do many DBAs do incremental backups only rarely? One reason is that in Oracle9i and below, RMAN scans all the data blocks to identify candidates for backup. This process puts so much stress on the system that doing incrementals becomes impractical.

It also didn't use to be able to do things like backup to tape (ooh) without external tools from people like Legato. And heck, there's a whole series of books about how to use it effectively - if that isn't the sign of a poorly designed tool, well... Heck, the fact that a simple google search will show that a lot of people were (and still are) using filesystem backups indicates a severe (if historical) weakness in provided backup tools. Contrast this to most other enterprise vendors, where doing a backup is as simple as saying, "Backup to this [tape|disk] device, and make it a level [0|1|2] incremental backup."

Re:DBA Comparisions - Oracle vs. PostgreSQL

[tweek]

Right now the parallelism is killing us. It does us no good to move to a bigger box because our warehouse loads have to be ordered a certain way. About the only thing more CPUs buys is the one rougue query done by a stupid user who thinks they know SQL.

I'm actually really interested in the stuff Greenplum is doing.

One thing we're really looking at from 8.1 is the bitmapped indices and the table partitioning.

Re:DBA Comparisions - Oracle vs. PostgreSQL

[Anonymous Coward]

Same here. We have several dozens of DBs at work. Lots of SQL Server, a fair amount of PostgreSQL, and a new MySQL. We have one full time DBA that covers pretty much all of that (programmers of the applications using them can handle most of the job easily, the DBA does the optimizing/avanced stuff/oversees everything/etc).

And then we got one app that needs Oracle... The thing has a 5 people team to look after it (including a highly paid senior DBA). The thing needs constant maintenance/patches/stuff seemingly never works right (dependacy problems, stuff that breaks, downtime, etc). They've had to get Oracle support over to fix things sometimes, and to call numerous times. Everybody's sick of it. It's a nightmare. Not only it was the most expensive DB we have, in terms of licensing (more expensive than SQL Server Enterprise Ed/PostgreSQL/MySQL - even DB2 is cheaper but we don't use it), but even moreso in terms of maintenance/emplyee costs. It's like a black hole that's sucking all your money (sucking is seemingly the only thing it's good at).

The decision has been taken that next time anything bad happens (and that's only a matter of time), the programmers who made it will be terminated, and the whole thing is getting re-written completely. From scratch. Likely C#/.NET 2.0 using LLBLGEN Pro and running on SQL Server (stable, quick dev using good tools, cheap app server, etc). This thing does nothing special/fancy. It's just too goddamn expensive and a nightmare all-around. It doesn't need Oracle at all. It was just some idiot's decision back when it was just a small project (the guy knew nothing of DBs and just assumed Oracle is always the best choice). The Oracle support team is basically already looking for a new job. We'll be saving a TON of money.

Re:DBA Comparisions - Oracle vs. PostgreSQL

[einhverfr]

Yeah, parallelism is the biggest issue with datawarehousing deployments. In the future, I would expect that Bizgres MPP should solve this problem but it is still vaporware to my knowledge.

This is the one area where I don't recommend PostgreSQL at the moment.

Re:DBA Comparisions - Oracle vs. PostgreSQL

[dodobh]

Put out an ad for a DBA who understands the basics. Ask on the PostgreSQL user lists. Oh, and Oracle DBAs can easily move to PostgreSQL.

What did I miss?

[Anonymous Coward]

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

I can see how Oracle must be dying to take advantage of such an opportunity in much the same way Microsoft jumps at the chance to inter-operate with something like Linux.

In other news a sarcastic posting to /. just took place.

Re:What did I miss?

[einhverfr]

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:MOD PARENT/REPLIES DOWN,THIS POST UP,FIRST POST

[chrismcdirty]

Of course that's all he has to offer. He's pretty much a spambot to plug the software he's working on. Thank god someone else finally realized that. Now if only the mods could realize it, too.

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:Oracle Database 10g Express Edition for free

[Anonymous Coward]

It is absolutely free. It has certain size-restrictions

A.K.A. crippleware.

Re:Oracle Database 10g Express Edition for free

[einhverfr]

I installed it once. Hated it. Uninstalled it.

It is a bloated piece of crap that seems like a full-blown Oracle installation with additional code included to add these checks, and less documentation.

200MB download? And it doesn't even include the JRE in the DB Server?

PostgreSQL is about 10% of that and has more features. Add the JRE and PL/J and you have maybe half that.

Replication on PG is no good

[ashpool7]

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

[Anonymous Coward]

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.

Wow. C. J. Date, _Introduction to Database Systems_, 8th edition. Don't apply for a job with my company until you've read it and understand what is fundamentally wrong with your statement above.

Re:Replication on PG is no good

[MagicMerlin]

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.

you have been nominated for dbdebunk quote of the week. congratulations!

Re:Replication on PG is no good

[ashpool7]

How about "I have tables that don't have DEFINED primary keys"

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.

Re:Replication on PG is no good

[Anonymous Coward]

I have tables that don't have primary keys and I don't want to ever have them.

Time to brush up on your relational theory? Maybe you mean "I have tables without indexes defined on the primary keys".... actually, I have no idea what you mean. What do these tables look like?

[I assume you know that keys are *logical* (i.e., part of the logical model) constructs, while indexes and so forth are *physical*.. of course SQL mixes this stuff up all the time.]

Re:Replication on PG is no good

[rtaylor]

I have tables that don't have primary keys ... I've normalized my DB

Which is it?

If you have two exactly duplicated tuples, how do you delete or update one without impacting the other?

Heck, even my audit or log structures get a transaction id or timestamp.

Re:Replication on PG is no good

[doghouse41]

The same restrictions apply to SQL Server replication of database tables.

I think you are missing something fairly fundamental if you don't understand why you need a primary key on a database table in order to replicate it. How else do you know which row to update or delete? How do you handle multi-master replication?
How do you deal with otherwise identical rows added to two replicated databases?

Re:Replication on PG is no good

[duffer_01]

You should look at SQL Remote from iAnywhere. Their replication does not require Primary keys and the replication is built right into the database.

Difficult, no

[lorcha]

But unintuitive? Yes. And of course, Goulet said the process was not "intuitive". Nobody ever said it was difficult.

Think of it like exiting vi. It is not difficult to type :q! but it is hardly intuitive. Same with enabling TCP/IP connections in postgres. If it were a one-liner in postgres.conf, then fine. But also pg_hba.conf? What the fuck does pg_hba mean? I don't know what hba stands for. How do I know to go looking there? I don't, because it's unintuitive.

Re:Difficult, no

[LWATCDR]

"But also pg_hba.conf? What the fuck does pg_hba mean? I don't know what hba stands for. How do I know to go looking there? I don't, because it's unintuitive."

Postgres is a SQL DATABASE SERVER! it is designed to be installed by people that know what they are doing and can READ.
That is clearly documented in the manual for setting up postgres. You only do it once in the lifetime of the server.
That is like saying "winning the 100 million dollars was great but they wanted me to drive all the way to there office to pick up the check!"

If that is the worst problem with Postgres then it is the best database server in the world!
BTW Postgres was the first SQL server I ever did an install for myself. I was not and still do not consider myself a Linux expert or a Postgres expert. However I found it not that hard to install and get working including getting the TCP/IP connections working.

Re:Difficult, no

[hclyff]

If you have to go through documentation, it's not intuitive.

Intuitive means you can use your intuition and get it work, as opposed to read the manual to get it work.

Re:Difficult, no

[MagicMerlin]

holy cow that was funny.

anyways, pg 8.2 will is going to have pg_hba implemented as a table inside the databse instead of .conf file.

Re:Difficult, no

[tanguyr]

Postgres is a SQL DATABASE SERVER! it is designed to be installed by people that know what they are doing and can READ.

LMAO! Most excellent comment - put that on a t-shirt.

Re:Difficult, no

[kbob88]

> What the fuck does pg_hba mean? I don't know what hba stands for.

Umm... this one is tough... If you'd RTFM, you'd see from the *2nd* sentence of the documentation section on the pg_hba.conf file, under the Client Authentication chapter:

"(HBA stands for host-based authentication.)"

I think even you can guess what the pg means.

Re:Difficult, no

[lorcha]

That is clearly documented in the manual for setting up postgres.

:q! is clearly documented in the vi manual as well, but I still say it's unintuitive.

Again, nobody said it was difficult. Just unintuitive.

And for the record, the worst problem with Postgres is that transaction IDs can wraparound, causing massive data loss if you don't vacuum frequently enough. Massive data loss, medium data loss, and even minute data loss should never happen with an RDBMS. It is currently 2006. When I put data in the database, I expect it to just be there. And always be there. Until I delete it. Always.

Re:Difficult, no

[lorcha]

If the average person can't intuit it, then it is unintuitive. Once you whip out the documentation, we are no longer talking about intuition.

It was a fucking rhetorical question, anyway.

Re:Difficult, no

[Doctor Memory]

:q! is clearly documented in the vi manual as well, but I still say it's unintuitive.

Dunno, it's more intuitive than "ZZ" (which is what I tend to use).

Re:Difficult, no

[GnuDiff]

AFAI remember PG does automatic periodic vacuums now since 8.x

Re:Difficult, no

[arkanes]

There's no such thing as intuition. Something is intuitive if you already know it, and it's not otherwise. Try installing and creating databases in Oracle without reading any documentation and no previous experience to draw on some time, I'd like to hear how that goes for you.

Re:Difficult, no

[budgenator]

vi! spoild young whippersnapper, real unix men use ed

Oracle is a great database (for billionaires)

[Peter Mork]

Thank you for stating what should be painfully obvious. By the time MySQL had gotten around to supporting nested queries and CREATE VIEW I had long since written it off as a toy (one that wasn't even fun to play with).

Of course, to get back on topic, the article discusses a comparison of Postgres and Oracle. Until this week I never saw any need to use Oracle. But, they now have native RDF-store and some support for graph queries. Larry is still a colassal a$$, but I am now forced to concede the existence of some smart developers at Oracle.

Sigh!

I can pronounce Oracle

[Chapter80]

Ever try to sell your client (or management) on using a database whose name they can't pronounce?

Post gre ess que ell
Post gres SQL
Post gres QL

...heard it all these ways.

Re:I can pronounce Oracle

[he-sk]

how about... postgres?

Re:I can pronounce Oracle

[ashridah]

Well, can't be worse than SQL server.
I hear that one as Sequel-server from suits on a regular basis.
All I can think of is "sequel to what?"

ash

Re:I can pronounce Oracle

[Anne Thwacks]

SQL is *NOT* Sequel. Sequel was IBM's product prior to SQL, which is an ANSI/ISO standard.

(Actually there are several SQL standards... As in "We've upped our standards, so UP YOURS!")

Re:I can pronounce Oracle

[ashridah]

Sorry, wasn't trying to imply that it *was* Sequel.

That's just how I keep hearing suits refer to MS's SQL server. I do recall that there really was a product called "Sequel" but I do admit that I had no idea it was pre-SQL.

oracle's rich feature

[dubbreak]

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

Not Typed?

[Anonymous Coward]

Stored procedure parameters are not typed

Ah, so that's why my postgres function definitions look like: create function foo(int, bigint) returns bigint

Only one conclusion one can draw

[RLiegh]

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

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.

Re:Only one conclusion one can draw

[Zerbs]

the only real contender is SQL Server

I haven't had a chance to use 2005 yet, anyone know if Microsoft has changed their trigger behavior so that you can actually do trigger processing at row level or statement level, instead of forcing everything to be statement level like in SQL Server 2000?

Re:Only one conclusion one can draw

[Anonymous Coward]

fucking christ. No one cares about your fucking site. How does this spam constantly get modded high enough for me to see it?

Re:Only one conclusion one can draw

[Anonymous Coward]

Which site?

Re:Only one conclusion one can draw

[Anonymous Coward]

ingres.com

Re:Only one conclusion one can draw

[Anonymous Coward]

LOL LOL LOL

Good one. "SQL Server" and "Enterprise" in the same sentence.

Availability of Source Code? Does it Matter?

[Cranky Weasel]

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

Re:Availability of Source Code? Does it Matter?

[bloodnok]

Yes, access to the source matters. Not because you want to hack it, but because you may want to put a debugger on it to track down a problem. With Oracle you don't have that option.

Instead you call Oracle support who ask a lot of questions. You answer the questions, they ask for a database dump. You spend time organising this only to be told that it's too big for them to deal with. You demand help. They ask more questions. You get nowhere.

I have been through this process many times. I have been an Oracle DBA since version 7, and a developer since version 5. I much prefer postgres. When things break, I can track down the problem myself. I probably can't fix it but I can provide useful information to those that can.

That is worth so much more to me than the ability to talk to an 'expert' at Oracle support.

Re:Availability of Source Code? Does it Matter?

[Senzei]

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.

I would guess that this is more a theoretical reassurance than an in-practice feature. Theoretically if the db broke and I had absolutely no other recourse I could debug it myself. In reality there are a lot of other options to fix the situation faster, but having that last resort available is nice.

Re:Availability of Source Code? Does it Matter?

[tweek]

There are also SEVERAL support companies who CAN understand the code (many of whom are PGSQL contributers) and fix it for you.

If this is really an issue (to the GP), they should purchase a support contract and/or buy one of the commercial builds of PGSQL (Greenplum, Pervasive, Mammoth).

I honestly don't know many DBAs myself who could fix the actual source itself.

Re:Availability of Source Code? Does it Matter?

[Anonymous Coward]

Reading the Oracle source is kind of like watching how they make sausage -- once you've done it, you'll never want to use Oracle again!

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

Re:Availability of Source Code? Does it Matter?

[Anonymous Coward]

Me! At my last job (a small VoIP firm) I hunted and contributed patches for result oid bugs and worked with the pg guys to track down some pretty gnarly issues around timezones will full names. Both these patches are in pg 8.1.

Source matters when you're doing anything leading edge. Pushing the envelope on any db, even oracle, is gonna introduce you to vendor bugs.

$0.02

Re:Availability of Source Code? Does it Matter?

[Procyon101]

Yes I've done this, in production. I had a MySQL server that would bomb out occassionally on a weird query. I found the bug, patched it (It was pretty trivial) and reported the bug and patch to MySQL.

I've also had MSSQL server go balls up when I worked at Microsoft, fired up the debugger, found the problem, reported it... Of course, I'm not going to rebuild SQL Server... that's a 2 week long excersize in pain.

Re:Availability of Source Code? Does it Matter?

[Anonymous Coward]

OK Oracle boy. This isn't as suprising as it seems. We run a large ERP-CRM on top of postgres, and we have on many occassions gone into no less than:

1) The ERP-CRM code
2) postgres
3) apache plug in code
4) and the kernel

Why? To fix the problem NOW you jackass. Just because youre too damn clueless doesnt mean the rest of us are. Now go pick up a phone and beg your vendor to help you. Beg like the jackass you are.

Re:Availability of Source Code? Does it Matter?

[Foresto]

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.

I can't say I debugged postgres, but I did read parts of the source to get a detailed explanation for some of its behavior. And yes, it was part of my job. When you have to give guarantees about the behavior of your systems, there is no substitute for reading the source code.

Re:Availability of Source Code? Does it Matter?

[syousef]

Your argument is that since you and your colleagues work in an environment where you have the time to fix this, no one else could possibly.

Well here's a few reality checks for you:

1) People will do amazing things in their spare time. Some of these people would only be to glad if someone would hire them to do some work on the side. Not all are basement bound computer nerds. The world's a big place.

As just one example, I'm constantly amazed at the complexity of Microsoft Flight Simulator aircraft that are put out as freeware. Hundreds of Gigabytes of freeware out there - some of it outstanding quality that's much better than the default Microsoft planes and very comparable to payware. We're talking hundreds or thousands of man hours on models, flight dynamics gauges, research etc. so yes this is a game but this is not trivial stuff.

If someone thinks its worth their spare time they make time to do it.

2) If the source code is available at the very least it can be audited by security experts etc. Often if the software is well written small tweaks which are impossible to make on closed source are easy with open source and won't take that much time. If it's a vital feature you happen to need it's much better to spend this time than try to beg a vendor to do it.

RMS is a bit on the fringe but if you've ever heard him speak about what got him started with his open source crusade, it was that he was refused access to the source code for a printer driver that had a bug and which he needed to fix. Basically there are very good reasons for wanting to have the source code to software you haven't written, but it's rarely that you want to use it as a basis for some kind of redesign or enhancement of the product.

Re:Availability of Source Code? Does it Matter?

[Cow Herd (Anonymous)]

RMS is a bit on the fringe but if you've ever heard him speak about what got him started with his open source crusade, it was that he was refused access to the source code for a printer driver that had a bug and which he needed to fix.

I've never heard him speak, therefore the printer driver bug was not what got him started on his open source crusade.

On a more serious note, RMS has never been on an open source crusade. He has, however, long been on a Free Software crusade.

In other news...

[adolfojp]

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

No oracle for FreeBSD

[Anonymous Coward]

As long as that situation exists, there is 0% chance of them being used on the platform we use.

PostgreSQL does run on FreeBSD however. And every day that passes without Oracle makes it eaiser to say 'We do not need Oracle'.

Re:No oracle for FreeBSD

[Anne Thwacks]

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.

Re:Comparisons

[Anonymous Crowhead]

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

postGRES

[Anonymous Coward]

you do realize that postgres is the successor to ingres, right? (hence the post-)

Re:postGRES

[Anonymous Coward]

Fork. The word is fork.

Re:postGRES

[WindBourne]

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

Re:postGRES

[Anonymous Coward]

Some might say that it is a regress, but I digress.

Anyone tried clustering postgres w/ slony?

[Serveert]

I want to do this, anyone tried this yet?

Re:Comparisons

[Anonymous Coward]

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

Funny

[ashpool7]

I suppose you could define the primary key as the order_id and item_id. In which case, I'd have to defer to not wanting to use something that ran on top of the database.

Re:Funny

[Anonymous Coward]

What if someone wants to order two of the same item?

I'm not convinced...

[C10H14N2]

...that ANYONE /needs/ Oracle.

I will accept that someone would need something like Oracle Financials and that would be contingent upon using the Oracle database, but structurally speaking, why is it necessary for anything in particular? I mean, cripes, GOOGLE uses MySql. If THEY don't need Oracle, who the hell does?

I've run into this before trying to sell a TINY Division on using MySQL or PostgreSQL--every single !#!#%ing engineer said the same thing: we don't need _anything_ beyond MySQL, hell PostgreSQL is even overkill, so let's use it. Absolutely not, was management's response no matter how many high-profile case studies we threw at them. It was as if the #1 requirement was "Must cost at least $57,000, but list for $75k, so the purchasing manager can get a nice fat bonus for 'saving' money."

Seriously, I'd like someone to explain what precisely about Oracle could ever be considered absolutely necessary that cannot be found anywhere else aside from organizational bias and insipid politics.

Re:I'm not convinced...

[Anonymous Coward]

Google uses a distributed cluster approach, hardly Oracle's area. Perhaps you've never worked with big datasets, but when we're talking about several TiB of data MySQL is a complete joke and PostgreSQL can't probably handle it. Big iron (read: Sun or IBM) + Oracle is the only way to do it.

Re:I'm not convinced...

[RicRoc]

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

[OpieTaylor]

My company has a requirement to do text search on binary documents stored in the database (currently Sybase).

What other DBMSs besides Oracle and SQL Server can do this?

I looked at MySQL and briefly at Postgresql, and no dice.

I'm just saying that maybe your database choice ought to depend on your requirements.

Re:I'm not convinced...

[MadMidnightBomber]

It was as if the #1 requirement was "Must cost at least $57,000, but list for $75k, so the purchasing manager can get a nice fat bonus for 'saving' money."

OK, I'll list postgres for $75K, but I'll let you have it for the low, low price of $10K, and if you call in the next 10 minutes, I'll throw in a copy of 'Practical PostgreSQL' and a 100-user licence for free!

Re:I'm not convinced...

[rjstanford]

Informix can - in fact, you can write datablades for any binary data even if they're not already available. I believe that DB2 can as well. But yeah, they're both non-free.

Re:I'm not convinced...

[kv9]

Google uses a distributed cluster approach, hardly Oracle's area.

what about AdWords?

Re:I'm not convinced...

[gral]

Yep, that is what I say about OpenOffice.org as well. The problem is that it is free. Some people rate the value of a product based SOLELY on how much they had to spend for it.

Sell OOo for $500 dollars. Provide training for $3000 per person. I don't care.

Re:I'm not convinced...

[LurkerXXX]

"I mean, cripes, GOOGLE uses MySql. If THEY don't need Oracle, who the hell does?"

If google's database gets corrupted, someone doesn't get back a few hits on a search. No big deal.

If your financial warehouse gets corruption in it, someone could end up losing a ton of money. That's a hugeg deal. Vastly different levels of confidence are needed in the data.

Not many places that really rely on the quality of their data are going to use a database system with a long history as accepting "February 31st" as a real date.

Re:I'm not convinced...

[jbolden]

Seriously, I'd like someone to explain what precisely about Oracle could ever be considered absolutely necessary that cannot be found anywhere else aside from organizational bias and insipid politics.

Here are a few answers:

1) Oracle allows you to tune types of transactions very heavily. For example some tables or transaction types can log while other do not.

2) Log miner combined with archiver allow you to generate activity reports offline

3) Complex partition tables with partition indexes

4) Grid databases so you can spread a consistent database over hundreds of servers

5) Really good ISAM compatibility on their mainframe versions so that legacy cobol code will work against semi-relational structures

Re:I'm not convinced...

[grantsucceeded]

Most applications do not need oracle.

Things oracle can do, that pg and mysql can't, include the below, but they are only truly needed in extreme cases

- massive IO thruput for datawarehouses, overlapping low end teradata. Many people call a pair of 250G drives a datawarehouse because it's .5T, but I'm talking reading sorting and analyzing say 10T of data in about a half an hour on just a few xeon boxes and a few FC arrays. It's a $200k system, but even on the same $200k of harddware mysql or pg will only push probably a few hundred megabytes/sec of the IO, meaning the same query *if* you managed to write it and parallelize it yourself on a distributed system of mysql or postres, since these would not present a single system image as RAC does. But hey, who *really* needs to to 10 gigabytes/sec of IO anyway?

- Very high availability: Not "I want my blog up 100% of the time" availability, but "we loose $250 per second that my website is down" availability. oracle is not the best here but is better than mysql or pg because of serviceability features, not because it doesn't have any bugs. e.g. rebuild index with now interruption, fix block corruption with no interruption etc. But then most of the real work for high availability is in the application anyway. but still you'll want the visibility and availability features the oracle backend offers.

Re:I'm not convinced...

[Jason Earl]

Personally, I've seen expensive solutions get undercut by commodity solutions too many times to get comfortable with that line of reasoning. Heck, we've all seen how Windows and later Linux have thrown the server operating system business on its head. At some point businesses invariably start wondering why they are paying so much for Oracle and Oracle talent when their competitors are getting the same job done with PostgreSQL.

Re:I'm not convinced...

[jdew]

The solution to this is really simple. A text column with a tsearch2 index on it with the contents of the file, generated at insert time via whatever tools are needed to pull the data out. ghostscript, whatever.

And a bytea column for the datafile itself.

This is the solution I use on postgresql, and it's working just fine :)

I'm of the opinion it's easier to deal with then the CTX stuff I used on Oracle 8i. And I'm not limited to the filetypes that CTX supports.

Re:I'm not convinced...

[shutdown -p now]

Big iron (read: Sun or IBM) + Oracle is the only way to do it.

*cough* DB2? *cough*

Re:I'm not convinced...

[Nefarious Wheel]

The software is valueable (sic), so the people who work with it are valueable

I've got mod points, but I'm not going to use them here because there is no category for "Cynical" which this post would most assuredly be modded up for.

However, he's mostly right. My father used to say when trying to sell an ugly piece of jewelry "If a piece doesn't sell, keep raising the price until it does". Worked for him.

Re:I'm not convinced...

[el_womble]

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:I'm not convinced...

[mdecarle]

You do good work man! I love OOo!

The problem is, everyone I talk about how good OOo is keeps looking at me with a "yeah, but it surely won't be compatible with MS Office". Even after I gave them a text or spreadsheet. "Try it!" makes them say: "Such a big download! Install it on my PC?". But they install small crap every day.

Same thing with The Gimp: I see people use illegal copies of Photoshop, or -even worse- buy that thing, while they never use any of the special features that The Gimp doesn't have. But again: "Yeah, but PS has more functionality, right? Oh, and that interface." is what they think.

Repeat with PostGeSQL, Linux (all flavors: "yeah, but I won't compatible with Windows?"), ...

Oh, well.

Re:I'm not convinced...

[indifferent children]

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.

I'm glad that raw leather is so expensive. That makes my salary look small in comparison. And our buggy-whip sales have been going up, up, up! I am so set for life.

Re:I'm not convinced...

[jedidiah]

The cost of Oracle varies based on what you need to do with it. The 'real' version of it can be had for as cheap as a desktop office suite typically goes for. A platform review might not be what's in order. You might need to simply re-evaluate how you're buying the product.

I worked for a dot.com that bought 3 MILLION in Oracle licenses they never used and never needed. Make sure that the person doing the buying just isn't playing bigshot or somesuch.

Oracle has had alternate versions and alternate licencing models since before any of these gratis-ware databases had any traction.

Re:I'm not convinced...

[theonetruekeebler]

As has been pointed out elsewhere, a DB crash at Google means little: somebody's web page doesn't get found, or it gets found in eight seconds instead of two. Hardly a disaster.

The Google search engine runs very simple SQL statements. When it comes to very simple statements, MySQL is blindingly fast. Plus, since it's open source, it's very easy for them to customize it to their rather specific needs. Oracle does not provide this option.

But you can bet your favourite shoes they don't use it for their financials and HR. In those arenas corporate officers go to jail when database transactions go wrong. Multibillion dollar corporations don't entrust that to some homebrewed system. They entrust it to known and trustworthy products from companies with decades of application-specific experience.

Oracle is heavyweight. It handles insanely complex transactions applied to insanely huge datasets distributed over multiple, asymmetrical instances in multiple locations. It handles it correctly, and in the hands of a skilled DBA, it handles it fast. It can be tuned in very subtle ways. It complies with standards. It has its own JVM. It runs on everything from laptops to mainframes whose footprint is measured in acres. It has a "trusted" variant where even the DBA and SA can't snoop on the data. It has replication and failover. It's trustworthy in environments where five seconds of downtime per year is unacceptable.

When you absolutely must not screw up your data and your transactions, it's the product to go to. Yes, many or most managers who invest in Oracle are overbuying. Corporate politics require them to act like their project is that important. And so what? Oracle's a known quantity. The rest of the company runs on Oracle, and if their DBA is out sick, there are two more on every floor of the building.

Re:I'm not convinced...

[Dukhat]

You can always solve a problem multiple ways. Depending on the project, the costs involved solving it the hard way may be greater than the costs of paying for licensing if the added features save you enough time.

I've migrated postgres from 6.something to 7.1, then to 7.2, then to 7.4, and now we are looking to move to 8.1 to solve some performance problems. With 8.1 we are getting closer and closer to having everything we need, but I have to admit that I am sick of upgrading my database over and over again to get new features.

We migrated to 7.1 to get referential integrity.
We migrated to 7.2 to get the new light-weight VACUUM which doesn't lock the entire table.
We even did an emergency upgrade from 7.2.3 to 7.2.4 to fix a problem where postgres would crash several times a day when the load was at its peak.
We migrated to 7.4 which improved performance dramatically, primarily because postgres' optimizer finally would re-order outer joins.
Now, we are migrating to 8.1, since it finally fixes the problem with referential integrity blocking crud statements if another crud statement is referencing the same row. This can be a big performance problem if you have a foreign key to a table with three rows, so you can have at most three statements updating a table referencing the table with three rows.

For more information on the referential integrity row locking problem that is fixed in 8.1:
http://archives.postgresql.org/pgsql-docs/2005-07/ msg00022.php

Postgres 8.1 will also allow us to maintain an up-to-date backup with point-in-time recovery:
http://www.postgresql.org/docs/8.0/interactive/bac kup-online.html

How often do you have to upgrade oracle, and how often do you have to dump all of your data and restore it?

I am really sick of upgrading, and I think there are still a few features that we could benefit from down the road. I'm still uneasy about replication in postgres, and postgres still doesn't have binary backups, so I think a full dump and restore takes much longer than necessary.

People think it is a waste of time to learn how to administer Oracle. What about all the time spent updating and testing our application for each new version of postgres? If our application didn't need to run 24/7, maybe all these upgrades wouldn't be so stressful and wouldn't require such extensive testing.

Re:I'm not convinced...

[rvw14]

The biggest problem with The Gimp is the name. It is really hard to sell your boss on a program with this name.

Re:I'm not convinced...

[aevans]

when was the last time a corporate officer went to jail for anything other than percieved ties with Republicans?

Clustering

[mislam]

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?

Re:Clustering

[tweek]

I feel your pain. The next project I have in our DR process is getting our warehouse PGSQL replicated offsite. Right now I think the easiest way to do this is going to be to use drbd and mirror the disks over the WAN. I don't like it but I just don't feel comfortable with Slony-I yet.

IIRC, they are trying to get Slony integrated into the mainline build. The nice thing about MySQL replication is that it doesn't require any schema changes and replicates objects as well as data.

Re:Clustering

[bloodnok]

Slony-I is intended to be an add-on to postgres and not part of the core. The reason for this is that slony upgrades are supposed to be separate from database upgrades. This allows you to use switchover to minimise the downtime of an upgrade. To upgrade from postgres 8.0 to 8.1 (currently running slony 1.1.2 on all databases, I think), we will be doing the following:

Upgrade to slony 1.1.5 at all nodes simultaneously (should require no more than 5 minutes of slony outage). This does not affect the production database at all.

Wait for slony to catch back up.

Pause replication, again not affecting production.

Upgrade subscriber database to 8.1 (full dump and restore required, or just drop the subscriber db, attach a new 8.1 subscriber db and do a sync).

Unpause replication.

Wait for slony to catch-up.

Switchover from the 8.0 database to the 8.1 database (just a few minutes of downtime).

Pause slony.

Upgrade the original provider database to 8.1.

The versions of slony at both the provider and subscriber must be compatible at all times. Keeping slony versioning separated from the versioning of postgres makes this much easier.

Re:Clustering

[Admiral Burrito]

Don't let the fact that Slony-I is not "built in" deter you from using it. I know the idea of something so critical being a separate project doesn't feel right, but you'll get over that once it's running.

The way Slony-I is built, it just sits on top of PostgreSQL. I would be far more worried about a product that hooks deep into the guts of the RDBMS - there is a lot more that can go wrong that way.

I've evaluated Slony-I and a commercial pgsql replication product*. The commercial one was a bit faster, but under high enough load I was able to break it (it could be fixed, but required restarting the server). I was not able to break Slony-I. If the server becomes overloaded then Slony-I replication can fall behind, resulting in slaves that are farther and farther out of date (this is true of any asynchronous replication product), but once the load drops back down it catches up again with no problem. That's not to say that Slony-I is unbreakable (I wasn't able to break it, but any software can have bugs), but I do think the approach it uses is fundamentally less error-prone than a more integrated design.

* I won't name the product, as this was a long time ago and I'd rather not tarnish the company's name with outdated information.

Re:Clustering

[Anonymous Coward]

I suggest you rather use WAL-shipping then, it does basically the same thing as DBRD, only it can be run asynchronously, so network being down for a few moments wont either a) break replication or b) stall the database

Excuese me...

[Savage-Rabbit]

Oracle's got PosgreSQL beat in terms of features (which, as someone else already noted, many Oracle users don't need), but I wouldn't try whining that PostgreSQL is "hard to configure" Not compared to Oracle it isn't!

For one thing, these days, Oracle ships with an Java GUI that makes the database creation process alot less painful plus the Enterprise manager also helps to keep an overview. Not that your complaint about the DB instance creation process really makes much sense. You are comparing a Sabertooth tiger with a common house cat which is pretty redundant. OracleDB is a much more complex product than Postgres is, they are in different weight classes. Also keep in mind that it's not just about features. While working for a former employer I watched MySQL get dumped for Postgres as their billing system grew because Postgres had more features. However after about a year Postgres got dumped for Oracle because Postgres kept suffering major faliures every two or three months or so at very high loads. Oracle simply proved to be more stable at high loads, it scaled better, had top notch (though expensive) support and most of all it offered better disaster recovery mechanisms whenever seldom it did experience a catastrophic faliure (once due to a power faliure caused by construction work) and disaster recovery cost us considerably less effort than it did with Postgres. If you run Oracle on top notch hardware on a properly set up, patched and configured OS administered by a good Sysadmin/DBA it is about the most stable setup you can get. For companies that are making millons using Oracle for, say, their billing system like my former employer, the cost of expensive servers, a good sysadmin and the occasional contracted Oracle specialist is really peanuts compared to the damage that downtime can do in terms of Customer confidence and simply lost revenue.

Re:Excuese me...

[Doctor Memory]

Oracle ships with an Java GUI that makes the database creation process alot less painful

I have never seen an Oracle DBA (and I've been one) use the Java GUI for anything even remotely complex. It's too slow for most of the complicated tasks, and for simple tasks (say creating a new user/role), it's even faster to use the command line.

You are comparing a Sabertooth tiger with a common house cat which is pretty redundant.

Maybe you could give a shout upstairs and ask your mom what "redundant" means?

OracleDB is a much more complex product than Postgres is

Gee, ya'think? Gosh, maybe that's why a good Oracle DBA can pull six figures, and even a haphazard part-timer like me can make enough to pay the mortgage. The question is, is all that complexity necessary? All the DBAs at major shops that I've worked at (Union Pacific, Freightliner, Nike, to name a few) have their own script generator that permits them to tweak a handful of parameters (base directory, number of redo files, etc.) then spits out the createdb script and executes it. Even that's more work than it takes to create a new database on PostgreSQL. Sure, Oracle's more configurable and more tunable, but it's high-maintenance kit. Some people install expensive engine management systems to get the last usable bit of power out of thier engines. If you need it, then you probably really need it, but if you don't, then it's not just a waste of money, it's a waste of time as well. Same with Oracle.

While working for a former employer I...blah, blah, blah

Sooooo..... Your point is that Oracle scales better and handles better at the limit than PostgreSQL? And that relates to relative ease of configuration....how? Nobody's arguing that PosgreSQL is better for large installations than Oracle (except you and your little strawman). The point is, Oracle's a lot more complex than a lot of shops (maybe even a lot of Oracle shops) really need.

Oh, and was your previous employer really billing millions of dollars a day through MySQL? I doubt it, unless they showed other suicidal tendencies...

Many DBAs miss the point

[philovivero]

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.

Re:Many DBAs miss the point

[tweek]

Support is a real issue though.

Example:
Our ETL vendor, Informatica, doesn't support PostgreSQL or MySQL and refuse to even help with the issues.
Our BI vendor, Actuate, rebuilt our environment and provided a custom build of the package to make it work with PostgreSQL.

Interestingly enough, Informatica costs a fortune compared to Actuate.

Re:Many DBAs miss the point

[darkora]

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

The thing is... Google and Yahoo also use a lot of Oracle. Otherwise, they wouldn't have all those job openings listed for Oracle DBAs, Oracle developers, and Oracle Applications/Financials people.

Re:Many DBAs miss the point

[Anonymous Coward]

there is also de point that no that many companies can affort to hire that number of PhDs

Re:Many DBAs miss the point

[LurkerXXX]

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

From the Yahoo's home page... 'Company info' link, 'Employment Opportunities', 'Find a job' link, 'job search' link, searching for 'dba' gives...

Oracle DBA",

Technical Yahoo, Sr which describes it as "We are looking for a seasoned Oracle DBA/Architect to join Yahoo! Mail team. You will be part of Mail DBA team to design, setup and manage multi-terabytes Oracle RAC databases for next generation of web-based applications." ,

Sr. Oracle DBA - Yahoo! Search & Marketplace,

IT Applications Support Analyst described as "This position will provide day-to-day applications support in troubleshooting issues related to Oracle 11i HR, Oracle Self Service, and several integrated 3rd party HR applications working closely with the Corporate IT Applications development team, DBA's, HRIS and various HR business groups/partners.",

Technical Yahoo described as "Plan, install, configure, maintain and administer Oracle databases."

And over at Google, searching for jobs at Google with the keyword Oracle we have:

Systems Specialist - Internal Applications which lists requirements as "Production support experience with Oracle or MySQL database servers, including user creation, monitoring of backups, basic queries and generation of data extracts." I wonder why they list Oracle. Maybe because they use Oracle as well??? hmmm.

Database Administrator - Phoenix which says "This person will be tasked with supporting databases of varied flavors including Oracle, SQL*Server, MySQL and Netezza (Postgres-type)."

Technical Accounting Manager which says "Recent Oracle 11i experience is a plus."

Database Administrator (Temporary) - Mountain View which states "The primary database platform this person will support will be MySQL and Oracle but will also be involved in assisting with Microsoft SQL, Netezza and PostGres. "

Googles jobs listing Oracle go on and on...

They use MySQL for very shallow applications that aren't that critical if they screw up a few records. If that happens, a few hits aren't returned. No biggie, there are probably 14,000 other hits for what you've searched for, and what you ne

Re:Many DBAs miss the point

[Anonymous Coward]

Agree wholeheartedly about the mediocre people in DBA positions in general but your comment about "since yahoo and google use MySQL it should be good enough for you" is just silly!

Databases are used for VERY different things (OLTP, OLAP, Relational datawarehousing, etc). As with any technology there are things which your application/business may require that this-or-that database product makes much easier or much harder(if not impossible in some cases).

As always.. Right tool for the right job. (This is as close to a "truth" in IT as I've come across in all my years)

Mike

Re:Many DBAs miss the point

[RzUpAnmsCwrds]

If you think MySQL is all you ever need database-wise, I humbly suggest you rethink your competence in the field.

Or perhaps you're not in the field at all. There are really three classes of databases today:

• Enterprise-Class databases (e.g. Teradata, Oracle 10g)
• Business-Class databases (e.g. Postgres, MSSQL)
• Lightweight databases (e.g. MySQL, SQLite)

Now, of course, there's a ton of overlap here. As MySQL becomes more feature-rich, it begins to enter the "business-class"; MSSQL is beginning to see some success in the "enterprise class" as well.

The bottom line is simple: you're not going to run your bank's accounting system on MySQL, but you're not going to run a PHP forum on Oracle either.

What a database is has changed - thanks to the Internet, we are now using databases for applications that were never though of before. If you think that MySQL is all you'll ever need, you may very well be right - most people will never need the kinds of features that Oracle offers. Of course, if all you think you'll ever need is MySQL, you're not a DBA and you probably won't be hiring one anytime soon.

I don't believe that I'll ever need to own a semi or a 777. Clearly, my Toyota Prius can't do everything. But it meets my needs well enough, and it does so without the overhead of a larger vehicle.

Re:Many DBAs miss the point

[LurkerXXX]

He was talking about corporate use. I was talking about corporate use. I thought that was kind of clear.

Yes a forum will run fine off off of MySQL. So? We used to run forums and BBS's off of flat text files. Having them run off a toy database is no great feat. Some specialized companies (search engines or online forums) might do well running MySQL on lots of their machines, but certainly not all, if they are a big corp. A few hits being missing from 20,000 returns is no big deal. Neither is a missing Godwin-invoking flame on a forum. However folks tend to get more upset when email they counted on having disappears. That's one of the reasons Yahoo! uses Oracle and not MySQL for mail.

The vast majority of companies aren't search engines or forums. The data they have in their databases means money to them, and needs to be consistant. Having your financial, HR database or customer records database accepting 'February 31st' as a valid date isn't acceptable (Yes, I know MySQL recently fixed it recently, after leaving that very very low hanging fruit alone for years). It's also not acceptable to have someone input one number into a field with a constraint, and have the database silently change it to a different number (and yes, I know all about the *new* strict feature. It doesn't solve the problem in all instances, there are still things you can tell the database to do, and it will silently do something else instead. Oh, btw, strick is on by default only on the windows version. Mature databases don't have a 'switch' to 'accept out-of-bounds data and silently change it to something else').

In the context of the corporate environment, which the grandparent had set the question, saying MySQL is always enough is beyond foolish.

Re:Many DBAs miss the point

[raduf]

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.

      You're probably right, but when you find a way to explain to people they'd be better profesionals if they didn't use all the fancy stuff, call me and tell me how you did it.
      I remember in the mysql 3.23 documentation, about foreign keys. they pretty much said there's no need to use them, ever, so they're ignored. I didn't really start to appreciate that kind of thinking (and courage) until these last few years when if you don't use the best and the latest, weather you need it or not, you're just not that good.

Separate column

[ashpool7]

order_id, item_id, quantity

Re:Separate column

[einhverfr]

So, what if I want to order two identical line items on an order so that I can track them differently on receipt you give me?

Still doesn't work.

I work with a few apps that don't use primary keys on some tables and occasionally it is a real pain. Indeed I am considering adding a separate serial key to one of the largest tables because you can run into some really weird data projection issues.

The desire not to have a primary key that is enforced as unique can lead to all sorts of problems. Of course the application I am thinking of (an open source package) plays fast and loose with the relational model anyway so I am developing a patch to make it conform to generally accepted relational practices (without touching the application sitting on top)

Re:Separate column

[ashpool7]

Application doesn't support that anyway. It wasn't a requirement for the design. If the shipped separately, you'd split the entry, create a ship_id (Linking to another table, or just a tracking number), and add that to the primary key.

There is a primary key, I just didn't define it explicitly in the database, leading to my dismissal of Slony I. However, I still am uneasy about a replication solution that rides on top of the database.

Re:Separate column

[einhverfr]

YOu could use log shipping. That doesn't run on top of the database but along side it.

Again, shipping and replaying the WAL entries will solve both your objections, though you may have more latency between updates on the replicant. Slony is still better (and more versatile/robust than MySQL's replication) but some people are uneasy if it is not bundled.

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:Corrections to the article

[budgenator]

your CRM is broke:

Warning: session_start(): open(/home/groups/h/he/hermesweb/htdocs/demo/herme s/misc/locks/sess_7fc3d3dfc4121739d686b6ad637f7da9 , O_RDWR) failed: Read-only file system (30) in /home/groups/h/he/hermesweb/htdocs/demo/hermes/ind ex.php on line 17

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/groups/h/he/hermesweb/htdocs/demo/hermes/ind ex.php:17) in /home/groups/h/he/hermesweb/htdocs/demo/hermes/ind ex.php on line 17

Warning: Cannot modify header information - headers already sent by (output started at /home/groups/h/he/hermesweb/htdocs/demo/hermes/ind ex.php:17) in /home/groups/h/he/hermesweb/htdocs/demo/hermes/ind ex.php on line 49

Warning: mysql_connect(): Unknown MySQL Server Host 'mysql' (0) in /home/groups/h/he/hermesweb/htdocs/demo/hermes/DBA L_mysql-1.0.0-b.php on line 40

Warning: mysql_query(): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /home/groups/h/he/hermesweb/htdocs/demo/hermes/DBA L_mysql-1.0.0-b.php on line 81

Warning: mysql_query(): A link to the server could not be established in /home/groups/h/he/hermesweb/htdocs/demo/hermes/DBA L_mysql-1.0.0-b.php on line 81
Error connecting to database. Was unable to process your request!
Login failed

Re:Corrections to the article

[SiggyRadiation]

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

A few weeks ago I was trying to create a plperl procedure that reads a file from the filesystem and returns it in a bytea field. (I needed a way to fill a bytea table with testdata.) Big problems. If the input-file contains nulls the bytea is cut off right there. I tried hard, including switching to plpython, but did not actually get both loading and saving to/from bytea to work.

So, I do believe that "somewhere in there" all data-exchanges between stored procedures and the rest of the system are text-based.

Re:Corrections to the article

[einhverfr]

Everything is typed. However, it may be presented as text strings depending on the interfaces. In general, where you are creating a stored procedure in something other than C, and where the data is not coming from the database, you are probably going to have to present the data as a text string. This is very simple. Represent NULL bytes as \000 and you should be fine. It is a matter of escaping the information.

In C, I assume you can just create the appropriate struct. And when the data is processed using SQL calls, etc. I am sure it retains its type. And when it is taken from the database and passed to another function, I am not sure how it is presented (it is however typed, regardless of how it is presented-- try passing a text field to a function that expects bytea sometime).

Re:MOD PARENT/REPLIES DOWN,THIS POST UP,FIRST POST

[wackysootroom]

Interesting that you call someone out for spamming their own software while you're trying to get others to sign up to a pyramid scheme to get yourself a free PSP. At least Tom actually creates something he's proud of. He's been a fixture in the postgresql and ruby communities for quite some time now.

Oh, the irony.

Backups backups backups!

[yem]

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.

Re:Backups backups backups!

[rjstanford]

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

Eww. I mean, if they don't already have one (and if not, well, ick to that too), then what you need are real incremental log backups so that you're continuously writing out your logs as they fill up rather than every xx minutes. That way, as long as your backup system can process fast enough, you know that "last full backup" + "last incremental backup" + "logs on disk" are always going to contain your full dataset. And ideally, that last portion should be miniscule.

But if they don't have this yet, I'm amazed that people are running mission-critical stuff on them.

Re:Backups backups backups!

[yem]

You're right of course - need to backup the current log as well.

PostgreSQL is tantilisingly close. It just needs to be easier. And available per-database instead of per-server.

There are ways to achieve this, eg Slony with it's new "log shipping" feature. But that has additional cruft like requiring every table in the database to be listed in a config file and unique primary keys actually ADDED to any tables which don't already have them.

Good backups is the only thing keeping me (and a dozen large message databases) on Sybase.

Re:Backups backups backups!

[rgigger]

They most certainly do have this. You can set it up to continuously back up from the transaction logs. It is a little rough around the edges, and you do have to do a little scriting. And unfortunately it is all or nothing. You can't just backup one database.

The nice thing about it is that you CAN do a lot of scripting and set it up in very customized ways to suit whatever needs you have.

I think they went it about it the right way. First make it work in a reliable, flexible way. Then create a way to make the common cases easier. The easy will come soon soon enough. But right now if your willing to take a little time it can work any way you want it to.

Re:Backups backups backups!

[Anonymous Coward]

They have a complete solution, including a robust PITR solution. He just doesn't know what's going on or refuses to read the mass of available documentation.

Re:MOD PARENT/REPLIES DOWN,THIS POST UP,FIRST POST

[chrismcdirty]

The difference is that I don't sneakily insert it into every post that I make.

Funny quote

[hey!]

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.

Re:Funny quote

[Zerbs]

I think every Oracle DBA who has worked with 8.0 or higher realizes that the only way to really configure TCP/IP connections on it is manually editing the TNSNAMES.ORA file. I suppose this is less of a problem in more modern setups where the clients are using Active Directory or something to find the Oracle Server.

I'm taking a look at it again

[ashpool7]

I didn't put a primary key on some of my tables because it didn't help anything at the time. Much later, I go to mess with Slony and discover in the installation documentation that you need primary keys on everything. Not all tables have primary keys defined, so I promptly gave up.

If you don't use something, you lose it, and I haven't designed databases in a while, so I honestly forgot about it until I got flamed. :-)

Postgres is good enough for them

[Anonymous Coward]

The largest public library consortium in the US is using Postgres. If it's good enough for them, it's good enough for 90% of situations.

You are doing two different things

[einhverfr]

By default, PostgreSQL is only accessible from localhost.

You can change the listen address in the postgresql.conf

The Host-based authentication configuration file (pg_hba.conf) provides a very fine-grained level of control for how people can connect to the database. In it you can allow specific networks to connect but not some machines within those networks, and you can use different types of authentication for different networks.

For example, you can allow the web server to connect only using an account named 'www' but allow other users on your network to authenticate transparently using Kerberos V. This is all configured in the pg_hba.conf.

Does this make more sense?

Relevant discussion topic, but keep in mind bias

[ttul]

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

64-bit postgres

[Anonymous Coward]

Postgres has supported 64-bit archictures way before Opterons were available. You can google up people running pgsql in 64-bit mode in DEC Alphas and Sparcs in the late 20th century.

8.1 just gave the option of having larger than 2GB shared buffers because people kept complaining about the limit not realizing that pgsql performs better w/ small shared buffers and large OS cache.

Sorry, but that's not quite correct

[rjstanford]

Google and Yahoo are both examples of very intensive but very simple applications. I call this "wide but shallow." Lots of data flowing through, but comparitivly basic usage patterns. Take a typical ERP system, on the other hand, and you're suddenly looking at a narrow scale but deep requirement package. I worked for over a decade on a tier-one warehouse management package that had over 750 tables, 3+ million LOC, and some very complicated business logic that still measured its throughput in millions of processed order lines per hour (each one requiring many database calls) the last time I benchmarked it, which was back in 2003 on a 4-way IBM.

Its like saying that since Freightliner is the choice of companies like Wal-Mart, that they'd make the best luxury cars for people looking for ideal interior fit and finish. Same principals, totally different application.

Reputation isn't everything. Structure is

[einhverfr]

extremely important too.

None of them have the reputation Oracle has in supporting its own core product.

True. And Tom Lane is employed by Red Hat, and so the main access to his expertise is over the email lists.

The companies I mentioned by name employ core PostgreSQL developers (exceptions being Sun and Fujitsu, the latter having contributed a great deal of code however), and provide support for general distributions of PostgreSQL.

Now.... When you call Oracle, you will get an engineer who is a DBA or an app developer who will try to help you. I almost guarantee that this engineer will have no access to the software engineers who built this version of Oracle. When you call COmmand Prompt (which support not only their proprietary fork, but also the community version too, 24x7), you get access to people know know the code, and if the engineer doesn't, he or she can definitely correspond with the people who are intimately familiar with the workings of that part of the database.

Imagine you run up against a bug in Oracle. The best you can hope for is a workaround. With PostgreSQL, the least you can hope for is a workaround. The best you can hope for is a patch.

Having said all this, I will say that the support on the email lists, esp. for performance tuning is phenominal. I ran into a bad query plan for an out of the box application. I was able to narrow down the bad query and submitted it along with an explain analyze to the list. I got a very good explenation as to why the problem existed, why the bahavior was not going to change, and what I could do to resolve it in the database without touching the code (the issue had to do with joining physically empty tables against very large tables). For the more complex issues such as "why is this not using an index like I think it should" it is not uncommon for a posts to go on 10-20 posts per thread. Sometimes I have even seen extraordinarily complicated problems be carried on for 50 posts. I have never seen anyone give up on the perform lists in particular.

I would rely on the lists for most support and then call Command Prompt, etc. for those time-sensitive issues where you cannot afford downtime or to wait for an answer.

Re:Reputation isn't everything. Structure is

[Doc Ruby]

As I mentioned, Postgres' open source offers the extreme advantage when dealing with a bug in the released RDBMS. However, it is possible for the right price to get an Oracle support person who is not only extremely qualified, who has access to the RDBMS developers, who has access to the source, but who will work on my premises. That kind of support is not available for Postgres - even the contributors to the Postgres source who can be hired directly don't have access to Oracle's professional services management structure and its resources for customer care.

The choice isn't quite as clear as it could be - which generally is in Postgres' favor. In extreme environments, like the ones on Wall Street where I've earned the largest share of my own income, Oracle and its support means "minimizing the risks", while Postgres means "economical risk mitigation". That's good enough for most people, and a testament to Postgres' value, especially per dollar.

for the same reason...

[Julian Morrison]

...that turning on you computer's networking and opening a hole in the firewall are two seperate operations.

I can see the conceptual theory, but still, it's bad design. Typical programmer-invented UI. Their mistake is "lets force the user to make the coder's job easy" and "implementation detail leaking out of the abstraction". We implemented TCP and access control seperately, so we configure them seperately.

A better way to do it would be to make the networking switch-on implicit: if you open a hole for TCP in the access file, this causes TCP to be enabled on the server. Therefore configuration follows interface ("these networking modes are supported, make it happen") not implementation.

Re:for the same reason...

[colinrichardday]

Causes TCP to be enabled on the server to which hosts?

And perhaps implementers may presume that database administrators
have some sophistication.

Neither supports SQL99 WITH RECURSIVE syntax

[mark-t]

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.

Re:Neither supports SQL99 WITH RECURSIVE syntax

[Anonymous Coward]

And Oracle's CONNECT BY is far easier to understand and more elegant to use than the standard syntax.

Re:Neither supports SQL99 WITH RECURSIVE syntax

[Anonymous Coward]

The MODEL clause is far more powerful.

THIS is how source code availability matters

[WebCowboy]

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.

Oracle runs best on a 35mil slide projector

[Anonymous Coward]

Remember that the platform Oracle performes best on is a 35mil slide projector.

Although now, with modern computers, I suppose that should read performs best in a powerpoint presentation.

This explains alot.

[Homestar Breadmaker]

No wonder digg is such a pile of shit, is everyone there as clueless as you?

Shutup you douche.

[Homestar Breadmaker]

Yeah, I'm sure everyone desperately wants a job an "Anonymous Coward Inc.".

Bit by bit published standard

[jbolden]

Bit by bit how the data is laid out is covered in Oracle literature. Database concepts chapter 2 covers blacks and 5 covers the rows (the data). There really isn't anything stopping you from writing a perl script to pull data out. Of course Oracle has all sorts of easy export routines so no one bothers.

Hardware overwhelms 99% of problems

[grantsucceeded]

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.

Re:Hardware overwhelms 99% of problems

[Anonymous Coward]

> if you loose or fail to recover even a few you risk goign out of business because customers stop trusting you.

What's the difference between a tight and a loose recovery? I've worked with databases for almost 35 years, and I've never heard that slang.

Re:Hardware overwhelms 99% of problems

[Anonymous Coward]

Let me add one to that:

- If you're an ASP and your customers get footed the bill for the hardware needed to run at acceptable performance levels. Then suddenly that extra $9,000 Itanium proc tends to make a difference if your competitors don't need it.

Then again the times when performance made the big difference between the enterprise class SQL DBMS and the toy DBMS are fleeting, several of the low-end and corporate SQL DBMS's have caught up nicely in that respect. It's data consistency (under normal operation as well as during hardware failure) where the big difference is. And no web developer running their forum over MySQL is ever going to understand that point. A few bloggers not able to log in to their site is just not the same thing as a several million dollars worth of bank transactions going missing.

Re:Hardware overwhelms 99% of problems

[Anonymous Coward]

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)

HMM, doing 2GB/sec reads means you have at least 10 FC channels and 40 disks in your disc array.

Check out Bizgres MPP, a commercialization of postgresql with query parellelization/distribution (http://www.bizgres.com/), they can do the same, using nodes with a few nodes with 2 Opterons + 2x8 SATA RAID arrays each. IIRC they did 350GB/sec on each array, meaning 0.7GB per node, so 2GB needs 3 nodes. And they already have built in node redundancy in addition to RAID's disk redundancy.

That's what I'd call cost-effective.

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

[dr2chase]

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.

Re:Reputation isn't everything. Structure is

[juergen]

You can get a PostgreSQL developer on your promises too. You can even train one of your own workers to be a PostgreSQL contributor/developer. As PostgreSQL is open, you don't need to rely on foreigners to get access to anything should you be inclined to invest the time and money to look yourself. You can get involved steering the future development. Or you can fork the project for total control. Now where are these Oracle options, aside from buying out the company?

That you can get deeper insight into Oracle's inner workings by their support than into any *open* source DB is self contradictionary. The process is different though, I admit that.

I agree on your second paragraph.

Re:Reputation isn't everything. Structure is

[Doc Ruby]

I don't want to beat this issue to death, as we're all really expressing different valuation of the different factors in the different products' support realities, according to our individual requirements. But I did say that

"even the contributors to the Postgres source who can be hired directly don't have access to Oracle's professional services management structure and its resources for customer care",

which classes Oracle's support beyond the reach of Postgres. And beyond the needs of most users, who will probably be just as well served by 3rd party Postgres support as by Oracle's basic (and affordable) support.

Nonsense...

[IANAAC]

... Oracle instance will crash suddenly, with nothing more than a notation in the log that...

That's nonsense. It doesn't crash. What it does do is wait for you to give it some more space. As an administrator, that's a fairly easy thing to do. Once you've done that, Oracle continues on its way as if nothing had happened.

CONNECT BY and CTX

[Anonymous Coward]

Postgres does not have support for CONNECT BY statements to generate hierarchical results.

Oracle's CTX text indexing/searching feature works great for fuzzy text, soundex, etc.

All I know is...

[glwtta]

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.

Oh come now.

[Ayanami Rei]

The parent isn't trying to hide the fact that he's participating in a pyramid scheme. And he actually had something to say in his post (meta-topical as it were). But Copeland will just post a barely tangental, non-sense reply to any story to blindly plug his software and books.

It's bullshit.

I thought the article seemed a bit pessimistic.

[Ayanami Rei]

I mean... wasn't Postgres unique way back in the day because it was a strongly typed object database? I haven't used it enough to know any better, but I was surprised to hear a claim that it didn't have typed parameters... it seemed completely off base.

And what's the big deal with editing conf files to modify the db bevahior? Any Oracle DB worth his salt is happy to get down and dirty with sqlnet.ora and tnsnames...

Oracle has a bug that prevents PostgreSQL ...

[dostick]

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.

Re:Reputation isn't everything. Structure is

[einhverfr]

In most of these cases, the support is not going to be what is really keeping you on Oracle. Parallel quieries for huge databases is the big one. No open source solution can match that one at this time.

MOD PARENT DOWN!

[Anonymous Coward]

Either the moderating system is fundamentally flawed (well, maybe it is), or the mods are simply on crack... I dunno. This isn't interesting, it's -1, TROLL or perhaps -1, FLAMEBAIT!

Even megacorps need some software that often relies on Oracle/Sybase or such (supposedly Wall Street uses Sybase a lot from what a friend that used to work there said). Quite often, companies WILL need features or what not that MySQL/PostgreSQL does NOT offer. Not everyone needs Oracle, but some do, and that doesn't make people incompetent, nor does it make a company mediocre.

Just because MySQL works for a shit simple toyish web app (I've seen 12yo kids put stuff together that was FAR more complex) doesn't mean it's good enough for everyone. As long as your DB scales well enough (optimize queries, add some caching and such features) and that you got enough hardware, running something like Digg isn't quite as hard as it may seem.

Just because the site is well known doesn't mean he has a clue. He's just yet another MySQL fanboi.

Disclaimer: I don't like Oracle. At all (I've left a otherwise great job just because they were switching to it). But it doesn't mean that those who need and use it are incompetent.

Please, mod accordingly.

Go learn some English

[Anonymous Coward]

You're either a troll --OR-- (you) have no idea what you're talking about.

Re:Reputation isn't everything. Structure is

[Anonymous Coward]

> The companies I mentioned by name employ core PostgreSQL developers (exceptions being Sun...

Josh Berkus is now employed by Sun, and Bruce Momjian now works for EnterpriseDB (both are part of the PostgreSQL Core Team).

WOW!

[Phil John]

<sarcasm>When you put it like that, it's so simple!</sarcasm>

I have nothing but respect for the PostgreSQL guys - they are a damn smart bunch, let's hope they can put some work into making upgrading a replicated setup a lot easier in the near future - there's so many points at which that could fail it scares me!

Um, varchar2, nvarchar2 ?

[coder111]

Hello,

this is what varchar2 and nvarchar2 types in oracle ar for- unicode characters.

I agree, it is extremely idiotic that normal varchar type is useless in oracle, but you can still use oracle and unicode.

--Coder

Solaris Optimized PostgreSQL

[HaydnH]

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.

Hard to learn

[redrabbit29]

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.

Re:Hard to learn

[Anonymous Coward]

wow, you couldn't shutdown oracle?

shutdown immediate or shutdown abort will drop it pretty quick, starting the database is as hard as typing "startup". I call bullshit on your story.

Re:Hard to learn

[Zerbs]

If you did this recently, I'm suprised you tried Oracle 8i. Some of the enhancements that have gone into 9 and 10 were to make tasks like administration, setup, and maintenance easier.

Oracle vs PostgreSQL: Security

[joxeanpiti]

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.

This is one reason I chose MySQL over Postgres

[lorcha]

The main reason I chose MySQL is that I have apps that only work with MySQL (such as MythTV). Why should I run two db servers?

But the second reson I chose MySQL is that it has true incremental backups with snapshot+binary logs, and it's dead easy to use. I looked at the docs in postgres on the subject, and found it to be extremely rough around the edges.

I'm surprised the Postgres team has not fixed this yet. They finally got around to making a non-blocking vacuum. You would think that functional incremental backups would be on the list as well. Pretty basic as far as db requirements go.

WTF

[lorcha]

I just wish MySQL had transactional full text indexing

I just wish PostgreSQL had full text indexing at all.

Hey now!

[Anonymous Coward]

Don't be so hard on Oracle! I know it is gougingly priced, but it is pretty good at certain things! It has those features, man, rich FEATURES!*

*(Feature Oracle as your DB and our PHBs get rich)

It does

[ttfkam]

It's called tsearch2. It has come bundled with PostgreSQL for a while now.

O'Reilly was talking about it at a conference in 2004, but I forget when it was actally released. Version 7.3 or something I think.

If you want to see something really cool in PostgreSQL, check out PostgreSQL's GIST support.

Why use Oracle

[sqlgeek]

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 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:Reputation isn't everything. Structure is

[einhverfr]

BTW, in times when I have seen people rule out PostgreSQL, I have seen them do it for two sets of reasons:

1) Investor relations in privately held firms-- I.e. the opinions of VC's.
2) Inability to meet technical demands (usually due to a lack of intraquery parallelism, though the currently vaporware Bizgres MPP ought to help with this if/when it comes out).

In my opinion, only the second one matters in the long run.

I have never heard someone say that they wanted to use Oracle because they could buy great support. If that level of support is truly necessary, often you have a need for intraquery parallelism which PostgreSQL doesn't offer, so you are left with a choice between Oracle, DB2, and Terradata.

Look in contrib

[einhverfr]

TSearch2 last time I checked was bundled with the PostgreSQL source, and unlike MySQL's full text indexing that only works on non-transactional data stores, it is fully transactional (as is essentially everything in PostgreSQL).

I didn't define the alternative.

[C10H14N2]

Everyone keeps responding as if I'm saying "No one needs Oracle, therefore everyone can get by with MySQL." No, what I'm saying is that many people could get by just as well on, say, a $1M Sybase or MS-SQL install as the $5M Oracle equivalent.

Re:I didn't define the alternative.

[LurkerXXX]

Everyone keeps responding as if I'm saying "No one needs Oracle, therefore everyone can get by with MySQL."

Probably because you say patently rediculous things like

"I mean, cripes, GOOGLE uses MySql. [computerworld.com.sg] If THEY don't need Oracle, who the hell does?"

Google DOES need Oracle. If you'd bother to look at their jobs board for positions they are trying to fill in Google, there are LOTS of them asking for Oracle DBA's or folks who know Oracle apps. Just because they can get away with using MySQL to do simple selects from large tables where data integrity doesn't matter for one application, doesn't mean they don't have plenty of other applications they use that require REAL data integrity, and/or are much more relient on inserts/updates or complicated nested subselects where MySQL would be totally inappropriate.

Depending on what type of application it is, Oracle may very well be much better suited for the job than Postrgres, MS SQL, or Sybase. I think Google knows their needs a lot better than you do, and they DO use Oracle to fill some of those needs.

I specifically addressed that in my initial post.

[C10H14N2]

...directly referencing Oracle Financials.

Re:I specifically addressed that in my initial pos

[LurkerXXX]

Who said I was talking only financials?

It's the same with Yahoo!. MySQL fanboys always claim 'Yahoo! uses MySQL not Oracle. That proves it's as good of a database'. Guess what? Yahoo! does use Oracle as well (and no, not just for financials. Just like Google).

In case you hadn't noticed, Google and Yahoo are very nich companies. Their largest 'product' (a search engine) requires massive ammounts of simple selects. It also doesn't require strict data integrity. If you only get 14,000 hits rather than 14,005, because the 5 are corrupt, who cares? The same goes for companies who's main product is an online forum (digg.com, etc). Simple selects. And if a few flamefests go missing now and then, no great loss.

The vast vast majority of companies out there aren't search engines or web forums. Most companies have data in their databases that are precious to them. I'm sorry to tell you, but the new *strict* feature in MySQL isn't all that. There are still plenty of areas where you can be running as strict and tell MySQL to do one thing, and it will not throw any errors, but will silently do another. That doesn't cut it when your data is precious.

That's one of the reasons Yahoo! uses Oracle for their email service, and not MySQL (Gee, look, a use other than financials!). People tend to get a lot more upset if an email they counted on having disappears than if they get 5 fewer hits on a web search than they potentially could have.

There are lots of applications in the world that aren't based on primarily simple selects, but use lots of inserts/deletes/updates/complex-nested-subquires where MySQL just isn't the right tool for the job, and even more where data integrity is a primary factor. That's why saying:

"I mean, cripes, GOOGLE uses MySql. If THEY don't need Oracle, who the hell does?"

is so patently rediculous.

MySQL Fanboyism? WTF?

[C10H14N2]

Professionally, I've mostly used MS SQL, DB2/Universe and Sybase. I've NEVER used MySQL professionally, so I can HARDLY be considered a "fanboy."

How do you rebuild SQL Server?

[bill_mcgonigle]

Of course, I'm not going to rebuild SQL Server... that's a 2 week long excersize in pain.

Wait - how do you do that? I don't have the source.

Does this mean you're on the SQL Server team and use MySQL? That I'd believe.

Re:How do you rebuild SQL Server?

[Procyon101]

I'm not on SQL anymore, but I did have the source at one time, and I have built it completely before, and believe me when I say it's not something you want to try. If you mean, by "how do you do that", how do I build SQL server, given the source, then that is something I'm not going to discuss outside of an MS NDA, and there are better people than I to talk to. And yes, I do use MySQL for some things (Not inside MS though... I know of no MS use of MySQL, nor of any production use whatsoever of any non-MS RDB.)

Honestly, all other things being equal and given the choice, I would take MSSQL over MySQL any day. Things not being equal though, I don't always have a Win2k3 server lying around, I don't always want a Win2k3 server over the other options, and some apps are written to work with MySQL only (drupal being my biggest reason for using MySQL).

Your Oracle career is so secure ...

[Anonymous Coward]

If you think your job is so secure, you ought to read this http://www.lewrockwell.com/north/north446.html from Gary North, in particular the story of the Linotype setter ...