Postgresql: Obtener el tamaño de base de datos, esquemas y tablas

 Algunas consultas para obtener el tamño de lo que ocupan tablas, esquemas y bases de datos en postgres:

 
-- Tamaño de las bases de datos
SELECT pg_database.datname,
       pg_shadow.usename AS owner,
       pg_database_size(pg_database.datname) AS size,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS pretty_size
FROM pg_database                                        
JOIN pg_shadow ON pg_database.datdba = pg_shadow.usesysid;

-- Tamaño de los esquemas
SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as porcentaje
FROM (                                                                     
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_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
GROUP BY schema_name
ORDER BY 1
;



-- Listado de tablas de todos los esquemas por tamaño
select 
    schemaname,
    relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname ||'.'||relname)) as table_size ,
	pg_relation_size(schemaname ||'.'||relname)
from 
(
  select 
      schemaname, 
      relname 
  from pg_stat_user_tables order by relname 
) t
order by 4 desc;


SELECT pg_database.datname,
       pg_shadow.usename AS owner,
       pg_database_size(pg_database.datname) AS size,
   pg_size_pretty(pg_database_size(pg_database.datname)) AS pretty_size
FROM pg_database                                        
JOIN pg_shadow ON pg_database.datdba = pg_shadow.usesysid;