Ask Slashdot: Is Postgres On Par With Oracle?

grahamsaa writes "I work at medium sized company that offers a number of products that rely fairly heavily on backend databases, some of which are hundreds of gigabytes and deal with hundreds or thousands of queries per second. Currently, we're using a mix of Postgres, Oracle, and MySQL, though we're working hard to move everything to Postgres. The products that are still on MySQL and Oracle were acquisitions, so we didn't get to choose the RDBMS at the time these products were designed. So far, we've been very happy with Postgres, but I know next to nothing about Oracle. It's expensive and has a long history of use in large enterprises, but I'm curious about what it offers that Postgres might not — I'm not saying this because I think that sticking with Oracle would be a good idea (because in our case, it probably isn't), but I'm curious as to how some companies justify the cost — especially considering that EnterpriseDB makes transitioning from Oracle to Postgres feasible (though not painless) in most cases. For those that use Oracle — is it worth the money? What's keeping you from switching?"

Comments

What's keeping you from switching?

[Anonymous Coward]

Stupid fucking managers

Re: What's keeping you from switching?

[jmhobrien]

Switching? Why would I want to switch to Oracle?

Re: What's keeping you from switching?

[KitFox]

Append "with no technology knowledge who met salespeople." and you're set.

Re: What's keeping you from switching?

[gmuslera]

Backdoors? Want one in the very place where you hold all your critical data? Even if they have good will (we are speaking about Oracle, so no hope on that) the government could eventually ask them to add some "extra functionality".

Re: What's keeping you from switching?

[Anonymous Coward]

Without whom you would likely be living in a hovel.

If your superiors decided that an Oracle solution was best, then it most likely was.

Sadly it seems you've let your jealously over compensation cloud your mind. My only advice is to remember that, when it comes to operating a business, IT workers are helpful but it is the managers who do the grunt work and take the real risks.

Re: What's keeping you from switching?

[MouseTheLuckyDog]

My only advice is to remember that, when it comes to operating a business, IT workers are helpful but it is the managers who do the grunt work and take the real risks.

ROTFL

Re: What's keeping you from switching?

[maxwell demon]

So he should switch to Oracle because Oracle might have backdoors? Are you hoping for extra protection against data loss by having the NSA maintaining an extra backup?

Re: What's keeping you from switching?

[Anonymous Coward]

So you too can not tell the difference between a NULL and an empty string :-) Tri-valued logic appears to have gone right over Larry's head, not an easy task given the size of said head.

Re: What's keeping you from switching?

[hawkinspeter]

I don't understand - oracle has a very clear difference between NULL and empty string. Tri-value logic is used throughout oracle's databases.

Re: What's keeping you from switching?

[K. S. Kyosuke]

Which may be exactly the thing that's wrong with Oracle - this NULL nonsense has never been a part of Codd's actual relational model. But, you know, Oracle...they love doing things their own way. Now we're stuck with imitations of the same nonsense elsewhere.

Re: What's keeping you from switching?

[malkavian]

You really obviously have no idea.
I've done both, and I've seen people swing the lead at both levels. A real IT manager isn't an easy job, but being the one whose plans (the real IT tech) make or break the infrastructure really isn't trivial.

Re: What's keeping you from switching?

[Anonymous Coward]

Many inexplicably overpriced things involve bribes and kickbacks.

From the classic no bid cost plus government contracts in exchange for a later job to the craze in buying artwork from the talentless wife of Chinese politicians, there is nothing new under the sun.

Was it a bribe? Oh no. While we were in Vegas, he just happened to win the exact same amount I lost.

Re: What's keeping you from switching?

[Anonymous Coward]

So he should switch to Oracle because Oracle might have backdoors? Are you hoping for extra protection against data loss by having the NSA maintaining an extra backup?

Oracle probably wouldn't even exist were it not for the Military-Industrial Complex. So you can expect that if the NSA wants backdoors, Oracle probably has an entire back porch.

Re: What's keeping you from switching?

[Anonymous Coward]

It's not that you don't understand, it's just that you're wrong - see http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null for example.

Re: What's keeping you from switching?

[RabidReindeer]

NULL Is one of the most abused characteristics of just about any RDBMS.

I always caution people that NULL is the data equivalent of NaN. Use it ONLY when you have no actual data to put there and want to know that there's no data there. If it's really blank, put blank.

Re: What's keeping you from switching?

[RabidReindeer]

My only advice is to remember that, when it comes to operating a business, IT workers are helpful but it is the managers^W CEOs who do the grunt work and take the real risks.

FTFY

Re: What's keeping you from switching?

[1s44c]

Maybe that's the case in a truly great company but in most real world companies the managers just don't know enough to make the right decisions. Most management decisions are based on their own prejustices, combined with who they know, who has the best salesmen, and who gives them the best gifts.

I worked in a huge multinational that exclusively used HP-UX servers. Why? Because a director of this company lived next door to a director of HP. They also chose recruitment agencies based on the free holidays they would provide to management. Corruption like this seems to be the rule not the exception and the upper management are always in the best position to gain from this corruption.

Re: What's keeping you from switching?

[Spliffster]

Not in our environment. I am working as developer for a large company, using oracle 10 and 11. There is no way our oracle servers can distinguish between NULL and an empty string (varchar2):

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372

Cheers
-S

Re: What's keeping you from switching?

[1s44c]

You really obviously have no idea.
I've done both, and I've seen people swing the lead at both levels. A real IT manager isn't an easy job, but being the one whose plans (the real IT tech) make or break the infrastructure really isn't trivial.

I wholeheartedly agree that being a real IT manager isn't easy. However I disagree with the assumption that real IT managers account for more than a tiny number of acting IT managers. Some know practically nothing about IT. Others have knowledge that's so outdated it's more of a hinderance to understanding than a help. Others are great with IT but got promoted to management and are rubbish at it. There are very few that know IT to a reasonable level and can manage.

Re: What's keeping you from switching?

[fuzzytv]

NULLs certainly make sense and are not a "nonsense." The fact that people don't know how to NULL properly does not make it incorrect. The proof that Codd did not include them into his books does not make them incorrect either.

Re: What's keeping you from switching?

[fuzzytv]

Yeah, I was just going to post exactly this. And IIRC it gets even funnier once you start programming in PL/SQL ...

Re: What's keeping you from switching?

[Outtascope]

It isn't that Oracle can't distinguish between an empty string and null, it's that some pedantic developer along the way decided to impose the philosophy that a zero length string and null are the same thing (with some post-doctoral research paper involving Zeno's paradox, Einstein's special relativity and the Poincare conjecture to prove that this is, in fact, the only mathematically appropriate way to do things), so it stores all zero length strings as nulls. It is impossible to store a zero length string in a varchar2 or char field in oracle. This makes it a giant PITA porting applications to Oracle (at least it did for me) and leads to some really nasty bugs if you are caught unaware by it.

But hey, it's cool either having to put a not null constraint on every string column (and dealing with the application logic grief that this can cause), or wrapping every string column returned from a query with NVL or NVL2 and using some magic value and hoping for no collisions. Clearly no one would ever want to distinguish between the values of "nothing" and 'I don't know".

This is just one of many reasons that I hope Larry Ellison's yacht sinks in the middle of the Pacific with him on board sleeping off a bender. (not really, but the imagery makes me smile nonetheless)

Re: What's keeping you from switching?

[nyet]

If your superiors decided that an Oracle solution was best, then it most likely was.

I call Poe's Law.

Re: What's keeping you from switching?

[gwolf]

The difference is pretty clear, if you do some decent amount of DB.
NULL means "I don't know". Empty string means a string of length 0 — A very different thing from sayin "I don't know". It means "I know, but the answer is empty".
Same as the number 0. 0 is a defined, existing value. It is not unknown/null/undefined.
Basically every RDBMS (except for MySQL) reliably handles the meaning of NULLs.

Re: What's keeping you from switching?

[K. S. Kyosuke]

I *know* what NULLs mean. I'm just saying that they shouldn't be there.

Re: What's keeping you from switching?

[K. S. Kyosuke]

The fact that people don't know how to NULL properly does not make it incorrect.

Apparently, the only way to use NULLs properly is to not use them at all.

Re: What's keeping you from switching?

[Anonymous Coward]

Perhaps one day you can join us in reality (or you can keep living in your echo chamber if you prefer).

Re: What's keeping you from switching?

[Anonymous Coward]

NO. Codd did advocate NULL values, and even distinguished
NULL values for missing information, and for inapplicable attribute.
Without an alternative concept of subtables, both are needed
in practice.
   

Re: What's keeping you from switching?

[Anonymous Coward]

>If your superiors decided that an Oracle solution was best, then it most likely was.

That is an appeal to authority, which is a logical fallacy. People at the top trust the people below them to help with decisions. It's the reason those people are hired in the first place.

Their being at the top of their company is not related to them actually knowing what they're talking about. As a manager, you manage people and they do the actual work. They just need someone to make sure everything runs smoothly and organized. Knowledge of the used tech is not a requirement.

A manager who decided on their own whether a product is good or not, is not using the people he/she hired for what they hired them for. They're just stroking their own ego. If they hired people for their technical knowledge, they should use them, or it's a waste of cost. And on top of that, it's those people who will be actually using the tech. If you have to give them a few weeks time to get used to a new system, as opposed to letting them use something the majority of them know already, that's also an added cost.

>Sadly it seems you've let your jealously over compensation cloud your mind.

There is no reason to assume this person is jealous. It sounds like you're trying to defend the top level, no matter what. And anyone who doubts them must obviously be jealous. You're putting the leadership above all else and anyone who doubts it must clearly be influenced by personal grief. That is just not realistic.

Re: What's keeping you from switching?

[Anonymous Coward]

when it comes to operating a business, IT workers are helpful but it is the managers who do the grunt work

False. They have grunts to do the grunt work for them.

"thousands of queries per second"?

[Anonymous Coward]

Is this a gay porno site or what?

Re:"thousands of queries per second"?

[K. S. Kyosuke]

I first missed the "site" part of your comment and wondered how does rule 34 apply to this situation.

what keeps us from switching ?

[KernelMuncher]

A big code base in PL-SQL I guess that nobody wants to re-write. We have lots of high dollar clients so it's easier to just stay with the status quo.

We have been experimenting with MongoDB with a few of our newer projects. We'll see if that becomes a viable alternative.

Re:what keeps us from switching ?

[Anonymous Coward]

I worked for a place once with a PL/SQL codebase that was near 20 million lines. It was a nightmare.

Re:what keeps us from switching ?

[Eravnrekaree]

Dont torture yourself trying to use some unusual paradigm in order to implement something in some faddish, newfangled NoSQL database when doing it in SQL will be easier, especially because someone heard some hype about something like MongoDB and thinks it must be used without really understanding if it is really better than SQL.

Re:what keeps us from switching ?

[Darinbob]

This is the best thing about SQL: it's a standardized language letting you switch between different database vendors with fluidity.

(and how says irony is dead?)

Re:what keeps us from switching ?

[Anonymous Coward]

Ever try to store an array of strings?

Ever try normalizing your schema? Even learning 1NF would help you understand everything that's wrong with that statement.

Re:what keeps us from switching ?

[aztracker1]

The issue is that normalization comes at a cost, and it really depends on your use case. If you are dealing with financial transactions, yes, SQL (relational db) is your best bet. If you are dealing with complex, fluid structures for mostly read scenarios.. a serialized version of your data in a no-sql-like one key to lookup works better.

It's emphatically not a one size fits all.. but the question becomes what is your major use case, and what performance needs do you have. NoSQL can scale horizontally in ways than SQL based databases simple can not. Outside of that horizontal scaling need, which is really quite rare, storing an entire object/document in your database as one record has some advantages in read/write when you aren't having to do so across too many records. There's a reason that many large operations put caching/nosql servers in front of their databases, and that is join operations, especially against large tables are fairly costly. Having to do more than 5-6 joins just becomes cumbersome, and means that another solution may have been better.

Re:what keeps us from switching ?

[Pedahzur]

EntepriseDB has a compatibility layer that lets you drop an Oracle application on top of PostgreSQL and run it (nearly) unmodified.

See http://www.enterprisedb.com/solutions/oracle-compatibility-technology for more.

Re:what keeps us from switching ?

[Craig Ringer]

2ndQuadrant, who I work for, have some PL/SQL conversion and compatibility tools in the works and are interested in hearing from more people with large PL/SQL codebases.

Re:what keeps us from switching ?

[Craig Ringer]

Yeah! And while we're at it we can use Java EE 6, which makes it super-easy to write apps that'll run on any of the portable Java application server runtimes!

People who think SQL is really a meangful standard haven't used more than one SQL RDBMS. Even basic read-only querying and DML is in practice only marginally standard. For example, Oracle doesn't support multiple VALUES lists, it has its own funky syntax for multi-valued insert, which is one of the more basic things around.

Re:what keeps us from switching ?

[Craig Ringer]

I work professionally with PostgreSQL and I totally agree - PostgreSQL or any RDBMS isn't the right choice for all jobs.

If the only way you can make it work is to build an inner-system or use EAV for everything, you shouldn't be using an RDBMS.

If you have a free-form data model that's not amenible to structural analysis and normalization, you shouldn't be using an RDBMS.

Unfortunately, most people think they have one or both of those things, but in fact they just haven't done the proper analysis and thought through it, so they jump straight for NoSQLWhateverIsFashionableToday. They realise all the features and code they have to write themselves at the application layer, do it badly, say their chosen database performs badly or is unreliable, and go looking for a different one.

I'm glad to see that modern RDBMSs are starting to gain better support for non-relational structures (PostgreSQL's hstore, improving json support, etc). Few applications these days work solely with data that's suited to relational modelling. Apps often benefit from globally transactional behaviour though, and it's nice not having to wrestle 2PC and transaction co-ordinators and the other horrors you get when dealing with more than one DB in an product.

(Pg plays really well with Redis too, by the way; it's a great caching layer and PostgreSQL's LISTEN/NOTIFY lets you do fine-grained invalidation of your Redis cache).

Re:what keeps us from switching ?

[dhasenan]

I think you mean HQL, not SQL.

Re:what keeps us from switching ?

[Anonymous Coward]

That's the vision. Not reality in enterprise.
EnterpriseDB has a whitepaper on Oracle compatibility that discusses "vendor lock-in": http://www.enterprisedb.com/wp-oracle-cost-containment

Re:what keeps us from switching ?

[evilviper]

A big code base in PL-SQL I guess that nobody wants to re-write

PL/SQL compatibility is EXACTLY what EnterpriseDB offers, for a price vastly lower than Oracle.

Re:what keeps us from switching ?

[sjames]

Compared to having your jaw removed,. a root canal is great!

Re:what keeps us from switching ?

[Anonymous Coward]

... did real people decide that SQL was a rational way to store data?!

I see what you did there.

Re:what keeps us from switching ?

[plopez]

SQL doesn't store data. SQL is a scripting language to an interface.

Re:what keeps us from switching ?

[Anonymous Coward]

Thats why you let your business logic coding framework abstract data access away from you. A customer is a customer with its attributes in any reasonable programming language, and whats the exact syntax and bitstream that needs to be sent between two or multitude of hw boxen is really irrelevant.

Re:what keeps us from switching ?

[divide overflow]

I'd call SQL a special purpose language designed for managing relational databases.

Re:what keeps us from switching ?

[Anonymous Coward]

From all the things done to me at the dentist, I found root canal treatment to be the least painful (I have had three).
Especially the pain afterwards, well there are no more nerves, so no pain.

Re:what keeps us from switching ?

[uncqual]

SQL has nothing to do with how data is stored or accessed. The storage and access are many levels below the language.

It's like saying that car A is superior to car B because car A is painted pearl purple and car B has a CVT held together with 12mm bolts instead of a manual trans.

Re:what keeps us from switching ?

[pestilence669]

The lack of cross-document ACID in MongoDB is both overblown and overlooked. Mongo will work in specific domains, as long as you put a lot of forethought into your schema. For a lot of cases, however, you just can't get around multi-stage commits, background batch processing, etc. ... essentially, hacking minimal "transaction" support offered by an RDBMS.

Re:what keeps us from switching ?

[Anonymous Coward]

This is the best thing about SQL: it's a standardized language letting you switch between different database vendors with fluidity.

... and usually, great expense unless you use FOSS databases in which case you are shit out of luck if the guy handling the integration work for your platform gets hit by a bus or just decides he wants to quit.

Re:what keeps us from switching ?

[Anne Thwacks]

I wroked in a place with about 5,000 lines of PL-SQL. That was a nightmare.

OTOH, Oracle need not fear people using pirate copies: there are so many bugs that without being signed up for an expensive support program, your system will never fly.

Re:what keeps us from switching ?

[Anonymous Coward]

Ever try to store an array of strings?

There's nothing preventing someone to create a string array type in a good RDBMS supporting a reliable type system. It would not even violate Relational Model although I would say it's highly questionable encapsulation practice. OTOH an array of string (or any other type for that matter) is just a table with two attributes: one is an index integer and the other is a string. Of course, you'll have to switch your mind from looping to set manipulation, but that's it.

Re:what keeps us from switching ?

[Codifex Maximus]

I'd call SQL a special purpose language designed for managing relational databases.

Technically, plopez is correct. SQL is the scripting language, with version differences between implementations, to the native interface for a particular RDBMS. Many RDBMS provide access to that native interface bypassing SQL.

Re:what keeps us from switching ?

[K. S. Kyosuke]

An array of strings is an array of strings. Chances are that in some applications, you don't want to have this as a relational predicate. I mean, even Date's doorstopper talks about custom compound types. If you're trying to turn everything into relational predicates, you might as well be storing strings themselves in relations over characters.

Re:what keeps us from switching ?

[K. S. Kyosuke]

It's emphatically not a one size fits all.. but the question becomes what is your major use case, and what performance needs do you have.

Performance seems to me like a poor reason to decide whether to do things in or outside of the relational model. The model is supposed to capture the semantics. The database is supposed to take care of the physical layout and performance. At least that's how grandpa Codd designed the whole thing, until IBM and Oracle screwed things up for everyone, starting with the "invention" of this horrible thing called SQL. It only shows that the commercially used implementations suck, not that the original idea is wrong for data management.

Re:what keeps us from switching ?

[Nivag064]

Java EE 7 is out with better support for HTML 5 etc.

See http://www.wildfly.org/

Re:what keeps us from switching ?

[macson_g]

Mongo will work in specific domains, as long as you put a lot of forethought into your schema

So will .ini files! And, who knows, they may even be more reliable!

Re:what keeps us from switching ?

[Anonymous Coward]

MongoDB certainly isn't for everyone, but their commercial support is awesome.

Oracle, on the other hand, is certainly the catch-all enterprise database - and its support is hell on earth.

Re:what keeps us from switching ?

[RabidReindeer]

I wroked in a place with about 5,000 lines of PL-SQL. That was a nightmare.

OTOH, Oracle need not fear people using pirate copies: there are so many bugs that without being signed up for an expensive support program, your system will never fly.

PostgreSQL is about the closest open-source equivalent to Oracle. Reputedly, they both come from the same parent. At any rate, converting PL-SQL to its PostgreSQL equivalent is no walk in the park, but is a lot easier than a lot of other conversions.

