postgres toolbox

tool.sql

-- find blocking PIDs
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

-- check vacuum stat
select * from pg_stat_progress_vacuum;

-- check running pg processes
select * from pg_stat_activity where datname = 'flowhostedwalletdb-production';

-- check specific process
select * from pg_stat_activity where pid = '239134';

-- kill process
select pg_cancel_backend(240123)

-- hard kill process
select pg_terminate_backend(240123)

-- check vacuum/analyze duration
SELECT pid, state, query, now() - query_start AS query_duration
FROM pg_stat_activity
WHERE query LIKE 'vacuum%' OR query LIKE 'ANALYZE%';

-- ceck running transactions order by start
SELECT pid, datname, usename, query_start, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start ASC;

-- check dead tuples (work for vacuum worker)
SELECT
    schemaname,
    relname AS table_name,
    n_dead_tup AS dead_row_count
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 0
ORDER BY
    n_dead_tup DESC;
    
SELECT
    SUM(n_dead_tup) AS total_dead_rows
FROM
    pg_stat_user_tables;