Slashdot Mirror
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?"
Stupid fucking managers
MongoDB, run away, run away quickly if you need anything close to ACID or XA.
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...
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.
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.
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.
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.
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.
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
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.
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.