Regardless, if you discover your developers have been indulging in extensive use of stored procedures, you should immediately escort them out the door. I'm speaking from bitter experience.

Stored procedures can be very efficient sometimes, but often, they just add additional load to the DBMS server that could have been distributed among application servers. Stored procedure code is also not as likely to be version-controlled and restoring code backups means a database restore. Also, splitting logic between the application server and database server can result in even the most trivial mods requiring a time (money) consuming "treasure hunt" to locate where the affected code is and careful co-ordination of the mods between the two serves.

Last, but hardly least is the fact if you do want to switch out DBMS products, it's going to be very, very expensive, since not only the aforementioned PL-SQL would have to be rewritten, but the applications would likely be seriously traumatized as well.

Re:what keeps us from switching ?

[tqk]

... and usually, great expense unless you use FOSS databases in which case you are shit out of luck if the guy handling the integration work for your platform gets hit by a bus or just decides he wants to quit.

WTF is your problem? No really, what does that even mean? You've never heard of proprietary developers suffering bus errors or quitting, not to mention failing to document their work? How is any of your rant exclusive to FOSS? You met one once, and they failed to part the red sea for you? We've all met people who say they're wizards but aren't, and people like that are likely the majority in every line of endeavour.

Glossy brochures and slick salesmen locking credulous buyers (tech. ignorant managers) into non-standard, proprietary tools is a much bigger problem than mere "find a developer who doesn't suck" follies. Hiding behind all that to think about are the boatloads of cash involved. Is that cash going to get you where you want to go, or is it being pissed into a river? At least with FOSS, you have a chance to objectively test for performance and features without needing to first help somebody buy themselves an island paradise.

Re:what keeps us from switching ?

[jedidiah]

No. Performance is just the wrong thing to be fixating on. Data management systems are not about being fast. They are about being correct. They are also about being robust. What you are really saying is that you don't care about your data.

You're willing to lose everything just to save a buck.

That's the wrong attitude to have regardless of how you decide your data needs to be organized.

Re:what keeps us from switching ?

[jedidiah]

As long as that FOSS database uses the same mental model and basic syntax as one of the commercial RDBMS products, the amount of tribal knowledge lost from your cheapness will be minimized.

The real problem is that you are far too cheap to pay more than one person to be the keeper of the keys and that person you are underpaying so eggregiously underpaid that they will likely leave at the first opportunity.

Re:what keeps us from switching ?

[jeremyp]

PostreSQL's ancestry is more related to the (probably defunct now) commercial Ingres database manager.

Re:what keeps us from switching ?

[jeremyp]

SQL is not a way to store data. Whoever gave you that idea? SQL is a language used to manipulate and modify relational databases. It can also be used with minor modifications to query other types of database e.g. a JCR compliant content store.

Similarly a relational database doesn't have to use SQL as a query language e.g. the original Postgres and Ingres both used QUEL.

By the way, the correct way to deal with arrays of strings is to normalise your schema first because that gives the most relational model with the best flexibility and is the "right" solutions" and then, if there is a performance issue, denormalise the string array again. PostrgeSQL has an array type, by the way.

Re:what keeps us from switching ?

[K. S. Kyosuke]

What you are really saying is that you don't care about your data. You're willing to lose everything just to save a buck.

Actually, that's exactly the opposite of what I'm saying.

Re:what keeps us from switching ?

[aztracker1]

Grampa Codd wasn't trying to handle a million simultaneous requests across billions of records with expected response times in a fraction of a second. You couldn't create a functional search engine, for instance with any RDBMS. When your highly normalized data takes 28 joins in a primary query, with 4 other queries to render the primary contents of a single page, and a server that has 8 cpu cores, and 24gb of ram is crawling, but a mongo instance as a test on another server with 1/3 the memory, and 1/2 the cpu load can handle 90% of the work load away from the database server then you need to rethink things.

If you think performance doesn't matter, you are fooling yourself, or dealing with projects, or people that simply don't care.

Re:what keeps us from switching ?

[aztracker1]

See RethinkDB, or CouchDB .... Just because you are moving away from a relational model doesn't mean throwing out everything a relational database gives you... You can store your data differently in order to scale your load more effectively. There are many techniques to do this. In my last job, I chose to export a denormalized version of updated records, so that they could be queried, and retrieved much faster in a non-relational database. The RDBMS was there, but pretty much write-only.

Re:what keeps us from switching ?

[fuzzytv]

PostgreSQL is about the closest open-source equivalent to Oracle. Reputedly, they both come from the same parent

Ummmm, what? AFAIK there's no common ancestor or anything like that, and the projects come from very different environments. PostgreSQL originates from Berkeley research projects, Oracle is a purely commercial project. They are both ACID-compliant WAL-based relational databases and may be similar in other aspects but there are many significant technical differences (e.g. in the MVCC implementation etc.).

Regardless, if you discover your developers have been indulging in extensive use of stored procedures, you should immediately escort them out the door. I'm speaking from bitter experience.

Stored procedures can be very efficient sometimes, but often, they just add additional load to the DBMS server that could have been distributed among application servers. Stored procedure code is also not as likely to be version-controlled and restoring code backups means a database restore. Also, splitting logic between the application server and database server can result in even the most trivial mods requiring a time (money) consuming "treasure hunt" to locate where the affected code is and careful co-ordination of the mods between the two serves.

This, however, is mostly nonsense. It's true that stored procedures may add load to the DBMS, however it's equally true they may significantly reduce the overhead. What's more efficient - transferring 1M rows to an application server and handle it there (often submitting the result back to the DBMS as a parameter for the next query), or just process it locally within a stored procedure? Not to mention that the "application servers" usually manipulate the data using languages that are very poor match for such operations (which is basically everything except for SQL).

If you had poor development practice (and the fact that you are saying that "stored procedure code is also not as likely to be version-controlled" strongly suggests this), then sure - it might be nightmare. But it's not a fault of stored procedures but purely your fault.

It's true that it may not be as straightforward to decide what to place into the application server and what should be placed into the stored procedures. You simply need to treat them as separate components - I know about apps that built API on the database using stored procedures, and access it almost exclusively just through this - and it works perfectly. But if you're just piling up a query here, a call to a procedure there, then sure - it's going to be a mess. I do see this frequently in ORM-based Java apps, that somehow start with the idea "ORM is going to handle all of this, no SQL code at all" and then they start to write plain SQL queries / procedures as workarounds for performance issues. Well, they certainly deserve that.

Last, but hardly least is the fact if you do want to switch out DBMS products, it's going to be very, very expensive, since not only the aforementioned PL-SQL would have to be rewritten, but the applications would likely be seriously traumatized as well.

Amen, brother! Many believe that all relational databases are somehow equal and you can painlessly migrate the application from one to another. Well, turns out it;s not true - the common API (SQL) does not mean they share the same features / characteristics etc.

Re:what keeps us from switching ?

[colinrichardday]

So PostgreSQL has only one guy handling the integration for your platform?

Re:what keeps us from switching ?

[RabidReindeer]

PostreSQL's ancestry is more related to the (probably defunct now) commercial Ingres database manager.

At one time I'd heard rumors that all 3 had common roots, but (officially, at least), Oracle claims to be totally self-designed.

Ingres took a vertical turn several years back, being bundled with various financial applications. Although it's rarely heard of these days, their website has been updated fairly recently.

Re:what keeps us from switching ?

[RabidReindeer]

Stored procedures can be very efficient sometimes, but often, they just add additional load to the DBMS server that could have been distributed among application servers...

This, however, is mostly nonsense. It's true that stored procedures may add load to the DBMS, however it's equally true they may significantly reduce the overhead. What's more efficient - transferring 1M rows to an application server and handle it there (often submitting the result back to the DBMS as a parameter for the next query), or just process it locally within a stored procedure? Not to mention that the "application servers" usually manipulate the data using languages that are very poor match for such operations (which is basically everything except for SQL).

The first line of defense against loading the DBMS server is to start with intelligent queries and organization. Logic should avoid chewing through millions of rows in a programmed manner no matter which machine the chewing is done on. The DBMS is optimized for information retrieval and updating, and stored procedures can potentially interfere with that optimization. So rule 1 is to reduce what gets acted on. If you do that, the relative overhead of pushing it out to client machines is less of an issue and even the fact that the client machines are probably going to be less powerful won't matter as much, because there will typically be more of them to share the computing load. Because they're also likely to be less expensive machines, the next cost per computation is often lower as well if the DB server's CPUs are being efficiently used.

If you had poor development practice (and the fact that you are saying that "stored procedure code is also not as likely to be version-controlled" strongly suggests this), then sure - it might be nightmare. But it's not a fault of stored procedures but purely your fault.

I'm not going to accept fault on that, because I don't do stored procedures except in cases of compelling need. But whereas applications in C# and Java are typically managed by an IDE and bound to a Version Control System, every database system I've had to clean up after basically had someone editing the procedure in the equivalent of Windows Notepad and jamming the code straight into the database with no local project management at all. A few DBMS's have the ability to retrieve earlier generations of database objects, but it's the exception rather than the rule.

In short, I'm making my recommendations based on what I commonly come in and find has been going on, not on what would be the case in an ideal world.

Re:what keeps us from switching ?

[K. S. Kyosuke]

When your highly normalized data takes 28 joins in a primary query

So what? That only means that the current DB solutions on the market are crap. It's like the interpreter-versus-compiler debate. If you really have to do those joins physically every time instead of interpreting them as constraints directing the physical storage and retrieval, of course it's going to be slow. That's why you don't write interpreters when you're serious about a language implementation. You write a compiler. Once you do that, the source file describes what is the code supposed to do (the semantics), but the way how it will get executed (the pragmatics) will be quite different. The problem is that IBM and Oracle established SQL as the baseline, started adding "features" which make optimizations difficult, and now we're in position when making any substantial improvements to the performance of these systems is very difficult, because of all those corner cases, backwards compatibility, sheer inertia of the existing systems, the works. The net result is that the only difference between Oracle and MongoDB is that Mongo DB doesn't have any separation between logical model and physical storage, while Oracle et al. pretend to have one (of course, everyone sees through it, but out of politeness, nobody speaks out).

I very much do think that performance matters. But systems like Oracle are the database equivalent of Ruby 1.8 implementation, making it virtually impossible to have good designs and top performance at the same time. What the DB people should be aiming for is something like their own DB equivalent of Stalin. Or at least GHC.

Re:what keeps us from switching ?

[fuzzytv]

Stored procedures can be very efficient sometimes, but often, they just add additional load to the DBMS server that could have been distributed among application servers...

This, however, is mostly nonsense. It's true that stored procedures may add load to the DBMS, however it's equally true they may significantly reduce the overhead. What's more efficient - transferring 1M rows to an application server and handle it there (often submitting the result back to the DBMS as a parameter for the next query), or just process it locally within a stored procedure? Not to mention that the "application servers" usually manipulate the data using languages that are very poor match for such operations (which is basically everything except for SQL).

The first line of defense against loading the DBMS server is to start with intelligent queries and organization. Logic should avoid chewing through millions of rows in a programmed manner no matter which machine the chewing is done on. The DBMS is optimized for information retrieval and updating, and stored procedures can potentially interfere with that optimization. So rule 1 is to reduce what gets acted on. If you do that, the relative overhead of pushing it out to client machines is less of an issue and even the fact that the client machines are probably going to be less powerful won't matter as much, because there will typically be more of them to share the computing load. Because they're also likely to be less expensive machines, the next cost per computation is often lower as well if the DB server's CPUs are being efficiently used.

Intelligent queries and organization - sure, no argument here. However there are cases when you really need to chew through millions of rows, and doing that in SQL directly without imperative code is nearly impossible. And I wasn't saying that it's a good idea to replace queries with stored procedures - that would be the dumbest thing in a relational database (which is exactly about running queries). If you can write a query solving your problem well, don't write procedures. No doubt about that.

I think we're both speaking about different cases, though, so I'm missing your points and you're missing mine. I'll illustrate my reasoning by an example of an actual project that I had in mind ...

About 2 or 3 years ago I was asked to help with a project in serious troubles - many things went wrong during the development, from starting the development on MySQL+Tomcat and switching to Oracle+Jboss, to heavy usage of Hibernate mixed with crazy "optimized" SQL queries.

