Message-ID: <4CC87496.9070703@ca.afilias.info>
Date: Wed, 27 Oct 2010 14:51:02 -0400
From: Steve Singer <ssinger@ca.afilias.info>
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: Divakar Singh <dpsmails@yahoo.com>
CC: Merlin Moncure <mmoncure@gmail.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>
In-Reply-To: <173298.80592.qm@web65406.mail.ac4.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10-10-27 02:14 PM, Divakar Singh 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?
> Best Regards,
> Divakar

In between test runs are you cleaning out the tables with a "DELETE FROM 
aaaaa" or are you using the TRUNCATE command? Or dropping the table and 
recreating it.

If your just using DELETE it might be that disk space is still being 
used by the old versions of the rows.

Also is postgresql using more space than oracle for storing the index 
data or the main table data?  and is any particular index larger on 
postgresql compared to Oracle.


>
>
> ------------------------------------------------------------------------
> *From:* Merlin Moncure <mmoncure@gmail.com>
> *To:* Divakar Singh <dpsmails@yahoo.com>
> *Cc:* Robert Haas <robertmhaas@gmail.com>; Mladen Gogala
> <mladen.gogala@vmsinfo.com>; pgsql-performance@postgresql.org
> *Sent:* Wed, October 27, 2010 11:36:00 PM
> *Subject:* Re: [PERFORM] Postgres insert performance and storage
> requirement compared to Oracle
>
> On Tue, Oct 26, 2010 at 11:10 PM, Divakar Singh <dpsmails@yahoo.com
> <mailto:dpsmails@yahoo.com>> wrote:
>  > Dear All,
>  > Thanks for your inputs on the insert performance part.
>  > Any suggestion on storage requirement?
>  > VACUUM is certainly not an option, because this is something related to
>  > maintenance AFTER insertion.
>  > I am talking about the plain storage requirement w.r. to Oracle, which I
>  > observed is twice of Oracle in case millions of rows are inserted.
>  > Anybody who tried to analyze the average storage requirement of PG
> w.r. to
>  > Oracle?
>
> There isn't much you can to about storage use other than avoid stupid
> things (like using char() vs varchar()), smart table layout, toast
> compression, etc. Are you sure this is a problem?
>
> merlin
>