Re: Postgres insert performance and storage requirement compared to Oracle

Profiling could tell you where is the time lost and where is your
program spending time. Having experience with both Oracle and Postgres,
I don't feel that there is much of a difference in the insert speed. I
am not using C++, I am using scripting languages like Perl and PHP and,
as far as inserts go, I don't see much of a difference. I have an
application which inserts approximately 600,000 records into a
PostgreSQL 9.0.1 per day, in chunks of up to 60,000 records every hour.
The table is partitioned and there are indexes on the underlying
partitions. I haven't noticed any problems with inserts. Also, if I use
"copy" instead of the "insert" command, I can be even faster. In
addition to that, PostgreSQL doesn't support index organized tables.

Divakar Singh wrote:
> Storage test was simple, but the data (seconds taken) for INSERT test
> for PG vs Oracle for 1, 2, 3,4 and 5 indexes was:
> PG:
> 25
> 30
> 37
> 42
> 45
>
>
>
> Oracle:
>
> 33
> 43
> 50
> 65
> 68
>
> Rows inserted: 100,000
> Above results show good INSERT performance of PG when using SQL
> procedures. But performance when I use C++ lib is very bad. I did that
> test some time back so I do not have data for that right now.
>
> ------------------------------------------------------------------------
> *From:* Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> *To:* Divakar Singh <dpsmails(at)yahoo(dot)com>
> *Cc:* pgsql-performance(at)postgresql(dot)org
> *Sent:* Mon, October 25, 2010 11:56:27 PM
> *Subject:* Re: [PERFORM] Postgres insert performance and storage
> requirement compared to Oracle
>
> On Mon, Oct 25, 2010 at 12:12 PM, Divakar Singh <dpsmails(at)yahoo(dot)com
> <mailto:dpsmails(at)yahoo(dot)com>> wrote:
> > Hello Experts,
> > My application uses Oracle DB, and makes use of OCI interface.
> > I have been able to develop similar interface using postgreSQL library.
> > However, I have done some tests but results for PostgreSQL have not been
> > encouraging for a few of them.
>
> Tell us more about your tests and results please.
>

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions