Tuning PostgreSQL for bulk imports

Kirk Strauser kirk at strauser.com
Sun Jan 7 22:18:17 UTC 2007

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

More information about the freebsd-questions mailing list