Schema Size

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Created page with "{{SnippetInfo|Schema and Database Size|lang=plpgsql|category=Administrative}} Author: Emanuel Calvo === Schema Size === This function provides a simple way to get the size of …")
 
Line 8: Line 8:
  
 
<source lang="sql">
 
<source lang="sql">
CREATE OR REPLACE FUNCTION pg_schema_size(pschema text) RETURNS bigint AS $$
+
CREATE OR REPLACE FUNCTION pg_schema_size(text) returns bigint AS $$
DECLARE
+
select sum(pg_relation_size(schemaname || '.' || tablename))::bigint from pg_tables where schemaname = $1
  total_size bigint := 0;
+
$$ language sql;
  partial_size bigint := 0;
+
  table_ regclass;
+
BEGIN
+
 
+
  FOR table_ IN SELECT (schemaname || '.' || tablename)::regclass from pg_tables where schemaname = pschema
+
  LOOP
+
    SELECT pg_relation_size(table_) INTO partial_size;
+
    total_size := total_size + partial_size; 
+
  END LOOP;
+
 
+
  RETURN total_size;
+
END;
+
$$ LANGUAGE plpgsql;
+
 
</source>
 
</source>
 
  
  

Revision as of 15:47, 30 April 2011

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. 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


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;
Personal tools