Schema Size

From PostgreSQL wiki

Revision as of 10:36, 19 December 2012 by Hwerner (Talk | contribs)

Jump to: navigation, search

Administrative Snippets

Schema and Database Size

Works with PostgreSQL

Any version

Written in


Depends on


Author: Emanuel Calvo

Schema Size

This function provides a simple way to get the size of one schema. Notice that this script doesn't detect if a schema exists or not, in the worst case will return 0.

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

One way to execute:

general_base=# select pg_size_pretty(pg_schema_size('public'));
-[ RECORD 1 ]--+--------
pg_size_pretty | 4782 MB

You can play a little, obtaining not only the tables in one schema. You can apply filters adding a parameter plus a condition:

CREATE OR REPLACE FUNCTION pg_schema_size_filter(text, text) returns bigint AS $$
SELECT sum(pg_relation_size(schemaname || '.' || tablename)) FROM pg_tables WHERE schemaname = $1 AND tablename ~ $2
$$ LANGUAGE sql;

Warning : you may need to surround schemaname with double-quotes if you have uppercase chars in your schema name :

SELECT sum(pg_relation_size('"' || schemaname || '".' || tablename))::bigint FROM pg_tables WHERE schemaname = 'SMD';

Database Size

Sometimes we need to know fast the size of our database, if you are lazy, just compile this function in every database that you have and execute it!

SELECT pg_size_pretty(pg_database_size(current_database()));
$$ LANGUAGE sql;
Personal tools