• Dba

Useful Queries

* To see which postgres transactions are blocked on which postgres transactions:

select distinct a.procpid as procpid1, a.current_query as current_query1, 'is waiting on' as "is waiting on", d.procpid, d.current_query
from pg_stat_activity a
join pg_locks b on (a.procpid = b.pid)
join pg_locks c on (b.relation = c.relation)
join pg_stat_activity d on (c.pid = d.procpid)
where a.waiting = 't'
and b.granted = 'f' and c.granted = 't';

* To list which db tables are using TOAST tables, and how many of them:

SELECT
    c.nspname, a.relname AS tablename, a.reltoastrelid::regclass AS toastname, b.relpages as usedpages
FROM pg_class a
JOIN pg_class b
    ON (a.reltoastrelid = b.oid)
JOIN pg_namespace c 
    ON (a.relnamespace = c.oid) 
WHERE a.reltoastrelid != 0 
ORDER BY b.relpages DESC;

* To query the db to find out if a specific db table is using TOAST tables:

SELECT
    c.nspname, a.relname AS tablename, a.reltoastrelid::regclass AS toastname, b.relpages as usedpages
FROM pg_class a
JOIN pg_class b
    ON (a.reltoastrelid = b.oid)
JOIN pg_namespace c 
    ON (a.relnamespace = c.oid) 
WHERE a.reltoastrelid != 0 
    AND c.nspname || '.' || a.relname = '<your schemaname.tablename here>'
ORDER BY b.relpages DESC;