lob/pg_insights

A collection of convenient SQL for monitoring Postgres database health.

pg_insights

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

How to Use

psql

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

Other

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

Scripts

active_autovacuums.sql (admin permission)

• Returns all running autovacuums operations.

analyze_stats.sql (read permission)

• Returns autovacuum analyze stats for each table.

bloat.sql (read permission)

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

buffer_cache_usage.sql (admin permission)

• 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.

cache_hit_rate.sql (read permission)

• 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.

index_hit_rate.sql (read permission)

• 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.

index_size.sql (read permission)

• Returns the size of each index in bytes.

reset_stats.sql (admin permission)

• Resets pg_stats statistics tables.

table_settings.sql (read permission)

• Returns the table-specific settings of each table.

table_size.sql (read permission)

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

table_size_with_indices.sql (read permission)

• Returns size of each table in bytes including all indices.

toast_size.sql (read permission)

• Returns total size of all TOAST data in each table in bytes.

unused_indices.sql (read permission)

• 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.

vacuum_stats.sql (read permission)

• Returns autovacuum stats for each table.

Contributing

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