There’s seems to be some people complaining about slow TRIGGERs in PostgreSQL, so I thought it might be a good occasion to go over some things that can slow TRIGGERs down.

Avoid using FOR EACH ROW triggers. The real killer I’ve run into is people creating triggers that are FOR EACH ROW, then they have large transaction blocks somewhere in their application. That creates a huge problem, because now you have triggers firing off for every insert you do, and if you’re doing a lot of inserts and your trigger does anything relatively costly, it will kill performance. I’ve seen situations where it takes nearly a minute to run a transaction block for a relatively small number of rows. When you have something like this, see if it’s possible to change your trigger so it runs FOR EACH STATEMENT.

Tune your triggers. If you’re using triggers, chances are they can be optimized. Look for code that can be minimized or things that aren’t needed. Things like selects tend to get overlooked, and you may have one’s that can be optimized, or be merged together. EXPLAIN ANALYZE is a great tool for this.

This entry was posted on Tuesday, January 29th, 2008 at 7:15 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.