Purpose of Query
The queries on this page provide the size of schemas and temporary space on a PostgreSQL database.
Applicable CAST Version
Release | Yes/No |
---|---|
8.3.x | |
8.2.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
CSS |
Query for CSS
Schema Size:
SELECT pg_catalog.pg_namespace.nspname, pg_size_pretty(SUM(pg_relation_size(pg_catalog.pg_class.oid))::BIGINT) FROM pg_catalog.pg_class INNER JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid GROUP BY pg_catalog.pg_namespace.nspname
Query result example
"c802_XXXX";"1414 MB"
"c736_XXXX";"162 MB"
"c736_XXXX";"123 MB"
Query result interpretation
The first column will be the schema name. The second column is the size of the schema.
Query for CSS
Schema Size:
with SchemaSize as ( select ps.nspname as schema_name, sum(pg_relation_size(pc.oid)) as total_size from pg_class pc join pg_catalog.pg_namespace ps on ps.oid = pc.relnamespace group by ps.nspname ) select ss.schema_name, pg_size_pretty(ss.total_size) from SchemaSize ss order by ss.total_size desc limit 10;
Do Note -
You can replace the limit 10 with another value, or even remove it to get the whole list of schemas.
The query may take some time to complete because it scans the whole server.
Query result example
Query result interpretation
The first column will be the schema name. The second column is the size of the schema.
Query for CSS
Temporary Data Size:
SELECT nspname, pg_size_pretty(SUM(pg_relation_size(pg_catalog.pg_class.oid))::BIGINT) FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace =pg_namespace.oid WHERE pg_is_other_temp_schema(relnamespace) GROUP BY nspname
Query result example
"pg_temp_6";"40 kB"
"pg_toast_temp_6";"8192 bytes"
"pg_toast_temp_6";"8192 bytes"
Query result interpretation
The first column will be the temporary schema name. The second column is the size of the schema. This query may produce no results if no temporary space is currently being used.
Notes/comments
Ticket # 5541
Related Pages