Queries when you have a postgresql based system

Queries when you have a postgresql based system March 24, 2025 by Eduardo Bellani

Are you managing/developing a PostgreSQL based application? Here are some scripts that might make your life easier dealing with your installation:

select
  schema_name,
  relname,
  pg_size_pretty(table_size)
  from
    (select
       pg_catalog.pg_namespace.nspname as schema_name,
       relname,
       pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
       from pg_catalog.pg_class
            join pg_catalog.pg_namespace on relnamespace = pg_catalog.pg_namespace.oid
    ) t
 where schema_name not like 'pg_%'
 order by table_size desc
 limit 5;

Code Snippet 1: Check the 5 largests tables (courtesy of Supabase's dashboard)

select * from cron.job_run_details order by start_time desc limit 5;

Code Snippet 2: Check the current running cron jobs

select act.query,
       act.datname,
       act.query_start,
       nspname as schema_name,
       relname as object_name,
       l.pid
  from pg_locks l
       join pg_class c on (relation = c.oid)
       join pg_namespace nsp on (c.relnamespace = nsp.oid)
       join pg_stat_activity act on (l.pid = act.pid)
 where l.pid in
       (select pid
          from pg_stat_activity
         where datname = current_database()
           and query != current_query())
 order by pid;

Code Snippet 3: See what is being locked by what (pg_terminate can unlock things)