All Projects → lob → Pg_insights

lob / Pg_insights

Licence: mit
A collection of convenient SQL for monitoring Postgres database health.

Projects that are alternatives of or similar to Pg insights

Pgmetrics
Collect and display information and stats from a running PostgreSQL server
Stars: ✭ 612 (+141.9%)
Mutual labels:  database, postgres, monitoring
Awesome Db Tools
Everything that makes working with databases easier
Stars: ✭ 479 (+89.33%)
Mutual labels:  database-management, database, monitoring
Questdb
An open source SQL database designed to process time series data, faster
Stars: ✭ 7,544 (+2881.82%)
Mutual labels:  database, postgres, monitoring
Postgres Operator
Production PostgreSQL for Kubernetes, from high availability Postgres clusters to full-scale database-as-a-service.
Stars: ✭ 2,166 (+756.13%)
Mutual labels:  database-management, database, postgres
Postgres
🐘 Run PostgreSQL in Kubernetes
Stars: ✭ 205 (-18.97%)
Mutual labels:  database, postgres
Migrate
Database migrations. CLI and Golang library.
Stars: ✭ 2,315 (+815.02%)
Mutual labels:  database, postgres
Temboard
PostgreSQL Remote Control
Stars: ✭ 218 (-13.83%)
Mutual labels:  database, monitoring
Liquibase
Main Liquibase Source
Stars: ✭ 2,910 (+1050.2%)
Mutual labels:  database-management, database
Condenser
Condenser is a database subsetting tool
Stars: ✭ 189 (-25.3%)
Mutual labels:  database, postgres
Clear
Advanced ORM between postgreSQL and Crystal
Stars: ✭ 220 (-13.04%)
Mutual labels:  database, postgres
Tdengine
An open-source big data platform designed and optimized for the Internet of Things (IoT).
Stars: ✭ 17,434 (+6790.91%)
Mutual labels:  database, monitoring
Npgsql
Npgsql is the .NET data provider for PostgreSQL.
Stars: ✭ 2,415 (+854.55%)
Mutual labels:  database, postgres
Obevo
Obevo is a database deployment tool that handles enterprise scale schemas and complexity
Stars: ✭ 192 (-24.11%)
Mutual labels:  database-management, database
Omnidb
Web tool for database management
Stars: ✭ 2,709 (+970.75%)
Mutual labels:  database-management, database
Simpledb
UC Berkeley's Database class CS186: Implement A Simple Database Management System
Stars: ✭ 189 (-25.3%)
Mutual labels:  database-management, database
Fluentmigrator
Fluent migrations framework for .NET
Stars: ✭ 2,636 (+941.9%)
Mutual labels:  database, postgres
Flask Boilerplate
Simple flask boilerplate with Postgres, Docker, and Heroku/Zeit now
Stars: ✭ 251 (-0.79%)
Mutual labels:  database, postgres
Massive Js
A data mapper for Node.js and PostgreSQL.
Stars: ✭ 2,521 (+896.44%)
Mutual labels:  database, postgres
React Agent
Client and server-side state management library
Stars: ✭ 235 (-7.11%)
Mutual labels:  database-management, database
Rpostgres
A DBI-compliant interface to PostgreSQL
Stars: ✭ 245 (-3.16%)
Mutual labels:  database, postgres

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!

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].