Postgresql Performance Tips for Data Loads

• Turn off fsync in postgresql.conf. This can make a huge difference.


• Temporarily disable triggers.
  
  Postgresql 8.x supports an ALTER statement:
  
  ALTER TABLE foo DISABLE TRIGGER ALL;
ALTER TABLE foo ENABLE TRIGGER ALL;
  
  With Postgresql 7.x you need to modify the system tables directly:
  

  UPDATE pg_class
     SET reltriggers = 0
   WHERE relname = 'foo';

  To re-enable:
  

  UPDATE pg_class
     SET reltriggers = (
           SELECT count(*)
             FROM pg_trigger
            WHERE pg_class.oid = tgrelid
         )
   WHERE relname = 'foo';

• Postgresql docs on efficient data loads