SQLize Online / PHPize Online  /  SQLtest Online

CREATE OR REPLACE VIEW v_used_size_per_user AS ( WITH used_per_schema_user AS ( SELECT pg_namespace.nspname as schema_name, pg_class.relname as table_name, pg_user.usename as table_owner, pg_size_pretty(pg_relation_size(pg_class.oid)) as table_size, SUM(pg_relation_size(pg_class.oid)) OVER (PARTITION BY pg_namespace.nspname, pg_user.usename) as used_per_schema_user_total_size, SUM(pg_relation_size(pg_class.oid)) OVER (PARTITION BY pg_user.usename) as used_user_total_size FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid JOIN pg_user ON pg_class.relowner = pg_user.usesysid ) SELECT table_owner, schema_name, table_name, table_size, pg_size_pretty(used_per_schema_user_total_size) as used_per_schema_user_total_size, pg_size_pretty(used_user_total_size) as used_user_total_size FROM used_per_schema_user ); SELECT table_owner, table_name, table_size, schema_name, used_per_schema_user_total_size, used_user_total_size FROM v_used_size_per_user ORDER BY pg_size_bytes(used_user_total_size) DESC, table_owner, pg_size_bytes(used_per_schema_user_total_size) DESC, schema_name, pg_size_bytes(table_size) DESC, table_name;