Find PostgreSQL database size?

This article will help you determine the size of a database in PostgreSQL. The database size includes tables, schemas, functions, and triggers. The contents of database tables can grow exponentially, which directly increases the size of the database. Thus, this resizing should be regularly monitored by the administrator so that the memory allocation for the database can be managed at any time and the process should not be affected due to memory issues.

Using pg database size select query

We will use the pg database size statement 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, a database name, and then selects a keyword, extracts the size in bigint format, and gives the size as output. Now we will implement this query using an example in the PostgreSQL environment.

>> select pg_database_size(‘Test’);

Check the result in the attached image.

Check the result for

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 clarify the result by converting bigint to a more understandable data type.

Using pretty query select pg size

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

>> SELECT pg_size_pretty( pg_database_size(‘database_name’) );

In this query, we are using the pg size pretty command, which takes the pg database size as an argument, which converts the output of the pg database size to the “KB” data type. We will now implement this query with a test database to understand this query in the PostgreSQL Query Editor.

Check the result in the attached image.

see the result in the attached image

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

Using pg_database.datname query

In this method, we will work with a query that will give us the size of all databases present on our server, in terms of kilobytes as the data type. We will use the following query for this method:

>> SELECT

pg_database.dataname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

In this query, we will use the select command to get the sizes of the databases. pg database.datname will collect all the databases present on the server and connect them with the pg size pretty command, which will get the size of the databases in the PostgreSQL environment. All of this data will be selected from the pg database command because all PostgreSQL databases are present in this location. We’ll take a closer look at this query by inserting it into the PostgreSQL environment.

SELECT

pg_database.dataname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

Check the result in the attached image.

see, all databases are present

As you can see, all the databases present in PostgreSQL are retrieved and presented, along with their sizes in kilobytes, in a table. This query helps the user to access all existing databases and gives the user a clear idea of ​​the memory load to become manageable. The above query is highly recommended to the user if he needs to get a general idea with which he can manage the load effectively in the case of space and performance.

Using the statistics option on the navigation bar

In all of the above methods, we have opted for the query and encoding features, but in this method, we will take advantage of the options available in pgAdmin 4. There are several options in the pgAdmin 4 window’s navigation pane that provide a variety of options. ease of processing data and information. So, we will also use one of the options to our advantage, namely the statistics option, which is the third option after “Properties” and “SQL”.

So to use this option for

Thus, to use this option to determine the size of a database, you must first find your database on the left side of the browser menu under the database heading. Then we will have to click and select the specific database that we want to know the size of. After that, we will have to click on “Statistics” to get all the statistical information related to a particular database. To better understand this method, we will try it on several databases that we have on our server.

We will first open a pgAdmin 4 window and then host our database in a PostgreSQL 14 environment.

We will first open a pgAdmin 4 window and then place

As you can see, there are two databases in the snippet above. We will first select a database named “Test”. Then we will select the “Statistics” option.

go, in the above snippet there are two databases present

After that, we will scroll down and find the “Size” section as the latest information available in this tab. We will be able to see the size of the database in kilobytes.

o we scroll down and find the section

We will now select another database, “postgres”.

b we will choose another database,

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

of this we will select the

Using the SQL shell (psql)

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

>> select pg_database_size(‘Test’);

The bigint size of the database will be returned by the SQL wrapper. Now let’s write a query to get the size of the database in kilobytes.

SELECT pg_size_pretty( pg_database_size(‘Test’) );

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

>> SELECT

pg_database.dataname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

This will give the size of the databases present on the server, in kilobyte data type.

Conclusion

In this tutorial, we have discussed several methods for determining the size of a database in PostgreSQL. We have discussed several query methods in PostgreSQL. First, we discussed the method in which the size of the database was given in bigint. But this conclusion was not clear regarding scalability. Hence, we have discussed one more method of converting the size from bigint to kilobytes. Then, this tutorial also discussed the method to get the size of all the databases present in the environment. After that, we examined the pgAdmin 4 options to check the size of the database.

Find PostgreSQL database size?

Leave a Reply

Your email address will not be published.

Scroll to top