Thread: postgres vs. oracle for very large tables

I've written some extensions to postgres to implement
chemical structure searching.  I get inquiries about
the performance of postgres vs. oracle.  This is a huge
topic, with lots of opinions and lots of facts.  But,
today I got some feedback stating the opinion that:
"Postgres performance diminishes with large tables
  (we’ll be going to upwards of hundreds of millions of rows)."

Is this pure speculation, opinion, known fact?
Does anyone know of measured performance of postgres
vs. oracle, specifically with very large tables?

TJ O'Donnell
www.gnova.com

On Mon, 2006-05-15 at 14:24, TJ O'Donnell wrote:
> I've written some extensions to postgres to implement
> chemical structure searching.  I get inquiries about
> the performance of postgres vs. oracle.  This is a huge
> topic, with lots of opinions and lots of facts.  But,
> today I got some feedback stating the opinion that:
> "Postgres performance diminishes with large tables
>   (we’ll be going to upwards of hundreds of millions of rows)."

Some points.

1:  A lot of people's experience with PostgreSQL was last with version
7.1, or 6.5 or something like that.  Even as far back as 7.4 performance
was not all that great.  with the release of 8.1, and with 8.2 on the
horizon, I've found PostgreSQL to be on an even footing with Oracle.
And for a lot of what I do with databases, even 7.4 is a fair match.

2:  Every database gets diminishing performance with larger tables.  The
data can't fit in memory, you have to scan your disks for more info,
less of the select sets can fit into cache memory, and so on.  I know
the basic gist of this is that postgresql slows down more than oracle.
My point is that a lot of people assume Oracle is fast, but don't test
it, and do test PostgreSQL.  When PostgreSQL slows down they use Oracle,
assuming it's gotta be faster.  Sometimes it is, sometimes it isn't.

3:  Your usage patterns are EVERYTHING.  We don't know what those are.
Is this a reporting / data warehousing database?  Or is this an airline
reservation system?  Big difference.

4:  Nominally, the biggest limiting factor for both Oracle and
PostgreSQL is the hardware you're running it on.  They both tend to run
well on similar hardware (multiple AMD 64 CPUs with lots of RAM and a
really fast RAID array.)  So, if Oracle is gonna cost you $120,000 a
year to license, you get to put that into your PostgreSQL over and above
what you'd put in your Oracle server, and, multiply that by how long
you'll keep the system online.  Three years is typical for a server.
So, imagine 3x your oracle license fees in hardware.  Think storage
arrays with dozens or hundreds of drives under a server with dozens or
CPUS and dozens of gigs of ram.

5:  You're gonna need to load test this to see.  You can get the oracle
10G express for free.  I think that one handles multiple connections,
but is limited to 2 gigs of data.  Make up identical datasets in both
databases, tune them, and see which is faster handling your load.

> Is this pure speculation, opinion, known fact?
> Does anyone know of measured performance of postgres
> vs. oracle, specifically with very large tables?

It's opinion, and probably somewhat informed, but we all have our
biases.  What I've found is that there are some things Oracle is quite
fast at, and other things that it's not so fast at.

And you can't publish a performance comparison of Oracle and anything
else cause Oracle will sue you for breaching your user contract, which
forbids such things.

On Mon, 15 May 2006 16:00:04 -0500, Scott Marlowe wrote:

> 5:  You're gonna need to load test this to see.  You can get
> the oracle 10G express for free.  I think that one handles
> multiple connections, but is limited to 2 gigs of data.

The exact limitations of Oracle Database XE are as follows:

"Any use of the Oracle Database Express Edition is subject to the following limitations;
1. Express Edition is limited to a single instance on any server;
2. Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server;
3. Express Edition may only be used to support up to 4GB of user data (not including Express Edition system data);
4. Express Edition may use up to 1 GB RAM of available memory."

On Mon, May 15, 2006 at 12:24:51PM -0700, TJ O'Donnell wrote:
> I've written some extensions to postgres to implement
> chemical structure searching.  I get inquiries about
> the performance of postgres vs. oracle.  This is a huge
> topic, with lots of opinions and lots of facts.  But,
> today I got some feedback stating the opinion that:
> "Postgres performance diminishes with large tables
>  (we?ll be going to upwards of hundreds of millions of rows)."
>
> Is this pure speculation, opinion, known fact?
> Does anyone know of measured performance of postgres
> vs. oracle, specifically with very large tables?

You're more likely to run into problems with large fields being toasted
than plain large tables. IIRC Oracle's large object support is better
than PostgreSQL's.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Tuesday 2006-06-13 16:19, Jim C. Nasby wrote:
> On Mon, May 15, 2006 at 12:24:51PM -0700, TJ O'Donnell wrote:
> > I've written some extensions to postgres to implement
> > chemical structure searching.  I get inquiries about
> > the performance of postgres vs. oracle.  This is a huge
> > topic, with lots of opinions and lots of facts.  But,
> > today I got some feedback stating the opinion that:
> > "Postgres performance diminishes with large tables
> >  (we?ll be going to upwards of hundreds of millions of rows)."
> >
> > Is this pure speculation, opinion, known fact?
> > Does anyone know of measured performance of postgres
> > vs. oracle, specifically with very large tables?
>
> You're more likely to run into problems with large fields being toasted
> than plain large tables. IIRC Oracle's large object support is better
> than PostgreSQL's.

There's more to it that that.  If the huge tables grow, VACCUMING for XID
maintenance could put Postgres at a disadvantage relative to Oracle.

There are "behavioral" variables involved.  Furthermore, it may be possible to
trade DBA tricks for initial cost of ownership.  Usually the accounting
doesn't work out (DBA salaries are even more expensive than Oracle
licenses) ... but grad students work cheap.

Jim C. Nasby wrote:
> On Mon, May 15, 2006 at 12:24:51PM -0700, TJ O'Donnell wrote:
>> I've written some extensions to postgres to implement
>> chemical structure searching.  I get inquiries about
>> the performance of postgres vs. oracle.  This is a huge
>> topic, with lots of opinions and lots of facts.  But,
>> today I got some feedback stating the opinion that:
>> "Postgres performance diminishes with large tables
>>  (we?ll be going to upwards of hundreds of millions of rows)."

It really depends. I have many customers with hundred of millions of
rows that don't have ANY problems.

Sincerely,

Joshua D. Drake


--

             === The PostgreSQL Company: Command Prompt, Inc. ===
       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
       Providing the most comprehensive  PostgreSQL solutions since 1997
                      http://www.commandprompt.com/