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 ?

[Anonymous Coward]

Ever try to store an array of strings?

Ever try normalizing your schema? Even learning 1NF would help you understand everything that's wrong with that statement.

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

Probably Not

[Greyfox]

But most shops don't need something as powerful as Oracle. By the time they get done slapping a front end with non-optimized spring and hibernate queries on top of oracle, they may as well just be storing their entire database in one big XML flat file. A while back I ran across a developer who was trying to join two tables manually using hibernate. Around 40000 records his application would run out of memory and crash half an hour later. The SQL join I wrote to test it handled at least 1.5 million records and ran in under 10 seconds (And this was on a Postgres database.)

So just because your shop is running Oracle, doesn't mean you can hire chimpanzees to write your font end code. Optimize your database design and queries and you can go a long way before you need the power of a commercial database system. Don't, and even the most advanced commercial database on the planet won't make your app suck any less.

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:The sorts of things you get

[greg1104]

There are a some ways to force a query plan onto Postgres that works effectively as hints. See my Hinting At PostgreSQL. It's also possible to overide how Postgres runs selectivity functions to get different results. That mechanism is powerful enough that you can do almost everything possible with hints and then some. The problem is that it's too difficult for most to develop their own statistics model just to fix a broken query. When the alternative is sucking on everything Oracle makes hard, I can't understand why people aren't willing to do this the right way sometimes.

Re:The sorts of things you get

[greg1104]

It doesn't have a slick UI, but you can do the same thing as Flashback on Postgres. You take a base backup of the database and regularly save write-ahead log files. When you need old data, you have to spin up a new database instance, ask it to replay to that point, and then get the data into the original instance. It won't win any design awards, but I recover lost data with this technique all the time.

Re:The sorts of things you get

[MacDork]

Off the top of my head, the sort of thing you don't get with Oracle:
select * from table limit 10 offset 20;
source code
free

I recently benchmarked postgres 9.2.4 on a Dell PowerEdge at Rackspace with a four disk raid 10, a two disk raid 1 for the WAL logs, and 48GB of RAM. It's good up to around 14000 transactions per second until you exceed what fits into RAM. Then it drops off to around 2000. That was the select benchmark with no writes involved.

grahamsaa, if you really want to know what postgres can do, I suggest you install it and run some benchmarks to find out for yourself. You can find all the info you need to do this in Postgresql 9.0 High Performance It won't cost you anything to do this and if you decide it can't handle your workload, then you can always go purchase Oracle.

Imo

[Charliemopps]

In my opinion:
Oracle is easier to hire for. A lot of "reporting people" know Oracle. If they had half a brain in their head they could write SQL for any DB... but if they had half a brain in their head you'd have to pay them more.

Oracles support is... worse than anything. We just stopped calling. It's better to live with the bugs than waste man hours on that cunt licking whore Oracle calls support. I'd rather traverse the 7 layers of hell in a thong than ever talk to Oracle support about anything ever again.

Oracle is Satan. They will fuck you in the most evil way imaginable. Whatever alternative you think will get you away from them, half way through the migration project oracle will buy the alternative company out. If torturing puppies were profitable, Oracle would have a puppy torturing product as a SASS. In fact, if torturing puppies just made the product slight less helpful to you, they'd probably do it as well... because their favorite pastime is making their product of less value to you.

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

Re:The sorts of things you get

[magman]

Oracle database version 12c does limit, offset and these things: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#SQLRF55636

Re: What's keeping you from switching?

[maxwell demon]

So he should switch to Oracle because Oracle might have backdoors? Are you hoping for extra protection against data loss by having the NSA maintaining an extra backup?

Re:MongoDB--run away

[macson_g]

Mongo is not worth the pixels it's been written on. I've been there, I've seen that, I don't wish my worst enemies to go there again.
You can store any arbitrary data, index it in any way, and run any language you like server-side with Postgres. And all this with full ACID, reliability, deterministic resource consumption and very decent performance.
MongoDB is a toy in comparison.

Re:The sorts of things you get

[greg1104]

My employer has been using AIX for stability reasons for a long time (since the very early 90s). At the moment neither MySQL nor Postgres guarantee AIX ODBC driver support. Only DB2 (obviously), Oracle and Sybase (IIRC) do that so there you are, another reason on to keep dealing with these companies

If you took any reasonable fraction of your database budget and pushed it toward a PostgreSQL support company, one of us would be happy to support an AIX ODBC driver. The options aren't just free and fully commercial. Smart companies help fund the features they need in order to enable a migration from the commercial databases to a free one.