Slashdot Mirror
Are Expensive RDBM Systems Worth The Money?
Death of Rats asks: "What are the actual advantages of high-end Relational Database Management Systems (such as Oracle) over the free or low-priced solutions? Is it worth paying the 10 to 30 thousand dollars (or more) for Oracle, when you can do (as far as I know) everything it can by using ASP with Access/MSSQL for Windows, PHP/MySQL for Linux, JSP with JDBC connections for most platforms, or any number of other free or cheap systems? Is there a considerable speed increase, or is there enough more functionality to warrant the outrageous costs? Are some of the technologies more suited to different tasks? Any time I have needed to use databases, the open-source solutions have done fine, so I am skeptical."
There is no one-solution-fits-all answer to this question, as it really depends on the requirements of the task at hand -- which will in turn determine what one needs from an RDBMS. I'm sure there are tasks out there which better suit the high-octane RDBMS systems, but what tasks do readers feel still need that kind of power? Has the distance between Enterprise RDBMS servers and the free alternatives shortened at all?
This is a laughable question to anyone who knows these products. In certain situations, Access or MySQL will outperform Oracle, even when Oracle is running on a million-dollar E10000. There's no way that Oracle can match what is essentially a flat-file read in one of these little databases.
BUT THAT'S BECAUSE ORACLE IS DOING A LOT MORE WORK!!! WORK THAT SERIOUS ENTERPRISE USERS NEED!
I have used Access/MySQL extensively (and successfully) for many years, and I currently use Oracle. In these Oracle database, my company maintains medical records in several 250GB+ databases (almost 1000 tables each) that at any given time have hundreds of users.
We would gladly stop transferring zillions of dollars a year to Oracle Corp. (and enriching Larry Ellison, that horrendous asshole), if you can give us a matching small-database solution. Sadly, we would be lost without Oracle. You ask what the advantages of a high-end RDBMS are - well here it comes: Does Access or MySQL...
1) Have many different index types to facilitate different types of joins and database uses? (Star queries, bitmap indexes, etc...)
2) Have a serious security model that is extremely fine-grained and capable (and correspondingly annoying to work with, of course)? We work with medical information, and unlike almost every other company out there today, we guard it extremely tightly.
3) Have fine-grained locking? I know the MySQL guys are tired of being pounded on because their locking isn't row level (I do know that they are improving this). But it is important when you have hundreds of users. And just as important is analysis capability: when users get locked, Oracle can tell us (with a little bit of sweat) what exactly the problem was, so that we can maybe fix it.
4) Have a great cost-based optimizer? Is the optimizer flexible enough to respond to hints for those rare situations when the programmer knows better than the database?
5) Have full-featured replication?
6) Have support for numerous production support tools that can report on time-consuming SQL statements and also do things like rebuild huge indexes without taking the database down?
7) Are completely multithreaded so it can take advantage of multiple processors? (Yes - most databases are IO constrained, but when you have powerful disk subsystems like we do, IO can increase to the point where CPU can be a bottleneck.)
8) Can horizontally or vertically partition huge tables so that they are spread across many different disks? This vastly increases IO.
9) Have a flexible and intelligent constraint system that has enterprise-level features such as the ability to enforce constraints on new rows but let pre-existing rows violate the constraints. Sounds stupid - but when a table has tens of millions of rows, it might take weeks to write the program to fix bad data - but in the meantime at least you know that no new bad rows are being created.
I don't mean to slam the smaller databases. In 95% of systems, they are probably OK. But for us in the remaining 5%, our databases are everything to us, and we'll pay almost any price for integrity and performance.
Atomicity. Either everything in a transaction gets committed, or everything doesn't. You make sure that no matter what happens, with anything, if you shipped one widget, that inventory is updated accordingly, etc.
Consistency. The database goes instantly from one valid state to another. It never allows integrity constraints to be disobeyed. Never.
Isoluation. The database gets updated in a manner such that until a transaction is complete, no other query shall see any portion of the results of that transaction.
Durability. Once committed, the results of a transaction are permanent. If the database returns success, then 2 milliseconds later a hole in time and space sends one of the db hard drives to 5th century china, after inserting a new drive, the state will be exactly as it should be, with no information lost.
MySQL, for example, fails every single portion of the ACID test. Yet people will tell you to run your business on it because "it's really fast!" Of course it's fast, it's the db equivalent of a honda burning nitrous, with no way to turn the nitrous off. It'll be fast as hell, but you know it's only a matter of time until it blows up.
A lot of free databases lack basic capabilities such as sub-selects. This is where the value you're searching for is actually the result of another search. Yes, it's fully possible to write code which will do this in the client, but that's ludicrously inefficient if you're working with moderately sized datasets or larger. Instead of letting the db take care of that natively, you not only do the work yourself, in a non-standard hack, but you pass large amounts of unused data across a network stack, which uses resources.
I'm not saying all free databases are evil, they aren't. PostgreSQL is a very reasonable database which passes 'ACI' out of 'ACID'. That might be good enough for a lot of applications. Hell, flat files are good enough for a lot of applications.
The key here is to evaluate what you're doing. Before you look at the cost of the database, look at the cost of downtime, the cost of lost data, the costs associated with misreported data (caused by, for example, the database updating non-atomically), etc.
If the answer to the above cost reporting questions are all 'zero', including salaried time, then use MySQL. If not, consider using a real RDBMS.
Contrary to slashthink, businesses aren't completely stupid, they don't spend large amounts of money unless there's the benefit is expected to exceed the cost. There's a reason why most every business on the planet uses a real RDBMS.
--
"Don't trolls get tired?"
You see, when properly setup, most databases keep the information written across numerous drive arrays. If you follow, as an example, Oracle's recommendations on how to setup your hardware, you'll never have data which cannot be reconstructed after the loss of a single drive.
Yes, almost all software will let you do stupid things like use files instead of raw partitions, or to use hardware which allows unrecoverable failure. The question is whether such a system is "properly configured" according to vendor documentation. In the case of commercial DBMS, such as DB2 or Oracle, they aren't.
Personally, I'd be a little upset if somebody claimed a database passed the 'ACID' test, but had inserted a notoriously unreliable single point of failure (hard drive) into the system. Wouldn't you?
--
"Don't trolls get tired?"
That's the extreme case; in rather less extreme cases, a benefit of Paying The Bucks for Oracle is that you can do online backups while the system is running, and have the expectation that any DBA that is worth his (or her) salt can bring it back up if there is a problem.
Those are the critical things that cause managers to be willing to pay the Big Bucks for licenses.
The fact that there's a sizable community of DBAs from which to hire is a nice bonus; the process feeds on itself in that it is profitable for everyone (well, almost everyone...) to get involved in certification activities. That means that there's a half-reasonable lowest common denominator set of skills out there.
Another "merit" is that there is a whole pile of third party software out there, whether in tools to help you manage databases, tools to help you build DB schemas, applications running atop Oracle ( e.g. SAP R/3 ), and such, again, with financial benefits to many of those involved. It is SAP's interests to promote Oracle sales, and vice-versa... (Albeit that being an example of a situation where there are some conflicts of interest!)
If you're not part of the solution, you're part of the precipitate.
High-end RDBMs (Oracle, MS-SQL, DB2) are beasts of a whole different nature from your regular, run-of-the-mill MySQL or Posgress installation.
First they have a price tag attached. A very high-end price tag. The figure you give for Oracle is the barely minimum a company would expect to expend at the lowest entry-level.
Second, they all must be baby-sited. You do not install Oracle from a CD, make it run and leave it alone. No, you musrt first pain-stakingly install it, sweat and curse for a couple of hours to make it run and them administer it forever. Leave it in the hands of the users for a couple of weeks and the meaning of the word entropy will soon be known at the upper organization levels.
Allright, these above are a bit dramatic but true. In a sense, RDBMS makers and specialists are fighting hard to defend the last outpost of what Allan Kay once called "the high priesthoood of a low cult" (talking about the computer people of the 60's).
Now, high-end software is for high-end jobs. First, they have a bunch of weird performance marks to show at Transaction Processing Performace Council results for TPC-C, for instance. Carrefully note the kind of hardware they are talking about. They are measuring RDBMS performance in machines costing millions of dollars!!
And then these beasts are capable of performance peaks unheard of in the free packages. Not that I do not like MySQL. I like it a lot and have used it in many enterprise systems I developed. But those were small enterprise systems. They did not had to sustain high transaction periods, nor high connection counts, nor gigabyte sized database tables. And certanly not all three together.
I am currently working in the development of a system for a large laboratory. They process thousands of clinical exams per day, mostly in the morning. The organic material for the exams must usually be processed within two to four hours after it is collected from a patient. We will pay whatever it takes to have peace of mind. And I tell you, those will be some well expend millions of dollars.
One thing that is different between commercial and free solutions is support.
I know that Oracle can offer you 24x7x365 support options if you have enough cash.
Can mySQL do that? My experience with support on open-source solutions has been good, but not good enough to bet my business on getting a problem fixed in a few minutes instead of a few hours or days...
Still, sites that use these databases are still prone to problems, just look at EBay!
-Hunter
RateVegas.com - Vegas Reviews
With a commercial database, you pay for proven reliability, proven scalability, support, thorough documentation, and features.
MySql is not a database in the same sense that Oracle is. The four basic criteria that need to be met by any database are summarized by the ACID acronym (Atomicity, Consistency, Isolation, Durability). MySql satisfies none of them and lacks a huge number of very basic features. It is not a database, but a simple SQL parser on top of a DBM file.
The ACID test assures that the data will always remain in a consistent state, despite failures. Most enterprises (banks, businesses, etc) would pay any amount for this feature.
Postgres is a real database. However, it is still _very_ slow and scales poorly. The documentation for Postgres has glaring omissions, and the database lacks some features.
It is often worth paying for a commercial database. There is not a substantial difference between Oracle the "other" commerical databases (DB2, Informix, Sybase, etc). All of those databases are enterprise-class and offer features and performance similar to Oracle. Oracle charges 3-4x as much money because they are the market leader and market leaders always do that. Managers are extremely comfortable with market leaders, programmers are familiar with it, and DBAs are easy to find. That justifies the extra cost for most corporations.
It is strange that this "ask slashdot" column lumped MSSQL in with Postgres and MySql. MSSQL is not free, and an enterprise license is not cheap (>$20k if I recall). I have very little experience with MSSQL but it is generally not well regarded.
The most important thing is to keep things portable across databases. The biggest danger with the high end database is their vendor-specific extensions keep you locked in. Once you've gone Oracle and done all the Oracleish things, it's hard to undo that. I fell into that trap with Informix.
Start small and keep it portable. Chances are that a solution like PostgreSQL will work quite well. If for some reason you start hitting a wall, it should be easy to move up if you were careful.
And that's what it's all about. Stay flexible, because it's a dynamic world.
Scalability & stability. If you don't care about those, use one of the free offerings.
We will know databases are a commodity technology when the free offerings are as good or better than the commercial offerings. Right now that isn't the case.
--
--
E_NOSIG
More to the point, if you are willing to do the work
of maintaining consistancy yourself where it counts,
you can often not only do so, but your code benefits
from it hugely. Example: my application has a few
statements which must be atomic (everybody does this,
AFAIK, certainly PostgreSQL, MySQL, MS, Ora, etc).
Now, I have one piece of code that needs to execute
a complex batch of statements with consistency and
rollback. I can easily write a library that supports
rolling back this one transaction. Oh, and look, this
one transaction also does not need to complete lightning
fast, and happens rarely. That means I can take advantage
of best speed everywhere else, and use table-level locking on this one transaction just to be safe. These are not options in most large RDBMS', but certainly are in something like MySQL. In fact, you can swap out which back-end storage system (MySQL, Berkely DB and soon Progress) to use on a table-by-table basis for best reliability/performance characteristics.
Bottom line is this: Oracle has it's place, but once you start
to need high-end performance, it falls over in the price/
performance arena. I can run the same database on
MySQL engine for 1/10 - 1/100 the cost and get the same
reliability.
What most people really need out of a database is to never
lose data. If your DB loses data, then get another
one fast. Otherwise, you should be looking at how
much you'll pay when you add the 4th processor....
It's a comples question with complex answers.
In the end you have to decide weather or not you need to scale up and when.
Of thhe free databases three that are closest to doing what oracle does are interbase, postgres and sybase 11.03 (not open source but free). Each one of these is suitable for a small to medium size company to start with. sybase is aging but still usable and postgres has a quirky set of features but keeps getting better at a very fast rate there are also a couple of companies prioviding support for it. Interbase has been around forever, is well tested and has a large userbase. It is supported by an exrtremely knowledgabel company called ibphoenix and borland too. It also runs equally well on windows or *nix and has great third party client side tools for administration.
The next tier up in price you have ms-sql, db/2, and informix, and sybase. MS-SQL is ok if you are willing to tie yourself to windows forever and are willing to pay extra for jdbc drivers and don't want to ever access from unix with php or perl or python (freetds is a valiant effort but it's still not 100% functional).
DB/2 is surprisingly affordable for the workgroup edition I was quoted about 6 grand for an unlimited user license. Compare this with the 5K per processor license for sql2000 and it's a bargain. Your mileage may vary depending on the size of your company and how afraid MS is of you switching. They offer deep discounts to large clients.
I have never used informix or sybase so maybe other people can talk about those but sybase does offer sqlanywhere studio which is a low cost alternative you can check out.
Mysql still has a way to go catch up with these databases so I would say forget about it for now.
In summary. The free databases do anywhere from 70-80% of what the big bucks databases do at about 70-80% of the speed. If you don't need that last 20% there is no need to spend the money.
Allthough none of these databases can compete with oracle head to head they would make great starting points for a company just starting out.
Of the open source databases Interbase is easiest to setup and use IMHO so it would get my final recommendation.
War is necrophilia.
OK this is just my take on the situation so do your own research OK?
;) and some related tools and start playing. You'll find the documents are excellant and the third party tools are awsome. If I was running a business on it I'd call the ibphoenix people up first. If I couln't come to an agreement with them then I'd call Borland. Some big companies may only want to deal with Borland though so it might be an easier sell to your boss.
There is no significant difference between the "commercial" version and the open source version. In fact it's more accurate to say that there is no commercial version at all. There is a fork between the Borland version called Interbase and the "community" version called firebird. This is due to the fact that Borland owns the trademark "Interbase" and the open source folks did not want to run afoul of any laws.
Right now Borland is developing the interbase fork and firebird people are develpoing theirs. I would trust the firebird fork better myself as Borland got rid of most of their interbase staff. The most experienced of the borland staffers went on to form the ibphoenix company which provides certified builds and support.
If I was just fooling around I would go download the firebird suite
War is necrophilia.
There is an excellant middleware for delhi called ASTA look into it.
War is necrophilia.
In my opinion, it most definitely can be, if the business requirements are used as a primary factor in the database engine selection;
... dynamic tuning, and those dreaded vendor-specific extensions are often used in support of this area. In a modern enterprise, expect millions of transactions (each transaction could be updating 30 tables, with several hundred rows) on an hourly or daily basis.
... 3+ dimensional location data). Similarly, can I distribute my data (I'm not simply referring to tuning) so that it resides where it is required? Without thee included features, what effort must I make to program around the DB engines deficiencies (usually called 'undocumented features' by the vendor).
1. Availability: how crash-proof is the RDBMS is 1 part of the equation. Another is the ability to backup and recover the db. In a 24x7x52 shop, you virtually never get the opportunity to shut down the DB. A simple file or filesystem backup will NEVER let you recover a transaction-consistent system. Also, how well does the engine support high-availability clusters, with automatic replicatin and hot-standby servers.
2. Performance: This is the 1 area where no product is perfect, but the tier-1 products such as Oracle & DB2 particularly excel
Application availability: A company doesn't purchase a database just to have a database, anymore than they should purchase hardware or an OS for their own sake. There are more applications such as ERP and CRM, as well as many client-server and two-tier apps written to support the tier-1 engines.
Scalability: Its fine to cluster servers; I wouldn't want to tailor postgres or mysql to distribute my database on many servers in a number of different geographic areas. Tier-1 vendors have addressed this in the vendor-specific tools (although ANSI 92 attempts to incorporate many of the special features). Size is another factor; can the engine process terabytes as easily as gigabytes? If you have a server with 20GB RAM, will the database take advantage by caching objects (either automatically or by administrative instruction)?
Support: While support contracts CAN be purchased to support most anything, can you call the vendor at 8am Xmas day with an issue that has caused the database to crater? I know from personal experience that Oracle provides that level of support with reasonable expertise available.
Features: While this is a double-edged sword, can the database support all of the required corporate data entities? I include single fields of 400MB (Seismic traces and well logs), as well as providing indexing strategies that reflect the application (i.e. spacial data
Cost: The support and development cost of applications for a free engine MAY actually be higher than purchase of a packaged application for an expensive db.
Security: How does one secure transactions (not just https from the web) between the visible server and the application servers and the database servers? For example, in the petrophysical world, the most dangerous information for competitors to gain is the knowledge of the latitude and longitude that a company is querying.
There are few clear-cut answers in a general sense, but I believe that it is fair to say that the more demanding the application, the higher the probabability that a high-priced RDBMS will be required.
Bill
By itself, no, but there are a number of organizations who will support mySQL for you.
It's no different than support for any other open source software. If you want training and support, you find a third-party company that does training and support.
They may not even really be "third party". Randal Schwartz' Stonehenge Consulting does Perl training. Randal is a key developer in the Perl community. Does he count as third party?
--
Also keep in mind that Sybase offers Linux x86 binaries of it's previous major release for free for any use (deployed commercial use okay!). They offer the current version free for development purposes. The previous major version is plenty good enough for most applications. There is no comparison between MySQL and a real industrial strength database like Sybase. Maybe one day MySQL will be up to snuff and then we'll all use it, but Sybase and Oracle have been working on this stuff for a long time.
Burris
Along those lines, there's also GemStone/S and GemStone/J, OODBMSs for Smalltalk and Java respectively. I don't know about the Java version, but the Smalltalk version is pretty cool stuff, and can interface with C and C++. A lot of people prefer not to call it an OODBMS, but an application server. It's both, more or less.
Working toward a usable PDA environment in the spirit of Newton OS: Dynapad
Good comments/theads pertaining to this were...
Why do we always have to see stories in groups of 3 (that means there's one more DB story coming tomorrow)?
I just came across this, and it seems to address the topic quite frankly. It is, however, wuite simplistic and provides little technical detail. Perhaps someone more familiar with the field could comment...
Your information is old; MySQL does have transaction support now. http://www.mysql.com/news/article-18.html
Yet another RDBM debate cooking up! This is like asking whether there is any point to mission critical software since the mainstream works just fine for the majority of cases. See here lies the point the majority doesn't need an Oracle database just like the majority won't ever need high end servers because their requirements and constraints can be accomplished with the use of off the shelf hardware software. Specialised software fills specific niches and becasue the market for it is relatively small the sales margins have to be high to justify writing said software. If you think that Oracle licensing is steep I encourage you to look at pricetags for geoscience software products. We're talking an excess of $50000US per SEAT. Are some of the technologies more suited to different tasks? No. Not at all. EVERYTHING can be shoehorned to run on a pentuim box with Lenux preinstalled on it. You can be sceptical all you want but once you are responsible for a system where a downtime could mean lives lost or millions wasted you will realize that a meager few thousand bucks is just peanuts. How much would you be willing to spend to protect the life of your kid? Or your wife? Or your sister? (think air traffic control software etc.) Please think those things through before posting them to slashdot front page.
Your pizza just the way you ought to have it.
Just like that old phrase says, noone ever got fired for buying Microsoft. The same goes for Oracle.
There is also the faction that will say that MSSQL cannot scale up to what Oracle can. I tend to agree with this line of thinking, but to each his own. In the end, most companies that have the money will end up purchasing something like oracle, just because they know they have a billion dollar company that has proven itself again and again and again.
Just my .02USD
When I first started working with Oracle (I'm a certified DBA now), I often had the same skepticism. I had worked with MySQL before, and couldn't understand what all the hype and the expense was for. What's the big deal about storing data in tables and querying them with SQL statements? No doubt there's a bit of sophisticated computer science that goes into that, but surely there's a limit to how complex it all can get. Why the hell does Oracle have to be so damn complicated (I hated trying to understand the docs, which always leave out the forest for all the trees), not to mention expensive?
After a while, I understood that as far as representing tables and data and SQL statements is concerned, this really is true. About this part, the questioner is right: An open source solution will represent your data just fine, no reason to pay out the nose if that's all you need. And MySQL is still almost always faster than the expensive commercial RDBMS's. But tables and SQL statements are only one small part of a database application, and may not be the most important part.
What you're getting for all that complexity and cost is all of the extra services that come with the data management, and these things really are worth it in many projects: Constraints that enforce data integrity, safe transactions according to the ACID principles, robust backup and recovery regimes that really can survive the worst catastrophe, distributed computing and clustering (for high availability and distributed load), and a lot of parameters to tune. All of this stuff seems like add-ons, if all you think about are tables and SQL statements. But for many applications, the data model is not what matters most; these things are by far the most important qualities of your RDBMS.
Oracle RDBMS's still could stand to be less expensive and complex; but I see the justification now much better than I used to.
Always keep a sapphire in your mind
... you've already answered your own question. When you need Oracle or DB2, believe me, you will KNOW it, or you're a moron and beyond hope anyway. At my current position I'm managing a Web team responsible for a site that lets users build complex queries that then sling around 16GB databases. We're using MS SQL Server and even with that we look covetously upon Oracle's reliability and scalability. There is no way on God's green earth I'd try to do this in MySQL. I love MySQL, but it's just not ready for prime time for these kinds of applications. Which is why I say you'll know when you need it; if there is any question in your mind, you probably either don't need it or aren't experienced enough to know what you need in the first place, so be careful that you don't put your Free Software Advocate hat on over your Responsible IT Professional hat :-)
-- Jason A. Lefkowitz
Read my blog.
Then there's the case of the company who's DB is worth $1M/hour when it's down. Who are you going to sue if MySql goes down for a day? Disclaimers aside, Oracle actually has some money you can go after.
Where the development has already occurred on an OS DB, there's lots of reason to keep it there (as long as it doesn't break because of an inherent design problem/bug). Similarly with a big $$ system. In either case, the cost of the database is usually small compared to the cost of redevelopment and/or breakage.
--
Free Software: Like love, it grows best when given away.
Although I love and use MySQL (with PHP and Nix), it is DEFINATELY NOT a full features RDBMS similar to Oracle. (last I saw, it didnt support Views, sub-selects, and a buncha other stuff). Interestingly enough, we are starting to use Oracle 8i here are work, and it offers some neat things like running Java Inside the DB (which makes data-processing faster, but puts the load on the DB Server). Also Oracle instance replication is neat.. you can just have a buncha boxes running Oracle with something similar to Hot-Swapable Databases (think Load Balancing, which I know the MySQL ppl where playing with at one time).. I'm no Oracle Expert, but it is definately More robust then MySQL / Access / MiniSQL, etc, but I would only consider it for Enterprise level apps.. -- Insert whitty line here
----- The internet has given everyone the ability to have their voice heard equally as loud.. even if they shouldn't be
IMHO, Oracle is not the price/performance leader in the commercial RDBMS market, nor is it the easiest with which to develop or to administer.
Sybase has long combined relative ease of use, development, and administration with pretty good performance. Lately, though, it seems Sybase would rather be in a different market -- not sure what market, though.
For projects which require a commercial RDBMS, I now choose DB2 UDB v7.1. And, yes, I tend to run it on Linux. It falls between Sybase and Oracle in the ease of use, development, and administration department. Performance-wise, for the kind of work I do, it's at least as good. Price-wise, it's a better deal.
Culturally, I rank Oracle alongside Microsoft. No, thanks!
PostgreSQL is under active and aggressive development, which is bringing major new features, such as outer joins, to their product. Version 7.1 should be out by mid-March. For small and medium projects with limited budgets, it's almost ideal. (I'd like to see stored procedures which can return results sets.)
There are many smaller companies and organizations who can benefit from web-based applications, but who have limited budgets. All of the commercial RDBMS products have fairly hefty licensing for Internet connectivity. That can price many of these organizations right off of the web (or force them to violate the license agreement, exposing them to costly litigation). PostgreSQL (and MySQL, I'm sure), are a viable solution for these businesses.
When I get some time, I'm going to try implementing one of my DB2 projects in PostgreSQL 7.1, just to see how it does. I already know I have to re-write the stored procedures as queries, so there will be a labor impact. It will be an interesting experiment.
Gordon.
He that breaks a thing to find out what it is has left the path of wisdom.
-- J.R.R. Tolkien
it's quite informative (at least read the first chapter): SQL for Web Nerds by Philip Greenspun of Philip and Alex's Guide to Web Publishing fame.
Seriously, go read it. It's an afternoon well spent, in that hopefully after reading it you'll understand why comparing MySQL to Oracle is comedic. You have to understand your tools before you pick which one to use (MySQL is for your MP3 id3 tags, Oracle is for your hospital's data storage or for your credit card company's data storage.)
--
News for geeks in Austin: www.geekaustin.org
News for Geeks in Austin, TX
North Carolina is entrusting their medicare system to some dude writing one page porn sites, are they?
What do you pay for? Well, mostly you're paying to see your pillow more often, or at least that's what the vendor is trying to sell you -- peace of mind.
:-).
I have (lately -- SQL 7, not just SQL 6.5) seen MS SQL databases that buy the farm every night and get rebooted, and this is referred to as "more stable than before". I've watched MySQL crumble under heavy concurrent read/write loads on large tables. I still haven't figured out an easy way to carve up PostgreSQL backend storage so it scales more like Oracle. And I've thus far managed to make every (yes, EVERY) database I've ever used core dump. I'm working on DB2 at the moment; apparently it runs across x86 Linux clusters, which of course is something I'd like to crash. I got Oracle 8i to crash (finally!) earlier this summer. That was unusually difficult; Sybase and Informix were relatively easier. So, when you go the commercial route, you're paying for features, branding, and a lot of stuff you already get from the free RDBMS packages. Plus, more people tend to know about the bugs (every last RDBMS I have seen has at least some bugs).
BUT when you get right down to it, even Postgresql (my favorite free database) simply does not have the application support or PROVEN OLTP scalability of something like Oracle. Today someone batted around the idea that "DB2 is okay up to around 10 or 15TB, but I talked to some banks and oil companies, and they say that beyond that, it just falls apart". It may be the case that Oracle scales a little better than DB2, but up until recently, the top 10 (size-wise) OLTP databases on earth were running NCR Teradata, IBM DB2, and I think there may have been one Oracle customer in there. UPS's database, the largest active OLTP database, was only around 12TB last time I looked.
12TB of live data is a HUGE amount. EMC's top-of-the-line Symmetrix, a piece of equipment costing a cool $3.2M, just barely fits 12TB if you're running everything mirrored. God forbid you blow out the cache on that monster. And of course there's the question of backups...
Basically, if you're tied into a vendor's specs (eg. "We only support MS-SQL here at Affymetrix" or "ArsDigita only supports Oracle as our ACS backend") then you don't have to choose, just get out the wallet and bend over. No lube for you!
Otherwise, in my neither limited nor hugely abundant experience, MySQL and Postgres work pretty damn well for read-only data marts and low- to mid-volume read-write backends.
Beyond that -- if MySQL can't handle your OLAP needs, and Postgresql can't handle your OLTP needs -- then maybe you have to look at Oracle or DB2 or whatever. But by then I strongly suspect you'll know why they charge so %$@#ing much
The differences are fairly marginal, but if you need them, you need them, and that's the end of it.
Remember that what's inside of you doesn't matter because nobody can see it.
Like it or not, Oracle is worth every penny, assuming you can afford it. If you, or your client, cannot afford the (admittedly) big bucks for an Oracle licencse, there are plenty of free (and Free) solutions available.
What do you get with a 10-30K (or, in my experience, a 50-80K) license? You get Oracle's years of development and expertise in managaging some of the biggest and baddest installations. Think 500-1000 gigabyte DBs in constant use for 15 years.
You get a whole bucketful of trained and talented DBAs (who are worth every damn penny of there high 5- and 6-figure incomes) who grok Oracle syntax.
You get Oracle Corporation's culture (at least, I hope it's still the culture). At Oracle, the technologists create cool stuff and pay the marketing department to find or create a market for said cool stuff, rather than marketing dictating what technologies are created. Thus, when you visit Oracle, you find 3 or 4 products that do nearly similar things, or overlapping technologies. (I think this is a good thing -- others may disagree)
Finally, you get name recognition. Don't laugh this off -- it's worth a lot of money to some people. If I say "Let's do it in Oracle", people respond, "Yeah, that would be best." If I say "Let's do it in PostgreSQL", I hear "Huh?".
It boils down to: If the money is there, spend the money on Oracle, and advertise that fact. If the money isn't there, use PostgreSQL, and just say you have a RDBMS back-end.
(Oh, MySQL isn't a RDBMS in my humble opinion. It's a fine solution for certain problems, but don't confuse a SQL interface to a filesystem with a true ACID-compliant RDBMS. Future versions of MySQL promise to alter this perception, which may change the dynamics.)
Potato chips are a by-yourself food.
There are a number of reasons why you would pick any particular RDBMS, but I have seen three which come up most often and are of the most importance.
Support
This is a big one. And an enterprise scale RDBMS needs a lot of support. Ever wonder why some Oracle DBA's make more than skilled developers? Administration of a large database is not trivial, and these databases often run the core of a corporation. Mission critical to the extreme is common for these systems (Hence the reason Oracle can charge millions for a large Oracle Applications ERP installation)
Performace
These big databases are built to handle huge data sets. Oracle supports all kinds of direct interfaces to read/write directly from a SCSI/Fibre channel RAID setup. A number of the Oracle extensions, or custom application also have to be extremely fast given the amount of data they are working. PL/SQL and embeded SQL, OCI, etc, are all tools to allow maximum performace with large heaps of tables and rows.
Add-ons
And by this I mean extra pieces of functionality or applications that are only provided with a specific RDBMS. Oracle Applications, which is a popular ERP package, requires an Oracle database. Likewise, PL/SQL is very popular, but only available for Oracle. Add in all the other niceities that Oracle ships with 8i, and you can see why the entire package is pretty impressive.
So, in the majority of situation a smaller opensource RDBMS like postgreSQL or mSQL will probably work just fine.
If you are n enterprise customer which needs extreme scalability and performace, You would be hard pressed to go with anything other than Oracle or IBM.
This all sounds simple enough, but producing a db that actually implements the ACID properties reliably is very, very hard. So hard that people tend to charge tens of thousands of dollars for the result.
Of course, in many circumstances you don't actually need a fully ACID-compliant db. In general, if your transactions don't involve strangers' money, and if data can be cross-checked and reacquired from external sources in the event of a problem, you can get by with a cheaper, non-ACID db server. But using a non-ACID db where you really need ACID-level reliability is asking for big-time trouble.
--
When all you have is a hammer, everything looks like a skull.
In particular, built-in enforcement of data constraints, triggers on data modification, and stored procedures are HUGE wins for many web sites. Using these correctly can move tremendous amounts of complication out of your PHP or ASP code and into the database where they can be more easily tracked and managed.
The lack of transactions in MySQL is often commented upon, and its very important. A complex set of related changes can fail in the middle for many reasons, leaving your data in an unknown or even a completely corrupted state.
Is it worth all of that to pay Oracle's fees? For a commercial web site, where dollars are at stake for every bit of downtime or lost data, then the answer is absolutely yes.
If, on the other hand, you're working on something that uses the database as structured storage rather than a full-fledged DBMS, and especially if you don't mind babysitting it from time to time, the cheap or free options are probably OK.
Anyone who has ever been a DB developer (such as myself) or a DBA knows tha thte vast majority of enterprise-class databases (Oracle, DB2) are NOT used for web sites. Web sites are cute, but rarely are they mission critical, and rarely do they hold vast amounts of information. Think large systems. Cam you imagine, say, Visa or Matercard running MySQL to handle thousands of transactions a second? Or how about truly large databases? I recently set up North Carolina's Mediare system on Oracle. That started out as a 3 TB database, and will grow steadily for the forseeable future. Has MySQL ever been tested against something of that size? Even the largest web sites do NOT generally compare to other entreprise-class projects, in either size, ort he critcality of data.