The largest problem was that they needed to monthly balancing (it was a system for managing retail network). The business process behind this is rather complex, and the process steps need to work with large sets of rows (chew through all the transactions for all sales, salesmen, branch ... and handle each transaction according to product type, if it's a cancellation or not, what were the previous transaction for that person, ...). Doing this in a single SQL query might be possible, but it would be incomprehensible, unmaintainable, ... a bit of imperative programming is a better match here.

So the developers decided to do this on the application server, and to mess it up as much as possible, they used the Hibernate definitions optimized for the front-end (lazy fetches everywhere, ...). The result? Gigabytes and gigabytes of data were moved back and forth, the process was running for unpredictable amounts of time - sometimes 12 hours, sometimes 60 hours (which is rather unfortunate as it's longer than a weekend). Even if they optimized the Hibernate mappings heavily for batch processing, it would probably run for hours (although probably more predictably), the application server would scream in pain etc.

This is the exact place where stored procedures are a good match - with a bit of

Re:what keeps us from switching ?

[motokochan]

Ingres is still supported and developed commercially by a company called Actian. They also have a GPL version of the database software you can download.

It's certainly not anything in high demand, but it seems that it's still commercially viable for now.

Re:what keeps us from switching ?

[Anonymous Coward]

What's more efficient - transferring 1M rows to an application server and handle it there...

Ah, NO!

Until recently, I had been dealing with a system (several hundred Java apps) backed by a database where the lead[!] developer was slurping over 1M records including all columns with no indexes from the database for every request to the Java apps just to get a count and quite a few requests took 15 days for the database to complete AND it managed to hammer the shared enterprise storage so hard that it affected all other clients using the same enterprise storage.

Re:what keeps us from switching ?

[DuckDodgers]

I work at a small company that has a Java web application in front of a Postgres database. For us, the PL/pgSQL (Postgres equivalent to PL-SQL) snowballed because it's something you can add and change while the database is running without kicking your current users off the web application. Unless of course you screw up.

It snowballed from there. Most of the application is still in Java, but we have a heavy set of stored procedures because once we had them working well enough to run on the production system, it's hard to justify re-implementing them in the Java code when the next big feature is a high priority.

I'm not saying this is a smart way to do things. It's not, and we have a boatload of documentation and testing for each release around the compatibility between the database, the stored procedures, and the application code. But I imagine the way it evolved for us is not unique.

Re:what keeps us from switching ?

[DuckDodgers]

Yeah, we just have a note with each release - run a copy of the software against a copy of the production database, and manually run all of the stored procedures and see what breaks.

Re:what keeps us from switching ?

[BigZee]

I was surprised to read some of your comments. When I've dealt with database performance problems, it's often not an inefficient piece of SQL that's the cause but the back and forth of vast quantities of data between the database and the app layer that is the problem. Only a change to the law of physics is going to fix that, or doing the processing in the right place. All modern databases are a lot more than simple data stores, they are also data processing engines.

Re:what keeps us from switching ?

[RabidReindeer]

I was surprised to read some of your comments. When I've dealt with database performance problems, it's often not an inefficient piece of SQL that's the cause but the back and forth of vast quantities of data between the database and the app layer that is the problem. Only a change to the law of physics is going to fix that, or doing the processing in the right place. All modern databases are a lot more than simple data stores, they are also data processing engines.

A lot of if depends on what type of application you are dealing with. When the applications are web applications, there's a limit to how much heavy computation you want done anywhere, since the response should be as close to real-time as possible. As a corollary to that, in most cases, there should be a 1-1 correspondence between displayed records and retrieved records. This may require advance (batch) computation to be done and/or de-normalization of the database, or even use of a non-SQL datastore.

For offline batch processes, the rules change. However, just because something can be done more efficiently when confined to the DB server doesn't always make it more cost-effective. Competent PL/SQL people are in short supply (especially when the DBA lays down the law and refuses to double up as an applications programmer), whereas it's fairly easy to find people trained in C#, Java, Python or even COBOL. As a result, overall operational cost and system reliability often favors shovelling records back and forth across the LAN despite its less optimal use of the database machine (and LAN) itself. In fact, I worked in one shop where people who were over-eager to develop stored procedures were slapped down.

There's no one correct answer for everything. There are, however answers that occasion cause for regret. Back circa Y2K, a lot of local shops bought into Java EJBS where Session EJBs invoked stored procedures for the dirty work. The original EJB architecture had its drawbacks independent of the database component, but those shops have abandoned that approach for reasons unrelated to the warts in EJBs.

Re:what keeps us from switching ?

[Anonymous Coward]

NoSQL databases - the new 2-column table.

Support contracts

[Anonymous Coward]

It is common for application support by vendors to only apply when specific database versions are used. If that doesn't include postgres, you're usually crazy to use postgres.

Re:Support contracts

[Anonymous Coward]

It is common for application support by vendors to only apply when specific database versions are used. If that doesn't include postgres, you're usually crazy to use postgres.

No, if it doesn't include Postgres, you're usually crazy to use that application.

Re:Support contracts

[colinrichardday]

Yes, +1

MongoDB--run away

[Anonymous Coward]

MongoDB, run away, run away quickly if you need anything close to ACID or XA.

Re:MongoDB--run away

[Maudib]

Mongo is ACID compliant at the document level.

Its not the right choice for everything, but defaulting to "run away" is just nutters.

Re:MongoDB--run away

[Billly Gates]

"Mongo is ACID compliant at the document level."

But not where it counts.

Re:MongoDB--run away

[interval1066]

MongoDB IS NOT acid compliant as far as enterprise is concerned, and its not meant to be. Use a spanner where a spanner is nessessary, and a hammer where a hammer is nessessary.

Re:MongoDB--run away

[Nuitari The Wiz]

I would call it experienced, same as me.

Where I work, someone had the bright idea that since you could stuff arbitrary data in mongo (for example multi dimensional arrays) that it should be the proper way of doing it.

So now I'm stuck untangling a website that manages millions of pieces of data in about 860 documents or so...

There are also various interesting issues that happen if you have a collection that start with _
Its only possible to edit it through the api, and not the command line mongo shell. The bug was open about 3 years ago and has yet to be addressed. Things like that make me feel that there is still a lack of maturity where it counts.

Re:MongoDB--run away

[macson_g]

Mongo is not worth the pixels it's been written on. I've been there, I've seen that, I don't wish my worst enemies to go there again.
You can store any arbitrary data, index it in any way, and run any language you like server-side with Postgres. And all this with full ACID, reliability, deterministic resource consumption and very decent performance.
MongoDB is a toy in comparison.

Re:MongoDB--run away

[lgw]

MongoDB can't be beat for "store it now, figure out what to index later" data, especially log files. It's probably the best tool for that job.

Where there's a gaping hole in available software, either open source or commercial, is a solution for cloud-scale commodity-server DB sharding solutions. If you know how to partition your data (such that the only queries that need to run across partitions are map-reduce-style queries, and everything ACID stays insdie one partition per query), it should be off-the-shelf easy to do that with 10 or 10000 cheap database servers.

But that's all homebrew now. AFAIK, all the big cloud players other than Google work that way for the non-logfile data. E.g., Facbook uses sharding over MySQL. But everyone has rolled their own, and no one has open sourced it or made a product from it. It's very strange.

previous life

[Anonymous Coward]

my previous employer had a similar decision to make when they were restructuring the company. the powers that be decided to pay Oracle big $$ just because of name recognition ... and for the off chance that it would make the company a more appealing acquisition candidate.

imo, if your enterprise is optimized for postgres, you'd be crazy to switch. rearchitecting would be a son-of-a-bitch.

Look at Enterprise DB

[Anonymous Coward]

Is not open source, but is a good alternative for testing postgres as first step with a high % of oracle compatibility http://www.enterprisedb.com/solutions/oracle-compatibility-technology and later you should probably migrate to the open source version that is great, sorry mysql guys but I love the complex sql querys than I'm able to create at postgres without temporary tables and the engine mess... (personal opinion).

Re:Look at Enterprise DB

[Tough Love]

Enterprise DB is basically PostgreSQL

Re: Look at Enterprise DB

[turbidostato]

Which is BSD licensed so your point is, again?

The sorts of things you get

[jbolden]

Materialized views (and all the related magic)
Flashback queries and flashback archives (they are really cool)
Index only scans (can be a major performance boost)
No transaction control in stored functions

Oracle handles queries that return 50k plus records far far better.

Oracle uses a statistical optimizer for execution plans in the engine. They are working through the 2nd generation of it to handle situations where they are lots of high frequency values

Temporary table undos

Oracle is really an excellent product for a database in which there will be DBA maintenance. If there aren't DBAs Oracle's complexity becomes a minus not a plus.

Re:The sorts of things you get

[Nerdfest]

Oracle's complexity and vendor lock-in is a minus to the extent that if there is *any* other way to solve the problem, including using MS-SQL, Sybase, or even DB2, use the alternative.

Re:The sorts of things you get

[Anonymous Coward]

Index only scans exist in Postgres 9.2, so I imagine your comparison here is quite out of date

Re:The sorts of things you get

[Anonymous Coward]

PostgreSQL 9.2 added index only scans.

Re:The sorts of things you get

[kuhneng]

Index only scans were added to postgresql (some caveats) in 9.2. The optimizer is cost/statistics based, though perhaps marginally less mature.

What I miss are strong partitioning support, implicit query parallelism, incremental backups, clustering (RAC), and materialized views. Most / all of these features matter primarily for reporting / analytic workloads.

PostgreSQL is a superb database, and dramatically easier to work with and manage than Oracle on a day to day basis. For transactional workloads at anything but the largest scale, it's excellent. On reporting and analytic workloads, it hits the wall much earlier but is still a good option for many needs.

Re:The sorts of things you get

[WaywardGeek]

I'd mod you up if I had mod points... I'm no fan of SQL, but I likely will use postgres on my next project where I have the freedom to choose.

Re:The sorts of things you get

[hibiki_r]

There's also the Evil Oracle Magic that lets you change query plans on the db directly, if Oracle itself is unable to come up with the best plan. In Postgres, the database is expected to figure everything out based on costs and statistics, which works well most of the time, but will kill you for specific kinds of queries. For instance, if you have 4 where clauses in different tables, postgres' static analysis will have no idea of whether each extra clause is any more or less selective than it'd be vs the entire dataset. If this is not the case, Postgres can make very wrong assumptions about how many rows you'll fetch, and thus come back with very silly query plans.

In Oracle, you have a chance of being saved by the fact that the optimizer learns from this kind of mistakes, or, in the worst case scenario, the DBA can just assign a very specific plan to your query on the fly, which leads to great performance gains without having to change code. Postgres keeps getting better in every release though, and Oracle's licenses are not getting any cheaper.

Re:The sorts of things you get

[Anonymous Coward]

This nice thing is that if you're doing a ton of OLAP stuff you can always ETL your data out to something like InfoBright or Vertica.

IMO the biggest reason to stay on Oracle RAC & good partitioning. It appears, however, with 9.2 and writable fdw's, that that's Postgres's next target. Federated servers are possible now.

Re:The sorts of things you get

[Anonymous Coward]

For what it's worth, 9.3 is getting materialized views.

Re:The sorts of things you get

[Anonymous Coward]

if the requirement is really big enough - but i struggle to imagine many big monolithic requirements that wouldn't be better subdivided anyway.

when it's my money it's Postgres - i am sure others would agree.

Re:The sorts of things you get

[denmarkw00t]

Flashback queries and flashback archives (they are really cool)

This this this. Working with Oracle was very interesting at a few years back. Odd things, like not being able to do a LIMIT, OFFSET in an easy mannor (read: any way but LIMIT, OFFSET) was so strange - the DBAs explained it as something to do with how Oracle manages row count and the uncertainty of the rows returned? idk, it's been a while. They did give us a way around...but, I digress.

Flashbacks are nasty cool - the way I understand it, as I was only watching the dev who about two hours before had hosed a production database, is that you can SELECT INTO FROM a point in time. We had a DBA on the line who walked him through the flashback, and before we knew it, the DB was back to the state it was in hours before.

HOWEVER. Go with Postgres. Stick with Postgres. No reason to shell out all that cash for licenses, and Postgres is powerful enough to do just about anything you need it to, imho.

Re:The sorts of things you get

[aztracker1]

I was going to make a few of the same points... my biggest issue with Oracle, is it isn't friendly to a dev-ops environment, where development overlaps with operations. In those types of environments, imho PostgreSQL, MongoDB and MS-SQL win over most of the rest. MySQL has always been so inconsistent that migrating apps to/from it have been problematic (same is often true for MS-SQL). If you don't have (a couple) full-time Oracle DBAs in house, then you are best avoiding it as much as humanly possible.

On the flip side, if you need *REALLY* big data storage in a transactional+relational database, you aren't going to see much in the way of competition here. Most will move to no-sql solutions for those instances though. ymmv.

Re:The sorts of things you get

[Anonymous Coward]

The CFO likes Oracle Financials (and probably hentai porn) is another...

Re:The sorts of things you get

[greg1104]

There are a some ways to force a query plan onto Postgres that works effectively as hints. See my Hinting At PostgreSQL. It's also possible to overide how Postgres runs selectivity functions to get different results. That mechanism is powerful enough that you can do almost everything possible with hints and then some. The problem is that it's too difficult for most to develop their own statistics model just to fix a broken query. When the alternative is sucking on everything Oracle makes hard, I can't understand why people aren't willing to do this the right way sometimes.

Re:The sorts of things you get

[greg1104]

It doesn't have a slick UI, but you can do the same thing as Flashback on Postgres. You take a base backup of the database and regularly save write-ahead log files. When you need old data, you have to spin up a new database instance, ask it to replay to that point, and then get the data into the original instance. It won't win any design awards, but I recover lost data with this technique all the time.

Re:The sorts of things you get

[MacDork]

Off the top of my head, the sort of thing you don't get with Oracle:
select * from table limit 10 offset 20;
source code
free

I recently benchmarked postgres 9.2.4 on a Dell PowerEdge at Rackspace with a four disk raid 10, a two disk raid 1 for the WAL logs, and 48GB of RAM. It's good up to around 14000 transactions per second until you exceed what fits into RAM. Then it drops off to around 2000. That was the select benchmark with no writes involved.

grahamsaa, if you really want to know what postgres can do, I suggest you install it and run some benchmarks to find out for yourself. You can find all the info you need to do this in Postgresql 9.0 High Performance It won't cost you anything to do this and if you decide it can't handle your workload, then you can always go purchase Oracle.

Re: The sorts of things you get

[SDrag0n]

I hate it when I get too many where clauses on my tables. You know, the kind where tables have where clauses.

Re:The sorts of things you get

[RedLeg]

Oracle is really an excellent product for a database in which there will be DBA maintenance. If there aren't DBAs Oracle's complexity becomes a minus not a plus.

I could not have said it better. To get your money out of Oracle, you must have a seasoned, professional DBA, usually two or more (production / development). Further, Oracle really takes advantage of higher end hardware, and that takes a different kind of dba experience and expertise to optimize. You're probably on it for a good reason, particularly if on higher end Sun hardware.

Oracle bought Sun for a reason, they could not afford for the platform to go away.

Hope this helps.....

-Red

Re:The sorts of things you get

[Anonymous Coward]

I have been an Oracle DBA for over 15 years. I agree Oracle is an excellent product. In fact, I have built two tech companies based on Oracle.

But, really, Oracle is totally replaceable by Postgres. The resistance does not come from feature limitations, rather environmental. EnterpriseDB has laid out "vendor lock-in" well in this whitepaper: http://www.enterprisedb.com/wp-oracle-cost-containment.

Re:The sorts of things you get

[Anonymous Coward]

Sybase just got bought by SAP and increased their price by 600%

Re:The sorts of things you get

[epine]

This this this.

Is that a pickup line? Does it ever work? Have you taped to your scrotum a servo motor extending a tie-rod to your zipper tab? Is is activated by Google Glass via a Bluetooth link whenever you say those words? That's a neat way to solve the eye contact problem.

Re:The sorts of things you get

[Anonymous Coward]

> Materialized views (and all the related magic)
Here you go
> Flashback queries and flashback archives (they are really cool)
Is that the same as time travel?

> Index only scans (can be a major performance boost)
Always glad to serve

> No transaction control in stored functions
Sorry -- I don't understand this one: Is that a feature or a mis-feature?

> Oracle handles queries that return 50k plus records far far better.
Would be interesting to quantify that. But yes, perhaps.

>Oracle uses a statistical optimizer for execution plans in the engine. >They are working through the 2nd generation of it to handle situations where they are lots of high frequency values
Here you go. Perhaps Oracle's statistics are more sophisticated: can you enlighten us?

> Temporary table undos
Don't know about this one.

> Oracle is really an excellent product
So is PostgreSQL.

Re:The sorts of things you get

[CBravo]

You know about postgres-xc ?

Re:The sorts of things you get

[magman]

Oracle database version 12c does limit, offset and these things: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#SQLRF55636

Re:The sorts of things you get

[theVarangian]

Oracle's complexity and vendor lock-in is a minus to the extent that if there is *any* other way to solve the problem, including using MS-SQL, Sybase, or even DB2, use the alternative.

My employer has been using AIX for stability reasons for a long time (since the very early 90s). At the moment neither MySQL nor Postgres guarantee AIX ODBC driver support. Only DB2 (obviously), Oracle and Sybase (IIRC) do that so there you are, another reason on to keep dealing with these companies. Whether you use MS-SQL, Sybase, or even DB2 is really irrelevant, they all force a degree of vendor locking, they are expensive and come to think of it, if you want support, MySQL is merely somewhat less expensive than the rest. I will agree that Oracle's pricing is armed robbery but they are not alone. I had a one third party driver vendor ask more money for a MySQL ODBC driver license for AIX (that allowed us to connect to one MySQL instance) than it cost to get a MySQL enterprise license. It was cheaper to migrate to DB2.

Re:The sorts of things you get

[Anonymous Coward]

If you have a problem so hard for an RDBMS to manage, maybe you're using a spanner to hammer those nails in.

Ever considered that?

Call a genuine language and do your algorithmic work there. Your database should optimise storage and speed for holding the data, not include your business.

Re:The sorts of things you get

[Anonymous Coward]

Unfortunately, you can't quantify that unless violating Oracle TOS.

I was working on my PhD thesis and performed a benchmark for federation tools. Oracle Heterogeneous Service showed the WORST performance of all. However I (accidentally) read the TOS and found that I can't release results of benchmarks without prior authorization.

That's very reassuring, isn't it?

Re:The sorts of things you get

[Anonymous Coward]

At the moment neither MySQL nor Postgres guarantee AIX ODBC driver support.

Is AIX ODBC any different from regular ODBC?

Re:The sorts of things you get

[jbolden]

I don't agree. The complexity isn't bad for databases that are designed for that complexity. You don't go from expecting a dedicated staff of dozens to only having one guy.

Re:The sorts of things you get

[macson_g]

Oracle has more features than PostgreSQL, no argument here. But Postgres development seems to b more agile, and these guys are catching up fast:

Materialized views (and all the related magic)

Partially there, more features to appear soon.

Index only scans (can be a major performance boost)

Available in PostgreSQL too.

Oracle uses a statistical optimizer for execution plans in the engine.

So is Postgres.

Re:The sorts of things you get

[jbolden]

postgres has been getting better and better all the time. Just like MSSQL the percentage of databases which it can't handle keeps going down. DB2 and Oracle are being forced into narrower and narrower niches.

Re:The sorts of things you get

[jbolden]

That isn't fast enough. Oracle can guarantee you that you can perform the operation in X seconds.

Re:The sorts of things you get

[jbolden]

No time travel isn't the same thing. Flashback is a time stamp associated with tables and savable to other DR databases. Which allows the database to be recovered to a point in time or a query answered from a point in time. "What were the list of all location is Massachusetts yesterday".

> No transaction control in stored functions Sorry -- I don't understand this one: Is that a feature or a mis-feature?

You want to be able to tell the engine how to execute.

Re:The sorts of things you get

[jbolden]

The first amendment still applies. Though you should contact Oracle.

Re:The sorts of things you get

[greg1104]

Whatever. If you paid me as much as an Oracle license costs, I could wrap the Postgres one with a service guarantee too.

Re:The sorts of things you get

[Anonymous Coward]

We don't have those in my country. And I looked around, couldn't find any independent benchmarks of Oracle software.

Re:The sorts of things you get

[jrumney]

And most important for the guy who makes the decision to drop six figures on an enterprise cloud solution, or whatever they're calling it this week, you get someone to shout at when it breaks.

Re:The sorts of things you get

[greg1104]

I recently benchmarked postgres 9.2.4 on a Dell PowerEdge at Rackspace with a four disk raid 10, a two disk raid 1 for the WAL logs, and 48GB of RAM. It's good up to around 14000 transactions per second until you exceed what fits into RAM. Then it drops off to around 2000. That was the select benchmark with no writes involved.

Note that much of this is tracing the physical limits of the hardware moving pages around memory and/or disk, not usually Postgres itself anymore. I've been tracing this shape of curve for years now. If you don't want the fall-off against larger than RAM workloads to happen so fast, you can use faster storage like SSD to pull that upwards a lot. Just have to make sure the storage supports reliable writes, which means only a few models of SSD--Intel's 320, 710, and DC S3700 models are popular.

In older versions there were a few places where the database would obviously bottleneck internally. As of 9.2, the main one that's left is that trivial statements run by a single client aren't as fast as, say, MySQL. A lot of that is that things like being able to replace operators has some penalty against a naive, unpluggable data type implementation.

Re: The sorts of things you get

[Anonymous Coward]

Just get off AIX.

Re:The sorts of things you get

[Anonymous Coward]

> No transaction control in stored functions Sorry -- I don't understand this one: Is that a feature or a mis-feature?

You want to be able to tell the engine how to execute.

No, the point of declarative languages like SQL is that you don't tell the engine how to execute.

Of course, given the context, I suspect you meant something completely different anyway, but failed epicly at putting it into words.

Re:The sorts of things you get

[magman]

> Flashback queries and flashback archives (they are really cool) Is that the same as time travel?

Nope, in Oracle you can run this query on any table to view the data it held yesterday:

select * from emp as of timestamp(sysdate-1);

>Oracle uses a statistical optimizer for execution plans in the engine. >They are working through the 2nd generation of it to handle situations where they are lots of high frequency values Here you go. Perhaps Oracle's statistics are more sophisticated: can you enlighten us?

At a first glance postgres seems to have all the important stuff in there, but I'm not sure how postgres handles the execution plans for prepared statements when you have skewed data and histograms on a column. This was a pain in oracle 10g and lower....

> Temporary table undos Don't know about this one.

Temporary tables in oracle are tables with data only available for the duration of your session. Everyone use the same table, but they only see their own data. Because there's no ACID compliance here there's very little redo/undo generation (WAL and undo)

Re: The sorts of things you get

[LDAPMAN]

The first amendment protects your speech from the government. It has nothing to do with oracle. Agreeing to a contract that says you won't disclose information is entirely different.

Re: The sorts of things you get

[jbolden]

The first amendment protects disclosures in the public interest. A tort requires government enforcement. The tobacco companies tried the same contractual argument against their employees and lost.

Re:The sorts of things you get

[jbolden]

Yes in general in SQL you want the engine to decide. Sometimes the engine is going to decide wrong. And that's where "hints" in Oracle make a huge difference.

Re:The sorts of things you get

[jbolden]

I doubt it. The engine has to be aware of how long it is doing things and schedule around various tasks that most complete every so often. That's a major overhaul of the code for Postgres.

Re:The sorts of things you get

[jbolden]

Oracle has more features than PostgreSQL, no argument here. But Postgres development seems to b more agile, and these guys are catching up fast:

No question. In 2000 Postgres was just incredibly slow. There was almost no Postgres applications that wouldn't have been better under Oracle excluding cost issues. The feature differences were tremendous. Today the differences are limited to a very small number of databases, Oracle is a niche product.

Re:The sorts of things you get

[flux]

Btw, PostgreSQL 9.3 (in beta 2 currently) has materialized views:

        http://www.postgresql.org/docs/devel/static/rules-materializedviews.html

Re:The sorts of things you get

[flink]

Off the top of my head, the sort of thing you don't get with Oracle:
select * from table limit 10 offset 20;

select a.* from table a where rownum >= 20 and rownum < 30

It's a little more complex in practice, but not too difficult once you know the idiom.

Re:The sorts of things you get

[dan_at_sqlite_org]

For those interested, SQLite also has a 2nd generation statistical optimizer for query plans in the works: http://www.sqlite.org/draft/queryplanner-ng.html And it already has index-only scans too.

Re:The sorts of things you get

[tqk]

Sybase just got bought by SAP and increased their price by 600%

SAP must really hate Sybase. Is there a Mafia vendetta in play here?

Re:The sorts of things you get

[Meetch]

> Flashback queries and flashback archives (they are really cool)
Is that the same as time travel?

Nope, in Oracle you can run this query on any table to view the data it held yesterday:

select * from emp as of timestamp(sysdate-1);

Better still: /* Which employee records did Joe accidentally drop again? */
select * from emp as of timestamp(sysdate-1) where empid not in (select empid from emp /* as of right now */); ... to see what you've dropped, and say if you know it was only that table affected then you don't even have to shutdown the database to get it back:
insert into emp (---insert above query here---);

At database level it's common before a potentially risky data change to create a flashback point, and if it messes up, shutdown, revert to the time you created the flashback, and pretend the changes never happened. It happens as fast as all the necessary extents can be written back to the data files and the database can be restarted.

But but but ... you are paying for those features. ~$10k per pair of intel cores per year before haggling... if all you want is a rock solid database, Postgres should be at least as good, and at a much lower price.

Re:The sorts of things you get

[greg1104]

Oracle has to do a complicated implementation in all cases because of how their rollback logs are structured, which makes certain types of things impossible to reverse without dipping into a record of earlier database states. But Postgres has fully transactional DDL in a way that's even a bit better than how Oracle approaches the problem. When you can rollback from DROP TABLE already, small jobs returning to an earlier state are possible without even dipping into the history.

We would have to dump a lot of disk space into the obvious ways to implement deeper rollback targets in Postgres, and Oracle has surely put a lot of engineering work into making that efficient. The design for Postgres I have in mind would be a disk pig. I know it would work though, because I'm already hacking together business SLA level rollback targets for people--things like "we must be able to recover from any fat finger error made in the last 2 hours in less than 10 minutes". All of the hard pieces needed are already inside of the replication and rollback code, they just need to be reassembled for this goal. There's even a generic background worker infrastructure in Postgres now, which makes it easy to create more schedule driven daemons like the existing background writer or autovacuum worker. Two years ago, even an inefficient Flashback clone would have been impossible for PostgreSQL to reach. Now enough of the internal components needed are there that it's just waiting for someone to pick an initial business goal target and build a UI to reach that one.

Re:The sorts of things you get

[greg1104]

My employer has been using AIX for stability reasons for a long time (since the very early 90s). At the moment neither MySQL nor Postgres guarantee AIX ODBC driver support. Only DB2 (obviously), Oracle and Sybase (IIRC) do that so there you are, another reason on to keep dealing with these companies

If you took any reasonable fraction of your database budget and pushed it toward a PostgreSQL support company, one of us would be happy to support an AIX ODBC driver. The options aren't just free and fully commercial. Smart companies help fund the features they need in order to enable a migration from the commercial databases to a free one.

Re:The sorts of things you get

[Anonymous Coward]

Just move the key where clause into an explicit inner view in your query. You probably should do this any way under the mantra of always minimizing what needs to be joined. "Filtering then joining" is usually faster than "joining then filtering".

Re:The sorts of things you get

[marcosdumay]

Also, Postgres has:

Booleans;
Arrays;
Identifiers with more than 30 characters (So that you can name it 'fk_table1_idname_table2_reason_for_the_relation', instead of 'fk_table_1_idname_')
Functional indexes...
And the most important feature: It's not sold by Oracle.

Re:The sorts of things you get

[Anonymous Coward]

My employer has been using AIX for stability reasons for a long time (since the very early 90s). At the moment neither MySQL nor Postgres guarantee AIX ODBC driver support. Only DB2 (obviously), Oracle and Sybase (IIRC) do that so there you are, another reason on to keep dealing with these companies

If you took any reasonable fraction of your database budget and pushed it toward a PostgreSQL support company, one of us would be happy to support an AIX ODBC driver. The options aren't just free and fully commercial. Smart companies help fund the features they need in order to enable a migration from the commercial databases to a free one.

I actually tried to convince a PHB of that once, "Get a guy to port Postgres ODBC driver to AIX 7" vs. "Buy Oracle license" .... guess what he opted to do.... he went for the Oracle license. You see the Postgres code requires gcc (at least it did back then), our AIX code is written wall to wall in xlc/xlC, the native AIX compiler and he figured opting for a port of the outdated ODBC driver to AIX 7-64 done by a developer we don't know anything about was a recipe for a headache. Oracle will rob you blind but their drivers are guaranteed to work on AIX there is no coding involved, only an installation script and Oracle specialists are a dime a dozen.

Re:The sorts of things you get

[greg1104]

Oracle is not guaranteed to work. Nothing can be guaranteed to work in software land; that's hard to do. What Oracle can do is say that they'll give you a refund of their high license fee if it doesn't work. And a company offering MySQL or PostgreSQL services can give you that same "guarantee". Want a 1 hour SLA for if you do hit a problem, and a team of developers available to resolve it? My company will sell you that for less than any Oracle license costs.

This is really a trust issue. Your PHB trusts Oracle to continue supporting something if they receive a lot of money. If he doesn't trust any company working with open source databases, that's his right. But saying Oracle provides guarantees in some unique way is FUD talking.

Re:The sorts of things you get

[Anonymous Coward]

My employer has been using AIX for stability reasons for a long time (since the very early 90s). At the moment neither MySQL nor Postgres guarantee AIX ODBC driver support. Only DB2 (obviously), Oracle and Sybase (IIRC) do that so there you are, another reason on to keep dealing with these companies

If you took any reasonable fraction of your database budget and pushed it toward a PostgreSQL support company, one of us would be happy to support an AIX ODBC driver. The options aren't just free and fully commercial. Smart companies help fund the features they need in order to enable a migration from the commercial databases to a free one.

Because that's what every enterprise operation worried about reliability wants to be: a one-off bastard configuration that literally no one else on the planet runs.

Re:The sorts of things you get

[Anonymous Coward]

Unfortunately, this really isn't a very good example of what makes PostgreSQL superior to Oracle. Oracle's version of this:

select
      *
from
      ( select
                        *
            from
                      foo
            where rownum 1000

This selects the 9000 rows from row 1000 to row 10000 (as ordered by the Order Clause). Also, if there is an index on foo on a,b it will be appropriately optimal.

It Depends

[djbckr]

Really, it depends. Is the stuff in Oracle using the database as a simple RDBMS? Then likely Postgres would be a good alternative. But there are many great features in Oracle that command the high price. The PL/SQL engine and all that comes with it is extremely powerful. Advanced Queueing is outstanding. The analytic functions are second-to-none. The tools that come with Oracle are great.

That said, I think most projects that need a database could do just fine with Postgres. I'm in the process of converting our corporate system from Oracle to PG now. I've worked with both systems extensively. For really large projects that need special features and absolutely bulletproof DR infrastructure, Oracle is the only way to go.

I choke when I say that, because I simply hate Oracle, the corporation. The database is stellar though...

Re:It Depends

[Nerdfest]

The problem with PL/SQL is that you're not really using it as a database anymore, you're using it more as an application, and you're tied to Oracle (Pro Tip: This is bad). If you're not really using it as a database, perhaps a language more open and flexible is more appropriate to your needs.

Re:It Depends

[Anonymous Coward]

This is not a problem - this is a benefit. Your code base works with the database - not against it.

Having written tons of PL/SQL, I know that our applications are magnitudes more efficient at returning data than those based on the separate database / app server model where the db is used basically as a smart bit bucket. Instead of sending raw SQL to the database, send the parameters to a package pipelined function and I'll return the exact rows you need in a much more efficient manner. And when it comes time to modify the db structure for feature changes - guess what, your middle tier code can stay the same, and use the same API. I'll work out the changes in the PL/SQL code, as I understand how the database works.

Yes it is proprietary - but so what - it works, and works well.

Re:It Depends

[Svartalf]

Here's a hint...you've got the same functionality (Just not the PL/SQL language) with Postgresql. In fact, you can do it in something resembling PL/SQL(PL/pgSQL), Tcl, Perl, or Python in Postgresql. Take your pick.

Sorry, just not buying the line you're selling there- it's not a net positive over Postgresql like you're making it out to be.

Re:It Depends

[ahabswhale]

You've also got your business logic spread all over the place. It sucks. I'm a contractor and I've yet to work at a company where having your business logic split between code and PL/SQL worked well. The ONLY benefit is performance, but in every other way, it completely sucks balls.

READ THE MANUAL FFS

[l0ungeb0y]

Most people I've met using Oracle don't know shit about it. It's great if you have lots of data and you want to harvest it with views and stored procedures. But the only people I've met seriously dealing with Oracle were qualified DBAs who only focus on DB dev and the Oracle DB was an internal DB that the web and remote entities DUMPED to.

It have never seen not used as a consumer facing DB for remote parties.
Though I have wrote a few apps that wrote to an internal Oracle DB and provided custom schema with procedures and views so that internal consumers could draft reports. But these were big ticket dudes and it was Marketing that wanted the views. I wrote the procedures for me to help me out as well as triggers etc. They were nice and gave me full admin access and Windows RDC into a Server. For a Global Retailer on the order of magnitude a brand like Levi's that was pretty hot and made my dick grow a couple inches.

But really Oracle and Postgres is Apples and Oranges. I say ditch Oracle, because you likely aren't using it for what it was created for and move to Postgres. The only reason why MySQL is so popular is because 90% of Web Developers don't know dick about what a DB is and how to properly use one. To them it's a data catchall, sock drawer etc. Rails style platforms and shit like Hive have only helped to propagate the fucktardery around DBs in Web Development.

When you can write 40% or more of your applicational business logic via stored procedures and views that Joe Blow Webscale can't possibly fuck up or mess with, you know you are an A-Grade Web Developer.

Re:READ THE MANUAL FFS

[Maudib]

Yes. Great developers use lots of...stored procedures?!?!?!?!

What fucking planet are you from?

Re:READ THE MANUAL FFS

[Anonymous Coward]

Business logic in stored procedures? I, for one, am glad I don't have to deal with that sort of nightmare.

Re:READ THE MANUAL FFS

[jellomizer]

One where you can change your logic faster.

Re:READ THE MANUAL FFS

[Hairy1]

Planet Oracle I believe. It is exactly this condesending attitude which we can do without. It is the same propoganda that the business rules should be in the DB so they are protected from the idiot know nothing developers. It is a claim in essenence that a DBA is superior and developers incompetent. There is such a thing as a business layer. The business rules can be enforced there. I know the orthodox thinking, but have never seen a good reason to believe it. I don't know how much time has been wasted on projects with developers fighting DBAs just to get their job done. Yes - stored procs do potentially have a role. In my experience it is a very limited role.

Re:READ THE MANUAL FFS

[Mitchell314]

Wimp. I write my system software in stored procedures. Except of using char* I just use a table column where every value is an ascii ubyte. :P

Re:READ THE MANUAL FFS

[Anonymous Coward]

Planet Oracle (yes, the one Larry purchased and colonized). It's actually a great place.
Everything that possibly could be is basically stored procedures, PL/SQL, compiled and executed in the RDBMS. Performance is great, security is great (as long as you're not a total idiot, or abusing dynamic SQL), configuration management is great.
The only stuff that isn't stored in the database is the front end presentation user interface layer.
This is how great enterprise software is architected.

Re:READ THE MANUAL FFS

[Anonymous Coward]

Been there, done that.

In 2010 I argued for hours over the course of several days about the right place to put business logic for a Java Web App. My claim was business logic should be in the Java code and well structured data should be in the database. It was a JSF / Hibernate app, if it matters, with a smattering of IBATIS and custom JDBC thrown in. (It was an 8 year old app at the time.) The DBAs were insisting that we write stored procs for everything - because somehow it was easier to change them later. (What?) And, if the schema changed, it was easier to update a Stored Proc with hard coded column names and relations, than it would be to alter a Hibernate mapping and recompile. (Double What??)

We "compromised" because the architect was a giant weenie which was worse than one way or the other. Some logic was in code, some was in the DB which made debugging a finger pointing exercise. All of it was a cluster fuck and unmaintainable. I left the project and company shortly after all this went down.

Re: READ THE MANUAL FFS

[Anonymous Coward]

Leave him alone! That's all he knows. Do you expect him to quit?

Re:READ THE MANUAL FFS

[casings]

Have you ever worked for a truly large company? I ask because you seem to trivialize the politics of the environment.

You are talking about cushy jobs for most of these people so there is incentive to CYA. You also have separate teams who report to separate managers who each control a layer in the application. You have the dba teams, the mainframe teams, the noc team, the platform team, the framework team, the other framework team, the application teams, the qa teams, the internal client teams, etc. If you looked at it from their perspective these people don't necessarily want to allow some wet behind the ears application team (because thats usually who are working at this layer anyway or worse offshore) to have so much control over what is essentially very proprietary business information. Can you really blame them though? If you're some medical company who deals with patient information, and you have HIPAA obligations, perhaps it can start to make sense? Even worse if you are publicly traded because then you have to deal with SOX.

Not to mention that there are many positive reasons to use stored procedures in general. Such as the ability to encapsulate your data structures in the database allowing you to change schema without affecting the application layer. Or allowing DBAs to identify areas to increase performance through indexes, etc. since they know every single query being run on their database. Or simply reducing round trips between the application layer and the database layer. Or increasing quality of code by inherently using transactions thereby hopefully reducing times when the database is in an incorrect state and not relying on an application developer to get that right. Also creates a uniform platform when you have multiple application teams. What about simply using stored procedures allowing your application to potentially switch database software with minimal code change, if written correctly.

There are many good reasons to use stored procedures.

Re:READ THE MANUAL FFS

[Ice Station Zebra]

We have one of "those" architects at work too. I just ignore him, more than likely he has never really coded an application and is only talking out his ass. Love iBatis/MyBatis. Way better than stored procedures and hibernate stuff.

Re:READ THE MANUAL FFS

[mooingyak]

Planet Oracle (yes, the one Larry purchased and colonized). It's actually a great place.
Everything that possibly could be is basically stored procedures, PL/SQL, compiled and executed in the RDBMS. Performance is great, security is great (as long as you're not a total idiot, or abusing dynamic SQL), configuration management is great.
The only stuff that isn't stored in the database is the front end presentation user interface layer.
This is how great enterprise software is architected.

That was mean. I'm going to have nightmares about this now.

Re:READ THE MANUAL FFS

[l0ungeb0y]

Well actually, to be blunt about it, I am from Earth.
But an alternate reality where we might use stored procedures with triggers to check shit and validate and roll back.
That is my most common usage scenario.

Yes, you can put that all in the middle tier -- but that can all be broken in the middle tier.

Re:READ THE MANUAL FFS

[t0y]

And because of the office politics stored procedures are good? Man... the DBA you describe is a developer. It's just that instead of using .net or java he's trapped in PL/SQL or T-SQL nightmare.

Re:READ THE MANUAL FFS

[jythie]

If you are fighting your DBAs to get the job done, your problem is political not technological. A good DBA and a clear separation of domains can make a developer's life easier and let them focus on the parts they are building.

Re:READ THE MANUAL FFS

[codealot]

Stop judging MySQL users. We use it because it has simple, foolproof replication. Period. And that's a damn good reason.

I know also what Oracle and pgsql can do. I've used both.

Re:READ THE MANUAL FFS

[Nerdfest]

I like Hibernate for applications up to a certain complexity at least. You can develop on Postgres, test on MySQL, and deploy on Oracle if you want and it's all transparent ... although I recently ran into a problem where there was something that would not work transparently in Oracle because of a strange way they do something. First time in the 2 years the system's been in development. If you really need to fine-tune things, iBatis is better, but more work and harder to maintain that Hibernate. The trick with Hibernate is to start developing from the business objects, not the database, a very basic thing that almost everyone gets wrong. Develop your models and let Hibernate design your database. Tweak it for performance afterwards where and when required

Re: READ THE MANUAL FFS

[SDrag0n]

There is such a thing as a database developer. You know why stored procedures are awesome? It's because letting a database engine use relational math properly can make thing wildly fast. The real problem is most application devs think they know all there is to know about databases but typically they barely even understand how an RDBMS work at a high level. With that though, don't let me stop you from running some query, copying a bunch of data across a network to load and process by an application, and then send some response back across the network to run another query to do something simple.

Re: READ THE MANUAL FFS

[SDrag0n]

Yes, there is such a thing as a database developer.

Re:READ THE MANUAL FFS

[Anonymous Coward]

Dude, SOX is NOT worse than HIPAA. If people think that they don't know either and or.

Re:READ THE MANUAL FFS

[fostware]

Sometimes, Oh hell yes...

SP are much faster, more efficient and flexible than fixing up the third party's munted Tomcat application.
This bloated rubbish is one of the only applications that fulfils the brief but there is absolutely no stored procedures or views, making it a slobbering mess speed wise.

Of course the business logic is better hidden, which is why I believe they did it that way.

Re:READ THE MANUAL FFS

[Anonymous Coward]

If you don't trust your developers with data then give them anonymized data or hire more trustworthy developers. Jesus christ, this isn't rocket science.

Re:READ THE MANUAL FFS

[Anonymous Coward]

Not to mention that there are many positive reasons to use stored procedures in general. Such as the ability to encapsulate your data structures in the database allowing you to change schema without affecting the application layer.

The other option is to put the equivalent of "stored procedures" outside the database and in a different layer (libraries/web APIs/services etc)

The disadvantage is it's a bit messier. The advantage is you can use better programming languages than what the DB supports AND you can potentially scale better since you use less CPU and other resources at the DB. Scaling a DB gets expensive fast.

Re:READ THE MANUAL FFS

[casings]

I didn't mean to compare, I meant if you were a publicly traded healthcare company that had to deal with both...

Re:READ THE MANUAL FFS

[hawkinspeter]

Surely one of the advantages of having business logic in PL/SQL is that you avoid having to transfer data between the database and the external services. Some things can be done a lot quicker at the database level by using stored procedures, but then again I don't have much love for PL/SQL - it's not the best language.

Re:READ THE MANUAL FFS

[Anne Thwacks]

The only stuff that isn't stored in the database is the front end presentation user interface layer.

So that is where the sex-bots are kept. I have always wanted to know.

Re:READ THE MANUAL FFS

[slartibartfastatp]

there were in the 80's. or if you like procedural programming. I'd rather embed code on data structures, not schemas, and have a much more flexible, maintainable software design.

not to mention, if you have a web application, the what's the point of having business rules in two places? Ok, you implemented ALL business rules in the DB? great, so go get version control for that, or reuse code.

Re:READ THE MANUAL FFS

[Anonymous Coward]

Stop judging MySQL users. We use it because it has simple, foolproof replication. Period.

Well I can see you've never actually tried to use MySQL replication, if that's what you believe.

Re:READ THE MANUAL FFS

[Anonymous Coward]

And when did you switch database structure or the database solution altogether easily because of using stored procedures? It's actually the other way around, using them you are stuck with a particular implementation, while on the other hand using plain SQL in the app provides exactly these features. All the rest remain, indexes can inferred and applied just the same, transactions save you from inconsistent database states and so on. Yes, you ask the developers to have some awareness of the database performance, but isn't it better than writing completely crapy statements that no DBA can clean up afterwards? This artificial split of teams in developers on one side and DBAs on the other, each living in their world, is totally crazy, but understandable from the point of view of Oracle for example that wouldn't be able to see their crap without an "expert" crowd, thus inventing the DBA positions ...

Re:READ THE MANUAL FFS

[Kjella]

All of our "business rules" are in the database and the reason is speed. Without going into too much detail we receive many and huge chunks of data from external parties and our job is to process these for quality, everything from whether the dates are dates to complex relationships between many values/existence checks in many tables. And I'm not talking about simple IT rules, there are teams of subject matter experts dedicated to finding new forms of invalid and improbable combinations and it is not feasible to push these quality controls out to the external parties. Could it be done in an application layer? In the "it's Turing-complete" sense but I'd love to see you try, we have trouble enough keeping up the performance on a beefy SQL server that runs everything without any network round-trips or in-app joins.

Re:READ THE MANUAL FFS

[Anonymous Coward]

I have in fact worked for a truly large company, moving data around between insurance companies and healthcare providers.

I personally think that stored procedures, triggers, packages, functions, et al, should be used only as a last resort to solve a particular problem, because they split your business logic between two different system, effectively:

1. Making versioning and deployments more difficult (much more, in my experience)
2. Making problem isolation more difficult
3. Building walls between team members, exacerbating the first two problems

Additionally there are the vendor lock-in and black box problems mentioned elsewhere on this page.

Now, I can understand that there may be a certain domain of problems that may be more effectively solved in the database; I've been around the block. But those mostly revolve around performance and consistency, and it's here that I think that NoSQL provides a real alternative. In a way, NoSQL is a the ultimate way of keeping logic out of data, because even schemas and transactions are a form of logic which has been "smeared" over into the database.

Just my $0.02.

Re:READ THE MANUAL FFS

[codealot]

You're kidding, right? We've been using MySQL replication for 10 years in production. Multi-master replication, statement-based and row-based. We've replicated between servers adjacent in a rack and servers that are separated by thousands of miles. And for the most part, everything Just Works.

The product may not be as academically pure, or whatever, as Postgres but that doesn't matter in a business setting. We use the tools that get the job done. On the other hand our team has been trying for the past month to get a Postgres (actually EnterpriseDB) instance to run in multi-master replication, or replicate DDL, without success. As far as we can see those are not supported features which make it almost useless to us.

It's comical how envious Postgres backers seem to be of MySQL's commercial success. And the quality of intelligent, thoughtful discourse on this forum is discouraging. Guess slashdot isn't what it used to be :(

Re:READ THE MANUAL FFS

[steppin_razor_LA]

Well put. Furthermore, stored procedures can enhance security (i.e. only allow the user that your application is connected to to perform specific predefined actions instead of direct table access). Also, I believe (although I could be wrong) that stored procedures are more likely to benefit from performance optimization within the database than dynamic SQL.

It's a beautiful dream (.NET/JAVA > TSQL in a heartbeat) but putting all of your business logic in your code is just another flavor of cool aid...

Re: READ THE MANUAL FFS

[Anonymous Coward]

> allowing you to change schema without affecting the application layer

LOL!

Re:READ THE MANUAL FFS

[Anonymous Coward]

And for the most part, everything Just Works.

"For the most part" meaning "Apart from all the things that don't", I presume? Then there's insanity like how excluding certain databases from replication goes by which database is set as the default, rather than on which database you're actually modifying, large deletes or updates on a large unindexed table send it into la-la land (OK, not generally a great idea in the first place, but easy to do by accident if you're experimenting), having to lock out writes on the master for as long as it takes to complete a mysqldump in order to set up the slave....

I can only assume you're a typical MySQL idiot who has no idea what he's doing, never read the documentation, and is generally oblivious to everything that can go wrong. Whenever something does fail, you probably just hack up a fix and go on your merry way without ever considering that maybe if you chose a sensible database in the first place these things wouldn't keep happening.

Re:READ THE MANUAL FFS

[antsbull]

Second this - I have been using iBatis+iBator for over 10 years, as well as JPA and Hibernate on a couple of projects, and iBatis, hands down is the easiest and the best performance. Its slightly more work, but I love having the control it gives.

What is stopping a migration? Oracle-isms

[Anonymous Coward]

Many legacy applications built using Oracle use one or more Oracle extensions to the SQL standard. Eliminating those Oracle-isms would take developer resources that (most) companies prefer to spend on enhancing the application (you get credit for new features, not old features being rewritten; why do you think there is still so much Cobol code still in use?)

Why Oracle?

[Hairy1]

The first reason to go with Oracle is its reputation. If you are responsibile for making a choice about which database to run, and you choose something that has the perception of being the second rate or the cheap option then if things go wrong and data is lost that decision might cost you, even if the data loss has nothing whatsoever to do with the quality or reliability of the database software. Is this unreasonable? It will depend on how conservative the organisation is. If it is a startup then they will be more comfortable with a open source database. If they are a financial organisation the licensce cost may be far less important than the perception of reliability.

The second reason to go with Oracle is lockin. Oracle DBA's in my experience have been trained to utilize the Oracle specific features of the product in such a way that moving to another database is impractical. Liberal use of stored procs, or even a decision to only use stored procs for data access has been a common theme. So has the idea that the business rules should be implemented in the database. All this does is couple your application to Oracle and lock you in. If you are buying an application the chances are that if they have developed against Oracle that you will have no choice about the database to run.

Oracle also has an ecosystem of professional support companies, and this too can provide an additional level of comfort for those making the decision about which database to run.

However, if you are like me and develop using a abstraction layer such as Hibernate, and refuse to write applications which tightly couple against specific flavours of database, you will retain the option of using Oracle if you or your customers choose, while keeping the door open to other options. My experience is that both MySQL and Postgresql provide a level of robustness at least equal to Oracle. They are far easier to install, do not require complex licensing, have highly experienced communities around them, as well as their own commercial support options.

Re:Why Oracle?

[glenebob]

I wrote against Postgres for years and avoided stored procedures as much as possible for exactly the reason you describe; to avoid lock in. I never understood why so many people are perfectly happy to dive right into lockedinville. Avoiding lock in always served me and my company well.

Re:Why Oracle?

[Nerdfest]

The first reason to go with Oracle is its reputation.

Amazingly, this is also a reason *not* to go with Oracle.

Re:Why Oracle?

[Splab]

So... How often have you had to switch out your database?

Re:Why Oracle?

[glenebob]

I never had to. But that's beside the point :)

There was always a latent threat of switching to MSSQL, for the exact reasons presented and debunked above. It could easily have happened, and I like to think I did a decent job of being about as ready for such a shift as possible.

Re:Why Oracle?

[Anonymous Coward]

I've done it multiple times. I was extremely happy I was never in sproc hell, because the switchovers take minutes instead of weeks.

Re:Why Oracle?

[Spykk]

Taking advantage of stored procedures does not necessarily mean you are locked in to a provider. You can always write your procedures in pure SQL and still get the benefits of making query logic available to multiple applications.

Re:Why Oracle?

[Anonymous Coward]

I wrote against Postgres for years and avoided stored procedures as much as possible for exactly the reason you describe; to avoid lock in.

The use of stored procedures has helped us to accelerate deployment on multiple platforms.

Procedure languages more or less execute sql statements with dirt simple conditional block checking and only minor semantical differences around result set/cursor handling nothing which can't be machine translated with a little tooling effort.

We use a base language with restricted set of features and from that are able to completely automate production quality procedure generation for multiple target platforms. Only a few dozen cases out of 1.5 million lines of procedure code do we bother to make language specific callouts external to compatibility feature set.

Re:Why Oracle?

[Anonymous Coward]

Pro tip: using stored procedures extensively may prevent management from bringing in MSSQL.

Re:Why Oracle?

[Anonymous Coward]

Yeah, and they'll be pissed at what you signed them up for dollars-wise. They cringe when you mention needing an Oracle server (though they'll sign off on one...because of reputation...if you actually NEED a server...) because of license fees. If MySQL/MariaDB or Postgresql will actually DO what you need to do, why even GO there? Especially when you've got commercial support available for either of those two...

Re:Why Oracle?

[Anonymous Coward]

It's often not just about switching out the database. Lock-in, almost by definition, introduces black-boxes into your code. By not relying on some "magic" package or function, you can be assured that you understand PRECISELY how your code works, and you have a much better shot at being able to think through all of the possible failure modes.

Re:Why Oracle?

[Anonymous Coward]

>Avoiding lock in always served me and my company well.

How many times have you switched databases then? Or how many different
DBMSs do you commonly test against?

Feature differences

[mysidia]

There are features Oracle provides that have no PostgreSQL equivalent.

• Price -- it costs a lot of money. For many governmental entities, this is a huge advantage -- as they are given a budget, and they need to spend it, otherwise their budget will get reduced -- if its an excuse to spend money, based on claims of productivity, they will often deny requests to use OSS, and mandate the use of Oracle, based on its productivity-improving and more-reliable qualities that some slick salesman persuaded them of, after taking them out for steak at a 5-star restaurant somewhere, or whatever. Also; I hear plenty of government workers saying Management has a no open source software policy; for security reasons, the more money spent on the product the better, as closed source code is deemed to be more secure... For me, and business i'm involved with, this is a huge negative for Oracle, and a reason I almost always pick Postgresql; yes, Oracle delivers more, BUT in many cases you pay Oracle for every extra cent of additional incremental value Oracle delivers over Postgres, and maybe 300% more.
• RAS features -- such as clustering Oracle RAC
• Development productivity tools such as - Pro*C
• SQL Language features where Oracle's implementation is superior -- such as BLOBs. Postgres manages these poorly, for example, you cannot reliably pg_dump blobs - if your application is BLOB happy (e.g. Sharepoint-like), then Postgres is not very suitable.
• SQL Language features that have no PostgreSQL analog -- such as CONNECT BY clauses, Java class based schema and table mappings; module languages; XML types; default value funciton parameters; organize stored procedure objects using packages; .

Re:Feature differences

[Anonymous Coward]

if your application is BLOB happy (e.g. Sharepoint-like), then Postgres is not very suitable.

And neither is Oracle. All DBMS vendors say they can handle BLOBSs, and they all SUCK at it. NEVER store binary data like sound or imagery in DB rows. Just don't.

Re:Feature differences

[Anonymous Coward]

Don't forget synonyms. I dislike using them but there are the occasions where they can make your day.

To be fair though, there are features in Postgresql provides that I wish Oracle had.
Comment on any flippin object in the database (including the database) is ' ... '.
More extensive datatypes out of the box (where's the boolean datatype Larry?).
Transactional DDL.
Domains.
A more sane (in my opinion) implementation of schemas. Why Oracle conflates users with schemas is beyond me.
Schema level privileges.

Re:Feature differences

[eggyknap]

I'm willing to be corrected here, but I understand CONNECT BY was Oracle's way of making recursive queries before the SQL standard invented them. Oracle and PostgreSQL (and presumably others) support standard recursion now. Postgres also has default function parameters, and extensions which sorta kinda but not really approximate Oracle's packages.

Re:Feature differences

[eggyknap]

Everyone should love transactional DDL.

Re:Feature differences

[Anonymous Coward]

Agree re: BLOBs.

Postgres' large-object support, however, is actually pretty nice -- block-addressable, and can be dumped by pg_dump easily if you use the proper dump format and options. A reference to a large object is stored in the database row; the objects themselves exist elsewhere and have a set of file-like access methods.

Not portable, but sometimes it's quite a bit nicer than storing those objects on the local filesystem or on a NAS via a protocol like NFS or CIFS.

Re:Feature differences

[Craig Ringer]

PostgreSQL supports the SQL-standard WITH RECURSIVE clause instead of the Oracle-specific CONNECT BY.

CONNECT BY is in many ways a nicer syntax, but the functionality is there.

Pg also has XML types, schemas and extensions to serve some of the same purposes as packages, etc. Default values of function params are also supported.

That's not to say it has full coverage of Oracle's feature set; it doesn't. There's no native materialized view support until 9.3, so you have to roll your own in currently released versions. There's no synchronous multi-master clustering in Pg (we're working on it). No autonomous transactions, and stored procs can't easily return multiple result sets. Partitioning in Pg is rudimentary and manual, at least in 9.3 and older, it might change in future.

OTOH, Pg is more extensible, has saner licensing, offers choice of support, etc, per my other post.

Re:Feature differences

[Anonymous Coward]

To be fair though, there are features in Postgresql provides that I wish Oracle had.

Does Oracle have proper SERIALIZABLE support yet?

Re:Feature differences

[mysidia]

I like transactional DDL; however, after an application is installed, DDL statements are extremely rare and in production: generally only allowed to be used by the DBA when applying a schema upgrade to support a new version of an application.

So in most cases transactional DDL isn't really an important factor.

Re:Feature differences

[Anonymous Coward]

Pray tell, how are BLOBs superior in Oracle?? Because SQL Developer silently omits LOBs from full database dumps?
Or even worse, for silently truncating LOBs to some length that is larger than most but not all of the values?
A database should help you to keep your data safe, not sabotage you if you didn't read every last page of the huge volume of fine print.

Re:Feature differences

[Anonymous Coward]

generally only allowed to be used by the DBA when applying a schema upgrade to support a new version of an application.

That situation alone is enough to make transactional DDL essential.

Oracle is Best DB!

[Anonymous Coward]

Oracle is big and great, and most of all expens--worth your money! Just remember to hire a proper DBA to manage all the thousands of patches you will get from Oracle. Other than that it's perfect for all uses.

Signed,
-- Oracle DBA.

Liability

[InfiniteZero]

When you work for a big corp. and have the money to burn, it's all about shifting liability to a 3rd party -- the bigger, the better, hence the saying, nobody ever gets fired for buying IBM.

In turn, with the money you pay them, a big 3rd party will more than likely throw all the man power at your problem until it gets fixed.

Re:Liability

[glenebob]

And the story of shifting liability is such a sham. Oracle isn't liable for anything. If you install Oracle and lose a bunch of data, you're still liable for it. And even if Oracle was liable, is that going to get your data back? No.

Re:Liability

[Anonymous Coward]

In turn, with the money you pay them, a big 3rd party will more than likely throw all the man power at your problem until it gets fixed.

+Millions funny

Re:Liability

[Anonymous Coward]

If you install Oracle and lose a bunch of data, you're still liable for it.

No, your company is liable for it. According to your boss (the only person whose opinion matters) you are not, because instead of trying to solve a problem, you were smart and said let's buy a solution. So then it doesn't matter if it works or not because you have someone to blame.

And even if Oracle was liable, is that going to get your data back?

Who cares? Not being the guy who gets the credit/blame was the goal, not some somethingsomething question about datawhatever. WTF are you talking about? Just restore the last tape. You think I want to work late? Just get the little tape guy on it and tell everyone we'll be back up and running on Monday.

Re:Liability

[glenebob]

How is any of that different with Postgres? Either way, you restore from back up and away you go. In neither case did blaming a software company get you out of trouble. That's why I say, it's a sham. You pay big money for something that, at the end of the day, isn't really worth a penny.

Re:Liability

[greg1104]

Or you could install Postgres, and if there's a problem you have resources all over the world to help fix them. At all times the quality of the outcome is in your hands. The risk with Oracle that people ignore is what happens when you have a serious problem, and they don't give a fuck. What do you do then? With commercial software, the vendor is an essential part of non-trivial issues. They have you by the short hairs if your server is down. I don't know why people think that situation involves them having any leverage on Oracle.

People who worry more about CYA than getting work done may not like Postgres, because it assumes an active, competent administrator is thinking and making decisions. If you don't have one of them involved, maybe you do need vendor hand-holding.

Re:Liability

[Anonymous Coward]

It's the screaming that gets the other party moving. The money is just to make them listen. It's the lubricant. And not a very good one, seeing the amounts you need.

It doesn't usually work very well, but some people just like to scream. A lot.

Re:Liability

[TheLink]

because it assumes an active, competent administrator is thinking and making decisions

I rely on Tom Lane being awake at 4am or whatever to help fix some rare weird Postgresql error ;).

Re:Liability

[Maudib]

I don't know where you work, but it sounds like hell.

Re:Liability

[Tom]

As with so many things, it depends.

Liability is largely a non-issue in the real world, and a huge issue in the minds of management. Do you know of any actual cases where Oracle was found liable?

Likewise, most customers simply won't be big enough to get a SWAT-team deployed to their location if something goes wrong. You'll be getting standard customer support. That might, in fact, be one of the reasons for going elsewhere. Many years ago, I worked for a company that was, at that time, the largest ADABAS installation in Europe. Our hotline number didn't go to customer support, it went straight to the engineers. You can bet they put their best people on the job when we reported an issue. I'm not sure we'd have had that kind of VIP status with Oracle.

So, it depends. If you need something that will please management, Oracle is a safe bet. If you need something that will be looked at right now when an issue shows up, I'd got with PostgreSQL and pick a support contractor nearby where I'm one of the biggest customers.

Re:Liability

[Anne Thwacks]

If you report a problem, and it is serios, Oracle can simply discontinue the product. You cannot fork it.

In soviet xxx, Oracle forks you!

Re:Liability

[Trekologer]

The liability isn't in a legal or financial sense. It is about shifting blame when bad things happen. Let's say your database takes a dump and everything is down and you don't know why. When the CTO is breathing down your neck about it, which would be the better response: "we're searching docs/blogs/forums for this problem" it or "we're on the phone with the vendor"? If you went with the big name vendor, it isn't you or your team's fault, even if it is a multi-hour outage, since they're working with the vendor. Since the vendor is "best in class", it is okay. I hate it but this is the unfortunate reality that often comes into play.

Re:Liability

[Anonymous Coward]

it's all about shifting liability to a 3rd party

Have you even read a EULA? That's not even remotely how it works.

a big 3rd party will more than likely throw all the man power at your problem until it gets fixed.

That's why Oracle has cleaned all the security flaws out of Java, right? And it's why Windows 8 is so good, because Microsoft is a big and well known vendor.

Stupid fucking developers?

[mveloso]

Maybe it's dumb-ass developers like this one who jerk off with technology instead of writing things that customers actually want?

Multi cpu queries

[Billly Gates]

Oracle has a patent that limits a query to only use 1 cpu. So the answer is a big NO.

Aren't software patents great?

Probably Not

[Greyfox]

But most shops don't need something as powerful as Oracle. By the time they get done slapping a front end with non-optimized spring and hibernate queries on top of oracle, they may as well just be storing their entire database in one big XML flat file. A while back I ran across a developer who was trying to join two tables manually using hibernate. Around 40000 records his application would run out of memory and crash half an hour later. The SQL join I wrote to test it handled at least 1.5 million records and ran in under 10 seconds (And this was on a Postgres database.)

So just because your shop is running Oracle, doesn't mean you can hire chimpanzees to write your font end code. Optimize your database design and queries and you can go a long way before you need the power of a commercial database system. Don't, and even the most advanced commercial database on the planet won't make your app suck any less.

Re:Probably Not

[Common Joe]

This is one of several reasons why I like to put as much business logic into SQL as I can. I'm not saying all business logic should go there, but overall I believe the closer your code is to the data, the better. I know a lot of people disagree with me, but if you're storing data in a database, then you probably want SQL to interact with the data as much as possible for performance reasons.

Does Postgres do online backup?

[alen]

Like you install netbackup on it or one of the other enterprise products And it backs up directly to your tape robot via the San. No shutting down the database. No copying to a snapshot. Online backup of the production db straight to tape. Full, diff and log backups straight to tape.

Does it support online restore?
Is it certified for VMware and hyper-v?
If you have a problem can you open a support case right away?

Re:Does Postgres do online backup?

[Anonymous Coward]

Yes, to your first set of backup questions:
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html

VM, don't know. Depends on the context you're referring to.

Yes, to your support question:
http://www.enterprisedb.com/products-services-training/services

Re:Does Postgres do online backup?

[eggyknap]

VMWare has invested quite a bit of work into PostgreSQL, and employs a few big names in the community. That said, I know nothing about it myself, and can't answer your question regarding it :)

Re:Does Postgres do online backup?

[glenebob]

I've had very few problems with Postgres that weren't actually a result of my own mistakes, and of course I handled them by fixing those mistakes. Any time I've actually needed to lean on someone else, the mailing lists have been very helpful, and very quick to respond. Not only that, but the mailing lists are frequented by the actual developers, so when you ask a question about some specific aspect of Postgres, there's a good chance you'll be answered by one of the people who actually built or at least maintains that piece of the code. As apposed to some clown reading from a script of trouble shooting bullshit who doesn't even know what language the software is written in. That, to me, is the very best kind of support there is.

Re:Does Postgres do online backup?

[abirdman]

Watch out if you're running Oracle on VMware. Everything works fine except the Oracle licensing. Got a VMware server with 32 processors? If you run Oracle on a VM hosted by that server, then you have to license all 32 cores (we're in the six figures here), even if the specific VM is only using 2 cores!. And they audit your usage. The work around is you can license the Oracle VM software to avoid that problem.

The biggest innovations Oracle has made in the last 10-15 years is how many ways they can slide their tentacles into your wallet. They don't want customers, they want "partners." Their pricing model is "How much money do you expect to make/save by using our product? That's the price." One huge, but difficult-to-quantify, feature of PostgreSQL is the fact of not having to be "partnered" again and again by Larry Ellison.

Re:Does Postgres do online backup?

[fuzzytv]

Sure, you don't need to shut down the database to do a backup. Just call pg_start_backup, do whatever copy you want (copy of the files, LVM snapshot, ...), and you're golden. And you can easily setup a read-only replica using the snapshot (preferably with a WAL archive), which is probably what you mean by online restore.

Opening a case ... you may submit a bug whenever you want, either using the web form http://www.postgresql.org/support/submitbug/ or by e-mail into the mailing list http://www.postgresql.org/list/pgsql-bugs/. However the PostgreSQL project does not provide any "corporate support" as such. If you need a support regular to the Oracle support (but actually responding and working as expected), you need to contact one of the companies that provide that - it might be EnterpriseDB, 2ndQuadrant, Command Prompt, Dalibo, Cybertec ... or one of the other companies.

I'm not sure about the VM thing, but as they do offer "vFabric Postgres" [http://www.vmware.com/products/application-platform/vfabric-postgres/overview.html] and that VMWare is generally a PostgreSQL-friendly company, I'd say it might be certified.

Re:Probably Not - Hibernate

[Hairy1]

Hibernate is a great tool in many ways, but it is far too easy to allow the actual details of what is going on under the covers to be hidden. As the parent suggests using Hiberate in a naieve way can be very dangerous. Complex queries are usually best handled by native SQL.

Postgres on par with Oracle? Nope.

[Anonymous Coward]

Postgres is bait and switch database. For the really needed features you need to buy a commercial version. What do you think why is the guy who works for EnterpriseDB the head of the steering committee? Incidentally, EnterpriseDB has hints, session trace and the event interface, all the things that the open source version so desperately misses.
Postgres has no parallelism worth mentioning. Nada, zilch, zero. Also, their partitioning is useless. There are no global indexes and the optimizer makes very strange decisions when partitions are involved. The answer to the question whether Postgres is on the same level as Oracle is a resounding no. Is Postgres good enough? I am a DBA, not a business user, but I would not recommend it for any company larger than ma's and pa's corner bar and grill. If you are looking for the alternatives to Oracle, there are several worth looking into, depending on the database type: Vertica, Greenplum, MS SQL and DB2. I would look hard into DB2, version 10 has number of Oracle compatibility features and is much cheaper than Oracle. IBM is going aggressively after Oracle's market share. Trust me, you don't want Postgres. There is a reason why people are not using it, although it has been around forever.

Re:Postgres on par with Oracle? Nope.

[rycamor]

Yeah, that's right. Postgres is only good enough for ma and pa's corner bar and grill. I can't imagine what the last few fortune 500 corporations I have consulted for were thinking.

Folks we have a class A hurtbutt here. Sorry the Postgres guys outbid you on that last project...

Yes PostgreSQL (community/open edition) may not have [xyz checkbox item] but it handles plenty of large databases just fine. The fact is, big companies, government/military contractors included, are seriously beginning to migrate many sections of their database infrastructure to Postgres. It's all I work on these days. Agreed that a company with a 60 terabyte OLTP database in Oracle should think twice about moving to Postgres (although Yahoo managed a 200+TB one years ago). But even for large corporations, most databases are still under the handful-of-terabytes size and are perfect candidates for PostgreSQL.

And yes, EnterpriseDB offers add-ons. How is that a bait-and-switch? It's simple honest business, and even with all the bells and whistles (some very nice ones) it still ends up costing a fraction of what Oracle costs.

One missing Oracle feature in PostgreSQL

[manu0601]

I did now know about EnterpriseDB oracle compatibility for PostgreSQL, that is interesting.

However there is still a strong Oracle feature missing here, which is called CYA. It is just like using Microsoft software: even if it does not work nobody will tell you were wrong by choosing it

Re:One missing Oracle feature in PostgreSQL

[aztracker1]

Have you actually looked at EnterpriseDB's executive list or board of directors? There's just as much CYA power there as there is with Oracle, probably more.

mark-up pricing

[Pharoah_69]

Personally, I think it all comes down to accountability; that and "in-house" buying and mark-up. With open source software, there isn't any "mark-up" pricing for businesses.

Price, it's not just for Governmets anymore

[frovingslosh]

You're omitting a key concept about price that I've seen many fools around me exploit. If they are managing a big project that has software costs of millions a year then they must be very important. If I'm managing a project that only costs the company a hundred thousand or so in software costs a year then I must be less important. It really doesn't seem to matter if my project does more or is actually more critical to the operation of the company, those are concepts that are too abstract for upper management to understand. So if you want your department to look important then waste as much money as you can.

Re:Price, it's not just for Governmets anymore

[mysidia]

If they are managing a big project that has software costs of millions a year then they must be very important.

That's insane.... if the NPV of the project is higher, then it's clearly a more important project.

Spending more on software lowers the NPV: unless the software adds more value than less-costly options.

On the other hand... a project where much more is spent, but destroys value is not an important project --- it needs to be optimized by reducing costs or scrapped.

I'm pretty sure companies' management understand value, and how to evaluate whether a project is improving value for the company better than another project, or whether a project is destroying value and needs to get cancelled or have its team size and costs cut in half.

The companies' whose management don't understand are failing or only succeeding by coincidence in spite of incompetence, and are doomed to failure eventually, assuming management doesn't get replaced with a competing more effective management.....

Oracle - only if you have good DBAs

[Ice Station Zebra]

If your DBAs believe in hands off the database and your tables have a lot a churn, you are going to be in for some pain.

Cost of switching

[fsterman]

Seriously, the cost of switching is what keeps everyone locked into their databases. Unless an alternative DB can provide a seamless drop-in replacement, the man-hours spent on re-engineering and the downtime vastly outweigh the performance improvements you might get out of it.

Re:Cost of switching

[aztracker1]

Around 1999 I was involved in a rewrite to move *to* oracle... which, at the time was the only real option for the needs of the software at the time. There were several DBAs hired in, and a few cross-trained for the project. It wasn't too bad. That said, for 99.999% of use cases Oracle is overkill. I'd much rather use PostgreSQL or MS-SQL, haven't touched DB2 in a long time, and FirebirdSQL is passable as well for some uses.

My experiance with Oracle.

[PenguinJeff]

Installing and testing.
I installed Oracle a few times and played with it. I didn't put a proper shutdown method in the shutdown scripts and there was also a mishap while testing the UPS. Both times I was unable to recover the Oracle database and had to reinstall. I had never had that much trouble with mysql. I installed it for someone else that had an Oracle expert and they where able to recover when we had a similar mishap there but all the googling in the world is nearly useless without a properly trained Oracle administrator. I'd suggest sticking with a database where the documentation is fully available and many many more people that can help you. There are easy free forums for mysql, maraiadb and postgresql.

For the most part the Oracle stuff works..

[dubist]

As much as I hate to admit it.. The Oracle stuff for the most part just works and if you have competent DBA's you don't have to worry about it. You may regret using oracle when you get the bill and sometimes it does not have the more esoteric features of the other DB's but you will be glad for its stability and its enterprise focused features in the long run.. And no one will sack you for choosing Oracle.

Lack of Benchmarks

[Anonymous Coward]

It think it might be expensive and that's something, but I suppose the main thing keeping from switching to Oracle or even calling them, is the lack of benchmarks. I can't get any data about whether or not we'd get anything out of it. It's almost as though the first rule of Oracle is that you don't talk about Oracle.

Re:Lack of Benchmarks

[greg1104]

Oracle doesn't allow people to publish benchmarks, period. Some trickle out via the audited TPC results. Those are all systems tuned to an extreme degree, including code optimizations for that specific workload that border on cheating

Re:Lack of Benchmarks

[i.r.id10t]

including code optimizations for that specific workload that border on cheating

You mean that if you had some sort of massive production db your application(s) that use it wouldn't be as optimized for what they do and how they do it as much as you possibly could optimize them?

Re:Lack of Benchmarks

[greg1104]

Of course you would optimize as much as possible for that workload. But you shouldn't then use those numbers as representative of the database's performance on other tasks. What I'm saying is that the TPC numbers are gamed so hard by commercial vendors that it's not clear they are useful as measures of anything except "how fast does the TPC workload run". And the restrictions on publishing results means no one else can publish anything but those gamed results.

Re:Lack of Benchmarks

[Anonymous Coward]

including code optimizations for that specific workload that border on cheating

You mean that if you had some sort of massive production db your application(s) that use it wouldn't be as optimized for what they do and how they do it as much as you possibly could optimize them?

In some benchmark configuration I have seen, to speed things up, people were playing trick with transaction logic, both on DB level and application level. And also IO trickery. On the plus side, it gives noticeable performance boost. On the minus side, nobody *ever* is going to deploy such configuration in a production environment, because in case something goes wrong, everything goes horribly wrong and your data are in shatters and there is no way to minimizes losses (iow, the only option is full restore from last backup).

As Oracle vs. MSSQL benchmarking, friends of mine did once run a subset of migration scripts (which happened to be pretty portable SQL) on both. Oracle has finished in 205 minutes - MSSQL in 210 minutes. Margin of error, was like 5% (mostly due to differences of how the scripts were run on Linux vs. Windows) i.e. literally no difference.

Re:Unpronouncable

[Anonymous Coward]

Ja! Das ist wahr, mein kapellmeister!

Ich bin ein anus Amerski: {}
Ich bin ein anus Sovski: o

As usual, "it depends"

[Craig Ringer]

Like most DB comparisons, it depends on the workload, non-technical business factors, and more.

Oracle has superior clustering to PostgreSQL, better native XML support, autonomous transactions, procedures that can return multiple result sets, a really solid embedded JVM for procedures, proven scaling to absurdly huge database sizes, etc.

PostgreSQL has transactional DDL, generally better standards adherence, no lock-in, streaming replias that don't cost you anything, multi-language stored procedure support, extreme extensibility, proven scaling to multi-terabyte database sizes, and probably more I take for granted and forget about.

With Pg you get a lot of choice of support provider, including "none, I can do it myself and I can always contract someone if I need help". With Oracle you get support from Oracle, or from a vendor who must comply with what Oracle wants in order to get access to the resources they depend on to offer support.

PostgreSQL has no per-cpu or per-core license fees so you can run it on a lot more hardware. You can also afford to buy a much bigger server for the money you're saving on licensing fees and upgrade it more often. This can make a huge difference; PostgreSQL's performance is generally very good, and in areas where it does fall behind Oracle you can make up for a lot by throwing bigger hardware at the job. You also don't have to face NDAs, license audits, not being able to afford to have a second off-site hot standby backup machine, being stuck on old versions because licensing new ones is just too expensive, etc.

So, really, a huge amount of it depends on the workload, business requirements, etc.

I work professionally with PostgreSQL as a member of the 2ndQuadrant team, but if I'm discussing planning with somebody I'm still quite prepared to say "I don't think PostgreSQL will do the job as well as [blah] here given the time frame and requirements". It doesn't come up much but it has, and I'd be doing them a dis-service by saying PostgreSQL's perfect for everything all the time.

I find PostgreSQL to be the safe and sensible default, but I consider alternatives or supplements to it when I run into workloads it's not ready for or not great at - like someone who has a hard business or compliance requirement for synchronous multi-master clustering, or somebody whose query pattern and data set is going to be a better fit for Greenplum than native PostgreSQL.

Re:As usual, "it depends"

[sgt scrub]

Nice. A real response. I'd like to add that PostgreSQL is excellent on multiple small machines for the money you would spend on a large machine for Oracle. PostgreSQL ability to replicate is the reason I prefer it. The "hot standby backup machine" is the greatest thing since sliced bread IMHO and the real reason to use PostgreSQL.

Re:As usual, "it depends"

[1s44c]

Greenplum

Pivotal, I mean err, EMC.

Re:As usual, "it depends"

[Anonymous Coward]

The problem with PostgreSQL is the absence of the tuning tools like trace and hints. Also no parallelism of any kind and their partitioning is a joke. The open source version is useless. That is intentional, the idea is to sell the commercial variants.

Quick answer:

[jd2112]

No, Not even close. Oracle has a lot of enterprise-grade functionality that does not and probably never will exist in PostgreSQL.

If you actually need that functionality it will probably be worth the exorbitant prices Oracle charges for it.
Perhaps a better question is what does Oracle do that PostgreSQL doesn't and is it necessary for your business?

(Disclaimer: I am not a DBA and I go out of my way to avoid working with Oracle, however I often run in to Oracle DBAs that know less about databases in general and sometimes Oracle specifically than I do.)

differences usually do not matter

[sribe]

In 95% of cases (or more) as one of the first response said: "stupid fucking managers", in 5% (or less) of cases, some very very very high-end features that almost nobody actually needs. Sorry, bathroom break is over, got to get back to the movie with the wife, otherwise I'd say more ;-)

But I'll leave you with this: the postgres folks are truly experts in database, and extremely forthright. Unlike the MySQL founders, if you go and ask this same question on the postgres mailing list, you will get an honest answer, not marketing bullshit.

Also, I see now that Craig Ringer has responded above. Anything he says, believe it ;-)

locking?

[Anonymous Coward]

Not familiar with PostgreSQL locking but we used to find that MS SQL Server would, what's it called, escalate its locks from block to row to table, well, we thought quite easily (you could criticize our design that brought this about, doing many insertions in a single transaction over a long period, but Oracle handled the same process without problems). More recent MSSQL Servers have improved this, though. How is PostGreSQL's locking?

Re:locking?

[adamgundy]

postgres uses MVCC, similar to oracle.

oracle uses an undo log and postgres a redo - the difference being that oracle is faster for multiple updates (and may be quicker to restart after a crash), but postgres doesn't suffer from running out of log space with large transactions, and rollback is very quick.

both postgres and oracle perform far better under locking scenarios than sqlserver.

recent postgres also have some very smart stuff if you use serializable isolation mode; supposed to be better than any other DB.

Re:locking?

[rycamor]

PostgreSQL has had full multiversion concurrency (MVCC) for over a decade or so (at least since version 7). It simply doesn't have this sort of locking issue.

Re:locking?

[glenebob]

As far as I know, Postgres has had MVCC from the start. I started dabbling in it at 6.4 (before it was enterprise ready IMO), and it certainly had it then.

MSSQL has also had a variant of MVCC since, um, 2005 I think. Can't be bothered to go look :)

Re:locking?

[fuzzytv]

AFAIK it was added in 6.5 (so it didn't have it in 6.4, at least not completely) ... http://en.wikipedia.org/wiki/Postgresql#Major_releases

Re:locking?

[glenebob]

I stand corrected. And somewhat confused. I found the 6.5 release notes:

"Multi-version concurrency control(MVCC)

        This removes our old table-level locking, and replaces it with a locking system that is superior to most commercial database systems. In a traditional system, each row that is modified is locked until committed, preventing reads by other users. MVCC uses the natural multi-version nature of PostgreSQL to allow readers to continue reading consistent data during writer activity. Writers continue to use the compact pg_log transaction system. This is all performed without having to allocate a lock for every row like traditional database systems. So, basically, we no longer are restricted by simple table-level locking; we have something better than row-level locking."

That seems to imply that the "MV" existed, perhaps all along, but that the "CC" part was new in 6.5. Maybe that explains my confusion. Anyway, that was a long time ago. Thanks for whacking me with the clue stick :D

Storing an array of strings (and other structures)

[ulatekh]

Ever try to store an array of strings? Better to store it as one field and parse it in code!

If you're trying to store complex data structures in SQL, I would recommend protocol buffers. Imagine XML, but more compact, and with built-in support for versioning. It's open source too.

The great thing about standards

[ulatekh]

This is the best thing about SQL: it's a standardized language letting you switch between different database vendors with fluidity.

That's the great thing about standards — there are so many to choose from.

Re:The great thing about standards

[Anonymous Coward]

Doesn't that merely show how Oracle have fucked things up with their database? If they, the putative #1, the market leader, the behemoth in the room, cannot make their SQL portable, either they hate your guts and deliberately fuck with you (in which case, why pay them for the privilege???) or they're incompetent, in which case, go for the cheapest incompetents, you're going to be no worse off in what you get.

Re:The great thing about standards

[Codifex Maximus]

Why in the world would Oracle want to make their SQL portable? So their customers could easily migrate to competing RDBMS? All they have to do is to get the managers to buy in and they're set.

Re:The great thing about standards

[Bert64]

And herein lies the problem, each vendor has every incentive to lock customers in and no incentive to follow standards - a serious flaw of the market. And to make matters worse, most of the end customers are not technically savvy enough to realise the business risk of getting locked in to a proprietary system.

And then you get lots and lots of wasted effort trying to port and convert, which is extremely detrimental overall. And although the detriment of porting others code *to* your environment is bad, unless everyone changes at once those who change first will be at a disadvantage.

Which is why you need big buyers (eg governments etc) to demand standards compliant products, and refuse to purchase anything which isn't. Some may see it as interfering in the market, but then this aspect of the market is fundamentally flawed.

Re:The great thing about standards

[jedidiah]

This isn't just about Oracle. Everyone decides to do things differently because they each evolve in a vaccum and then once standards do come along, those standards bodies don't have any balls.

If you can't point to a relevant standard that Oracle is ignoring then you have no leg to stand on. That doesn't just go for Oracle but it applies to any other vendor.

Oracle has some features from the mid 90s that competitors are just getting around to implementing now. Sometimes you just have to make something up because there isn't a standard there yet.

Re:The great thing about standards

[mooingyak]

And herein lies the problem, each vendor has every incentive to lock customers in and no incentive to follow standards - a serious flaw of the market.

Not exactly. The dominant player has this motivation. Smaller players have the opposite -- make their stuff as much like the big dog so they can more easily poach customers.

Re:The great thing about standards

[Bert64]

No, the smaller players have motivation to provide one-way migration capabilities... So they waste a lot of effort trying to reverse engineer the dominant player rather than actually improving their products core functionality.
What this also means is that your lock-in is generally even worse with the smaller players because none of the competitors have ever thought about providing a migration path.

Imo

[Charliemopps]

In my opinion:
Oracle is easier to hire for. A lot of "reporting people" know Oracle. If they had half a brain in their head they could write SQL for any DB... but if they had half a brain in their head you'd have to pay them more.

Oracles support is... worse than anything. We just stopped calling. It's better to live with the bugs than waste man hours on that cunt licking whore Oracle calls support. I'd rather traverse the 7 layers of hell in a thong than ever talk to Oracle support about anything ever again.

Oracle is Satan. They will fuck you in the most evil way imaginable. Whatever alternative you think will get you away from them, half way through the migration project oracle will buy the alternative company out. If torturing puppies were profitable, Oracle would have a puppy torturing product as a SASS. In fact, if torturing puppies just made the product slight less helpful to you, they'd probably do it as well... because their favorite pastime is making their product of less value to you.

Re:Imo

[rycamor]

Heh... put some passion into it, why don't you?

I get the sense that this personal annoyance is one huge reason lots of companies are migrating to Postgres. it seems every time I consult on one of these, sooner or later someone will say the name "Oracle" with a sigh and an eye roll, communicating that the sooner they no longer have to deal with that company, the better. I get the sense that some would gladly move to PostgreSQL even if it were nowhere near as good as it is.

Re:Imo

[Anne Thwacks]

Lots of us DID move when pg was nowhere near as good as it is. I don't know of anyone who regretted never having to call Oracle support again.

Re:Imo

[Anonymous Coward]

Fuck Oracle, Fuck Google, Fuck Walmart, Fuck Apple, ... they are all fucking jokes.

Re:Imo

[Anonymous Coward]

Oracles support is... worse than anything. We just stopped calling.

Doesn't that create an opportunity for 3rd party companies to provide Oracle support?

If Oracle is as bad as you say, then would that be a potentially profitable business venture?

Re:Imo

[fuzzytv]

Never heard the phrase "You've been Oracled" before?

Wow.

[adolf]

We go on Slashdot from repeating that IT is like a janitor's job, to discussions about the idiosyncrasies of different databases and the specialties required therein.

Is there a middleground wherein an IT janitor can do his job AND properly maintain a database? Because that seems the question.

The vendor/author/distribution/licensing of that database seems irrelevant.

i like Oracle

[Anonymous Coward]

i rather use Oracle 10g/11g Express Edition instead of Postgres. I like Oracle's web interface. haven't figured out how to use Postgres yet. it is kinda complicated.

Oracle is a huge waste of money

[SDrag0n]

I'm not very familiar with PostgreSQL but I can tell you that Oracle is a giant money machine. To do anything with Oracle is complex and so it takes "experts". Usually people who talk about how Oracle is the one and only true solution are people who have spent their life making a lot money off of "solving" complex solutions that didn't really need to be that way to begin with. My advice is to stay as far away from Oracle as possible.

Almost all of the junk they sell to execs is designed to sound fancy but is usually a lie. Guess what a "personal Oracle cloud" is? A machine that sits in your server room that everyone else calls a server. It's just junk designed to cost more money.

Re:Oracle is a huge waste of money

[Anonymous Coward]

Well, it's just the cost of running business. I am afraid that if you have OLTP RDBMS with 2000 concurrent users, 200 transactions per second,, 24x7x365 uptime requirement, as is the norm in the days of the web applications, and 5 TB of data on which reports are also needed, PostgreSQL is not an option. What I've described is a medium size company, not a big company. Oracle is not the only game in town for such configurations but is one of the very few options. All are commercial.

You will need developers or third party software and you'll find out that very few standard COTS applications support PostgreSQL and good developers are hard to find. Of course, you would use some application server like Tomcat or JBoss, along with some Java based framework, like Hibernate, Groovy, Struts2 or Spring and that means bad queries. You will discover that there are no hints which would help you with a query performing badly. There is no parallelism, so you will have to read your partitioned table in a single thread and wait for it. And wait. And wait.

What Oracle gives you is the way of doing complex things. Very complex things like parallelism and fault tolerance. There are other commercial databases that can do the same but the money will not be insignificant, any way you cut it. Oracle people are the easiest to come buy, everything works with Oracle and Oracle can accommodate the business and regulatory requirements. Sure, Oracle is expensive but there are alternatives and I haven't noticed any en mass migration toward any of those alternatives. It's all the cost of doing business. Using PostgreSQL in a situation described above would be much, much more expensive. PostgreSQL is not a competitor for that kind of stuff. Microsoft and IBM, the usual suspects, are the competitors. That is the fight that the sales forces for all 3 companies are fighting on the daily basis. Postgres can only make them laugh.

Re:Oracle is a huge waste of money

[Anne Thwacks]

Before you believe this post, you might want to do your own research (not rely on mine;-)

Re:Oracle is a huge waste of money

[fuzzytv]

Well, that's many questions in one post, so let's discuss that one by one ...

1) OLTP RDBMS with 2000 concurrent users, 200 transactions per second on 5TB of data

It's really difficult to judge without more detailed info. I've seen such workloads on a single PostgreSQL machine - grab a machine with plenty of cores, say, a 32-core (64 with HT) R820 from Dell, stuff it with RAM, spinners for WAL and SSD for data, and you're done. It depends on what a "transaction" is actually doing, but let's ignore that. Actually you'll need this 3x for two standby machines, and a place for the storing WAL. You can use one standby for failover, the other one for long reporting queries, etc.

The 24x7x354 part is tricky - you can't do that with plain PostgreSQL, as even a hot standby means a short outage / reconnect during the outage. So you'll have to engineer the application accordingly - in case the master is gone, search for the new master and reconnect. Not a big deal, IMHO, but I don't want this to look like I'm playing down this deficiency.

If you want something more automatic, look at pgpool-II, or maybe Postgres-R and Postgres-XC. pgpool-II runs on top of PostgreSQL, the other two are forks.

2) COTS

Well, this may be true, although a COTS software that already supports multiple databases is usually simple to port to PostgreSQL. The problem here is that this is a circle - the company developing the software doesn't see any demand because the companies are not requesting PostgreSQL support. But this is changing.

Hints are a topic that appears frequently in discussions like this. I personally am grateful there's no such thing. However once you're stuck with Hibernate, you're hosed anyway.

3) Complex things ...

