Re: Postgres vr.s Oracle

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

> On Sun, Dec 14, 2008 at 10:47 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> 10-100x?
>>
>> I am confused because sometimes I hear that Postgres has bad performance
>> from ex-Oracle users, but in general I hear that Oracle and Postgres
>> have similar performance behavior from people porting applications.

I think there are problem cases where Postgres really doesn't perform well.
That's the only time you're going to see a 10-100x factor anyways. Even if you
compared SQL-Lite to Oracle you would expect to see vaguely comparable results
unless you're hitting on a case that SQL-Lite just isn't intended to handle.

For example there are some queries in the DBT-3 power test which Postgres
doesn't optimize well. In some cases Postgres is just entirely lacking the
ideal plan type. It's kind of pointless to talk about *how* much slower some
random inappropriate plan is than some entirely different plan Oracle uses.
Who cares if it's 10x or 100x or even 1000x slower. No amount of optimization
is going to fix it unless we address the algorithmic problem anyways.

> The OLTP cases where Oracle starts to outperform Postgres is usually
> around 25 heavy concurrent sessions.  When you start scaling into
> hundreds of sessions, Oracle really starts to shine.

Postgres is definitely not optimized for systems with many concurrent
sessions. In the past we've basically dismissed such systems as misconfigured
on the basis that there's not much point in having many more sessions than the
number of processors in your system. If you have a big RAID array then it's
possible the number of spindles might be more relevant than number of
processors. But the point is that there's no performance advantage to
configuring more sessions than the system can actually make progress on
concurrently rather than queueing up the requests and processing them
sequentially.

There is a counter-argument to this however. If you have a system with complex
multi-request transactions then it's a question of programming convenience.
The easiest model to work with is to start a database session and use the
database to maintain transaction state -- which is what it's for after all.
That's more robust and convenient than using autocommit or otherwise trying to
manage short stateless requests. But it does risk leaving you with relatively
long-lived sessions which are often idle and forces you to have a large number
of backends running.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!