pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jesper Krogh <jesper@krogh.cc>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Divakar Singh <dpsmails@yahoo.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: Wed, 27 Oct 2010 21:47:23 +0200
Message-ID: <4CC881CB.8090806@krogh.cc> (raw)
In-Reply-To: <AANLkTimMSMcqkAY9jMi8UZKveY3YS-p-V60KckvtDB6g@mail.gmail.com>
References: <402913.53404.qm@web65405.mail.ac4.yahoo.com>
	<AANLkTi=0iZDF7EHebukEJ+jxFN8giCy3iXxYuSe_mLUY@mail.gmail.com>
	<AANLkTi=cy67frLbx+YH_uUk5vqPsxt8u-Q_cpU74pYMq@mail.gmail.com>
	<208728.42197.qm@web65409.mail.ac4.yahoo.com>
	<AANLkTikeYj52q1v1RcwCYR=9Kv5mxJmh+Q+KvXcmibYK@mail.gmail.com>
	<770715.39613.qm@web29003.mail.ird.yahoo.com>
	<AANLkTinaW3ZtGJbzgAARG0WfAOJxSPAbdVAZa6X_2dh+@mail.gmail.com>
	<4CC741FF.8050709@vmsinfo.com>
	<AANLkTinntqfT1b9xaRfGGqVwwsGhyA7=JhLsuQ10Xzyy@mail.gmail.com>
	<4CC74E31.6060307@vmsinfo.com>
	<AANLkTimf9Nj8A+Spiu9q3suvZ+h71CHXRFTXFo_5aBYj@mail.gmail.com>
	<AANLkTi=-fK6ODDNJPntq3OeUJQzKtHbsmkdDM851waOG@mail.gmail.com>
	<252538.42200.qm@web65405.mail.ac4.yahoo.com>
	<AANLkTik1Acnzpm62XgHADZtZ0sgkxSo=43jKrX7+N_f1@mail.gmail.com>
	<173298.80592.qm@web65406.mail.ac4.yahoo.com>
	<AANLkTin90tsRz4BNQP1gqrg6zCpQp0Yt0bQXKQHC22Ps@mail.gmail.com>
	<4CC8728B.9050205@krogh.cc>
	<AANLkTimMSMcqkAY9jMi8UZKveY3YS-p-V60KckvtDB6g@mail.gmail.com>

On 2010-10-27 20:51, Merlin Moncure wrote:

>> 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.
>>      
Or configureabillity.. Not directly related to overall space consumption
but I have been working on a patch that would make TOAST* kick in
earlier in the process, giving a "slimmer" main table with visibillity 
information
and simple columns and moving larger colums more aggressively to TOAST.

The overall disadvantage of TOAST is the need for an extra disk seek if
you actually need the data. If the application rarely needs the large
columns but often do count/filtering on simple values this will eventually
lead to a better utillization of the OS-page-cache with a very small 
overhead
to PG (in terms of code) and 0 overhead in the applications that benefit.

Keeping in mind that as SSD-drives get more common the "the extra disk seek"
drops dramatically, but the drive is by itself probably still 100-1000x 
slower than
main memory, so keeping "the right data" in the OS-cache is also a 
parameter.

If you deal with data where the individual tuple-size goes up, currently 
TOAST
first kicks in at 2KB (compressed size) which leads to a very sparse 
main table
in terms of visibillity information and count and selects on simple values
will drag a huge amount of data into the cache-layers thats not needed 
there.

Another suggestion could be to make the compression of text columns kick in
earlier .. if thats possible. (I dont claim that its achiveable)

Unless the tuple-header is hugely bloated I have problems creating a 
situation in my
head where hammering that one can change anything significantly.

* http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg159726.html

-- 
Jesper



view thread (53+ messages)  latest in thread

Message-ID: <4CC881CB.8090806@krogh.cc>
Permalink:  ../4CC881CB.8090806@krogh.cc/
Also on:    postgresql.org/message-id/4CC881CB.8090806@krogh.cc

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jesper@krogh.cc, mmoncure@gmail.com, dpsmails@yahoo.com, robertmhaas@gmail.com, mladen.gogala@vmsinfo.com
  Subject: Re: Postgres insert performance and storage requirement compared to Oracle
  In-Reply-To: <4CC881CB.8090806@krogh.cc>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox