Goal The schema size is the sum of the sizes of all tables in the schema.
Type Sofware measure (Numeric values (software measures) about the database)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH materialized_views AS (SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type, 1 AS weight
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid INNER JOIN pg_type ON
pg_attribute.atttypid=pg_type.oid
WHERE relkind = 'm' AND attnum>=1 AND  typrelid=0
UNION SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type, 
(SELECT Count(*) AS cnt FROM pg_class AS c INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.oid=pg_type.typrelid) AS weight
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid INNER JOIN pg_type ON
pg_attribute.atttypid=pg_type.oid
WHERE relkind = 'm' AND attnum>=1 AND  typrelid<>0),
materialized_views_summary AS (SELECT nspname, Sum(weight) AS ts
FROM materialized_views
GROUP BY nspname),

other_tables_summary AS (SELECT A.table_schema, 
SUM (CASE
WHEN A.data_type = 'USER-DEFINED'
AND B.relnatts>=0
THEN B.relnatts
ELSE 1
END) AS ts
FROM information_schema.columns A

LEFT JOIN (
SELECT n.nspname, c.relname, c.relnatts
FROM pg_class c
INNER JOIN pg_catalog.pg_authid u 
ON u.oid = c.relowner
INNER JOIN pg_catalog.pg_namespace n 
ON n.oid = c.relnamespace
WHERE c.relkind IN ('c','r')
AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres')
) B
ON A.udt_name = B.relname
AND A.udt_schema = B.nspname
AND A.data_type = 'USER-DEFINED'

INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name

INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name

WHERE (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
GROUP BY A.table_schema)

SELECT table_schema, Sum(ts) AS ts
FROM (SELECT table_schema, ts
FROM other_tables_summary
UNION SELECT nspname, ts
FROM materialized_views_summary) AS foo
GROUP BY table_schema
ORDER BY ts DESC, table_schema;

Collections

This query belongs to the following collections:

NameDescription
Find quick numeric overview of the databaseQueries that return numeric values showing mostly the number of different types of database objects in the database
Categories

This query is classified under the following categories:

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.

Further reading and related materials:

Reference
Piattini, M., Calero, C., Sahraoui, H. A., & Lounis, H. (2001). Object-relational database metrics. L'Objet, 7(4), 477-496.