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?

[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?

[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?

[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?

[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?

[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?

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

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 ?

[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 ?

[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 ?

[plopez]

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

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 ?

[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 ?

[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]

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 ?

[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 ?

[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 ?

[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 ?

[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

MongoDB--run away

[Anonymous Coward]

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

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.

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

[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

[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]

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

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

[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

[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

[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

[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

[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

[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

[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

[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

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

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

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

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

[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

[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

[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

[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

[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

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

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.

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

[eggyknap]

Everyone should love transactional DDL.

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.

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.

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.

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

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.

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.

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

Re:Look at Enterprise DB

[Tough Love]

Enterprise DB is basically PostgreSQL

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.

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

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.

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.

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?

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

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

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