In PostgreSQL, built-in functions like pg_database_size(), and pg_relation_size() are used to get the database and table size respectively. The pg_size_pretty() function can be used with the collaboration of the pg_database_size(), pg_relation_size() to present the database/table size in a human-readable format.

This post will present a thorough understanding of pg_database_size(), pg_relation_size(), and pg_size_pretty() functions with examples.

How to Find the Database Size Using pg_database_size?

Use the pg_database_size() function to get the Database size. The syntax of the pg_database_size() function will be as follows:

pg_database_size('database_name');

Example #1: How to Use the pg_database_size() function in PostgreSQL?

We already have a database named “example”. Let’s execute the below-given command to see the total size of the selected database:

SELECT pg_database_size('example');

The output shows that the pg_database_size() function successfully returned the size of the selected database.

Example #2: How to Use the pg_size_pretty() Function With the pg_database_size() Function?

The database size in the above-given example is not easily readable. Let’s use the pg_size_pretty() function to convert the resultant database size into human-readable format:

SELECT pg_size_pretty(pg_database_size('example'));

Now, the size is more understandable. This is how the pg_size_pretty() function assists us in formatting the database size.

Example #3: How to Fetch the Size of All Databases in Postgres?

Let’s execute the below statement to find the size of all the databases:

SELECT pg_database.datname, 
pg_database_size(pg_database.datname) AS size 
FROM pg_database;

In this example, we utilized the pg_database.datname, with the SELECT query to fetch/collect all the databases available in the server. Next, we conjugated them with pg_database_size() and AS SIZE to get the size of all databases. Following will be the output:

The output proved that the pg_database_size successfully fetched the sizes of all the databases. Let’s utilize pg_size_pretty() function to convert the resultant sizes into human-readable format:

SELECT pg_database.datname, 
pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
 FROM pg_database;

This is how you can fetch the size of all the databases using a single statement.

How to Find the Tables Size Using pg_relation_size?

Use the pg_relation_size() function to get the table size. The basic syntax of the pg_relation_size() function will be as follows:

pg_relation_size('table_name');

Example #1: How to Use the pg_relation_size() function in PostgreSQL?

We already have a table named “bike_details”. Let’s run the below statement to see the total size of the targeted table/relation:

SELECT pg_relation_size('bike_details');

The output shows that the pg_relation_size() function successfully returned the accurate size of the targeted relation.

Example #2: How to Use the pg_size_pretty() Function With the pg_relation_size() Function?

This example will teach you how to fetch the table’s size in a human-readable format:

SELECT pg_size_pretty(pg_relation_size('bike_details'));

Now, users can clearly understand that the selected table carries 8192 bytes.

Example #3: How to Get the Total Size of a Table Including Indexes/Additional Objects?
The pg_relation_size() function fetches only the table’s size, and it omits the size of indexes/additional objects. To fetch the total size of a table including indexes/additional objects, the pg_total_relation_size() function is used in PostgreSQL:

SELECT pg_size_pretty (pg_total_relation_size ('bike_details'));

The output verified the working of pg_total_relation_size() function as it calculates the table size accurately.

Conclusion

In PostgreSQL, built-in functions like pg_database_size(), pg_relation_size(), and pg_total_relation_size() are used to get the database and table size. The pg_total_relation_size() function is used to fetch the total size of a relation including indexes/additional objects. The pg_size_pretty() function can be used with the collaboration of the pg_database_size(), pg_relation_size() to present the database/table size in a human-readable format. In this write-up, you have learned how to get the size of a database or a table in PostgreSQL with the help of different examples.