PostgreSQL Database Size
by Dharmesh Patel
Determining the size of a PostgreSQL database is essential for managing storage and optimizing 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.
Need more development tips?
Stay tuned to our blog.