Showing content from https://github.com/lob/pg_insights below:
lob/pg_insights: A collection of convenient SQL for monitoring Postgres database health.
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.
- Resets pg_stats statistics tables.
- 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 total size of all TOAST data in each table in bytes.
- 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!
RetroSearch is an open source project built by @garambo
| Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4