Useful Postgres Admin Queries

postgres-queries.rst

Useful Postgres Admin Queries

• pg_stat_activity (currently executing queries):

  select * from pg_stat_activity where current_query not like '<%';
  

• pg_user (all database users):

  select * from pg_user;
  

• pg_database (all databases and their sizes):

  select datname,
         pg_size_pretty(pg_database_size(datname))
    from pg_database
   order by pg_database_size(datname) desc;
  

• pg_tables (all tables and their size, with/without indexes):

  select tablename,
         pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as size,
         pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
    from pg_tables
   where schemaname = 'MY_SCHEMA_NAME';
  

• pg_namespace (all schemas):

  select * from pg_namespace order by 1;