Re: Kudos on the 64 bit PostgreSQL for Windows

> -----Original Message-----
> From: Joshua J. Kugler [mailto:joshua(at)eeinternet(dot)com]
> Sent: Friday, October 01, 2010 10:36 AM
> To: pgsql-general(at)postgresql(dot)org
> Cc: Dann Corbit
> Subject: Re: Kudos on the 64 bit PostgreSQL for Windows
>
> On Thursday 30 September 2010, Dann Corbit elucidated thus:
> > I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it
> > seems to perform wonderfully. I moved 1.5 million records that are 3K
> > wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in
> > less than 6 minutes.
>
> > I was thinking of using SQLite for the project,
> > but I calculate the same data movement would have taken about one
> > month!
>
> This is a bit OT, but were you using transactions in SQLite?

I was using a data movement tool with the SQLite ODBC driver.
I also tried a script of SQL inserts. Efficiency was the same.
The actual operation is performed using an INSERT/SELECT.
In many respects, the comparison is not fair, because we have
written a custom ODBC driver for PostgreSQL that can use bulk
mode (COPY) on insert and SQLite does not have a bulk mode.
We can only do bulk mode operations with database systems that
have some mechanism for this.

> 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.