Postgres usefull query

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