PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.
If you want to find the size of a database, in our example the sqview database, please tape:
sqview-# SELECT pg_database_size(‘sqview’);
The result in octal, for a pretty print we will use the pg_size_pretty function which converts the size in bytes to human understandable format.
sqview-# SELECT pg_size_pretty(pg_database_size(‘sqview’));
Well if we need to get the size of all databases
sqview-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
datname | size
postgres | 3537 kB
template1 | 3537 kB
template0 | 3480 kB
freedom | 25 MB
anakeen | 6081 kB
sqview | 106 MB
rt | 8201 kB
PostgreSQL can also give the size of a table ‘users’ like this
sqview-# SELECT pg_size_pretty(pg_relation_size(‘users’));
This value exclude indexes and some auxiliary data.
If you want to include them use pg_total_relation_size instead of pg_relation_size as shown below.
sqview-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
to find the largest table in the postgreSQL database.
sqview-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
sqview | 9666