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.