Skip to content

Instantly share code, notes, and snippets.

@MrHen

MrHen/db_sizes.sql

Last active July 3, 2019 16:01
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MrHen/c3b92050c4274b28f96ef16147d37978 to your computer and use it in GitHub Desktop.
Save MrHen/c3b92050c4274b28f96ef16147d37978 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres Debugging
Raw
db_sizes.sql
SELECT
pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
Raw
locks.sql
-- https://wiki.postgresql.org/wiki/Lock_Monitoring
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
Raw
queries.sql
SELECT
pid
, date_trunc('second', query_start::time) as start_time
, age(clock_timestamp(), query_start)
, usename
, application_name
, client_addr
, query
FROM pg_stat_activity
WHERE query != '<IDLE>'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);
SELECT * FROM pg_stat_activity;
Raw
table_sizes.sql
SELECT
schemaname,
relname as "table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables
-- WHERE relname = 'mutant_campaigns_campaign_334'
ORDER BY pg_total_relation_size(relid)
DESC
;
Raw
views.sql
SELECT
DISTINCT n.nspname as schema_name
, c.relname as view_name
FROM pg_depend d
JOIN pg_rewrite w on w.oid = d.objid
JOIN pg_class c on c.oid = w.ev_class
JOIN pg_namespace n on n.oid = c.relnamespace
WHERE d.refclassid = 'pg_class'::regclass
AND d.classid = 'pg_rewrite'::regclass
AND d.refobjid = 'schema_goes_here.table_goes_here'::regclass
AND c.oid <> 'schema_goes_here.table_goes_here'::regclass
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment