lob/pg_insights

README

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!

README

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!

lob/pg_insights(opens in a new tab)

README

pg_insights

How to Use

psql

Other

Scripts

active_autovacuums.sql (admin permission)

analyze_stats.sql (read permission)

bloat.sql (read permission)

buffer_cache_usage.sql (admin permission)

cache_hit_rate.sql (read permission)

index_hit_rate.sql (read permission)

index_size.sql (read permission)

reset_stats.sql (admin permission)

table_settings.sql (read permission)

table_size.sql (read permission)

table_size_with_indices.sql (read permission)

toast_size.sql (read permission)

unused_indices.sql (read permission)

vacuum_stats.sql (read permission)

Contributing

README

pg_insights

How to Use

psql

Other

Scripts

active_autovacuums.sql (admin permission)

analyze_stats.sql (read permission)

bloat.sql (read permission)

buffer_cache_usage.sql (admin permission)

cache_hit_rate.sql (read permission)

index_hit_rate.sql (read permission)

index_size.sql (read permission)

reset_stats.sql (admin permission)

table_settings.sql (read permission)

table_size.sql (read permission)

table_size_with_indices.sql (read permission)

toast_size.sql (read permission)

unused_indices.sql (read permission)

vacuum_stats.sql (read permission)

Contributing

`active_autovacuums.sql` (admin permission)

`analyze_stats.sql` (read permission)

`bloat.sql` (read permission)

`buffer_cache_usage.sql` (admin permission)

`cache_hit_rate.sql` (read permission)

`index_hit_rate.sql` (read permission)

`index_size.sql` (read permission)

`reset_stats.sql` (admin permission)

`table_settings.sql` (read permission)

`table_size.sql` (read permission)

`table_size_with_indices.sql` (read permission)

`toast_size.sql` (read permission)

`unused_indices.sql` (read permission)

`vacuum_stats.sql` (read permission)

`active_autovacuums.sql` (admin permission)

`analyze_stats.sql` (read permission)

`bloat.sql` (read permission)

`buffer_cache_usage.sql` (admin permission)

`cache_hit_rate.sql` (read permission)

`index_hit_rate.sql` (read permission)

`index_size.sql` (read permission)

`reset_stats.sql` (admin permission)

`table_settings.sql` (read permission)

`table_size.sql` (read permission)

`table_size_with_indices.sql` (read permission)

`toast_size.sql` (read permission)

`unused_indices.sql` (read permission)

`vacuum_stats.sql` (read permission)