Checking PostgreSQL Database Size

PostgreSQL Database Size

Determining the size of a PostgreSQL database is essential for managing storage and optimising performance. PostgreSQL offers several functions to retrieve the size of databases, tables, and indexes. Here's a guide on how to check the size of your PostgreSQL database using specific SQL queries:

Using 'pg_database_size' Function

The pg_database_size function returns the size of the specified database in bytes. This function is useful for finding the size of the current database or any other database within your PostgreSQL instance.

Steps to Determine the Database Size

Here’s a step-by-step example of how you might determine the size of the current database:

1. Connect to PostgreSQL

First, connect to your PostgreSQL database using a client such as psql, pgAdmin, or any other database management tool.

$ sudo su postgres // example of linux

2. Run the Query

Execute the following SQL query to get the size of the current database in a human-readable format.

Query for the Current Database

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;

Query for a Specific Database

Replace your_database_name with the name of the database you want to check.

SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;

3. View the Results

The query will return a result like:

database_size
--------------
120 MB
(1 row)

This output shows the size of the specified database in a human-readable format, making it easier to understand and manage.

By following these steps, you can monitor and manage the size of your PostgreSQL databases, ensuring optimal performance and storage utilisation.

Found this article useful?

Explore more guides and solutions from our team.

Check out more posts

in Miscellaneous

Dharmesh Patel 28 May, 2024