Re: PostgreSQL Write Performance

>> What is the actual problem you are trying to solve?

I am currently developing a database system for a high speed measurement machine.

The time taken to perform measurement per unit is in term of ~30 milliseconds. We need to record down the measurement result for every single unit. Hence, the time taken by record down the measurement result shall be far more less than milliseconds, so that it will have nearly 0 impact on the machine speed (If not, machine need to wait for database to finish writing, before performing measurement on next unit)

Previously, we are using flat file.. However, using flat file is quite a mess, when come to generating reports to customers.

Thanks and Regards
Yan Cheng CHEOK

--- On Tue, 1/5/10, Dann Corbit <DCorbit(at)connx(dot)com> wrote:

> From: Dann Corbit <DCorbit(at)connx(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, 2:03 PM
> > -----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?
>
>
> --
> 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
>