PostgreSQL – Size of a Database
Last Updated : 15 Apr, 2025
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:
pg_database_size('database_name'):
Returns the size of the specified database in bytes.pg_size_pretty(bytes):
Converts the size in bytes to a more readable format (KB, MB, GB).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.
Similar Reads
PostgreSQL - Loading a Database
In this article we will look into the process of loading a PostgreSQL database into the PostgreSQL database server. Before moving forward we just need to make sure of two things: PostgreSQL database server is installed on your system. A sample database. For the purpose of this article, we will be us
3 min read
PostgreSQL - Show Databases
In PostgreSQL, viewing a list of all databases on a server requires specific commands, as it doesn’t support a direct SHOW DATABASES statement like MySQL. Instead, you can use the \l or \l+ commands in psql or query the pg_database view to display all databases. In this article, we will guide us thr
3 min read
PostgreSQL - Size of Indexes
In PostgreSQL, index management is essential for optimizing query performance and ensuring efficient database storage. One important function for assessing the storage requirements of table indexes is the pg_indexes_size() function. In this article, we will explain the pg_indexes_size() function, it
4 min read
PostgreSQL - Create Database
Creating a database in PostgreSQL is an important task for developers and database administrators to manage data effectively. PostgreSQL provides multiple ways to create a database, catering to different user preferences, whether through the command-line interface or using a graphical interface like
5 min read
PostgreSQL - Backup Database
All commercial firms and corporations are never 100% free of data threats. Being wary of scenarios like data corruption, host or network failures or even deliberate storage facility destruction is extremely important. Therefore, backing up data is a critical activity that every firm depends on to en
12 min read
PostgreSQL - Copy Database
Copying a PostgreSQL database is essential for tasks like testing, backup, and database migration. PostgreSQL offers efficient tools for duplicating databases either within the same server or across different servers, ensuring data consistency and integrity. This guide explains step-by-step methods
4 min read
PostgreSQL - Size of a Table
PostgreSQL provides a variety of functions to help you query the size of your tables. We'll focus on the 'pg_relation_size()' function to get the size of a table and enhance the readability of the output using the 'pg_size_pretty()' function. In this article, we will be using a sample database for r
3 min read
How to Setup a PostgreSQL Database Cluster
A PostgreSQL database cluster refers to a collection of databases managed by a single instance of the PostgreSQL server. Setting up a PostgreSQL cluster is an essential task for organizing multiple databases and achieving high availability, scalability, and load balancing. Whether we are working wit
5 min read
How to use PostgreSQL Database in Django?
This article revolves around how can you change your default Django SQLite-server to PostgreSQL. PostgreSQL and SQLite are the most widely used RDBMS relational database management systems. They are both open-source and free. There are some major differences that you should be consider when you are
2 min read
PostgreSQL - Size of value
The size of a value in a database table refers to the space required to store that specific value. Accurately gauging the storage requirements for different data types can be crucial for database optimization and performance. In this article, we will explore the 'pg_column_size()' function in Postgr
2 min read