PGH

PGH is a CLI tool to help you manage your PostgreSQL database. It provides a list of utility commands to help you keep track of what's going on.

pgh $DATABASE_URL total_table_size
+-----------------------------+------------+
| name                        | size       |
|-----------------------------+------------|
| posts                       | 99 GB      |
| media                       | 99 GB      |
| comments                    | 11 GB      |
| users                       | 4511 MB    |
| oauth_access_tokens         | 4359 MB    |
| followers                   | 3403 MB    |
| devices                     | 2645 MB    |
| notifications               | 1821 MB    |
+-----------------------------+------------+

Example calculates the size of each table including indexes.

Instalation

pip install pgh

Usage

pgh DATABASE_URL COMMAND

Where DATABASE_URL should be a valid Postgres connection URI with the format:

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

Example:

pgh postgres://andre@localhost/test index_sizes

+---------------------------+---------+
| name                      | size    |
|---------------------------+---------|
| h_table_id_index          | 4096 MB |
| b_table_id_index          | 3873 MB |
+---------------------------+---------+

Commands

Command	Description
bloat	show table and index bloat in your database ordered by most wasteful
blocking	display queries holding locks other queries are waiting to be released
cache_hit	calculates your cache hit rate (effective databases are at 99% and up)
calls	show 10 most frequently called queries
index_size	show the size of indexes, descending by size
index_usage	calculates your index hit rate (effective databases are at 99% and up)
locks	display queries with active locks
long_running_queries	show all queries longer than five minutes by descending duration
outliers	show 10 queries that have longest execution time in aggregate
ps	view active queries with execution time
records_rank	show all tables and the number of rows in each ordered by number of rows descending
seq_scans	show the count of sequential scans by table descending by order
table_size	show the size of the tables (excluding indexes), descending by size
total_table_size	show the size of the tables (including indexes), descending by size
unused_indexes	show unused and almost unused indexes

Roadmap

• Integrate with AWS to to get the connection string from RDS (something like pgh --rds command);
• Integrate with Heroku API to get the connection string (something like pgh --heroku command);
• Implement pull command to copy data from a remote database to a target;
• • Implement diagnose command to generate a report of the general health of the database;
• Support connection parameters as specified here.

Acknowledgements

This tool is heavily based on the command tools built by Heroku. A lot of the commands and database queries present here are either inspired or directly taken from commands and database queries from heroku cli and heroku pg extras.