Wednesday, September 7, 2011

Postgresql-Optimizing huge inserts/copy's

It boils down to disabling fsync which forces writes to disk of finished transactions.

I found about this interesting tip on facebook...what are the odds to that? Thanks Jorge Lopez!

To disable it, just set the environment variable PGFSYNC to no.

Note: I read about that environment variable being related to previous versions of postgresql. I'm using 9.0 and  disabling (fsync = off) in the postgresql.conf file as it seems  that it's no longer there.

http://www.postgresql.org/docs/9.0/static/libpq-envars.html

Side effects:

Should system failures arise, losing data is bound to happen, so you're advise to enable it once you've loaded your data.

 

Reference:

http://www.mail-archive.com/pgsql-sql@postgresql.org/msg01068.html

 

 

No comments:

Post a Comment