Sunday, January 7, 2007

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

2 comments:

rudi butt said...

Thanks so much for a great solution. Question: how do you align the text in column 2, 3 and so on to the right (these would be numbers).
Best wishes
Rudi

mla said...

@Rudi: Sorry, I don't understand the question. You mean how to output right-justified numbers? Using psql?