Know the size of a postgresql schema

Sometimes we want to know the current size of a certain schema in our Database. For  that create the following function.

CREATE OR REPLACE FUNCTION pg_schema_getsize(text) returns bigint AS $$
SELECT sum(pg_relation_size(schemaname || ‘.’ || tablename))::bigint FROM pg_tables WHERE schemaname = $1
$$ LANGUAGE sql;

To execute this:
select pg_size_pretty(pg_schema_getsize('backup_tables'));

pg_size_pretty
—————-
36 GB
(1 row)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s