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

Queries to estimate bloat in tables and indexes.

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. Only works for BTree indexes, not GIN, GiST, or more exotic indexes. Still needs cleanup.

An overhauled table bloat check. Lists tables which are likely to be bloated and estimates bloat amounts. Requires PostgreSQL >= 8.4 and a 64-bit compile. Cannot estimate bloat for tables containing types with no stats functions (such as original JSON).

Query to list all tables which have "no stats" columns and thus can't be estimated.

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.

Various queries to introspect index usage.

Queries for foreign keys with no index on the referencing side. Note that you don't always want indexes on the referencing side, but this helps you decide if you do.

Check indexes and looks at whether or not they are potentially duplicates. It does this by checking the columns used by each index, so it reports lots of false duplicates for partial and functional indexes.

Checks for tables which are getting too much sequential scan activity and might need additional indexing. Reports in four groups based on table size, number of scans, write activity, and number of existing indexes.

Checks for indexes with relatively light usage stats, for possible removal.

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.

Lists direct locks on tables which conflict with locks held by other sessions. Note that table locks are often short-lived, and as a result this will often result in zero rows.

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

• The authors of the check_postgres.pl script, especially Greg Sabino Mulainne, for supplying the original bloat queries on which our bloat queries are based.
• Andrew Gierth for help on various system queries.
• ioguix for collaborating on bloat calculation math.