Message-ID: <4CC881CB.8090806@krogh.cc>
Date: Wed, 27 Oct 2010 21:47:23 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.12) Gecko/20100915 Thunderbird/3.0.8
MIME-Version: 1.0
To: Merlin Moncure <mmoncure@gmail.com>
CC: Divakar Singh <dpsmails@yahoo.com>, Robert Haas <robertmhaas@gmail.com>,
	Mladen Gogala <mladen.gogala@vmsinfo.com>,
	pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement
	compared to Oracle
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>
In-Reply-To: <AANLkTimMSMcqkAY9jMi8UZKveY3YS-p-V60KckvtDB6g@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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