Ask Slashdot: Is Postgres On Par With Oracle? 372
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?"
What's keeping you from switching? (Score:5, Insightful)
Stupid fucking managers
MongoDB--run away (Score:5, Insightful)
MongoDB, run away, run away quickly if you need anything close to ACID or XA.
Re: What's keeping you from switching? (Score:5, Insightful)
It Depends (Score:5, Insightful)
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...
READ THE MANUAL FFS (Score:2, Insightful)
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:The sorts of things you get (Score:4, Insightful)
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.
Why Oracle? (Score:5, Insightful)
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: What's keeping you from switching? (Score:2, Insightful)
Liability (Score:4, Insightful)
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:Why Oracle? (Score:5, Insightful)
I wrote against Postgres for years and avoided stored procedures as much as possible for exactly the reason you describe; to avoid lock in. I never understood why so many people are perfectly happy to dive right into lockedinville. Avoiding lock in always served me and my company well.
Re:READ THE MANUAL FFS (Score:5, Insightful)
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:what keeps us from switching ? (Score:5, Insightful)
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.
One missing Oracle feature in PostgreSQL (Score:4, Insightful)
I did now know about EnterpriseDB oracle compatibility for PostgreSQL, that is interesting.
However there is still a strong Oracle feature missing here, which is called CYA. It is just like using Microsoft software: even if it does not work nobody will tell you were wrong by choosing it
Re:READ THE MANUAL FFS (Score:5, Insightful)
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.
As usual, "it depends" (Score:5, Insightful)
Like most DB comparisons, it depends on the workload, non-technical business factors, and more.
Oracle has superior clustering to PostgreSQL, better native XML support, autonomous transactions, procedures that can return multiple result sets, a really solid embedded JVM for procedures, proven scaling to absurdly huge database sizes, etc.
PostgreSQL has transactional DDL, generally better standards adherence, no lock-in, streaming replias that don't cost you anything, multi-language stored procedure support, extreme extensibility, proven scaling to multi-terabyte database sizes, and probably more I take for granted and forget about.
With Pg you get a lot of choice of support provider, including "none, I can do it myself and I can always contract someone if I need help". With Oracle you get support from Oracle, or from a vendor who must comply with what Oracle wants in order to get access to the resources they depend on to offer support.
PostgreSQL has no per-cpu or per-core license fees so you can run it on a lot more hardware. You can also afford to buy a much bigger server for the money you're saving on licensing fees and upgrade it more often. This can make a huge difference; PostgreSQL's performance is generally very good, and in areas where it does fall behind Oracle you can make up for a lot by throwing bigger hardware at the job. You also don't have to face NDAs, license audits, not being able to afford to have a second off-site hot standby backup machine, being stuck on old versions because licensing new ones is just too expensive, etc.
So, really, a huge amount of it depends on the workload, business requirements, etc.
I work professionally with PostgreSQL as a member of the 2ndQuadrant team, but if I'm discussing planning with somebody I'm still quite prepared to say "I don't think PostgreSQL will do the job as well as [blah] here given the time frame and requirements". It doesn't come up much but it has, and I'd be doing them a dis-service by saying PostgreSQL's perfect for everything all the time.
I find PostgreSQL to be the safe and sensible default, but I consider alternatives or supplements to it when I run into workloads it's not ready for or not great at - like someone who has a hard business or compliance requirement for synchronous multi-master clustering, or somebody whose query pattern and data set is going to be a better fit for Greenplum than native PostgreSQL.
Re:READ THE MANUAL FFS (Score:4, Insightful)
Re:It Depends (Score:5, Insightful)
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: READ THE MANUAL FFS (Score:4, Insightful)
Re:MongoDB--run away (Score:5, Insightful)
Re: What's keeping you from switching? (Score:4, Insightful)
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? (Score:4, Insightful)
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 keeps us from switching ? (Score:5, Insightful)
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's keeping you from switching? (Score:5, Insightful)
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.