I want to see total database size and data occupied size in postgres. I need query (don't want any third application to check) to check that because based on the query i will develop some report. Thanks in advance.
3
- 1select pg_database_size('<db_name>'); --- >in bytes. Also, select pg_size_pretty(pg_database_size('<db_name>')); -->- in text with unitsrajorshi– rajorshi2022-01-25 04:47:53 +00:00Commented Jan 25, 2022 at 4:47
- 1that query used to check total database size or data occupied size? . thanks for answerGobinath– Gobinath2022-01-25 04:51:04 +00:00Commented Jan 25, 2022 at 4:51
- That query will give you overall size of the database which includes the relation sizes plus the overhead like dead rows in relations and deleted index leaf block entries. To get the size minus the overhead , there are multiple ways. The easiest is to use extension pgstattuple for all relations. pgstatindex for all indexes.rajorshi– rajorshi2022-01-25 05:05:29 +00:00Commented Jan 25, 2022 at 5:05
Add a comment |
1 Answer
THe size of database and table in postgres can be found using following command.
To get the databse size:
SELECT pg_size_pretty( pg_database_size('dbname') ); To get the table Size:
SELECT pg_size_pretty( pg_total_relation_size('tablename') ); The below command used to get the list of all databases with their respective size.
SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as Size FROM pg_catalog.pg_database d order by CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END desc LIMIT 20; 