Re: Migration study, step 1: bulk write performance

Mikael,

I've just recently passed such an experience, i.e. migrating from
another vendor to postgres of a DB about the same size category you
have.

I think you got it right with the fsync turned off during migration
(just don't forget to turn it back after finishing ;-), and using tables
without indexes/foreign keys. In our case recreating all the
indexes/foreign keys/other constraints took actually longer than the raw
data transfer itself... but it's possible that the process was not tuned
100%, we are still learning how to tune postgres...

What I can add from our experience: ext3 turned out lousy for our
application, and converting to XFS made a quite big improvement for our
DB load. I don't have hard figures, but I think it was some 30%
improvement in overall speed, and it had a huge improvement for heavy
load times... what I mean is that with ext3 we had multiple parallel big
tasks executing in more time than if we would have executed them
sequentially, and with XFS that was gone, load scales linearly. In any
case you should test the performance of your application on different FS
and different settings, as this could make a huge difference.

And another thing, we're still fighting with performance problems due to
the fact that our application was designed to perform well with the
other DB product... I think you'll have more work to do in this regard
than just some search/replace ;-)

Cheers,
Csaba.