From: Steve Singer <ssinger@ca.afilias.info>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: Merlin Moncure <mmoncure@gmail.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 14:51:02 -0400
Message-ID: <4CC87496.9070703@ca.afilias.info> (raw)
In-Reply-To: <173298.80592.qm@web65406.mail.ac4.yahoo.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>
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
>
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: ssinger@ca.afilias.info, dpsmails@yahoo.com, mmoncure@gmail.com, robertmhaas@gmail.com, mladen.gogala@vmsinfo.com
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
In-Reply-To: <4CC87496.9070703@ca.afilias.info>
* 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