Oracle and Postgresql

On Sun, Aug 31, 2008 at 11:29:32AM -0700, M2Y wrote:

Hello,

I am a CS graduate and I have a brief idea of Postgres and Oracle.
But, I dont have an in-depth knowledge in any of them. I have a
couple of questions and

I want to compare both of them in terms of functionality,
performance, advantages and disadvantages.

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

That depends which enterprises. At the moment, people who know
Postgres are not common, which gives them advantages in negotiations.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, Aug 31, 2008 at 12:29 PM, M2Y <mailtoyahoo@gmail.com> wrote:

Hello,

I am a CS graduate and I have a brief idea of Postgres and Oracle.
But, I dont have an in-depth knowledge in any of them. I have a couple
of questions and

I want to compare both of them in terms of functionality, performance,
advantages and disadvantages.

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

I got started using PostgreSQL for a "throw away" project. One that
needed to be done fast and cheap and if it didn't work out it was ok,
because we were only gonna spend a week or so setting it up. After
setting up pgsql to handle the single project, we started building
more things for that server, and it eventually grew into the corporate
intranet server, backending other apaches behind it, handling LDAP
auth for all the major apps in the company, storing files for
different groups, especially files that needed to be on the corporate
intranet, and running postgresql in the background too.

With that kind of project, free often wins out because it lowers your
cost of ownership on a low budget project.

When you get into the multi-million dollar integration projects
involving every sub group in an organization the budget is often huge,
and the bosses want something they've heard of / seen in action and
know can handle the load. A bad db choice could sink the whole
multi-million dollar project. A $200k insurance policy (I.e. Oracle)
is a small price to pay.

I think some of it is inertia. We've always used Oracle, let's just
keep on using it. The more conservative the IT department is, the
less likely they are to take chances with new technology.

It used to be there was about an 80/20 split between what things you
could do with either postgresql or oracle, and the other 20% was
oracle only land. I think that number is dropping quickly, and we're
into the 1 or 2% club of what Oracle can do that PostgreSQL isn't fast
enough for.

The other thing that holds back PostgreSQL right now is a lack of
experienced pgsql DBAs and application developers. That will change
over time.

As more people become familiar with using and maintaining pg servers,
and the usage starts to take up, there will be a multiplying effect as
more PHBs hear the name with good things said about it.

At 2:29pm -0400 on Sun, 31 Aug 2008, Srinivas wrote:

I want to compare both of them in terms of functionality, performance,
advantages and disadvantages.

If you publish anything, watch out for the Oracle licensing no-nos.
Specifically, I believe they disallow certain comparisons. I believe
performance is one of them.

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

Many reasons, some legit, some not. I expect a Greg or two will
seriously add to and correct this list, but:

1. Oracle was "first", and has vendor lock-in momentum.
2. Oracle is still the "de facto" in terms of
speed/performance/concurrency (but that gap is closing fast)
3. Oracle has application lock-in as well. There've more than a few
threads on this list regarding which applications.
4. Oracle is company-backed, so there is ostensibly "someone to blame"
or sue if something goes wrong. Don't underestimate the managerial
"need" for blame.
5. Influencing individuals within a company may prefer it *because* it's
expensive, as it helps "justify" their higher salary.
6. Mucho better advertising to the right people. Much harder for an
open source project to fund advertising to the same level.
7. Though I don't personally buy it, I have heard others complain
loudly that there is no print-version of Postgres documentation.

There's a starting list.

Kevin

On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter <hunteke@earlham.edu> wrote:

7. Though I don't personally buy it, I have heard others complain
loudly that there is no print-version of Postgres documentation.

This one should be taken off the list. The postgresql online
reference manual is in print( volumes 1 - 3)
http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

7. Though I don't personally buy it, I have heard others complain
loudly that there is no print-version of Postgres documentation.

This one should be taken off the list. The postgresql online
reference manual is in print( volumes 1 - 3)
http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1

Printable docs is a silly reason, but that link is to the docs for 8.2.4, so the
argument is not negated by the existence of that book. A dearth of books in
general is certainly a weak point for Postgres however.

Need some more reasons why Oracle over Postgres?:

1) Oracle has in-place upgrades. I know for a fact this has prevented some
large companies from going to Postgres.

2) Oracle RAC

3) Oracle sales people and a huge marketing department

4) Oracle runs on Windows. We do too, but did not for a long time and the
perception that we don't (or don't do it well) is still out there.

5) Training, certification, and the aforementioned books. We're very slowly
gaining traction in this area.

6) We've been tainted by our lumping together with MySQL, which makes
open-source databases in general look bad when people familiar with Oracle
examine MySQL and assume Postgres is similar in attitude, community,
features, and robustness.

I'm sure EDB could expand this list a bit as well. :)

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200808311937
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAki7K7gACgkQvJuQZxSWSsgCkwCg0F3mlLiIu9gRskL8EP3+u2MZ
Da4AniP2XkQOOn++1mYM0BSOe0Urp0B9
=1zO8
-----END PGP SIGNATURE-----

Another thing that has only been mentioned obliquely is support. When an
organization selects an enterprise wide mission critical database system
they get a lot of perceived security from purchasing a world-wide 24x7
support contract from a company with the resources that Oracle can
provide. Yes, you have to write a big check but you are insuring against
a big risk. As others have stated, many of the factors that have a major
influence on the choice of a database are not technical.

Bill

M2Y wrote:

Hello,

I am a CS graduate and I have a brief idea of Postgres and Oracle.
But, I dont have an in-depth knowledge in any of them. I have a couple
of questions and

I want to compare both of them in terms of functionality, performance,
advantages and disadvantages.

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

One significant factor is scaling and clustering.

Right now, PostgreSQL has network-based master-to-slave(s) clustering
using Slony that permits slaves to operate online read-only as reporting
servers etc. It also covers failover with the use of an external
heartbeat/STONITH setup. However, it's not completely transparent to the
application and it's not seen as "part of postgresql".

There are some other options that I'm largely unfamiliar with out there,
but right now it seems that Slony is the way to go with Pg.

Oracle, by contrast, has multiple-master clustering on shared storage
(which can be iSCSI, FireWire, SAS, SCSI, or basically whatever else you
like) with a network side-channel for inter-node messaging. All nodes
can be active readers and writers in cooperation. I haven't used it and
can't speak for its performance, reliability or admin-friendliness, but
it seems to see very wide use in large systems.

I'm pretty sure they also support master->slave replication and
failover, etc.

Multiple master clustering offers scaling options other than upgrading
your DB server to add more disks/RAM/CPU, or moving reporting onto a
separate read-only slave, so it'd be very nice to have on any project
where serious growth looks likely.

That's not to say you can't build some pretty big systems with Pg (and
they certainly exist) ... but the scale-out options added by
multiple-master clustering will certainly weigh decisions between the
two database systems for some types of loads.

--
Craig Ringer

M2Y wrote:

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

Databases are a critical part of many companies' business. I work for
telecom company, and if we were to lose our databases, we'd be out of
business, period. So, "free and decent enough" are not good enough. If
you are going to bet your business on anything, you want to be as sure
as possible that "it" is reliable and that you can expect quick action
if "it" should break.

I tried to introduce PG into our business 4 years ago. Managers
listened to the benefits but went with Oracle. No one ever explained
why, but I've worked in this industry for 30 years and have a pretty
good idea. Oracle is a big company, and we have a 24/7 support contract
with them. If something goes wrong any time of the day, we can call
them and open a support issue.

PG is a great piece of work and equal in performance and features to
commercial DBMSs. You can get support contracts from several companies.
But there is no single entity that "owns" PG. CTOs/CIOs like to sleep
at night.

--
Guy Rouillier

On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote:

CTOs/CIOs like to sleep at night.

If you buy Oracle, and there's a problem, the conversation with the
CEO is that "Oracle broke." With PG, even if you have exactly the
same level of support, "that database you selected broke."

The sad reality is that choosing something with a high industry
presence makes it easier to offload blame for failures, especially
when dealing with non-technical management.

On Sunday 31 August 2008, Christophe wrote:

On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote:

CTOs/CIOs like to sleep at night.

