Steve Madsen
Collecting Statistics from PostgreSQL in Rails

The PostgreSQL database includes a statistics collection facility that can give you information about your database, tables, indexes and sequences. I just posted a new Rails plug-in that makes it very easy to gather this information and display it in a Rails application.

pgsql_stats screenshot

All of the counters described in the PostgreSQL manual are represented in the models in the plug-in. To name a few:

  • Number of scans over the table or index
  • Cache hit/miss counters (and cache hit rate, by a simple computation)
  • On-disk size

In the above screenshot (taken very soon after the server was started), it’s easy to see that the cron_runs table is by far the largest in the database, followed by its primary key index. Of the entities that have been touched, a large percentage of requests are being satisfied by the buffer cache. You can’t see it in that image, but I’ve defined ranges that turn the green bars red if the cache hit rate falls below 75%.

I’ve set up a Google Group forum for further discussion. Some additional information is available in the README, and the plug-in can be installed like any other:

$ script/plugin install \ http://svn.lightyearsoftware.com/svn/plugins/pgsql_stats

All on one line, of course.

Update Sep. 10, 2007: There is now a usage example on the Google Group that shows how to get the results shown in the screenshot.

Update Jul. 23, 2008: Part of the fallout of Google disabling my account appears to be that the group I set up for discussion and support disappeared, too. I have moved discussion and support to my own forums: pgsql_stats forum.