Convenient SQL for monitoring Postgres database health. This repository is inspired by commands from Heroku's pg_extras repository.
How to Use
You can run a script using
-f option. For example:
$ psql postgres -f sql/cache_hit_rate.sql
It also works with aliases you have setup with
$ 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.
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.
Pull requests for bug fixes, improvements, or new SQL are always welcome!