PostgreSQL – Size of a Database
In this article, we will look into the function that helps us to evaluate the size of a given database. The pg_database_size()
function is used to get the size of a database.
Syntax: select pg_database_size('database_name');
Now let’s list all the available database available on our server and find their sizes in our example using the below command:
\l
This gives us the following result:
Example 1:
Here we will query for the size of the dvdrental database in our server using the below command:
select pg_database_size('dvdrental');
Output:
To make the result readable, one can use the pg_size_pretty()
function. The pg_size_pretty() function takes the result of another function and format it using bytes, kB, MB, GB or TB as required. So the above output can be modified as below:
SELECT pg_size_pretty ( pg_database_size ('dvdrental') );
Output:
Example 2:
Here we will query for the size of the zoo database in our server using the below command:
SELECT pg_size_pretty ( pg_database_size ('zoo') );
Output:
Example 3:
Here we will query for the size of the sales2020 database in our server using the below command:
SELECT pg_size_pretty ( pg_database_size ('sales2020') );
Output:
Example 4:
Here we will query the size of every database in our current server using the below command:
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
Output: