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.

Lately, a server I’ve been maintaining has been running out of database connections. Upon further investigation, the culprit is clearly PHP’s pg_pconnect function. Persistent connections with PHP is buggy to say the least, and it’s often mistaken for connection pooling. Connection pooling is different from the persistent connection implementation that PHP uses, since persistent connections are only per Apache process. That means that each Apache process (likely per user), has it’s own pool of connections that only it can use. What’s worse is the connection isn’t closed until the Apache process times out, so most of the open connections are idle. The flaw in this design is astonishing. Right now, there’s 38 open database connections and only two of them are actively being used. Also, by default, PHP has pgsql.max_persistent set to -1, meaning each Apache process is allowed unlimited database connections. If you’re set on using persistent connections over pooling, you should set that to no more than 1 or 2.

The real solution is to use a “middleman” process to provide true connection pooling. This can easily be done easily with pgpool.

This is the pgpool configuration I’m using right now:

listen_addresses = ‘localhost’
port = 5433
socket_dir = ‘/tmp’
backend_host_name = ‘localhost’
backend_port = 5432
backend_socket_dir = ‘/var/run/postgresql’

Now, the only change you have to make to your PHP projects, is to use pg_connect instead of pg_pconnect, and connect to port 5433, instead of 5432. That’s it! Pgpool will automatically pool your database connections, and you will avoid running out of connections!

hmnos ocea
uogu rfsinlduhbg