Re: PostgreSQL Write Performance

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Yan Cheng Cheok
> Sent: Monday, January 04, 2010 9:05 PM
> To: Scott Marlowe
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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"

You might use the copy command instead of insert, which is far faster.
If you want the fastest possible inserts, then probably copy is the way
to go instead of insert.
Here is copy command via API:
http://www.postgresql.org/docs/current/static/libpq-copy.html
Here is copy command via SQL:
http://www.postgresql.org/docs/8.4/static/sql-copy.html

You might (instead) use this sequence:

1. Begin transaction
2. Prepare
3. Insert 1000 times
4. Commit

If the commit fails, you will have to redo the entire set of 1000 or
otherwise handle the error.

Or something along those lines. Of course, when information is not
written to disk, what will happen on power failure? If you do something
cheesy like turning fsync off to speed up inserts, then you will have
trouble if you lose power.

What is the actual problem you are trying to solve?