Convenient SQL for monitoring Postgres database health. This repository is inspired by commands from Heroku's pg_extras repository.

You can run a script using psql's -f option. For example:

$ psql postgres -f sql/cache_hit_rate.sql

It also works with aliases you have setup with psql:

$ alias psql_my_db="psql -h my_db.com -d my_db -U admin"
$ psql_my_db -f sql/cache_hit_rate.sql

You can also copy/paste any of the SQL in the sql/ directory and run with the Postgres client of your choice.

• Returns all running autovacuums operations

• Returns autovacuum analyze stats for each table

• Returns the approximate bloat from dead tuples for each table
• This bloat can also be index bloat

• Returns the distribution of shared buffers used for each table
• Requires the pg_buffercache extension
• Includes the total bytes of a table in shared buffers, the percentage of shared buffers a table is using, and the percentage of a table the exists in shared buffers

• Returns the cache hit rate for indices and tables
• This is the rate of queries that only hit in-memory shared buffers rather than having to fetch from disk
• Note that a queries that are cache misses in Postgres's shared buffers may still hit the in-memory OS page cache, so a miss not technically go all the way to the disk
• Both of these rates should be 99+% ideally

• Returns the index hit rate for each table
• This rate represents the percentage of queries that utilize 1 or more indices when querying a table
• These rates should be 99+% ideally

• Returns the size of each index in bytes

• Returns the table-specific settings of each table.

• Returns the size of each table in bytes
• Does not include size of the tables' indices

• Returns size of each table in bytes including all indices

• Returns indices that are rarely used
• Note that sometimes the query optimizer will elect to avoid using indices for tables with a very small number of rows because it can be more efficient

• Returns autovacuum stats for each table

Pull requests for bug fixes, improvements, or new SQL are always welcome!