postgresql-handy-queries-to-monitor-write-intesive-operations.sql

--  get list queries and their running time
SELECT pid,query, age(clock_timestamp(), query_start), usename 
FROM pg_stat_activity
WHERE datname = 'database_name' 
ORDER BY query_start desc;

-- get rough count of records in table without actually running the count(*) query 
-- thanks to: https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'table name';

-- find the pids of queries that is locking a table
-- if it is a paritioned table use relkind = 'p'
 SELECT pid
 FROM pg_locks l
 JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
 WHERE t.relname = 'table name';


-- find the locks for a parituclar pid
SELECT page,virtualtransaction,pid,mode,granted 
FROM pg_locks  WHERE pid = 16016;


-- find queries that could block the execution of another query
SELECT pg_blocking_pids(<pid of the query that is blocked>);

-- get the pid of the current psql session
SELECT pg_backend_pid();

-- kill a query using it's pid
SELECT pg_cancel_backend(<pid to be killed>);
-- if the above didn't work then try
SELECT pg_terminate_backend(<pid to be killed>);

-- size of every table
SELECT schemaname AS table_schema,
       relname AS table_name,
       pg_size_pretty(pg_relation_size(relid)) AS data_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

-- size of database
SELECT pg_database_size('databaseName');

-- delete duplicates from table without unique identifier
DELETE   FROM table_name T1
  USING       table_name T2
WHERE  T1.ctid    < T2.ctid       -- delete the "older" ones
  AND  T1.updated_at    = T2.updated_at       -- list columns that define duplicates
  AND T1.name = T2.name;

-- thanks to laurenz-albe and rgreenjr https://stackoverflow.com/users/6464308/laurenz-albe and https://gist.github.com/rgreenjr/3637525