Re: Postgres insert performance and storage requirement compared to Oracle

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