Postgres is among the most frequently utilized relational databases. It stores the data in tables that can be retrieved or manipulated according to the requirements. Other than tables, a database can store several other database objects, like sequences, views, functions, etc.
The stored data in the database and tables occupy some storage space. Checking/Finding databases and table sizes before performing any specific functionality on them helps us organize the data efficiently. This can be done using different built-in Postgres functions, which we are going to discuss in this post.
Quick Outline
This blog will elaborate on the following topics:
- How to Find/Check the Database Size in Postgres?
- How to Find/Check the Table Size in PostgreSQL?
- Bonus Tip 1: How to Find//Check Database Size Through Meta Command?
- Bonus Tip 2: How to Find/Check Table Size Using Meta Command?
- Conclusion
How to Find/Check the Size of a Database in Postgres?
Finding/getting the database size helps us manage our data effectively. The need for checking/finding the database size arises when we have to create any new database object, remove redundant or unnecessary data, etc. The database size can impact the performance, storage requirements, and overall efficiency of a database system.
To find/check databases’ size, the Postgres queries as well as the pgAdmin’s statistics tab can be utilized.
Method 1: How to Find the Database Size via PostgreSQL Queries?
A convenient and recommended approach to check/get the database size is using the built-in pg_database_size() function. The stated function accepts a database name as an argument and retrieves its total size. It can also be utilized to check/find the size of all available databases. The fundamental syntax of this pg_database_size() function is given as
pg_database_size('db_Name')
In the above syntax:
- The database’s name “db_Name” is provided to the pg_database_size() function as an argument.
- The function will retrieve the total storage space that the selected database(s) takes.
Example 1: Getting the Size of a Database via pg_database_size()
In this example, we will find/check the size of the “test_database” by implementing the Postgres pg_database_size() function. The query for this purpose can be illustrated as
SELECT pg_database_size('test_database');
Note that inserting the “;” at the end of the above query is important when executing the query in psql. We will execute this query in the SQL Shell i.e. psql to get the size of the “test_database”. The same query can be executed in pgAdmin to get the database size.
Providing the “test_database” to the pg_database_size() function has returned the size of the given database.
The returned size of the selected database is a little bit complicated. This is because the returned size is more close to a machine-readable format; however, we can convert it into the human-readable format.
Example 2: Converting the Database Size to Pretty/Readable Format
Wrap the “pg_database_size()” within the “pg_size_pretty()” function to get the database size in an easily understandable format. The pg_size_pretty() function is especially meant to make the returned size in a human-friendly format such as GB, MB, KB, etc. The query can be tailored as
SELECT pg_size_pretty(pg_database_size('test_database'));
The above query will make the output easy to understand for a human, like this:
This is how we can get the size of a specific database.
Example 3: Getting/Finding the Size of All the Databases in PostgreSQL
We can fetch the size of all Postgres databases by using the “pg_database” catalog, as demonstrated in the following query.
SELECT pg_database.datname,
pg_database_size(pg_database.datname) AS size_of_database
FROM pg_database;
Where the “pg_database.datname” will show the name of the databases and “pg_database_size (pg_database.datname)” will retrieve the size of the corresponding databases:
We can also convert the retrieved database sizes into a human-readable format by using the pg_size_pretty() function on the size column, as shown below.
SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size_of_database
FROM pg_database;
This query will enlist all the databases along with their sizes in a human-friendly format in a separate column.
Method 2: How to Find the Database Size Using pgAdmin Statistics?
We can get the size of a PostgreSQL database from the pgAdmin as well. The same queries that we implemented in psql, can also be executed into the pgAdmin’s query tool to get the desired results. In addition to this, we can use database statistics to get the database size without executing any particular query. Follow the below-enlisted steps for better understanding:
Step 1: Select the Database
First, open the “pgAdmin” and specify your login credentials. Expand the “Server” option from the side panel and then expand the “Databases” option.
Step 2: Check Size
Left-click on the database whose size you want to get, navigate to the “Statistic” tab from the top menu, and then find the database size that will be located at the bottom of the list:
How to Find/Check the Table Size in Postgres?
Tables are the most important objects of a database that are used to store the data in a well-structured format. Checking the table size in Postgres before making significant changes can help us evaluate the potential impact on storage and performance, which allows us to plan accordingly and avoid unexpected issues. For this purpose, a couple of approaches can be utilized, such as Postgres Queries and pgAdmin Statistics.
We will demonstrate both methods one by one.
Method 1: How to Find the Table Size Using Postgres Queries?
In Postgres, the built-in pg_relation_size() function is used alongside the SELECT query to get the size of the selected table. The structure of the pg_relation_size() function looks like the following:
pg_relation_size('tab_Name')
In the above structure:
- The table’s name, whose size we want to fetch, is provided to the pg_relation_size() function as an argument.
- The pg_relation_size() function will retrieve the size of the specified table.
Example 1: Getting the Size of the Table Using the pg_relation_size() Function
To get the table size using psql, first, you have to switch to the database in which the table is residing. For example, in this case, the table “exam_result” is in “test_database” so we need to first, switch to the “test_database” from the default “Postgres” database, and then we can calculate the size of the “exam_result” table. This is done by running the following meta-command in the psql.
\c database_name
For this specific case, it will be:
\c test_database
Executing this meta-command will connect us to the “test_database”.
Once connected to the desired database, execute the following query to get the table’s size:
SELECT pg_relation_size('exam_result');
Here, “exam_result” is a table whose size needs to be fetched:
Example 2: Getting/Finding the Table Size in Human-Readable Format
To convert the returned table size into a human-friendly format, use the pg_relation_size() alongside the pg_size_pretty() function. As a result, the table’s size will be retrieved in “KBs”, “MBs”, etc. The query can be tailored as
SELECT pg_size_pretty(pg_relation_size('exam_result'));
The query returns the following results:
You can notice that the returned size is more readable and understandable than the previous one.
NOTE: Here one thing is important: the table size returned by the pg_relation_size() function is the size of the table only. However, there can be many other indexes and objects that might be associated with a database table. For this purpose, consider the following example.
Example 3: Getting the Total Size of the Database Table Along With Associated Indexes/Objects
Use the pg_total_relation_size() function to find/check the size of a table along with all of its associated objects. Wrap the pg_total_relation_size() function within the pg_size_pretty() function to obtain the table size along with its indexes and other related objects in an easily understandable format:
SELECT pg_size_pretty(pg_total_relation_size('exam_result'));
The total size of the “exam_result” is returned by the query.
Method 2: How to Find the Size of the Table Using pgAdmin Statistics?
Users can also utilize the pgAdmin’s statistics tab to obtain the size of the selected table, as illustrated in the below-provided steps.
Step 1: Locate Tables
First, expand the specific database(in which the table is present) from the side panel. Expand the “schema” option and then “public”. Then go to the “tables” option, and select it.
Step 2: Find All Tables’ Size
Left-click on the “Tables” option and navigate to the “Statistics” tab from the top bar.
All available tables in the selected “test_database” are described under the statistics tab. If we scroll right to the end, we will be able to see the size of all the tables.
Step 3: Find the Size of a Single/Specific Table
The description of a specific table can also be found using the statistic tab. We simply have to select the table, provided under the “tables” option, and then get statistics for it.
This is what the “messages” table looks like. Scroll down a little bit to get information on the sizes associated with the “messages” table.
Bonus Tip 1: How to Find/Check Database Size Through Meta Command?
The “\l+” command retrieves the list of existing databases, including default and user-defined databases. It provides detailed information about all available databases, such as database name, owner, size, etc. This meta-command can be executed to list the sizes of all databases. To use this command, open the psql terminal and follow the stated steps.
Step 1: Enable Expanded Display
The “\l+” meta-command retrieves a lot of columns containing information about the available databases. Due to the excessive number of columns, the output retrieved can be messy and difficult to understand. Therefore, it is recommended to execute the “\l+” command in the expanded display. For this purpose, first, execute the “\x” meta-command as follows:
\x
Note: Execute the same meta-command again to turn off the expanded display.
Step 2: Find Database Size
Now type the “\l+” command and hit the enter key to fetch the sizes of all available databases in the expanded display:
\l+
Bonus Tip 2: How to Find/Check Table Size Through Meta Command?
We can get the sizes of all the tables in a specific database by describing the table using the “\d+” meta-command. But first, the connection to the specific database must be established. For this purpose, execute the “\c db_name” meta-command. After being connected to the specific database, we will run the”\dt+” meta-command.
\d+
This command will retrieve a detailed description of all the tables available in that database like this:
We got the sizes of all the tables created in the “test_database” database.
Conclusion
To find the database and table sizes in PostgreSQL, the pg_database_size() and the pg_relation_size() functions are used, respectively. These functions take the name of the database and table respectively as input and return their sizes. The pg_size_pretty() function is usually complemented with these functions to return the more human-readable sizes of the database and table.
To get the total size of the selected table alongside all its objects, use the pg_total_relation_size() function instead of pg_relation_size(). In addition to this, the size of the databases and tables can also be found using the pgAdmin’s statistics tab. All these methods are extensively elaborated in this Postgres blog post.
