PostgreSQL – Size of a Database

Efficient database management is essential for ensuring optimal performance in PostgreSQL. One critical aspect of this is monitoring the database size to manage storage and plan for scaling. PostgreSQL offers powerful built-in functions like pg_database_size() to calculate the size of a specific database, and pg_size_pretty() to format the result in human-readable formats such as KB, MB, or GB.

In this article, we will explain the Size of a PostgreSQL Database, how to find the size of a database in PostgreSQL, explore examples for querying individual and multiple databases, and learn how to interpret the results effectively.

Why Monitor Database Size in PostgreSQL?

Monitoring PostgreSQL database size is essential to:

• Optimize performance by identifying storage bottlenecks.
• Plan scaling strategies for growing datasets.
• Manage disk space usage and avoid unexpected system failures.

Functions for Calculating PostgreSQL Database Size

PostgreSQL provides several functions to retrieve database sizes:

1. pg_database_size('database_name'):
   Returns the size of the specified database in bytes.
2. pg_size_pretty(bytes):
   Converts the size in bytes to a more readable format (KB, MB, GB).
3. pg_total_relation_size('relation_name'):
   Calculates the size of a specific table, including indexes.

Syntax

SELECT pg_database_size('database_name');

Here, ‘database_name‘ is the name of the database whose size you want to check. The result is returned in bytes, which can be difficult to interpret for larger databases.

Now let’s list all the available database on our server and find their sizes in our example using the below command:

\l

Output

Examples of Finding Database Size in PostgreSQL

Let’s explore some practical examples to understand how to use these functions effectively. These examples will help us calculate the size of individual databases, tables, and indexes, offering a comprehensive view of storage usage in PostgreSQL.

Example 1: Querying the Size of a Specific Database

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: Querying the Size of Another Database

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: Querying the Size of the sales2020 Database

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: Querying the Size of All Databases on the Server

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

This query lists all databases along with their sizes in a readable format. It’s a convenient way to get an overview of storage usage across our entire PostgreSQL instance.

Conclusion

Monitoring PostgreSQL database size is crucial for maintaining database performance and managing storage resources effectively. PostgreSQL provides built-in functions like pg_database_size() and pg_size_pretty() to simplify the process of calculating and interpreting database sizes.

By using these functions, database administrators can gain insights into storage usage, optimize performance, and plan for scaling effectively. Whether we are managing a single database or an entire server, understanding these tools is essential for efficient database management.