@
you're reading...
PostgreSQL

Get the PostgreSQL Database and Schema Size

To get the Database Size, execute this query:
postgres=# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;

datname | size
————————+———
template1 | 6329 kB
template0 | 6089 kB
postgres | 6449 kB
gcc_dashboard | 7649 kB
ep | 213 MB
ces_payroll | 2684 MB
l2s_acctg | 6720 MB
l2s_encrypted | 11 GB
ces_live_final20101205 | 2723 MB
l2s_cms | 5514 MB
aol | 10 MB
rims_db | 15 MB
ces_live_final | 83 GB
data_warehouse | 19 GB
(14 rows)

Sometimes we want to know the current size of certain schema in our Database. I have check PostgreSQL’s manual and have found this.

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
—————-
38 GB
(1 row)

Advertisements

Discussion

No comments yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s