Well Oracle certainly gives you a way of doing complex things in a very complex way - no doubt about that. It's true that PostgreSQL doesn't have all the features built-in, however many can be implemented using third-party tools (e.g. pgpool-II, already mentioned) in a reasonably simple way.

4) Oracle people are the easiest to buy ...

I've had OCP in the application development (not sure if it's still valid), I know developers who went through the same tests etc. Frankly, it's not worth the paper the certificate is printed on. All it gives you is a proof the developer memorized some freaky procedure names and went through a test. It gives you exactly no idea if the developer is able to design a reasonable schema. It's way easier to just hire good developers and give them guidance and a few weeks to learn ...

And there are many (competing) companies providing PostgreSQL support, not a single corporate entity controlling the whole support chain.

Another thing is that sooner or later you're going to run into an issue that your experts don't know. With Oracle you have no access to the actual developers, so they can't answer your questions, you can't show them suspected bugs etc. I've had quite good experience with the web forums at oracle.com, but once you have to contact the actual support, it's pure bureaucratic madness.

5) I haven't noticed any en mass migration ...

Sadly many of these migrations are covered by strict NDA :-( The reasons for that vary. Some companies don't publish details of the platform at all (they've been running on Oracle without announcing that, and they won't announce the switch to PostgreSQL), other companies still need some level of support from Oracle (and this might significantly influence the relations + pricing). So the fact that you haven't heard about the migrations is not a surprise.

6) Finally ...

All that said, although I'm not unbiased (as I'm participating in the PostgreSQL development), I'm far from saying "PostgreSQL is the answer to everything." There will always be cases where sticking with Oracle is the right choice - either it's cheaper, there's a feature you can't live without or something else.

If you go w/ Oracle

[GodfatherofSoul]

Based on a rumor I heard about a local mega-development firm, make sure you build your app with the flexibility to swap out DB implementations and make sure the Oracle sales guys *know* it. Prices change drastically when they know you're not locked in. As for PostgreSQL, from my experience where I'm sure you'll lose out is with tools and a development environment.

Re:If you go w/ Oracle

[BigZee]

Although this seems like a sensible approach, it really isn't. When you choose a database, there are many factors that will affect your decision. One of those will be features. Your approach would basically prevent you from using the features for which you're paying. If you ignore those features, all you're doing is reducing your database to a simple data store.

Re:Just in case....

[Anonymous Coward]

funny =)

Answers

[Goody]

For those that use Oracle — is it worth the money?

Absolutely not.

 

What's keeping you from switching?"

Applications that require Oracle.

Re: Array of strings (or other type)

[Anonymous Coward]

Done. Been handled natively by PostgreSQL for over a decade. Combine with pivots or windows for some really interesting stuff.

http://www.postgresql.org/docs/9.2/static/arrays.html

