Schema Size

Administrative Snippets

Schema and Database Size

Works with PostgreSQL

Any version

Written in

plpgsql

Depends on

Nothing

Author: Emanuel Calvo

Schema Size

This function provides a simple way to get the size of one schema. Note that this script returns 0 if the specified schema does not exist.

CREATE OR REPLACE FUNCTION pg_schema_size(text) returns bigint AS $$
select sum(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(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

Here is a modified version of the script that allows you to supply a case-sensitive regular expression to only consider a subset of table names within the schema:

CREATE OR REPLACE FUNCTION pg_schema_size_filter(text, text) returns bigint AS $$
select sum(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint from pg_tables where schemaname = $1 and tablename ~ $2
$$ language sql;

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!

CREATE OR REPLACE FUNCTION sizedb() RETURNS text AS $$
SELECT pg_size_pretty(pg_database_size(current_database()));
$$ LANGUAGE sql;