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/