PostgreSQL is an object-relational database management system (ORDBMS). It’s a free and open-source tool available under BSD licence.

Thanks to some built-in functions, you can get several information regarding database and/or table size. You will find below the main and most used examples.

Database size (in octal)

dbpg-# SELECT pg_database_size(‘db_1’);
pg_database_size
----------------
342733824
(1 line)

Database size (in human understandable format)

dbpg-# SELECT pg_size_pretty(pg_database_size(‘db_1’));
pg_size_pretty
--------------
327 MB
(1 line)

All databases size

dbpg-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
datname  | size
---------+-----
postgres | 3537 kB
db_1     | 327 MB
db_2     | 4732 kB
db_3     | 29 MB
db_4     | 168 MB
temp0    | 3928 kB
(6 lines)

Table size (excluding indexes and some auxiliary data)

dbpg-# SELECT pg_size_pretty(pg_relation_size(‘users’));
pg_size_pretty
--------------
64 kB
(1 line)

Table size (including all data)

dbpg-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
pg_size_pretty
--------------
152 kB
(1 line)

Find the largest table in database

dbpg-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
--------+---------
db_1    | 8732
(1 line)

 For the full documentation, please visit http://www.postgresql.org/