Re: PostgreSQL Write Performance

Instead of sending 1000++ INSERT statements in one shot, which will requires my application to keep track on the INSERT statement.

Is it possible that I can tell PostgreSQL,

"OK. I am sending you INSERT statement. But do not perform any actual right operation. Only perform actual write operation when the pending statement had reached 1000"

Thanks and Regards
Yan Cheng CHEOK

--- On Tue, 1/5/10, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> Subject: Re: [GENERAL] PostgreSQL Write Performance
> To: "Yan Cheng Cheok" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Date: Tuesday, January 5, 2010, 11:45 AM
> On Mon, Jan 4, 2010 at 8:36 PM, Yan
> Cheng Cheok <yccheok(at)yahoo(dot)com>
> wrote:
> > I am not sure whether I am doing the correct
> benchmarking way.
> >
> > I have the following table ;
> >
> > CREATE TABLE measurement_type
> > (
> >  measurement_type_id bigserial NOT NULL,
> >  measurement_type_name text NOT NULL,
> >  CONSTRAINT pk_measurement_type_id PRIMARY KEY
> (measurement_type_id),
> >  CONSTRAINT
> measurement_type_measurement_type_name_key UNIQUE
> (measurement_type_name)
> > )
> >
> > I make the following single write operation through
> pgAdmin :
> >
> > INSERT INTO measurement_type ( measurement_type_name
> )
> > VALUES('Width');
> >
> > It takes 16ms to write a single row according to
> "Query Editor" (bottom right corner)
> >
> > Am I doing the correct way to benchmarking? I am not
> sure whether this is expected performance? For me, I am
> expecting the time measurement is in nano seconds :p
>
> It would be great if a hard drive could seek write,
> acknowledge the
> write and the OS could tell the db about it in nano
> seconds.  However,
> some part of that chain would have to be lieing to do
> that.  It takes
> at LEAST a full rotation of a hard drive to commit a single
> change to
> a database, usually more.  Given that the fastest HDs
> are 15k RPM
> right now, you're looking at 250 revolutions per second, or
> 1/250th of
> a second minimum to commit a transaction.
>
> Now, the good news is that if you make a bunch of inserts
> in the same
> transaction a lot of them can get committed together to the
> disk at
> the same time, and the aggregate speed will be, per insert,
> much
> faster.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>