If you buy Oracle, and there's a problem, the conversation with the
CEO is that "Oracle broke." With PG, even if you have exactly the
same level of support, "that database you selected broke."

The sad reality is that choosing something with a high industry
presence makes it easier to offload blame for failures, especially
when dealing with non-technical management.

Sadly I can second that. I've seen quite some companies go with a completely
inappropriate product, just because "they're the market leader". And
certainly there is the other side of the coin: The CTO who decides doesn't
want anyone to rock his boat. So often s/he decides to go with biggest market
presence, simply because IF something breaks it's not their fault - the
argument being "we went with the best there is, it's their fault". (maybe
it's the "best there is", but often not for the application in question).

I've got one customer who didn't care - they're running on postgresql for 8
years now and nobody noticed - hehehe. I just told the CEO "it's a SQL
database" - which satisfied his curiosity :-)
But that's certainly not the case with everyone...

Uwe

On Sun, Aug 31, 2008 at 10:44:38PM -0400, Guy Rouillier wrote:

M2Y wrote:

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

Databases are a critical part of many companies' business. I work
for telecom company, and if we were to lose our databases, we'd be
out of business, period. So, "free and decent enough" are not good
enough. If you are going to bet your business on anything, you
want to be as sure as possible that "it" is reliable and that you
can expect quick action if "it" should break.

What they want to have is a huge entity they can blame when everything
goes wrong. They're not interested in the actual response times or
even in the much more important time-to-fix because once they've
blamed Oracle, they know the responsibility is no longer on their
shoulders.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

hi, all:
I use a plproxy to execute sql like below:
CREATE OR REPLACE FUNCTION public.dmlexec(query text,op text) RETURNS
setof integer AS $$
CLUSTER 'testcluster';
RUN ON all;
$$ LANGUAGE plproxy;
create or replace function public.dmlExec(query text, op text) returns
integer as $$
declare
ret integer;
begin
execute query;
return 1;
end;
$$ language plpgsql;

when I exectue sql like 'select dmlexec('delete from table_test where
id = 2', 'test') at the same time in numerous process, the delete sql
will blocked.....
so I must select pg_cancel_backend(...).

is there a deadlock??

postgresql version: 8.3.0

thanks all:)

I agree with David's comment. The business I work for recently proposed a FOSS based solution for a client, but were told no FOSS solutions would be considered. We had a subcontractor offer a support contract for an application based on the same FOSS components, but with a support contract.

This was perfectly acceptable to our client, who now has a working solution, for which they pay annual maintenance, in return for a lack of internal liability.

For many businesses, risk avoidance is a core driver. What they require is someone else to blame if things go wrong, hence the companies making a living with contracts for Postgres support.

Cheers,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

David Fetter <david@fetter.org> 09/01/08 6:09 PM >>>

On Sun, Aug 31, 2008 at 10:44:38PM -0400, Guy Rouillier wrote:

M2Y wrote:

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

Databases are a critical part of many companies' business. I work
for telecom company, and if we were to lose our databases, we'd be
out of business, period. So, "free and decent enough" are not good
enough. If you are going to bet your business on anything, you
want to be as sure as possible that "it" is reliable and that you
can expect quick action if "it" should break.

What they want to have is a huge entity they can blame when everything
goes wrong. They're not interested in the actual response times or
even in the much more important time-to-fix because once they've
blamed Oracle, they know the responsibility is no longer on their
shoulders.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, August 31, 2008 10:44 pm, Scott Marlowe wrote:

The other thing that holds back PostgreSQL right now is a lack of
experienced pgsql DBAs and application developers. That will change
over time.

And built-in, simple to use, reliable, flexible and fast replication.
Many a Pg admin or implementer has looked on with envy at what Oracle does
"out of the box" in terms of replication alone. Yes, there are
third-party options (Slony, etc), but they're *third party*, and nowhere
near as reliable and robust as Pg itself. (thank heavens for Skytools,
btw)

This is /finally/ being addressed, although (very) belatedly. The Pg core
dev team always argued that replication was an add-on and should not form
part of the core (ie, similar nonsense excuses the MySQL team used for
"add-ons" such as triggers, etc).

