isaaclw

SELECT blocks.pid,
   (select query from pg_stat_activity where pid = blocks.pid) as blocked_query,
   blocks.blocking_pid,
   (select query from pg_stat_activity where pid = blocks.blocking_pid) AS blocking_query
FROM (
    SELECT pid, unnest(pg_blocking_pids(pid)) AS blocking_pid
    FROM pg_locks
    WHERE granted = false
) AS blocks;

isaaclw

select pid, now() - query_start, query
from pg_stat_activity
where state = 'active' and now() - query_start > '5min';

isaaclw

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes

isaaclw

select pid, query_start, state, query from pg_stat_activity where pid in (
    select pid from pg_locks where granted = true and relation in (
        select distinct relation from pg_locks where granted = false)
    );