Pretty epic psql config

.psqlrc

-- Be quiet while setting these values
\set QUIET 1

-- Show time of all queries
-- \timing

-- Colors appear entirely broken with command-completion. Leaving commented for now.
/*\set PROMPT1 '%[%033[0;31m%]%n%[%033[0;37m%]@%[%033[0;33m%]%m %[%033[0;34m%]%~ %[%033[1;31%# m%]❯%[%033[1;33m%]❯%[%033[1;32m%]❯ %033[0m'*/
\set PROMPT1 '%n@%m %~ ❯❯❯ '
\set PROMPT2 '%R> '

-- Set client encoding
\encoding unicode

-- Ignore errors from entered commands, not from scripts
-- \set ON_ERROR_ROLLBACK interactive

-- Be verbose
\set VERBOSITY verbose

-- "the expanded mode is used whenever the query output is wider than the
-- screen, otherwise the regular mode is used"
\x auto
\pset format wrapped

-- History files are cluster/user/db-specific
\set PGCLUSTER `echo $PGCLUSTER | tr '/' '-'`
\set HISTFILE ~/.psql_history- :PGCLUSTER - :USER -:DBNAME
-- Infinite history?
\set HISTSIZE
\set HISTCONTROL ignoredups

-- Show actual SQL executed by backslash commands
-- \set ECHO_HIDDEN ON

-- Autocomplete keywords (like SELECT) in upper-case, honoring whatever case
-- you starting in: http://robots.thoughtbot.com/an-explained-psqlrc
\set COMP_KEYWORD_CASE preserve-upper

\pset border 2
\pset null ∅
\pset linestyle unicode

\unset QUIET

-- From https://github.com/AF83/dotfiles/blob/9a9b3dbe64291ecfa0fe3d0a16302cfbf6592a39/.psqlrc

-- \echo '\nCurrent Host Server Date Time : '`date` '\n'
-- 
-- \echo 'Administrative queries:\n'
-- 
-- \echo '\t:activity\t-- Server activity'
-- \echo '\t:bloat\t\t-- show table and index bloat in your database ordered by most wasteful'
-- \echo '\t:blocking\t-- display queries holding locks other queries are waiting to be released'
-- \echo '\t:cache_hit\t-- calculates your cache hit rate (effective databases are at 99% and up)'
-- \echo '\t:conninfo\t-- Server connections'
-- \echo '\t:dbsize\t\t-- Database Size'
-- \echo '\t:index_size\t-- show the size of the indexes, descending by size'
-- \echo '\t:index_usage\t-- calculates your index hit rate (effective databases are at 99% and up)'
-- \echo '\t:locks\t\t-- Lock info'
-- \echo '\t:locks\t\t\t-- display queries with active locks'
-- \echo '\t:long_running_queries\t-- show all queries taking longer than five minutes ordered by duration'
-- \echo '\t:ps\t\t\t-- view active queries with execution time'
-- \echo '\t:seq_scans\t\t--show the count of seq_scans by table descending by order'
-- \echo '\t:settings\t-- Server Settings'
-- \echo '\t:tablesize\t-- Tables Size'
-- \echo '\t:total_index_size\t-- show the total size of the indexes in MB'
-- \echo '\t:unused_indexes\t\t-- Show unused and almost unused indexes, ordered by their size relative'
-- \echo '\t\t\t\t   to the number of index scans. Exclude indexes of very small tables (< 5 pages),'
-- \echo '\t\t\t\t   where the planner will almost invariably select a sequential scan, but may not'
-- \echo '\t\t\t\t   in the future as the table grows.'
-- \echo '\t:uptime\t\t-- Server uptime'
-- \echo '\t:uselesscol\t-- Useless columns (must be run as superuser)'
-- \echo '\t:waits\t\t-- Waiting queries'
-- \echo '\n'
-- 
-- \echo 'Development queries:\n'
-- 
-- \echo '\t:show_slow_queries\t-- Show slow queries (requires pg_stat_statements)'
-- \echo '\t:sp\t\t\t-- Current Search Path'
-- \echo '\t:clear\t\t\t-- Clear screen'
-- \echo '\t:ll\t\t\t-- List\n'
-- 
-- \echo 'Help:\n'
-- 
-- \echo '\t:menu\t\t-- This help menu'
-- \echo '\t\\h\t\t-- Help with SQL commands'
-- \echo '\t\\?\t\t-- Help with psql commands\n'

-- Administration queries

\set menu '\\i ~/.psqlrc'

\set settings 'select name, setting,unit,context from pg_settings;'

\set locks  'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;'

\set conninfo 'select usename, count(*) from pg_stat_activity group by usename;'

\set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, query, state from pg_stat_activity;'

\set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'

\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

\set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_relation_size(C.oid) DESC LIMIT 40;'

\set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'

-- Taken from https://github.com/heroku/heroku-pg-extras
-- via https://github.com/dlamotte/dotfiles/blob/master/psqlrc
\set bloat 'SELECT tablename as table_name, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat, CASE WHEN relpages < otta THEN ''0'' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste, iname as index_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat, CASE WHEN ipages < iotta THEN ''0'' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,''?'') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting(''block_size'')::numeric) AS bs, CASE WHEN substring(v,12,3) IN (''8.0'',''8.1'',''8.2'') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1,2,3,4,5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> ''information_schema'' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END DESC;'
\set blocking 'select bl.pid as blocked_pid, ka.query as blocking_statement, now() - ka.query_start as blocking_duration, kl.pid as blocking_pid, a.query as blocked_statement, now() - a.query_start as blocked_duration from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on bl.pid = a.pid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on kl.pid = ka.pid on bl.transactionid = kl.transactionid and bl.pid != kl.pid where not bl.granted;'
\set cache_hit 'SELECT ''index hit rate'' as name, (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) as ratio FROM pg_statio_user_indexes union all SELECT ''cache hit rate'' as name, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;'
\set index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0 GROUP BY relname ORDER BY sum(relpages) DESC;'
\set index_usage 'SELECT relname, CASE idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'
\set locks 'select pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, substr(pg_stat_activity.query,1,30) as query_snippet, age(now(),pg_stat_activity.query_start) as "age" from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_stat_activity.query <> ''<insufficient privilege>'' and pg_locks.pid=pg_stat_activity.pid and pg_locks.mode = ''ExclusiveLock'' order by query_start;'
\set long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS query FROM pg_stat_activity WHERE pg_stat_activity.query <> ''''::text AND now() - pg_stat_activity.query_start > interval ''5 minutes'' ORDER BY now() - pg_stat_activity.query_start DESC;'
\set ps 'select pid, application_name as source, age(now(),query_start) as running_for, waiting, query as query from pg_stat_activity where query <> ''<insufficient privilege>'' AND state <> ''idle'' and pid <> pg_backend_pid() order by 3 desc;'
\set seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
\set total_index_size 'SELECT pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0;'
\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
--

-- Development queries:

\set sp 'SHOW search_path;'
\set clear '\\! clear;'
\set ll '\\! ls -lrt;'
\set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;'