Tuning PostgreSQL for bulk imports

[Kirk Strauser]

On Sunday 07 January 2007 15:15, Ivan Voras wrote:

> There's a dedicated mailing list for PostgreSQL performance:
> pgsql-performance/at/postgresql.org, which can give you really good
> advice, but here's some tips:

I've read, read, and re-read the general tuning tips, and done as much as 
seemed reasonable.  I was sort of hoping for a FreeBSD-specific 
magic "go-fast switch".

> - What might help you is to keep the WAL (write-ahead-log, i.e. journal)
> files on a completely separate (and fast) drive from the rest of the
> database, to allow parallelism and speed. For best results, format it
> with 32k blocks/8k fragments.

Thanks for the idea.  Assuming I actually get my wish of a matched set of 4 
high speed drives, would I be better off setting one aside for the journal, 
or striping them all together so everything benefits?

> - If you don't specifically need the atomicity of transactions, you
> might divide your import into many small transactions, for example one
> for every 100,000 rows instead of doing 8 million at once.

Would that actually make a difference in total elapsed time spent importing?

> Also, what version of PostgreSQL are you using? As a general rule, the
> newer the version, the faster it is. This is especially true if you're
> using 7.x - go to 8.1.5 immediately (but don't use 8.2 until 8.2.1 gets
> out).

We're already running 8.2 because it fixed some problems we were having with 
8.1.5.  Other than the excessively long import times, it's absolutely 
screaming and we couldn't be more pleased.
-- 
Kirk Strauser
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20070107/507aa6fe/attachment.pgp