Postgres and Fsync

After an upgrade to a server I sometimes help out with, the perl script responsible for importing about 300 000 records into a Postgres database went from taking 2.5 to 6 hours.

Not overly fond of reworking things that aren't broken, I decided not to tweak the scripts to use COPY instead of the usual massive, bulky number of INSERTs... There had to be some bottle-neck somewhere...

Ultimately, checking out the warning logs pointed me to setting the fsync setting in postgres to "off"  - which made things tons better. Sure things won't work nicely if there's an OS or hardware freak-out, but if that happens, the state of the database will pretty much be the least of my problems.

Time taken to import those records now: 1.5 hours. I'm pretty convinced I'm missing something else subtle out here, but such a performance gain for so little effort is worth a few words here.

http://www.postgresql.org/docs/8.1/interactive/runtime-config-wal.html provides some more info.