Skip site navigation (1) Skip section navigation (2)

Re: Postgres insert performance and storage requirement compared to Oracle

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Divakar Singh <dpsmails(at)yahoo(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: 2010-10-27 18:28:06
Message-ID: AANLkTin90tsRz4BNQP1gqrg6zCpQp0Yt0bQXKQHC22Ps@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Wed, Oct 27, 2010 at 2:14 PM, Divakar Singh <dpsmails(at)yahoo(dot)com> wrote:
> yes this is a very clearly visible problem.
> The difference b/w oracle and PG increases with more rows.
> when oracle takes 3 GB, PG takes around 6 GB.
> I only use varchar.
> I will try to use your tips on "smart table layout, toast compression".
> Assuming these suggested options do not have any performance penalty?

These will only be helpful in particular cases, for example if your
layout is bad :-).  toast compression is for dealing with large datums
(on by default iirc).  Also it's very hard to get apples to apples
comparison test via synthetic insertion benchmark.  It's simply not
the whole story.

The deal with postgres is that things are pretty optimized and fairly
unlikely to get a whole lot better than they are today.  The table
layout is pretty optimal already, nulls are bitmaps, data lengths are
using fancy bitwise length mechanism, etc.  Each record in postgres
has a 20 byte header that has to be factored in to any storage
estimation, plus the index usage.

Postgres indexes are pretty compact, and oracle (internals I am not
familiar with) also has to do MVCC type management, so I am suspecting
your measurement is off (aka, operator error) or oracle is cheating
somehow by optimizing away storage requirements somehow via some sort
of tradeoff.  However you still fail to explain why storage size is a
problem.  Are planning to port oracle to postgres on a volume that is
>50% full? :-)

merlin

In response to

Responses

pgsql-performance by date

Next:From: Reid ThompsonDate: 2010-10-27 18:32:58
Subject: Re: temporary tables, indexes, and query plans
Previous:From: Scott MarloweDate: 2010-10-27 18:28:02
Subject: Re: CPUs for new databases

pgsql-hackers by date

Next:From: Jesper KroghDate: 2010-10-27 18:42:19
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Divakar SinghDate: 2010-10-27 18:14:30
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group