"How to Retrieve Database and Table Sizes in PostgreSQL"

In PostgreSQL, there are several built-in utility functions, such as pg_database_size(), pg_relation_size(), and pg_size_pretty(), that help gather information about databases including table sizes." Let’s explore them.

The pg_size_pretty() function can be used with the collaboration of the pg_database_size(), and “pg_relation_size()” to present the database/table size in a human-readable format.

The following details about these specific functions highlight their roles and functionalities in PostgreSQL-:

1. pg_size_pretty()

Purpose: It converts a size in bytes to a human-readable format such as KB, MB, or GB.

Use Case: It's useful only when able to present database sizes in a more

understandable way for humans, especially it deals with large datasets or reporting database statistics.

2. pg_database_size('database_name')

Purpose: Returns the size of the specified database in bytes.

Use Case: This function helps to determine how much disk space a specific database is using. It’s used to monitor the growth of your database and manage storage more effectively.

3. pg_relation_size('table_name')

Purpose: Returns the size of the specified table (relation) in bytes.

Use Case: Useful for monitoring the size of individual tables.

In this post, we will explore the functions pg_database_size(), pg_relation_size(), and pg_size_pretty() with some examples.

• Let's first understand how to find the Database Size Using pg_database_size?

Use pg_database_size() function to get the Database size.

The syntax is - pg_database_size('database_name');

Example #1: Use of pg_database_size() function.

We already have a database named “Cardiac_Failure” in our server. Let’s execute the function to see the total size of the selected database:

SYNTAX - SELECT pg_database_size('Cardiac_Failure');

The output shows that the pg_database_size() function successfully returned the size of the selected database. But the result is not easy to understand, so we can use another function as below.

Example #2: Use the pg_size_pretty() Function With the pg_database_size() Function.

The database size in the above-given example is not easily readable. So we use the pg_size_pretty() function to convert the database size into human-readable format and getting the result as -

SYNTAX - SELECT pg_size_pretty(pg_database_size('Cardiac_Failure'));

The above result is more understandable for us.

There is another way also which shows how to fetch the database size to understand easily -

This is how the pg_size_pretty() function also explains database size.

Example #3: Fetching the Size of All Databases in Postgres SQL Server.

Let’s execute the below statement to find the size of all the databases which we have in our server.

SYNTAX –SELECT pg_database.datname, pg_database_size(pg_database.datname)

AS size FROM pg_database;

OR

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

EXPLANATIONS – Above both function examples, demonstrate the utilization of the datname, with the SELECT query to fetch 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 by both functions.

Now we can use pg_size_pretty() function to convert the current sizes into human-readable format:

SYNTAX - SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))

AS size FROM pg_database;

This is how the size of all the databases fetching using a single statement.

• Next is How to Find the Tables Size Using pg_relation_size?

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

SYNTAX - pg_relation_size('table_name');

Example #1: Use the pg_relation_size() function in PostgreSQL.

We have a table named “cardiaccomplications” under 'Cardiac_Failure’ database. Let’s run the below statement to see the total size of the targeted table/relation with the database:

SYNTAX - SELECT pg_relation_size(' cardiaccomplications');

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

Example #2: Use of the pg_size_pretty() Function With the pg_relation_size() Function.

This example explains to fetching the table size in a human-readable format.

SYNTAX - SELECT pg_size_pretty(pg_relation_size('cardiaccomplications'));

Now, it’s clearly understandable that the selected table carries 224 kb.

Example #3: Getting the Total Size of a Table Including Indexes.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, the pg_total_relation_size() function is used in PostgreSQL:

SYNTAX - SELECT pg_size_pretty (pg_total_relation_size (‘cardiaccomplications’));

It result shows the pg_total_relation_size() function calculates the table size accurately from the database.

Conclusion

So, we can conclude that 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 accurately. The pg_total_relation_size() function is used to fetch the table’s total size of a relation including indexes. The pg_size_pretty() function can be used with the other function of  pg_database_size(), pg_relation_size() to present the database/table size in a human-readable format. Through the explanations and examples provided above, we have learned how to effectively determine the size of a database or table in PostgreSQL........ Keep exploring and enhancing your understanding of PostgreSQL!......