PostgreSQL

Find Postgres Database Size?

This article will guide finding the size of a database in PostgreSQL. The size of a database comprises tables, schemas, functions, and triggers. The content of databases’ tables can increase exponentially, which directly increases the size of the database. So, this change in size should be monitored by the admin on a regular basis so that at all times, the memory allocation for the database can be managed, and the process must not be affected due to memory issues.

Finding the databases size:

We will go through several ways to look for the database size in the PostgreSQL environment.

  1. By using select pg database size query.
  2. By using select pg size pretty query.
  3. By using pg_database.datname query.
  4. By using the statistics option in the navigation bar.
  5. By using SQL Shell (psql).

Let us look into these methods in PostgreSQL by implementing them on a Test database.

By using select pg database size query:

We will use a pg database size instruction to find the database size in this method. The syntax for this instruction is written below:

>> select pg_database_size('Name_Of_Database');

The pg database size function takes a parameter, the name of the database, and then selects keyword, fetches the size in bigint and gives the size as an output. Now we will implement this query with an example in the PostgreSQL environment.

>> select pg_database_size('Test');

Check the output in the appended image.

Graphical user interface, text Description automatically generated with medium confidence

As the output suggests, the size of the database named “ Test ” is “ 9044771 ” in bigint, but this makes the size of the database unclear, and we should make the result clear by converting the bigint into a more understandable datatype.

By using select pg size pretty query:

In this method, we will use the pg size pretty query in the query editor to find out the size of the database. The syntax for this query is given below:

>> SELECT pg_size_pretty( pg_database_size('database_name') );

In this query, we use the pg size pretty command, which takes the pg database size as an argument that converts the pg database size output to a “KB” datatype. Now we will implement this query with a test database to understand this query in the PostgreSQL query editor.

Check the output in the appended image.

A screenshot of a computer screen Description automatically generated with low confidence

This query gives the size of the database named “Test“ in the KB data type, which is more understandable than the bigint data type.

By using pg_database.datname query:

In this method, we will work with a query that will give us the size of all the databases present on our server in the form of Kilobytes as a datatype. We’ll use the following query for this method:

>> SELECT

pg_database.datname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

In this query, we will be using the select command for fetching databases’ sizes. The pg database.datname will collect all the databases present in the server and conjugate them with the pg size pretty command that will fetch the size of the databases in the PostgreSQL environment. All this data will be selected from the pg database command because all the databases of PostgreSQL are present at this location. We will take a closer look at this query by inserting it into the PostgreSQL environment.

SELECT

pg_database.datname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

Text Description automatically generated

Check the output in the appended image.

A screenshot of a computer screen Description automatically generated with medium confidence

As you can see, all the databases present in PostgreSQL are being fetched and presented along with their sizes in Kilobytes in the form of a table. This query helps the user reach all the databases present and enables the user to have a clear perspective of the memory load to become manageable. The above query is highly recommended for a user if they have to get an overall view with which they can do efficient load management in the case of space and performance.

By using the statistics option in the navigation bar:

In all the above methods, we have opted for queries and coding functions, but in this method, we will take advantage of the options available in pgAdmin 4. There are several options present in the navigation bar of the pgAdmin 4 window that provides a lot of ease while handling data and processing information. So, we will also use one of the options for our benefit that is the statistics option which is the third option after “ Properties ” and “ SQL ”.

So, to use this option for finding out the size of a database is to first find your database on the left-hand side in the browser menu under the heading databases. Then we will have to click and select the certain database whose size we want to find out. After this, we will have to click on the “ Statistics “ option to get all the statistical information related to the certain database. To better grasp this method, we will try this on several databases present on our server.

First, we will open the pgAdmin 4 window, and then we will locate our database in the PostgreSQL 14 environment.

A screenshot of a computer Description automatically generated with low confidence

As you can see, we have two databases present in the above snippet. First, we will select the database named “Test”. Then we will select the “Statistics” option.

A screenshot of a computer Description automatically generated with medium confidence

After this, we will scroll down and will locate the Size section as the last information available in this tab. We will be able to see the database’s size in kilobytes.

A screenshot of a computer Description automatically generated with medium confidence

We will now choose the other database, ” postgres “.

A screenshot of a computer screen Description automatically generated with medium confidence

After this, we will select the “Statistics” option and scroll down to see the size of this database.

A screenshot of a computer Description automatically generated with medium confidence

By using SQL Shell (psql):

In this method, we will use the SQL shell for finding the size of the database. Open the SQL shell and write the following query:

>> select pg_database_size('Test');

Text Description automatically generated

The size of the database in bigint will be returned by the SQL shell. Now we will write a query for getting the size of the database in Kilobytes.

SELECT pg_size_pretty( pg_database_size('Test') );

Text Description automatically generated

This query will generate the size of the database “ Test ” in the kilobytes data type. Now we will write the query to generate the database size on the server.

>> SELECT

pg_database.datname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

Text Description automatically generated

This will give the size of the databases present in the server in the kilobytes datatype.

Conclusion:

In this guide, we discussed several methods for finding the size of the database in PostgreSQL. We discussed several query methods in PostgreSQL. First, we discussed a method in which the size of the database was given in bigint, but this output was unclear with respect to scalability. Hence, we discussed another method of converting the size from bigint to kilobytes. Then the method for getting the size of all the databases present in the environment was also discussed in this guide. After this, we explored pgAdmin 4 options to check the database size.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.