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)