Leaving this one vital issue so late has caused damage to Pg's reputation
in my view.

Come on Tom, how about talking to the Command Prompt folks (who are
?about? to release Mammoth Replicator as Open Source) and look at possibly
merging their Pg-mod replication work into Pg itself? If it's doable and
conforms to the Pg requirements, it will make up - and save - a mountain
of time. The alternative will be a few years of stabilising any new
replication code before it's considered safe to adopt in production.

Their. I've had my moan for the day, and I feel much better :-)

Cheers
Henry

On Mon, September 1, 2008 4:09 am, Craig Ringer wrote:

Right now, PostgreSQL has network-based master-to-slave(s) clustering
using Slony that permits slaves to operate online read-only as reporting
servers etc. It also covers failover with the use of an external
heartbeat/STONITH setup. However, it's not completely transparent to the
application and it's not seen as "part of postgresql".

There are some other options that I'm largely unfamiliar with out there,
but right now it seems that Slony is the way to go with Pg.

...and Skytools (as used by Skype, I believe, which is now Open Source),
which is far simpler to use and in my experience far more reliable.

Cheers
Henry

Scott Marlowe, 31.08.2008 22:44:

I think some of it is inertia. We've always used Oracle, let's just
keep on using it. The more conservative the IT department is, the
less likely they are to take chances with new technology.

It used to be there was about an 80/20 split between what things you
could do with either postgresql or oracle, and the other 20% was
oracle only land. I think that number is dropping quickly, and we're
into the 1 or 2% club of what Oracle can do that PostgreSQL isn't fast
enough for.

For me (personally) the ratio is more like 70/30, because we are making extensive usage of Oracle's windowing functions (and ability to easily deal with hierarchical data using CONNECT BY).

The lack of windowing functions is a bit frustrating as I'm otherwise a big Postgres fan!

Regards
Thomas

Henry wrote:

On Sun, August 31, 2008 10:44 pm, Scott Marlowe wrote:

The other thing that holds back PostgreSQL right now is a lack of
experienced pgsql DBAs and application developers. That will change
over time.

And built-in, simple to use, reliable, flexible and fast replication.
Many a Pg admin or implementer has looked on with envy at what Oracle does
"out of the box" in terms of replication alone. Yes, there are

Well actually it appears a lot of DBAs are not satisfied with Oracles
"out of the box" solution and buy third party stuff when it comes
to replication. So this argument doesn't really bite.

Regards
Tino

On Mon, Sep 01, 2008 at 10:31:25AM +0200, Thomas Kellerer wrote:

Scott Marlowe, 31.08.2008 22:44:

I think some of it is inertia. We've always used Oracle, let's
just keep on using it. The more conservative the IT department is,
the less likely they are to take chances with new technology.

It used to be there was about an 80/20 split between what things
you could do with either postgresql or oracle, and the other 20%
was oracle only land. I think that number is dropping quickly, and
we're into the 1 or 2% club of what Oracle can do that PostgreSQL
isn't fast enough for.

For me (personally) the ratio is more like 70/30,

It's about to increase :)

because we are making extensive usage of Oracle's windowing
functions

http://umitanuki.net/pgsql/wfv04/design.html

(and ability to easily deal with hierarchical data using CONNECT BY).

It's even more fun with CTEs.
<http://wiki.postgresql.org/wiki/CTEReadme&gt;

The lack of windowing functions is a bit frustrating as I'm
otherwise a big Postgres fan!

Those will both be fixed, at least to a large degree, in 8.4 :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter, 01.09.2008 16:08:

because we are making extensive usage of Oracle's windowing
functions

http://umitanuki.net/pgsql/wfv04/design.html

I knew there was work going on regarding this, but I didn't know how definite the decision was to integrate that into 8.4

It's too bad lead(), lag() won't make it

It's even more fun with CTEs.
<http://wiki.postgresql.org/wiki/CTEReadme&gt;

Recursive CTE are quite cool, and a valid "replacement" for CONNECT BY.
I've used it already with SQL Server 2005 and once you get the idea it's really nice.

Those will both be fixed, at least to a large degree, in 8.4 :)

What exactly means "large degree" :)

Regards
Thomas