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.
