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?

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

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 ?

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

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

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

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.

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

[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

[Anonymous Coward]

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

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

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

As usual, "it depends"

[Craig Ringer]

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

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

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

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

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

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

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

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

Re:Feature differences

[Craig Ringer]

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

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

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

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

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

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