PostgreSQL Useful Queries
If you are programmer or DB administrator you need some useful query like how much data occupied by one database or how much data is for single or multiple tables, Below are useful queries.
Open terminal
psql -d test #Login to your database which you want to know size.
SELECT pg_database_size(‘test’); # Check database size in Octal format.
SELECT pg_size_pretty(pg_database_size(‘test’)); # Check database size in actual format.
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; # Check all database size
SELECT pg_size_pretty(pg_relation_size(‘users’)); # Check particular table size
SELECT pg_size_pretty(pg_total_relation_size(‘users’)); #Check particular table size with relative table.
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1; #Find largest table
