psqlrc

\set QUIET 1

-- Customize prompt
-- PROMPT2 is printed when the prompt expects more input, like when you type SELECT * FROM<enter>
\set PROMPT2 '[more] %R > '

-- Display how long commands take
\timing on

-- Printing options
\pset pager off
-- ascii, old-ascii, unicode
\pset linestyle unicode
-- 0 (no border), 1 (internal dividing lines), 2 (table frame)
\pset border 2
\pset null ¤
-- Expanded output (on|off|auto)
\x auto

-- Configure history to ignore sequential duplicate commands
\set HISTCONTROL ignoredups

-- Autocomplete keywords (like SELECT) in upper-case, even if you started typing them in lower case.
\set COMP_KEYWORD_CASE upper

-- Query aliases
-- system
\set version 'SELECT version();'
\set db_size 'SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database WHERE datname !~ \'postgres|template\' ORDER BY db_size;'
\set extensions 'SELECT * FROM pg_available_extensions ORDER BY installed_version;'
\set extensions_heroku 'SELECT * FROM pg_available_extensions WHERE name IN (SELECT unnest(string_to_array(current_setting(\'extwlist.extensions\'), \',\'))) ORDER BY installed_version;'

-- connections
\set conn 'SELECT count(*), state FROM pg_stat_activity GROUP BY state;'
\set ps 'SELECT pid, backend_start, application_name, wait_event, SUBSTRING(query, 0, 60) || \' ...\'  AS query, state FROM pg_stat_activity ORDER BY backend_start;'
\set ps_active 'SELECT pid, backend_start, now() - query_start AS duration, application_name, SUBSTRING(query, 0, 100) || \' ...\' AS query, state FROM pg_stat_activity WHERE state != \'idle\' ORDER BY backend_start;'
\set ps_apps 'SELECT (CASE WHEN application_name = \'\' THEN wait_event ELSE application_name END) AS application, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2;'

-- diagnostics
\set slow_historical 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls/1000/60) as average_time, calls, SUBSTRING(query, 0, 100) || \' ...\'  AS query FROM pg_stat_statements WHERE calls > 100 ORDER BY 2 DESC LIMIT 10;'
\set slow_active 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE query <> ''''::text AND state <> \'idle\' AND now() - query_start > interval ''5 minutes'' ORDER BY now() - query_start DESC;'
\set blocking 'SELECT kl.pid AS blocking_pid, ka.query AS blocking_query, now() - ka.query_start AS blocking_duration, bl.pid AS blocked_pid, a.query AS blocked_query, 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 locks 'SELECT pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, SUBSTRING(pg_stat_activity.query, 0, 80) || \' ...\' AS query, 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\' AND pg_stat_activity.pid <> pg_backend_pid() ORDER BY query_start;'
\set bloat 'WITH constants AS (SELECT current_setting(\'block_size\')::numeric AS bs, 23 AS hdr, 4 AS ma), bloat_info AS (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, constants GROUP BY 1,2,3,4,5) AS foo), table_bloat AS (SELECT schemaname, tablename, 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 FROM bloat_info JOIN pg_class cc ON cc.relname = bloat_info.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> \'information_schema\'), index_bloat AS (SELECT schemaname, tablename, bs, 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 bloat_info JOIN pg_class cc ON cc.relname = bloat_info.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> \'information_schema\' JOIN pg_index i ON indrelid = cc.oid JOIN pg_class c2 ON c2.oid = i.indexrelid) SELECT type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste FROM (SELECT \'table\' as type, schemaname, tablename as object_name, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat, CASE WHEN relpages < otta THEN \'0\' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste FROM table_bloat UNION SELECT \'index\' as type, schemaname, tablename || \'::\' || iname as object_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat, CASE WHEN ipages < iotta THEN \'0\' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste FROM index_bloat) bloat_summary WHERE raw_waste > 10000000 ORDER BY raw_waste DESC, bloat DESC;'
\set cache_hit 'SELECT \'index hit rate\' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT \'table hit rate\' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'
\set vacuum_stats 'WITH table_opts AS (SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, \'\') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), vacuum_settings AS (SELECT oid, relname, nspname, CASE WHEN relopts LIKE \'%autovacuum_vacuum_threshold%\' THEN substring(relopts, \'.*autovacuum_vacuum_threshold=([0-9.]+).*\')::integer ELSE current_setting(\'autovacuum_vacuum_threshold\')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE \'%autovacuum_vacuum_scale_factor%\' THEN substring(relopts, \'.*autovacuum_vacuum_scale_factor=([0-9.]+).*\')::real ELSE current_setting(\'autovacuum_vacuum_scale_factor\')::real END AS autovacuum_vacuum_scale_factor FROM table_opts) SELECT vacuum_settings.nspname AS schema, vacuum_settings.relname AS table, to_char(psut.last_vacuum, \'YYYY-MM-DD HH24:MI\') AS last_vacuum, to_char(psut.last_autovacuum, \'YYYY-MM-DD HH24:MI\') AS last_autovacuum, to_char(pg_class.reltuples, \'9G999G999G999\') AS rowcount, to_char(psut.n_dead_tup, \'9G999G999G999\') AS dead_rowcount, to_char(autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), \'9G999G999G999\') AS autovacuum_threshold, CASE WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup THEN \'yes\' END AS expect_autovacuum FROM pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid ORDER BY 1;'

-- tables and indexes
\set total_table_size 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS table_size, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (\'pg_catalog\', \'information_schema\') AND n.nspname !~ \'^pg_toast\' AND c.relkind=\'r\' ORDER BY pg_total_relation_size(c.oid) DESC;'
\set table_size 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS table_size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (\'pg_catalog\', \'information_schema\') AND n.nspname !~ \'^pg_toast\' AND c.relkind=\'r\' ORDER BY pg_table_size(c.oid) DESC;'
\set table_indexes_size 'SELECT c.relname AS table, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (\'pg_catalog\', \'information_schema\') AND n.nspname !~ \'^pg_toast\' AND c.relkind=\'r\' ORDER BY pg_indexes_size(c.oid) DESC;'
\set index_size 'SELECT c.relname AS name, pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS index_size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (\'pg_catalog\', \'information_schema\') AND n.nspname !~ \'^pg_toast\' AND c.relkind=\'i\' GROUP BY c.relname ORDER BY sum(c.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 index_usage_detailed 'SELECT * FROM (SELECT stat.relname AS table, stai.indexrelname AS index, CASE stai.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stai.idx_scan / (stat.seq_scan + stai.idx_scan))::text || ''%'' END hit_rate, ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) AS k ORDER BY k) AS cols, stat.n_live_tup rows_in_table FROM pg_stat_user_indexes AS stai JOIN pg_stat_user_tables AS stat ON stai.relid = stat.relid JOIN pg_index AS idx ON (idx.indexrelid = stai.indexrelid)) AS sub_inner ORDER BY rows_in_table DESC, hit_rate ASC;'
\set missing_indexes 'SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan > 0 THEN ''Missing Index?'' ELSE ''OK'' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname=''public'' AND pg_relation_size(relname::regclass) > 80000 AND seq_scan-idx_scan > 0 ORDER BY too_much_seq DESC;'
\set seq_scans 'SELECT relname AS name, seq_scan AS count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
\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;'

-- rails
\set delayed_job 'SELECT attempts, left(handler,140) AS handler, created_at, failed_at, locked_by FROM delayed_jobs ORDER BY created_at;'

\echo 'Type :version to see the PostgreSQL version, :ps to see running processes \n'

\unset QUIET