Thursday, May 31, 2012

How I learned to stop worrying and love Postgres hooks

Recently, Theo added a statsd interface to reconnoiter. He and I talked about the possibility of getting Postgres query stats (duration and number of tuples returned) into reconnoiter through this interface. His original idea was to get some logging hooks added to Postgres and pull the info that way. It just so happens that I had just seen Guillaume's talk on hooks in Postgres at PGCon. The hooks in Postgres are among the more poorly documented features. I knew there were hooks, but really didn't know much more until I attended this very informative talk.

I did some hacking and the solution I came up with is now known as pg_query_statsd. It uses the ExecutorStart and ExecutorEnd hooks to turn on and pull instrumentation data and then ships that data over to statsd (really reconnoiter posing as statsd) via UDP so there is no blocking. There are some buffer stats that I can enable and may add that feature in the future. It also has a hostname and port GUC so you can change then reload the config to send the data to a new host.

A couple notes about compatibility. First, I currently send two metrics in one packet. The official statsd from Etsy does not support this. I sent them a pull request to fix this. Second, since the documentation is non-existent, I had to make some guesses as to how I was supposed to handle taking the hooks and calling default functions. It may not play nice with other modules. If someone happens to see a problem in this area, or any area really, I take pull requests.