My two cents

[kilodelta]

MySQL isn't bad. It's replication features are REALLY nice. And it does scale well. But the thing is, Oracle effectively owns it and is trying to kill it.

MariaDB is the replacement. Haven't had any experience with that one though.

Re:My two cents

[Anonymous Coward]

mysql isn't bad for things where obscure corner cases (race conditions, referential failures, other things that a proper RDBMS makes "just work" and mysql keeps on promising but never quite delivers) biting you aren't likely to be properly diagnosed.

Yes, I know what the fanbois say: "follows acid", "default engine these days", yadda yadda. A little further in the same manual it explains just how much it does this, and the coverage isn't nearly complete enough to be useful.

Basically, people claiming that mysql is an acceptable RDBMS have no idea what the latter is supposed to deliver, and should stick to the former, which they'll likely do, and keep quiet about it, which they won't. It's a toy, and not a good one at that. Yes, it's quite possible for a toy to be popular.

Re:My two cents

[Anonymous Coward]

MySQL isn't bad. It's replication features are REALLY nice.

Where the fuck does this stupid meme come from? MySQL replication is HORRIBLE.

PostgreSQL

[hardgeus]

I used to work with Oracle pretty heavily in the 90s, and 7 years ago quite a bit...Solid database, but worth the money?

I consider PostgreSQL the best piece of software I have ever used. Given how much I have used it, this is saying a lot.

