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

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

[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

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

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