Hello to all the readers!
Find size of Postgresql database
One of my database was growing in size thereby consuming the disk. I got to know which database was taking how much of the disk space. And here is how to find out the size of each PostgreSQL database.
SELECT pg_size_pretty(pg_database_size('analytics'));
pg_size_pretty
----------------
82 GB
(1 row)
The above SQL query just finds out the size of specific database and prints the size in MBs.
But I wanted to see size of all the databases present. Therefore I have to run the following SQL query for it.
SELECT datname, pg_size_pretty(pg_database_size(datname)), pg_database_size(datname) FROM pg_database ORDER By 3 DESC;
datname | pg_size_pretty | pg_database_size
-------------+----------------+------------------
analytics | 82 GB | 88474907295
ops | 9293 kB | 9515679
postgres | 7741 kB | 7926431
template1 | 7725 kB | 7910047
template0 | 7577 kB | 7758339
(5 rows)
Find size of Postgresql table
Next I wanted to see how much disk space each table inside analytics database was consuming. For that I have to run the following SQL query.
SELECT pg_size_pretty(pg_relation_size('metric'));
pg_size_pretty
----------------
12 GB
(1 row)
This way we can find the size of a specific table. But this excludes the size of indexes and other objects associated with the table. For that.
SELECT pg_size_pretty(pg_total_relation_size('metric'));
pg_size_pretty
----------------
59 GB
(1 row)
Now I wanted to actually see the total size of all the tables inside analytics database including indexes and other objects.
table_name | pg_size_pretty | pg_total_relation_size
------------------------------------+----------------+------
metric | 59 GB | 62955061248
attrib | 19 GB | 20479025152
user_agent | 2616 MB | 2743238656
ip | 880 MB | 922468352
-- snip --