Thread: Not to start a flame war but what does Oracle have that Postgresql does not?

I have used Postgres a bit and actually use it as the back end for a
program we use at my office. Truth is that it has worked so well that
I have not had to mess with it for years at a time and then only to
move to a new server or update. I have no experience with Oracle at
all. Since I work with small datasets of only a few hundred thousand
records to a million records and only a handful of tables Postgres
does everything we need here. Right now it is supporting 50 users and
running on a P3 600 with a single hard drive and 512 MB of ram. Yes
the response time is "human instant".
So I am wondering what features does Postgres lack to equal Oracle or
DB2?  This is more for my own satisfaction since I can not imagine any
uses here that wouldn't run on a modern hexcore server with a few
gigabytes of ram and a raid of SATA drives.

Just 512MB of ram?!

Holy cow!

I've always heard that Oracle has several features and capabilities that
PostGres lacks (and it is infinitely more expensive, too), but I just worked
6 years ago and for a short period with Oracle so
I don't know how to fully answer your question.
But, I believe that Oracle can run a single query in multiple processors,
something that AFAIK PostGres is still unable to (Correct me if I'm wrong).

Best,
Oliveiros

----- Original Message -----
From: "LWATCDR" <lwatcdr@gmail.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, March 23, 2011 1:58 PM
Subject: [NOVICE] Not to start a flame war but what does Oracle have that
Postgresql does not?


>I have used Postgres a bit and actually use it as the back end for a
> program we use at my office. Truth is that it has worked so well that
> I have not had to mess with it for years at a time and then only to
> move to a new server or update. I have no experience with Oracle at
> all. Since I work with small datasets of only a few hundred thousand
> records to a million records and only a handful of tables Postgres
> does everything we need here. Right now it is supporting 50 users and
> running on a P3 600 with a single hard drive and 512 MB of ram. Yes
> the response time is "human instant".
> So I am wondering what features does Postgres lack to equal Oracle or
> DB2?  This is more for my own satisfaction since I can not imagine any
> uses here that wouldn't run on a modern hexcore server with a few
> gigabytes of ram and a raid of SATA drives.
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

LWATCDR, 23.03.2011 14:58:
> So I am wondering what features does Postgres lack to equal Oracle or
> DB2?  This is more for my own satisfaction since I can not imagine any
> uses here that wouldn't run on a modern hexcore server with a few
> gigabytes of ram and a raid of SATA drives.

Things that Oracle has that PostgreSQL does not:

- materialized views
- Flashback queries and flashback archive
- A lot more index types and table organization types - which are relevant in a DWH environment
- RAC
- Much better partitioning (it's a lot easier and more flexible - including partitioning for indexes)
- real stored procedures (and the possibility to have transaction control in the stored procedure)
- Index only scans
- Better XML support
- I haven't used it, but the new "Edition" feature in 11gR2 seems
   to be very cool when dealing with schema migration
- A tool for an online redefinition of tables without the need to lock the table
   when doing the ALTER TABLE...
- Much more monitoring tools built-in
- no need to configure (auto) vacuum

Things that Postgres has that Oracle does not:

- transactional DDL (including TRUNCATE)
- can use more than one index for one table in one query
- multiple sessions sharing the same full table scan
- better support for arrays
- hstore module
- Possibility to define comments on every object in the database.
- a smaller footprint and easier to install
- generate_series()
- a slightly better support for windowing functions
- The sequence implementation in Postgres is better
   (due to the fact that a column can "own" a sequence, in Oracle there is
    no way to define a dependency between a column and a sequence)


I don't really know DB2, but as far as I can tell DB2's dictionary based compression and
its XML support are both a lot better than in Oracle or Postgres.

Regards
Thomas

What do you mean by "real stored procedures"?  I only know PostGreSQL
stored procedures.  What are they missing?

RobR

Rob Richardson, 23.03.2011 16:40:
> What do you mean by "real stored procedures"?  I only know PostGreSQL
> stored procedures.  What are they missing?
>
Postgres only has functions, no procedures.

It's not a big difference but there is one and basically anything you could do with a procedure you can also do a with
afunction. The call syntax is different. That reminds me of another thing that Oracle has: Packages - which are quite
niceto organize a large set of procedures and functions 

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Rob Richardson, 23.03.2011 16:40:
>> What do you mean by "real stored procedures"?  I only know PostGreSQL
>> stored procedures.  What are they missing?

> Postgres only has functions, no procedures.

Ignoring trivial syntax differences, I think the important point is that
in Oracle procedures execute outside the database engine, so to speak.
That means they can start and commit transactions.  In Postgres,
functions are called inside a transaction and they can't commit it or
start a new one.  They can run sub-transactions (savepoints) but they
can't commit a whole transaction.  So for example you can never make
partial results of a function's execution visible to another session.

            regards, tom lane

-----Original Message-----

Thomas Kellerer <spam_eater@gmx.net> writes:
> Rob Richardson, 23.03.2011 16:40:
>> What do you mean by "real stored procedures"?  I only know PostGreSQL
>> stored procedures.  What are they missing?

> Postgres only has functions, no procedures.

Ignoring trivial syntax differences, I think the important point is that
in Oracle procedures execute outside the database engine, so to speak.
That means they can start and commit transactions.  In Postgres,
functions are called inside a transaction and they can't commit it or
start a new one.  They can run sub-transactions (savepoints) but they
can't commit a whole transaction.  So for example you can never make
partial results of a function's execution visible to another session.

            regards, tom lane

--

Thanks, Tom.  That's interesting.  I've wanted to use transactions in
functions in PostgreSQL, and not been able to.  For example, trying to
save debugging information to a table before raising an exception from
the function.  The exception gets raised, the transaction gets rolled
back, and the saved debugging information isn't there anymore.

RobR

On 03/23/2011 11:35 AM, Thomas Kellerer wrote:
> LWATCDR, 23.03.2011 14:58:
>> So I am wondering what features does Postgres lack to equal Oracle or
>> DB2? This is more for my own satisfaction since I can not imagine any
>> uses here that wouldn't run on a modern hexcore server with a few
>> gigabytes of ram and a raid of SATA drives.
>
> Things that Oracle has that PostgreSQL does not:
>
> - materialized views
> - Flashback queries and flashback archive
> - A lot more index types and table organization types - which are relevant in
> a DWH environment
> - RAC
> - Much better partitioning (it's a lot easier and more flexible - including
> partitioning for indexes)
> - real stored procedures (and the possibility to have transaction control in
> the stored procedure)
> - Index only scans
> - Better XML support
> - I haven't used it, but the new "Edition" feature in 11gR2 seems
> to be very cool when dealing with schema migration
> - A tool for an online redefinition of tables without the need to lock the table
> when doing the ALTER TABLE...
> - Much more monitoring tools built-in
> - no need to configure (auto) vacuum
>
> Things that Postgres has that Oracle does not:

   - VARCHAR (i.e., the standard type, not Oracle's variation)
   - the ability to use TEXT as an unlimited VARCHAR
   - much lower licensing fees

> - transactional DDL (including TRUNCATE)
> - can use more than one index for one table in one query
> - multiple sessions sharing the same full table scan
> - better support for arrays
> - hstore module
> - Possibility to define comments on every object in the database.
> - a smaller footprint and easier to install
> - generate_series()
> - a slightly better support for windowing functions
> - The sequence implementation in Postgres is better
> (due to the fact that a column can "own" a sequence, in Oracle there is
> no way to define a dependency between a column and a sequence)
>
>
> I don't really know DB2, but as far as I can tell DB2's dictionary based
> compression and
> its XML support are both a lot better than in Oracle or Postgres.

Anecdotally, you can get PG to outperform Oracle in some scenarios.  (I know
people who've conducted the exercise.  It requires tuning for the particular
application - sometimes PG will tune better than Oracle.)

The next question is to compare Oracle to DB2.  Then each of the three
against, say, SQL Server or one of the lesser-known enterprise DBMSes.

http://troels.arvin.dk/db/rdbms/
has good comparisons.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg