Find PostgreSQL database size
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/