I remember reading on the old Slashy-Dotty some quote from some language-designer that I will now paraphrase:

"There are two kinds of languages: Those that people complain about, and those that nobody uses."

The point of this anecdote being that the more you use a tool and learn its nuances and flows, the more you find to bitch about.

I have been using PostgreSQL for over 10 years. And in this time I have found very little to complain about. I may have a handful of anecdotes about its failings, and most of those are mediocre complaints, at best.

It is a solid workhorse.

All I know about Oracle...

[mendax]

Aside from the fact that it exists and that it's expensive is the experience we had in the mid-1980's with it when I was an undergrad. It ran on a Prime minicomputer. Even though only students were banging away at the server, it crashed a lot. The computer operator in the mainframe fishbowl had to called to restart it. If it was at night when there was no operator on duty, no Oracle. A very reliable minicomputer running a then unreliable database server. I suspect they've fixed their problems since then.

Re:All I know about Oracle...

[Meetch]

Heh! When I were younger, in the early '90s, our uni had a Pyramid with 2 whole CPUs running AT&T Unix! Programming students could bring it to its knees with their buggy chat client projects. Unix, or something very much like it, is now not only potentially free, but infinitely more stable. And now Oracle gives you high availability services that will auto restart things, or you're running on a RAC cluster, and when an instance crashes the end user simply experiences a bit of a pause, if that. There's nothing like being able to patch the database software across all 3 nodes in a cluster (1 instance down at any one time) while the users are logged in and actively using the application it back-ends.

Four Yorkshire-men aside, sometimes you need those 3 instances as 1 crashes because you're using the new feature and another inexplicably refuses do any work because of some buggy load balancing algorithm. (ok, perhaps some exaggeration here, but not by much)

It's all about vendor lock-in

[czha168]

The issue is organizational rather than technical.

Legacy applications aside, enterprises that have invested in Oracle has teams of Oracle skill sets in both development and operation. Furthermore, there are operating procedures that are Oracle specific.

All of these are costs of migration from Oracle. Yes, Oracle is SQL based. But you are locked-in once you invested heavily in it.

The way out: slow does it. Put together an PostgresSQL adaption strategy and do it case by case, leveraging opportunities when they present themselves.

At the end, it takes real commitment, just like getting off addiction or bad habits.

Hint: EnterpriseDB may ease your pain.

Clustering...

[evilviper]

Clustering is the big thing that Oracle (RAC) does well that Postgres can't yet touch. If you've got many thousands of customers using your service at any given time, you don't want to have to dick around with having only a single DB server.

Postgres 9.x does have the option of streaming replication to a hot standby server, so there's *some* opportunity for HA, but that's still a long way off from Oracle RAC with a four-node, multi-master DB always up and accepting requests.

If your application does mostly reads of the DB, then read-only slaves are a reasonable alternative that Postgres can do, but you're still bottlenecked on the really mission critical writes to the DB.

Postgres is a really good option that keeps getting better. But if you do go with Postgres, just watch out for the frozen XIDs. I hate having to juggle those stupid vacuum processes.

Re:Clustering...

[evilviper]

FWIW, when you're faced with the option, go with Postgres if at all possible. Oracle is a crufty mess that's painful black magic to administer. There's sufficient documentation out there that you CAN manage it, but it seems to be made up of nothing-but dark corners and ancient cruft.

I hate, hate, HATE always having to manually manage table spaces, data file size limitations, recompiling invalid objects, the HORRIBLE, painful, not entirely documented syntax of the tools (eg. expdp), the admin hostile tools that make the MS-DOS command-line look futuristic (sqlplus), etc. From an administrative perspective, Oracle is a nightmare in comparison to the relatively approachable and user-friendly Postgres.

Re:Clustering...

[magman]

I hate, hate, HATE always having to manually manage table spaces, data file size limitations, recompiling invalid objects, the HORRIBLE, painful, not entirely documented syntax of the tools (eg. expdp), the admin hostile tools that make the MS-DOS command-line look futuristic (sqlplus), etc. From an administrative perspective, Oracle is a nightmare in comparison to the relatively approachable and user-friendly Postgres.

When did you work with the Oracle DB? 1995? Tablespace management: use bigfile tablespaces and autoextend if you need lots of data, one datafile will go to 32TB with a normal 8k blocksize. Recompiling invalid objects... Something has invalidated them, like a drop of a dependent object or something, and there's a script to recompile for you. Syntax for expdp: expdp help=y. sqlplus does take some time to get used to, I'll give you that one.

Oracle administration is not black magic, the documentation is good and the community is really amazing. You have views where you can delve deep into why something is not performing well, and the high availability/disaster recovery solutions are really the best there is.

Re:Clustering...

[evilviper]

When did you work with the Oracle DB? 1995? Tablespace management: use bigfile tablespaces and autoextend if you need lots of data

Bigfile wasn't introduced until 10g (the previous version of Oracle), and there were some specific limitations that prevented up from using it, but I don't recall what they were.

and there's a script to recompile for you

Right, but it's not something you're just going to stick in crond and forget about. It's another unnecessary job for the admin, which is okay because I have absolutely nothing else to do with my day than the care and feeding of Oracle? Is that it?

The problem is plain and simple, no other databases need this level of babysitting. They just do the sane thing, automatically.

Syntax for expdp: expdp help=y.

Yeah, I know, but the syntax is still cryptic as all hell, and takes several tries to put together a working 3-line expdp/impdp command, with just a couple remaps...

Oracle administration is not black magic

No, it's just crufty, tedious, painful, and a lot of completely unnecessary work, which is what IT is supposed to be freeing people from. Postgres gets all this stuff right, while Oracle gets it all terribly wrong.

and the high availability/disaster recovery solutions are really the best there is.

Postgres is catching-up quickly, and doing it in not just cheaper, but also much more flexible ways.

Re:Clustering...

[turp182]

Burst into laughter at "the admin hostile tools that make the MS-DOS command-line look futuristic (sqlplus)".

Oracle without Toad is... Oracle without Toad. It's expensive, but so is time.

Re:Clustering...

[evilviper]

Oracle without Toad is... Oracle without Toad. It's expensive, but so is time.

TOAD doesn't help me do an impdp from a file.

TOAD doesn't allow me to make a change to 6 different RACs at the same time, but a command-line tool can be pretty easily scripted to do so.

In short, Oracle is still quite painful even with TOAD. And for those without the money (or that don't want to use Windows) there is also TOra.

Re:Clustering...

[Meetch]

From experience, TOAD isn't great for managing RAC stuff (I use TOAD, but not for that!) I'd use the database management interface that comes with the database installation, or take a step up from that and use Oracle's OMS, especially given that its license is now essentially free if you avoid trying to use certain largely irrelevant frills like cloud management. OMS will help you do your impdp and if you know how, compare / make changes to multiple databases simultaneously if you dare. You can even download and install a limited license Oracle database for free - http://www.oracle.com/technetwork/products/express-edition/overview/index.html - it comes with a browser based GUI and all, if resource limited - but comes in setup.exe and RPM form.

Manual management of tablespaces??? Create your datafiles for your tablespaces on an ASM diskgroup, set them to autoextend, set up an email alert for when the tablespace gets to 90% full or whatever suits so you can add more files if you underprovisioned, and set up another alert to tell you when your ASM diskgroup is getting full. Manual management is very much dark ages stuff (although some currently supported software insists that is how you configure things... or asserts its configuration on you which then you have to bend to your will... grrrr).

Datafile size limitations - default sizes these days with default block / ASM extent sizes amount to 32G per datafile in 11g. If you know the data will be huge, increase the extent sizes. Doubling one will double the other, and so on... or create a HUGEFILE tablespace, which is 1 essentially unlimited size file.

Recompiling invalid objects? From the database server, login as SYSDBA and: @?/rdbms/admin/utlrp ... done! Incidentally, utlirp followed by utlrp allows you to upgrade your database objects from say 32 bit to 64 bit. Oracle's upgrade methodology for the most part allows you to choose one of a few methods depending on source and target platform, and if you follow the documented procedure it will usually just work.

Oracle databases are more complex because the software can do more for you. If you don't need the complexity, install Postgres - I'd choose Postgres in a heartbeat if all I needed was a solid RDBMS with a useful interface. And I'm sure you can still do funky things with Postgres anyway if you want to practice black magic! Horses for courses though. If it doesn't make sense to use Oracle, and you're not forced to use it, then don't!

Re:Clustering...

[Meetch]

Dude, I reckon you're behind the times. There have been 4 major releases since 10g - 10gR2, 11gR1, 11gR2 and now 12c. Even if you take R1/R2 as one major release, that's still 2 major versions. 10g is what, 8 years old and afaik unsupported by oracle except maybe in extended fashion on the latest patch level. What were the limits back in '05 on "current" Postgres?

Invalidation of interdependent objects will show you ultimately which part of your application is broken when you compile a new package or change some DDL, and thus when it's safest to do the more invasive stuff - unless of course you've used object editioning as it's supposed to be used (as of 11gR2) so both old and new code can coexist on your database, and you can switch users over to the new code, piecemeal or wholesale without interruption or pesky invalidations if you know what you're doing.

There are still gotchas though, like the magical GoldenGate, which can give you an architecture independent replica of your database (for a price of course) oh except for the handful of special tables you want to copy but it doesn't support yet. ;)

I suggest playing with XE to see what the free version can do for you now. And read up on 12c features. It may only be a matter of time before Postgres can do the ones you want, and Postgres may even do it better when it does the implementation (I've no doubt Oracle's database code resembles spaghetti even now). Postgres exists for a reason, and I only wish it were better recognised than MySQL as a real long time ACID compliant RDBMS.

Yep, I've joined the dark side, but perhaps I can still be turned back...

Re:Storing an array of strings (and other structur

[Anonymous Coward]

Postgres has native support for XML. It's stored in compact binary form.

Any one performed benchmarks

[Anonymous Coward]

I am surprised how many developers etc exist in the world and how heavily they stand behind a particular database without doing any benchmark comparisons.
I have done a fair amount of testing over the years. MySQL is a lot faster then Postgres or Oracle.

Oracle also scales real well

[Sycraft-fu]

Now few people need that, but if you have a massive database, massive transaction rates, etc that can be the kind of thing you need a heavy hitter like Oracle to do. There are workloads that just aren't possible on lesser DBs. As I said, not all that common, but they are there.

Overall when it comes to the "free vs enterprise" type question there are three things I think you really need to look at:

1) Can you, or someone else really support the free solution, or is there GOOD support you can pay for if not? If you don't have a support contract, everything is on YOU. Make sure you weigh the cost of your (and other's time) and productivity vs the cost of a support contract. In the case of things like Postgres, you can find companies that support it, however they have a great range in their competence levels.

