Collecting Statistics from PostgreSQL in Rails

Posted by on Aug 13, 2007 in Blog, News | Tags: , , | 2 Comments

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.

2 Comments

  1. Roderick van Domburg
    August 29, 2007

    Thanks, this is looking very useful. Could you share the pieces of controller and view with us to render the overview like the one in the screenshot? That’d be stellar!

  2. Steve
    August 29, 2007

    Hi, Roderick.

    Sure, I’m happy to share some of the logic I use in my controller and view to get the results I show in the screenshot. I’ll put them on the Google Group in the next few days and make another mention here on the blog.