SQL Queries - Common SQL Queries - How to get the size of schemas and temporary space on PostgreSQL database

Purpose of Query

The queries on this page provide the size of schemas and temporary space on a PostgreSQL database.

Applicable CAST Version

Applicable RDBMS

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”

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