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

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

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.

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