Re: PostgreSQL Write Performance

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.