Re: Kudos on the 64 bit PostgreSQL for Windows

> -----Original Message-----
> From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
> Sent: Friday, October 01, 2010 6:08 PM
> To: Dann Corbit
> Cc: 'Joshua J. Kugler'; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Kudos on the 64 bit PostgreSQL for Windows
>
> On 2/10/2010 2:06 AM, Dann Corbit wrote:
> >> SQLite can
> >> deliver several thousand inserts per second if inside of a
> transaction.
> >> If you were only getting a few inserts per second, then you were not
> >> using transactions, thus SQLite was on "autocommit" mode, and thus
> >> committing after every insert, thus the performance penalty.
> >
> > For this application, SQLite has no chance to compete. I would not
> want
> > to rewrite applications unless there were a dire need. I am not sure
> > that it would make sense to do 400 MB in one big transaction either,
> > so some kind of fiddling would be needed. PostgreSQL flies like an
> > arrow right out of the box. Problem solved.
>
> If you're inserting one row per transaction, PostgreSQL shouldn't be
> all
> that fast either, because it has to wait for data to fsync() to disk.
> If
> you're using a storage controller with write-back caching (usually
> battery backed cache) this doesn't apply, but otherwise postgresql
> usually lands up waiting a disk rotation or two, so you should be
> seeing
> insert rates below 100/s on most storage systems if it's working
> properly.

The inserts are processed via an INSERT/SELECT statement.
A custom driver has been written that performs this operation
using the COPY API. I get many thousands of rows per second.
The operations all take place in binary mode also (any necessary
conversions are performed by the client).

> Are you using synchronous_commit=off and/or a commit delay?

I do not use synchronous_commit=off

> Do you have fsync=off set in your postgresql.conf?

No.

> If so, I hope you're
> aware of the serious data integrity risks.
> http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html
>
> If neither of these are true and you're not using battery-backed cache,
> check to make sure your storage subsystem is honouring fsync requests.
> Some SSDs are known to ignore fsync, which *will* cause data corruption
> if you have an OS crash or power loss.