Month: November 2022

Below is a script to check queries that are running for more than 15 minutes.

SELECT datname,query,state,query_start,
now() - query_start AS query_time
FROM pg_stat_activity
WHERE (now() - query_start) > interval '15 minutes';

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter

Here is a script to get the size of all your PostgreSQL databases.

SELECT 
  d.datname AS Name, 
  pg_catalog.pg_get_userbyid(d.datdba) AS Owner, 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(
    pg_catalog.pg_database_size(d.datname)
  ) ELSE 'No Access' END AS SIZE 
FROM 
  pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END DESC;

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter

Here is a script to get the top 20 biggest tables

SELECT 
  nspname || '.' || relname AS "relation", 
  pg_size_pretty(
    pg_relation_size(C.oid)
  ) AS "size" 
FROM 
  pg_class C 
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE 
  nspname NOT IN (
    'pg_catalog', 'information_schema', 
    'pg_toast'
  ) 
ORDER BY 
  pg_relation_size(C.oid) DESC 
LIMIT 
  20;

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter

Here is a script that checks current active sessions in PostgreSQL

SELECT 
  datname, 
  usename, 
  application_name, 
  now() - backend_start AS "Session duration", 
  pid, 
  query 
FROM 
  pg_stat_activity 
WHERE 
  state = 'active' 
ORDER BY 
  "Session duration" DESC;

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter