Introducing pgstatspack, a statistics package for postgresql

When performance or behavior during the development or test phase is not what you’ve expected, the normal way to go is explain or explain analyze the statements involved.

But how about production environments? And how can you tell what has happened? The most common scenario is probably when the behavior is unacceptable, so instant action is required. That is not the way you, as a DBA, want to be notified how about the dissatisfaction of the performance.

Some time ago, glenn fawcett of sun microsystems encountered probably a situation in which he wanted to know what was going on on a database level. He also had experience with the oracle database, and with a statistics package called “statspack”. What glenn did, was emulate some of the functionality of statspack, and create a version for postgresql, heavily focussed on logical IO and physical IO.

Later, this package was lost, and corresponding page also. In fact, I managed to get the package using the internet time machine (http://www.archive.org/web/web.php). Glenn recently got notified of this fact, and created a post about the package on his blog.

Well, I got into the same situation as Glenn. I have a postgresql database, and want to know what’s going on. In fact, I want to know more than just logical and physical IO, I want to know everything that is available. I also want to know postgresql version, database size, parameters, etc. Also, I want to make use of it to do analysis on daily basis of the snapshots, so I can do capacity management and do predictions about growth and performance.

That is why I’ve enhanced pgstatspack, and made it available open source: http://pgfoundry.org/projects/pgstatspack

Enjoy!