2) Are there any features you need that you have to trade off for the free stuff? If so, the enterprise stuff is probably the right answer. Don't assume you can just cowboy up a solution that is "just as good" because you probably can't. If you need those features, pay for them. However if all the features are either "don't care," "meh," or "nice but no biggie," then the free option can be a winner.

3) Can the free solution scale to the level that we are talking about (performance or size)? If not, then it is probably not for you.

So like with databases: Just hosting a single website? Hell ya use Postgres or MySQL. Oracle is not likely to buy you anything. However dealing with a 2PB data set that does hundreds of thousands of transactions per second, all of which is absolutely mission critical and has to be always online? Ya, you probably need something heavier hitting, and more reliable.

Your actual setup probably falls somewhere in between those big extremes. You need to determine where and thus what side it falls on.

One thing not to underestimate in some workloads is how well the procedural SQL is. Hopping back and forth from a program to the database is expensive, CPU wise, so in some cases you really need to do some procedural SQL for performance reasons. How well that works can vary DB to DB. If you have stuff like that, worth testing to see how it performs in other DBs. Does it have what you need to implement it, and how fast is it?

Re:Oracle also scales real well

[marcosdumay]

Wake me up when you can make a cluster of Oracle databases, all actively handling requests for the same database at the sme time.

By the way, Postgres does that, but not out of the box. That's the advantaje of FOSS, you simply get a third party plugin, and there are plenty of companies that'll support something like that for you.

The big difference

[Anonymous Coward]

You can install PostGreSQL. Try that trick with Oracle. Hahahaha.

Re:Storing an array of strings (and other structur

[ulatekh]

Protocol buffers are database-independent, i.e. using them keeps one from being tied to one particular database implementation.

Oracle is expensive for a reason

[ghinckley68]

IT JUST WORKS. postgres and mysql do not even come remotely close to oracle. not by a mile.

they are simple RMDS Oracle is an application stack the db is only a small part of what oracle can do.

Re:Oracle is expensive for a reason

[Anonymous Coward]

That is why the minimum install of Oracle is 2GB in size.

Re:Oracle is expensive for a reason

[Anne Thwacks]

IT JUST WORKS

You were kidding, right?

Re:Oracle is expensive for a reason

[fuzzytv]

Well, he's either kidding or he never installed Oracle using their Universal Installer.

Re:Oracle is expensive for a reason

[Meetch]

The UI is much prettier these days, and improving with each release... thankfully! Never had to do any installations up to version 8, but 9i was _bleep_, 10g was pretty crap... 10gR2 bearable. 11g was a step in the right direction and 11gR2 not too shabby. Haven't had the time to install 12c yet myself, but the installers for their other (predominantly weblogic) work pretty well when you can get your head around the installation guides, assuming you're doing something non-vanilla. IMNSHO of course.

Oh yeah there was the time I uninstalled a piece of application software with the OUI, and as well as what was expected it deleted 1 library out of another oracle installation. Finding that was LOADS of fun. But that was in the 10g days. If you go with 12c, when they release an upgrade you're supposed to install the new software, unplug your database from the old home and plug it into the new one (aka pluggable databases). I'd wear a little pain with the installer for that feature.

Also not for the faint of heart, the grid infrastructure software which provides the cluster / ASM support weighs in around the ballpark of 1G of RAM usage, before starting up your RDBMS. Not much of an issue when the cost of RAM is such that the average home enthusiast can afford 16G or more of the stuff. But when you use the features (because you need them), it's approximately worth it.

Re:Storing an array of strings (and other structur

[Anonymous Coward]

I wonder if XML is a standard.

Re:Storing an array of strings (and other structur

[KingMotley]

But is it Web Scale?

In my experience, it's ease of use

[pestilence669]

I've used just about every major RDBMS in production. Oracle, in my experience, is the most forgiving and has a query optimizer that nearly eliminates the need to think. PostgreSQL works wonderfully in the hands of a competent engineer. Oracle works well for people that barely know SQL. Much of Oracle's complexity comes with the training wheels it provides... at the expense of cost, configuration, tuning, administration, customer service, etc.

Re:In my experience, it's ease of use

[fuzzytv]

It's difficult to compare the optimizers, because Oracle has parallel queries and some other features that complicate the optimizer considerably, but I've run into some very nasty issues with it in the recent versions I've been working with (10g and newer), from crazy plans to serious crashes. Never had the same issues with PostgreSQL. Obviously, both optimizers share the same weaknesses - like give them conditions on correlated columns and the estimates are going to be way off, possibly resulting in poor plan.

One thing that frequently shoots in the developer's face on Oracle is that the stats are not collected based on the activity, but you have to setup this as part of a maintenance window. Well, good bless autovacuum.

Security record

[alonz]

One thing that does stand out in Oracle's favor is the security record.

Oracle has real good internal processes for secure software design, implementation review, and emergency response. (I know this from people who have actually worked there and were subject to these processes– it's not just marketing fluff).

Postgres developers, on the other hand, willfully ignore security issues in their code on flimsy grounds.

Re:Security record

[Anne Thwacks]

One thing that does stand out in Oracle's favor is the security record.

Emailing everything to the NSA is good?

Re:Security record

[fuzzytv]

So you can prove that? Because when a company says "we have a process for that " it's often equivalent to a cluster-fuck in the future.

I can't really imagine anything more secure that the transparent development model used by PostgreSQL - you can see all the decisions in the mailing list, you can comment whatever you want, you can ask questions about supposed issues, you can see all the individual commits etc. And many people actually do that, so this is an example of a project where the "many eyeball theory" actually works. Can you do the same with Oracle, or do you simply trust to the corporation because they said so?

It's not that long ago when a serious security vulnerability was discovered in PostgreSQL, and in retrospect I think it was handled well (and the next feature will be handled even better). For more details see http://www.postgresql.org/support/security/

Who would prefer you living in a hovel, you mean.

[Anonymous Coward]

They'll tell you "We have to be competitive with your salary, your job is a global market!" but when the executive rewards are out, defend the pay increases with "We have to get the best CEO to be competitive!".

Four hundred extra people could be employed if the CEO was kicked out.

Given the CEO actually produces ZERO output, how much more productive would you be without him?

Note too that first of all your company needs customers. They buy your shit.
Then you need workers, they make your shit.
You don't need managers. AT BEST they just ensure that the workers are more effective, but without good workers, your managers are doing nothing, protecting nothing. You can manage for a few years having sacked EVERY manager.
Last of all you need the executive board. You never actually need one.

Almost there

[DeBaas]

The big thing Postgres still lacks is a licensing scheme so complex and obfuscated that you will always be breaking some rule, allowing the vendor to extort you into selling something new.

Anyone that has dealt with Oracle will know what I mean....

Re:Almost there

[Meetch]

Amen! At least they don't license based on available RAM yet. Oh darn, I hope Larry doesn't read this and get any ideas. ;)

Re:Who would prefer you living in a hovel, you mea

[Codifex Maximus]

>>Last of all you need the executive board.
But, without the executive board, where would all the profit go?

A convenient meme for the NSA.

[Ungrounded Lightning]

Also; I hear plenty of government workers saying Management has a no open source software policy; for security reasons, the more money spent on the product the better, as closed source code is deemed to be more secure...

And that's a convenient meme for the spooks who have been getting the big companies to embed spyware in their systems, where the systems' closed-source or as-a-service nature makes it difficult-to-impossible to detect such spyware, even as it's doing you harm.

Perhaps the revelations about the Prisim program will drive a reevaluation of such policies and a move toward open systems which CAN BE CHECKED for embedded nastiness.

If you are asking this question...

[Anonymous Coward]

Then you really shouldn't be the one making decisions.

Each system has its uses... But when you are managing mission critical data with gigabytes of data, you want something with a good solid support base. You should be paying for ongoing support. If you license Oracle on Sun (now Oracle) hardware, its a hell of a lot cheaper then Oracle on x86. And should shit hit the fan, you have Oracle resources to fall back on to help you save your ass.

CONNECT BY?

[Anonymous Coward]

This has been superseded by SQL standard CTE (aka Common Table Expressions) Oracle has that. PostgreSQL has that too.

Superior in some ways, not in others...

[WoLpH]

Both Oracle and Postgres have some merits.

Personally I would say, if you need more modern features/possibilities, go for Postgres.
If you don't like to do things manually (materialized views for example), use Oracle.

postgres is not oracle

[cartman]

I'm a big fan of postgres and have been for many years. I consider it to be an excellent RDBMS.

That said, postgres is not a replacement for Oracle. Oracle has a large number of enterprise features which are too numerous to list here.

One important thing which springs to mind is the lack of index clusters in Postgres, even in the most recent versions. The postgres equivalent (cluster table using index) is just not the same thing at all, or even close. This by itself could easily cause some complex queries to take more than 5x longer in postgres.

Another important thing is that postgres has no equivalent to RAC or other clustering technologies available in commercial RDBMSes. Hot standby is not the same thing.

There are many other examples that are too numerous to list here.

Re:postgres is not oracle

[Anonymous Coward]

Another important thing is that postgres has no equivalent to RAC or other clustering technologies available in commercial RDBMSes.

Oracle RAC is overrated. It costs arm and leg - and lowers performance very noticeably.

I know two customers who at different time tried to use it.

One has removed it from production because performance impact was too great.

Second, after one year of uptime, simply couldn't bear paying the licensing fee anymore and also scrapped it.

As far as I can tell, Oracle RAC is most of the time sold as a gap filler, because top brasses want to have 100%, bullet-proof system with perfect uptime. But as practice shows, most DB problems are related to pressing wrong button. And RAC can't "fool-proof" the system.

Re:postgres is not oracle

[fuzzytv]

Well, that's certainly true. The reason is that PostgreSQL is not meant to be a drop-in replacement for Oracle (although many use it for this purpose), but an independently developed product.

There are many possible features that are not implemented in PostgreSQL (yet), or not to the degree available in Oracle. Examples might be partitioning, parallel queries, ... Why don't we have some of these features yet? Well, the development is driven by features needed by users (either directly or through companies providing PostgreSQL services), but there's no almighty corporate entity that would commit to delivering a feature in the next version and then throw 100 devs on the implementation.

BTW there already are products based on PostgreSQL that might be interesting for some RAC users - Postgres-R [http://www.postgres-r.org/] and Postgres-XC [http://postgres-xc.sourceforge.net/].

I did this analysis for a large enterprise

[Anonymous Coward]

Years ago I was on a technical team which looked at which database engines to standardize upon. From a ten-thousand-foot view, all DBMSs are good. Enterprises and critical applications are run on Oracle, DB2, MySql, etc. Ultimately, the decision is a monetary decision, not a technical decision. And there are three components to determining the monetary costs. First, what's the difference in purchase costs for a specific footprint? (Usually the non-Oracle footprint is cheaper). Secondly, are you going to provide database support in-house for Postgres or buy support? (If in-house, is your resume current? Because when a critical application fails because of database problems, that database needs to be brought back to life as soon as possible. I was never confident that we could keep a team of crack developers hanging around for years waiting to fix a few emergencies. So the real cost comparison is 7x24 support.) Thirdly, how much would it cost to port the applications currently using Oracle to another DBMS? This was the killer in our analysis. There are not only the costs of the port, but also the lost business opportunities when that application team is tied up in the port instead of building new revenue streams.

Re: I did this analysis for a large enterprise

[Anonymous Coward]

You should consider DB2 V10.X. Can match everything oracle does. Can even run 98% of Oracle Pl/SQL.
Can do better than RAC, no issues with nodes owning rows and moving them around.

Re: I did this analysis for a large enterprise

[Anonymous Coward]

Yes, this is exactly what happened when we released our analysis. Too many technical people already knew the answer, and the answer was _____ (insert favorite DBMS).

The real question? What makes sense for the shareholders?

Obvious troll is obvious (n/t)

[Mateo_LeFou]

thank you

Re: Who would prefer you living in a hovel, you me

[multimediavt]

I'm guessing that you've never owned or operated a medium to large sized business? If you had you would know all of what you said is nonsense, except the CEO pay disparity issue. That is a legitimate issue to be addressed. The rest is a rant of the ignorant. Quality managers are rare, but they are essential to business operations and continuity.

Cost options

[luillo]

I know there are some options for Oracle like Standard and Standard One which are less costly and can be used in servers with four sockets or less.

Re:The sorts of things you get. Features!

[Meetch]

I've been a relatively mild-mannered open source advocate for over 20 years now, and have been running Linux for all of it. My first DBA job was with Postgres (6 or 7, ~12 years ago now!) and now Oracle. This is all about databases, completely ignoring the application related acquisitions they've made in the last decade...

A lot of difference I see and is evident from the discussions here is that Oracle usually has the features earlier (not always, but yes, usually). The earliest example I've witnessed is Postgres' Write-Ahead Logging, which was definitely cool, but Oracle were there first. More recently, with 11gR2 you have advanced compression (pay $$$$ and it will store all your data compressed if you want) and with 12c there are a bunch of features that make me drool. Pluggable databases is just one of them.

Again, not entirely sure about Postgres, but Oracle build a lot of instrumentation into the database software itself. Tracing custom events is a great way of profiling your application as well as database deficiencies. Pay for the license to unlock the full power of ASH or AWR and you have a great deal of ability to see exactly what's going on and figure out how best to resolve any performance issues. The best bit is that this instrumentation doesn't make the database run like a dog. A few percent overhead gives you a lot of debugging power, and it's ALWAYS turned on with basic event tracking always happening anyway. But you can add MOAR.

I see some impressive performance on Oracle databases these days, but not entirely convinced that Postgres cannot meet them. But then, Oracle can run on anything from 32 bit x86 to some seriously beefy hardware (and when it does, it runs well). I'm not entirely sure about Postgres, but I know Oracle has been compiled for RISC architecture (Power, SPARC, HPUX, others??) for a long time. These days they to lean towards x86 - and will even sell you a "database machine" (google for Exadata). This extends to scaling out on any of the supported architectures with their cluster software (Grid Infrastructure) these days, which is quite mature now. Again, Postgres probably does this, but each generation sees a significant improvement for Oracle.

Having said all that, leading edge can also be bleeding edge... The biggest problem for me with Oracle continues to be the time it takes to resolve software bugs combined with their support infrastructure. While it usually gets there in the end, for the price you pay for enterprise support one might expect quicker resolution if you happen to be the first person to hit upon a specific problem. Unfortunately this tends to tie with the need to certify with all the Oracle applications they release and support. The one and only bug I reported when I was a Postgres DBA was around a date calculation issue - from the behaviour I reported it was tracked down and patched in ~ 2 days, and I had a workaround for the meantime anyway.

Oracle have also done some cool stuff in the open source domain with OCFS (and now OCFS2) and the free domain with their base GI cluster software, as well as the plain cool domain with ASM (dynamically manageable disk pooling with Stripe And Mirror Everything methodology providing solid data robustness) and ACFS which lets you carve out clustered POSIX compliant filesystems on top of ASM at will. This all helps with scaling (don't need OCFS2 now if you use ACFS tho).

Hmmm, it seems they really are turning me to the dark side.... heeellllllppppp!!!!

compact xml

[Craig Ringer]

Actually PostgreSQL's XML type is a thin wrapper around "text", not a compact binary representation. It does get TOAST compressed which might've what made you think so.

Re: Storing an array of strings (and other structu

[Anonymous Coward]

Postgres also has support for json, and while it is simply stored as a string, it at least can be validated on insert and deeply indexed.

MariaDB

[cmurf]

MariaDB is the drop in replacement for MySQL, leg by the original MySQL developers. Fedora has moved to it in recently released Fedora 19. And RHEL 7 will support MariaDB at least as an alternative for MySQL. Two months ago Wikipedia moved from MySQL to MariaDB. SkySQL has merged with MariaDB. So the momentum is behind MariaDB.

well duh

[slashmydots]

They didn't make a complete catrastrofuck called the Java web plugin and they didn't ruin Open Office so I'd say they're beating Oracle.

Re:Probably Not - Hibernate

[ahabswhale]

Hibernate is a piece of shit. A leaky abstraction that's more leak than abstraction. I don't get the fascination with this technology.

Re:Probably Not - Hibernate

[fuzzytv]

Let's not get into bashing Hibernate here. I'll just say that the only project where I've seen reasonably efficient and working usage of Hibernate was the one project with the highest number of people actually knowing SQL well. Which is exactly what most ORM promises you won't need ...

I hope not

[JamesRing]

If Postgres is on par with Oracle then it has fallen a long way.

Practically - No.

[Anonymous Coward]

We migrated from Oracle to Postgres on our customer facing applications - with no impact whatsoever from a performance perspective (we have terabytes of data stored in our databases).

That being said, Oracle and Postgres are administered differently so there was a learning curve for our DBAs, and some code had to be rewritten if it used 'oracle-isms' (e.g. non-standard SQL functions).

Not a big deal.

Why the big corps use Oracle

[independent123]

Oracle will handle highly concurrent transactions with minimal locking, and provide consistent reads at the same time. This is the most difficult thing transactional databases do. You can also throw horrendous queries and really bad applications at it and Oracle will handle it. Like the other major companies Oracle gives fairly good support for their product and the product documentation is superb. The big corps have the money to pay for it and like to play it safe.

Re:The sorts of things you get. Features!

[Lotana]

Hmmm, it seems they really are turning me to the dark side.... heeellllllppppp!!!!

Oracle is a database company with massive resources. Thus database is what they are good at. Price is the biggest downside that I can see.

So if you can afford it (ie. You work for a VERY profitable/large company) and you need the features that are not available (Or not good enough) in other offerings, then join Larry and don't look back. Evil may not be good, but it sure has a nice database engine!

Solution

[NewYork]

Postgres should hire more MBAs.

Oracle? Oracle? You don't need no stinkin' Oracle!

[Anonymous Coward]

Who would buy a $5000 Nikon body and a bunch of expensive lenses when a pocket camera can do almost everything a DSLR does?

Re:The sorts of things you get. Features!

[Anonymous Coward]

Evil may not be good, but it sure has a nice database engine!

There are lots of things that can be said about Oracle, some more flattering than others, but I'm fairly sure "nice" isn't one of them.