Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

We are sorry to see you leave - Beta is different and we value the time you took to try it out. Before you decide to go, please take a look at some value-adds for Beta and learn more about it. Thank you for reading Slashdot, and for making the site better!

Ask Slashdot: Is Postgres On Par With Oracle?

timothy posted about a year ago | from the you-must-answer-in-the-form-of-a-satirical-query dept.

Databases 372

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

Sorry! There are no comments related to the filter you selected.

What's keeping you from switching? (5, Insightful)

Anonymous Coward | about a year ago | (#44266789)

Stupid fucking managers

Re: What's keeping you from switching? (0)

jmhobrien (2750125) | about a year ago | (#44266855)

Switching? Why would I want to switch to Oracle?

Re: What's keeping you from switching? (2, Insightful)

gmuslera (3436) | about a year ago | (#44266979)

Backdoors? Want one in the very place where you hold all your critical data? Even if they have good will (we are speaking about Oracle, so no hope on that) the government could eventually ask them to add some "extra functionality".

Re: What's keeping you from switching? (-1, Offtopic)

binarylarry (1338699) | about a year ago | (#44266859)


I promise you slashfilter it's not ascii art

Re: What's keeping you from switching? (4, Insightful)

KitFox (712780) | about a year ago | (#44266903)

Append "with no technology knowledge who met salespeople." and you're set.

Stupid fucking developers? (0, Flamebait)

mveloso (325617) | about a year ago | (#44267023)

Maybe it's dumb-ass developers like this one who jerk off with technology instead of writing things that customers actually want?

"thousands of queries per second"? (1, Funny)

Anonymous Coward | about a year ago | (#44266795)

Is this a gay porno site or what?

Re:"thousands of queries per second"? (-1, Flamebait)

rudy_wayne (414635) | about a year ago | (#44266825)

Is this a gay porno site or what?

Most likely.

what keeps us from switching ? (5, Informative)

KernelMuncher (989766) | about a year ago | (#44266801)

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.

MongoDB--run away (5, Insightful)

Anonymous Coward | about a year ago | (#44266853)

MongoDB, run away, run away quickly if you need anything close to ACID or XA.

Re:MongoDB--run away (1)

Maudib (223520) | about a year ago | (#44266935)

Mongo is ACID compliant at the document level.

Its not the right choice for everything, but defaulting to "run away" is just nutters.

Re:MongoDB--run away (5, Funny)

Billly Gates (198444) | about a year ago | (#44267105)

"Mongo is ACID compliant at the document level."

But not where it counts [] .

Re:what keeps us from switching ? (1)

Anonymous Coward | about a year ago | (#44267011)

I worked for a place once with a PL/SQL codebase that was near 20 million lines. It was a nightmare.

Re:what keeps us from switching ? (4, Insightful)

Eravnrekaree (467752) | about a year ago | (#44267063)

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.

Re:what keeps us from switching ? (-1)

WaywardGeek (1480513) | about a year ago | (#44267135)

"Better than SQL" ?!? That's like "Better than a root canal."

Not that new fangled NosSql will save the day anytime soon, but crap... did real people decide that SQL was a rational way to store data? Really? Were they zombies? Ever try to store an array of strings? Better to store it as one field and parse it in code!

Re:what keeps us from switching ? (4, Informative)

Anonymous Coward | about a year ago | (#44267149)

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 ? (5, Informative)

aztracker1 (702135) | about a year ago | (#44267259)

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 ? (5, Informative)

Craig Ringer (302899) | about a year ago | (#44267499)

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:what keeps us from switching ? (5, Funny)

Darinbob (1142669) | about a year ago | (#44267101)

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 ? (-1)

Anonymous Coward | about a year ago | (#44267215)

How? Is he on after Who?


Re:what keeps us from switching ? (2)

Craig Ringer (302899) | about a year ago | (#44267393)

Yeah! And while we're at it we can use Java EE 6, which makes it super-easy to write apps that'll run on any of the portable Java application server runtimes!

People who think SQL is really a meangful standard haven't used more than one SQL RDBMS. Even basic read-only querying and DML is in practice only marginally standard. For example, Oracle doesn't support multiple VALUES lists, it has its own funky syntax for multi-valued insert, which is one of the more basic things around.

Re:what keeps us from switching ? (1)

dhasenan (758719) | about a year ago | (#44267503)

I think you mean HQL [] , not SQL.

Re:what keeps us from switching ? (2)

Craig Ringer (302899) | about a year ago | (#44267385)

2ndQuadrant, who I work for, have some PL/SQL conversion and compatibility tools in the works and are interested in hearing from more people with large PL/SQL codebases.

Support contracts (1)

Anonymous Coward | about a year ago | (#44266817)

It is common for application support by vendors to only apply when specific database versions are used. If that doesn't include postgres, you're usually crazy to use postgres.

previous life (3, Interesting)

Anonymous Coward | about a year ago | (#44266875)

my previous employer had a similar decision to make when they were restructuring the company. the powers that be decided to pay Oracle big $$ just because of name recognition ... and for the off chance that it would make the company a more appealing acquisition candidate.

imo, if your enterprise is optimized for postgres, you'd be crazy to switch. rearchitecting would be a son-of-a-bitch.

Look at Enterprise DB (1)

Anonymous Coward | about a year ago | (#44266889)

Is not open source, but is a good alternative for testing postgres as first step with a high % of oracle compatibility and later you should probably migrate to the open source version that is great, sorry mysql guys but I love the complex sql querys than I'm able to create at postgres without temporary tables and the engine mess... (personal opinion).

Re:Look at Enterprise DB (2)

Tough Love (215404) | about a year ago | (#44267455)

Enterprise DB is basically PostgreSQL

The sorts of things you get (5, Interesting)

jbolden (176878) | about a year ago | (#44266895)

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 (3, Insightful)

Nerdfest (867930) | about a year ago | (#44266919)

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.

Re:The sorts of things you get (0)

Anonymous Coward | about a year ago | (#44267339)

The CFO likes Oracle Financials (and probably hentai porn) is another...

Re:The sorts of things you get (5, Informative)

Anonymous Coward | about a year ago | (#44266943)

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 (0, Redundant)

Anonymous Coward | about a year ago | (#44267079)

PostgreSQL 9.2 added index only scans.

Re:The sorts of things you get (5, Informative)

kuhneng (241514) | about a year ago | (#44267095)

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 (1)

WaywardGeek (1480513) | about a year ago | (#44267155)

I'd mod you up if I had mod points... I'm no fan of SQL, but I likely will use postgres on my next project where I have the freedom to choose.

Re:The sorts of things you get (4, Interesting)

hibiki_r (649814) | about a year ago | (#44267163)

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 (4, Informative)

greg1104 (461138) | about a year ago | (#44267491)

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 (0)

Anonymous Coward | about a year ago | (#44267167)

This nice thing is that if you're doing a ton of OLAP stuff you can always ETL your data out to something like InfoBright or Vertica.

IMO the biggest reason to stay on Oracle RAC & good partitioning. It appears, however, with 9.2 and writable fdw's, that that's Postgres's next target. Federated servers are possible now.

Re:The sorts of things you get (5, Informative)

Anonymous Coward | about a year ago | (#44267187)

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

Re:The sorts of things you get (0)

Anonymous Coward | about a year ago | (#44267209)

if the requirement is really big enough - but i struggle to imagine many big monolithic requirements that wouldn't be better subdivided anyway.

when it's my money it's Postgres - i am sure others would agree.

Re:The sorts of things you get (4, Interesting)

denmarkw00t (892627) | about a year ago | (#44267243)

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 (3, Informative)

greg1104 (461138) | about a year ago | (#44267511)

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 (1)

aztracker1 (702135) | about a year ago | (#44267295)

I was going to make a few of the same points... my biggest issue with Oracle, is it isn't friendly to a dev-ops environment, where development overlaps with operations. In those types of environments, imho PostgreSQL, MongoDB and MS-SQL win over most of the rest. MySQL has always been so inconsistent that migrating apps to/from it have been problematic (same is often true for MS-SQL). If you don't have (a couple) full-time Oracle DBAs in house, then you are best avoiding it as much as humanly possible.

On the flip side, if you need *REALLY* big data storage in a transactional+relational database, you aren't going to see much in the way of competition here. Most will move to no-sql solutions for those instances though. ymmv.

It Depends (5, Insightful)

djbckr (673156) | about a year ago | (#44266909)

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

READ THE MANUAL FFS (2, Insightful)

l0ungeb0y (442022) | about a year ago | (#44266915)

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.


Maudib (223520) | about a year ago | (#44266945)

Yes. Great developers use lots of...stored procedures?!?!?!?!

What fucking planet are you from?


jellomizer (103300) | about a year ago | (#44267037)

One where you can change your logic faster.

Re:READ THE MANUAL FFS (4, Insightful)

Hairy1 (180056) | about a year ago | (#44267051)

Planet Oracle I believe. It is exactly this condesending attitude which we can do without. It is the same propoganda that the business rules should be in the DB so they are protected from the idiot know nothing developers. It is a claim in essenence that a DBA is superior and developers incompetent. There is such a thing as a business layer. The business rules can be enforced there. I know the orthodox thinking, but have never seen a good reason to believe it. I don't know how much time has been wasted on projects with developers fighting DBAs just to get their job done. Yes - stored procs do potentially have a role. In my experience it is a very limited role.

Re:READ THE MANUAL FFS (5, Insightful)

casings (257363) | about a year ago | (#44267129)

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.


t0y (700664) | about a year ago | (#44267407)

And because of the office politics stored procedures are good? Man... the DBA you describe is a developer. It's just that instead of using .net or java he's trapped in PL/SQL or T-SQL nightmare.

Re:READ THE MANUAL FFS (3, Insightful)

jythie (914043) | about a year ago | (#44267423)

If you are fighting your DBAs to get the job done, your problem is political not technological. A good DBA and a clear separation of domains can make a developer's life easier and let them focus on the parts they are building.


Anonymous Coward | about a year ago | (#44267083)

Planet Oracle (yes, the one Larry purchased and colonized). It's actually a great place.
Everything that possibly could be is basically stored procedures, PL/SQL, compiled and executed in the RDBMS. Performance is great, security is great (as long as you're not a total idiot, or abusing dynamic SQL), configuration management is great.
The only stuff that isn't stored in the database is the front end presentation user interface layer.
This is how great enterprise software is architected.


mooingyak (720677) | about a year ago | (#44267227)

Planet Oracle (yes, the one Larry purchased and colonized). It's actually a great place.
Everything that possibly could be is basically stored procedures, PL/SQL, compiled and executed in the RDBMS. Performance is great, security is great (as long as you're not a total idiot, or abusing dynamic SQL), configuration management is great.
The only stuff that isn't stored in the database is the front end presentation user interface layer.
This is how great enterprise software is architected.

That was mean. I'm going to have nightmares about this now.


Anonymous Coward | about a year ago | (#44267127)

Leave him alone! That's all he knows. Do you expect him to quit?


l0ungeb0y (442022) | about a year ago | (#44267345)

Well actually, to be blunt about it, I am from Earth.
But an alternate reality where we might use stored procedures with triggers to check shit and validate and roll back.
That is my most common usage scenario.

Yes, you can put that all in the middle tier -- but that can all be broken in the middle tier.


Anonymous Coward | about a year ago | (#44266981)

Business logic in stored procedures? I, for one, am glad I don't have to deal with that sort of nightmare.


Mitchell314 (1576581) | about a year ago | (#44267067)

Wimp. I write my system software in stored procedures. Except of using char* I just use a table column where every value is an ascii ubyte. :P


Anonymous Coward | about a year ago | (#44267099)

Been there, done that.

In 2010 I argued for hours over the course of several days about the right place to put business logic for a Java Web App. My claim was business logic should be in the Java code and well structured data should be in the database. It was a JSF / Hibernate app, if it matters, with a smattering of IBATIS and custom JDBC thrown in. (It was an 8 year old app at the time.) The DBAs were insisting that we write stored procs for everything - because somehow it was easier to change them later. (What?) And, if the schema changed, it was easier to update a Stored Proc with hard coded column names and relations, than it would be to alter a Hibernate mapping and recompile. (Double What??)

We "compromised" because the architect was a giant weenie which was worse than one way or the other. Some logic was in code, some was in the DB which made debugging a finger pointing exercise. All of it was a cluster fuck and unmaintainable. I left the project and company shortly after all this went down.


Ice Station Zebra (18124) | about a year ago | (#44267211)

We have one of "those" architects at work too. I just ignore him, more than likely he has never really coded an application and is only talking out his ass. Love iBatis/MyBatis. Way better than stored procedures and hibernate stuff.

What is stopping a migration? Oracle-isms (1)

Anonymous Coward | about a year ago | (#44266921)

Many legacy applications built using Oracle use one or more Oracle extensions to the SQL standard. Eliminating those Oracle-isms would take developer resources that (most) companies prefer to spend on enhancing the application (you get credit for new features, not old features being rewritten; why do you think there is still so much Cobol code still in use?)

Why Oracle? (5, Insightful)

Hairy1 (180056) | about a year ago | (#44266925)

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:Why Oracle? (4, Insightful)

glenebob (414078) | about a year ago | (#44267021)

I wrote against Postgres for years and avoided stored procedures as much as possible for exactly the reason you describe; to avoid lock in. I never understood why so many people are perfectly happy to dive right into lockedinville. Avoiding lock in always served me and my company well.

Feature differences (2)

mysidia (191772) | about a year ago | (#44266957)

There are features Oracle provides that have no PostgreSQL equivalent.

  • Price -- it costs a lot of money. For many governmental entities, this is a huge advantage -- as they are given a budget, and they need to spend it, otherwise their budget will get reduced -- if its an excuse to spend money, based on claims of productivity, they will often deny requests to use OSS, and mandate the use of Oracle, based on its productivity-improving and more-reliable qualities that some slick salesman persuaded them of, after taking them out for steak at a 5-star restaurant somewhere, or whatever. Also; I hear plenty of government workers saying Management has a no open source software policy; for security reasons, the more money spent on the product the better, as closed source code is deemed to be more secure... For me, and business i'm involved with, this is a huge negative for Oracle, and a reason I almost always pick Postgresql; yes, Oracle delivers more, BUT in many cases you pay Oracle for every extra cent of additional incremental value Oracle delivers over Postgres, and maybe 300% more.
  • RAS features -- such as clustering Oracle RAC []
  • Development productivity tools such as - Pro*C []
  • SQL Language features where Oracle's implementation is superior -- such as BLOBs. Postgres manages these poorly, for example, you cannot reliably pg_dump blobs - if your application is BLOB happy (e.g. Sharepoint-like), then Postgres is not very suitable.
  • SQL Language features that have no PostgreSQL analog -- such as CONNECT BY [] clauses, Java [] class based schema and table mappings; module languages; XML types; default value funciton parameters; organize stored procedure objects using packages; .

Re:Feature differences (0)

Anonymous Coward | about a year ago | (#44267085)

if your application is BLOB happy (e.g. Sharepoint-like), then Postgres is not very suitable.

And neither is Oracle. All DBMS vendors say they can handle BLOBSs, and they all SUCK at it. NEVER store binary data like sound or imagery in DB rows. Just don't.

Re:Feature differences (0)

Anonymous Coward | about a year ago | (#44267411)

Agree re: BLOBs.

Postgres' large-object support, however, is actually pretty nice -- block-addressable, and can be dumped by pg_dump easily if you use the proper dump format and options. A reference to a large object is stored in the database row; the objects themselves exist elsewhere and have a set of file-like access methods.

Not portable, but sometimes it's quite a bit nicer than storing those objects on the local filesystem or on a NAS via a protocol like NFS or CIFS.

Price, it's not just for Governmets anymore (1)

frovingslosh (582462) | about a year ago | (#44267125)

You're omitting a key concept about price that I've seen many fools around me exploit. If they are managing a big project that has software costs of millions a year then they must be very important. If I'm managing a project that only costs the company a hundred thousand or so in software costs a year then I must be less important. It really doesn't seem to matter if my project does more or is actually more critical to the operation of the company, those are concepts that are too abstract for upper management to understand. So if you want your department to look important then waste as much money as you can.

Re:Feature differences (0)

Anonymous Coward | about a year ago | (#44267277)

Don't forget synonyms. I dislike using them but there are the occasions where they can make your day.

To be fair though, there are features in Postgresql provides that I wish Oracle had.
Comment on any flippin object in the database (including the database) is ' ... '.
More extensive datatypes out of the box (where's the boolean datatype Larry?).
Transactional DDL.
A more sane (in my opinion) implementation of schemas. Why Oracle conflates users with schemas is beyond me.
Schema level privileges.

Re:Feature differences (2)

eggyknap (1589525) | about a year ago | (#44267333)

Everyone should love transactional DDL.

Re:Feature differences (1)

eggyknap (1589525) | about a year ago | (#44267331)

I'm willing to be corrected here, but I understand CONNECT BY was Oracle's way of making recursive queries before the SQL standard invented them. Oracle and PostgreSQL (and presumably others) support standard recursion now. Postgres also has default function parameters, and extensions which sorta kinda but not really approximate Oracle's packages.

Re:Feature differences (5, Informative)

Craig Ringer (302899) | about a year ago | (#44267457)

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.

Oracle is Best DB! (0)

Anonymous Coward | about a year ago | (#44266973)

Oracle is big and great, and most of all expens--worth your money! Just remember to hire a proper DBA to manage all the thousands of patches you will get from Oracle. Other than that it's perfect for all uses.

-- Oracle DBA.

Liability (3, Insightful)

InfiniteZero (587028) | about a year ago | (#44266997)

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.

Re:Liability (2)

glenebob (414078) | about a year ago | (#44267201)

And the story of shifting liability is such a sham. Oracle isn't liable for anything. If you install Oracle and lose a bunch of data, you're still liable for it. And even if Oracle was liable, is that going to get your data back? No.

Re:Liability (1)

Anonymous Coward | about a year ago | (#44267349)

If you install Oracle and lose a bunch of data, you're still liable for it.

No, your company is liable for it. According to your boss (the only person whose opinion matters) you are not, because instead of trying to solve a problem, you were smart and said let's buy a solution. So then it doesn't matter if it works or not because you have someone to blame.

And even if Oracle was liable, is that going to get your data back?

Who cares? Not being the guy who gets the credit/blame was the goal, not some somethingsomething question about datawhatever. WTF are you talking about? Just restore the last tape. You think I want to work late? Just get the little tape guy on it and tell everyone we'll be back up and running on Monday.

Re:Liability (1)

glenebob (414078) | about a year ago | (#44267437)

How is any of that different with Postgres? Either way, you restore from back up and away you go. In neither case did blaming a software company get you out of trouble. That's why I say, it's a sham. You pay big money for something that, at the end of the day, isn't really worth a penny.

Re:Liability (0)

Anonymous Coward | about a year ago | (#44267325)

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.

+Millions funny

Multi cpu queries (1)

Billly Gates (198444) | about a year ago | (#44267031)

Oracle has a patent that limits a query to only use 1 cpu. So the answer is a big NO.

Aren't software patents great?

Probably Not (4, Informative)

Greyfox (87712) | about a year ago | (#44267047)

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:Probably Not - Hibernate (1)

Hairy1 (180056) | about a year ago | (#44267091)

Hibernate is a great tool in many ways, but it is far too easy to allow the actual details of what is going on under the covers to be hidden. As the parent suggests using Hiberate in a naieve way can be very dangerous. Complex queries are usually best handled by native SQL.

Does Postgres do online backup? (1)

alen (225700) | about a year ago | (#44267069)

Like you install netbackup on it or one of the other enterprise products And it backs up directly to your tape robot via the San. No shutting down the database. No copying to a snapshot. Online backup of the production db straight to tape. Full, diff and log backups straight to tape.

Does it support online restore?
Is it certified for VMware and hyper-v?
If you have a problem can you open a support case right away?

Re:Does Postgres do online backup? (1)

Anonymous Coward | about a year ago | (#44267115)

Yes, to your first set of backup questions:

VM, don't know. Depends on the context you're referring to.

Yes, to your support question:

Re:Does Postgres do online backup? (1)

eggyknap (1589525) | about a year ago | (#44267341)

VMWare has invested quite a bit of work into PostgreSQL, and employs a few big names in the community. That said, I know nothing about it myself, and can't answer your question regarding it :)

Postgres on par with Oracle? Nope. (0)

Anonymous Coward | about a year ago | (#44267117)

Postgres is bait and switch database. For the really needed features you need to buy a commercial version. What do you think why is the guy who works for EnterpriseDB the head of the steering committee? Incidentally, EnterpriseDB has hints, session trace and the event interface, all the things that the open source version so desperately misses.
Postgres has no parallelism worth mentioning. Nada, zilch, zero. Also, their partitioning is useless. There are no global indexes and the optimizer makes very strange decisions when partitions are involved. The answer to the question whether Postgres is on the same level as Oracle is a resounding no. Is Postgres good enough? I am a DBA, not a business user, but I would not recommend it for any company larger than ma's and pa's corner bar and grill. If you are looking for the alternatives to Oracle, there are several worth looking into, depending on the database type: Vertica, Greenplum, MS SQL and DB2. I would look hard into DB2, version 10 has number of Oracle compatibility features and is much cheaper than Oracle. IBM is going aggressively after Oracle's market share. Trust me, you don't want Postgres. There is a reason why people are not using it, although it has been around forever.

One missing Oracle feature in PostgreSQL (3, Insightful)

manu0601 (2221348) | about a year ago | (#44267119)

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

Re:One missing Oracle feature in PostgreSQL (1)

aztracker1 (702135) | about a year ago | (#44267359)

Have you actually looked at EnterpriseDB's executive list [] or board of directors [] ? There's just as much CYA power there as there is with Oracle, probably more.

mark-up pricing (1)

Pharoah_69 (2866937) | about a year ago | (#44267121)

Personally, I think it all comes down to accountability; that and "in-house" buying and mark-up. With open source software, there isn't any "mark-up" pricing for businesses.

Unpronouncable (-1)

Anonymous Coward | about a year ago | (#44267133)

farts are smelly when they come from American anuses. only sturdy Soviet anuses produce pleasing fartolocules.

Re:Unpronouncable (0)

Anonymous Coward | about a year ago | (#44267335)

Ja! Das ist wahr, mein kapellmeister!

Ich bin ein anus Amerski: {}
Ich bin ein anus Sovski: o

Oracle - only if you have good DBAs (1)

Ice Station Zebra (18124) | about a year ago | (#44267197)

If your DBAs believe in hands off the database and your tables have a lot a churn, you are going to be in for some pain.

Cost of switching (1)

fsterman (519061) | about a year ago | (#44267207)

Seriously, the cost of switching is what keeps everyone locked into their databases. Unless an alternative DB can provide a seamless drop-in replacement, the man-hours spent on re-engineering and the downtime vastly outweigh the performance improvements you might get out of it.

Re:Cost of switching (1)

aztracker1 (702135) | about a year ago | (#44267381)

Around 1999 I was involved in a rewrite to move *to* oracle... which, at the time was the only real option for the needs of the software at the time. There were several DBAs hired in, and a few cross-trained for the project. It wasn't too bad. That said, for 99.999% of use cases Oracle is overkill. I'd much rather use PostgreSQL or MS-SQL, haven't touched DB2 in a long time, and FirebirdSQL is passable as well for some uses.

My experiance with Oracle. (1)

PenguinJeff (1248208) | about a year ago | (#44267237)

Installing and testing.
I installed Oracle a few times and played with it. I didn't put a proper shutdown method in the shutdown scripts and there was also a mishap while testing the UPS. Both times I was unable to recover the Oracle database and had to reinstall. I had never had that much trouble with mysql. I installed it for someone else that had an Oracle expert and they where able to recover when we had a similar mishap there but all the googling in the world is nearly useless without a properly trained Oracle administrator. I'd suggest sticking with a database where the documentation is fully available and many many more people that can help you. There are easy free forums for mysql, maraiadb and postgresql.

For the most part the Oracle stuff works.. (2)

dubist (2893961) | about a year ago | (#44267301)

As much as I hate to admit it.. The Oracle stuff for the most part just works and if you have competent DBA's you don't have to worry about it. You may regret using oracle when you get the bill and sometimes it does not have the more esoteric features of the other DB's but you will be glad for its stability and its enterprise focused features in the long run.. And no one will sack you for choosing Oracle.

Lack of Benchmarks (0)

Anonymous Coward | about a year ago | (#44267317)

It think it might be expensive and that's something, but I suppose the main thing keeping from switching to Oracle or even calling them, is the lack of benchmarks. I can't get any data about whether or not we'd get anything out of it. It's almost as though the first rule of Oracle is that you don't talk about Oracle.

Re:Lack of Benchmarks (1)

greg1104 (461138) | about a year ago | (#44267443)

Oracle doesn't allow people to publish benchmarks, period. Some trickle out via the audited TPC results. Those are all systems tuned to an extreme degree, including code optimizations for that specific workload that border on cheating

As usual, "it depends" (5, Insightful)

Craig Ringer (302899) | about a year ago | (#44267377)

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.

Quick answer: (1)

jd2112 (1535857) | about a year ago | (#44267409)

No, Not even close. Oracle has a lot of enterprise-grade functionality that does not and probably never will exist in PostgreSQL.

If you actually need that functionality it will probably be worth the exorbitant prices Oracle charges for it.
Perhaps a better question is what does Oracle do that PostgreSQL doesn't and is it necessary for your business?

(Disclaimer: I am not a DBA and I go out of my way to avoid working with Oracle, however I often run in to Oracle DBAs that know less about databases in general and sometimes Oracle specifically than I do.)

differences usually do not matter (2)

sribe (304414) | about a year ago | (#44267427)

In 95% of cases (or more) as one of the first response said: "stupid fucking managers", in 5% (or less) of cases, some very very very high-end features that almost nobody actually needs. Sorry, bathroom break is over, got to get back to the movie with the wife, otherwise I'd say more ;-)

But I'll leave you with this: the postgres folks are truly experts in database, and extremely forthright. Unlike the MySQL founders, if you go and ask this same question on the postgres mailing list, you will get an honest answer, not marketing bullshit.

Also, I see now that Craig Ringer has responded above. Anything he says, believe it ;-)

locking? (0)

Anonymous Coward | about a year ago | (#44267439)

Not familiar with PostgreSQL locking but we used to find that MS SQL Server would, what's it called, escalate its locks from block to row to table, well, we thought quite easily (you could criticize our design that brought this about, doing many insertions in a single transaction over a long period, but Oracle handled the same process without problems). More recent MSSQL Servers have improved this, though. How is PostGreSQL's locking?

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?