A collection of useful little scripts for database analysis and administration, created by our team at PostgreSQL Experts.

A stored procedure which kills idle transactions on PostgreSQL versions 8.3 to 9.1. Intended to be called by a cron job. Takes idle time, polling time, and exempted user list parameters. Outputs pipe-delimited text with the data about the sessions killed.

A stored procedure which kills idle transactions on PostgreSQL versions 9.2 and later. Intended to be called by a cron job. Takes idle time and exempted user list parameters. Outputs JSON with the data about the sessions killed.

An overhauled index bloat check. Lists indexes which are likely to be bloated and estimates bloat amounts. Requires PostgreSQL > 8.4, superuser access, and a 64-bit compile.

An overhauled table bloat check. Lists tables which are likely to be bloated and estimates bloat amounts. Requires PostgreSQL >= 8.4, superuser access, and a 64-bit compile.

Tools and a set of queries to analyze lock-blocking.

Requires: Postgres 9.2+

Lists waiting transaction locks and what they're waiting on, if possible. Includes relation and query information, but realistically needs to be accompanied by full query logging to be useful. Needs to be run per active database.

In addition to the staff of PostgreSQL Experts, we are indebted to:

• The authors of the check_postgres.pl script for supplying the original bloat queries on which our bloat queries are based.
• Andrew Gierth for help on various system queries.