Re: Postgres insert performance and storage requirement compared to Oracle

On Wed, Oct 27, 2010 at 2:42 PM, Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
> On 2010-10-27 20:28, Merlin Moncure wrote:
>>
>> 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? :-)
>>
>
> Pretty ignorant comment.. sorry ..
>
> But when your database approaches something that is not mainly
> fitting in memory, space directly translates into speed and a more
> compact table utillizes the OS-page cache better. This is both
> true for index and table page caching.
>
> And the more compact your table the later you hit the stage where
> you cant fit into memory anymore.
>
> .. but if above isn't issues, then your statements are true.

Yes, I am quite aware of how the o/s page cache works. All else being
equal, I more compact database obviously would be preferred. However
'all else' is not necessarily equal. I can mount my database on bzip
volume, that must make it faster, right? wrong. I understand the
postgres storage architecture pretty well, and the low hanging fruit
having been grabbed further layout compression is only going to come
as a result of tradeoffs.

Now, comparing oracle vs postgres, mvcc works differently because
oracle uses rollback logs while postgres maintains extra/old versions
in the heap. This will add up to big storage usage based on various
things, but should not so much be reflected via insert only test.

merlin