PostgreSQL is a relational database management system that helps us store data for different web apps, mobile apps, etc. In Postgres, tables are the objects of a database that depict the data in the form of rows and columns. While working with databases, finding the sizes of the database objects can help us in storage management, memory optimization, resource management, etc.

This write-up will explain how to check the size of databases or tables in PostgreSQL using pgAdmin.

How to Check Database Size or Table Size in Postgres Using pgAdmin?

In Postgres, different built-in functions are used to check the size of database objects, such as the pg_relation_size(), pg_database_size(), pg_size_pretty(), etc. However, pgAdmin helps us get the size of database objects with or without using these built-in functions.

Getting Database Size Using pgAdmin

This section illustrates how to get the database size in pgAdmin:

- Using pgAdmin’s Statistics
- Using pg_database_size() Function
- Using pg_size_pretty() Function

Example 1: Getting All Databases Size Using pgAdmin’s Statistics

Open the pgAdmin, click on the “Databases” option under the “Servers” tree, and then select the pgAdmin’s statistics tab to get the size of all databases:

The databases’ sizes can be seen in the “Size” column.

Example 2: Getting the Size of a Specific Database Using pgAdmin’s Statistics

The size of a specific database can be obtained by selecting that particular database and then clicking on the "Statistics" tab:

The above snippet shows the size of the “postgres” database.

Example 3: Getting the Size of a Specific Database Using pg_database_size() Function

Alternatively, you can use the “pg_database_size()” function from the pgAdmin’s query tool to get the database size:

SELECT pg_database_size('postgres');

The stated function retrieves the database size in bytes.

Example 4: Getting the Size of a Database Using pg_size_pretty() Function

Bytes are difficult to comprehend if the databases contain enormous amounts of data. To get the data in an easily understandable format, wrap the “pg_database_size()” function within the “pg_size_pretty()” function, as follows:

SELECT pg_size_pretty(pg_database_size('postgres'));

This time, the database size is retrieved in “KBs”.

Example 5: Getting the Size of All Databases Using pg_database_size() Function

Use the pg_database_size() function with the “pg_database” catalog to get the size of all databases, including the standard system databases:

SELECT pg_database.datname, 
pg_database_size(pg_database.datname) AS all_databases_size 
FROM pg_database;

- Here, the “pg_database” is a system catalog that contains information regarding the databases.
- The “datname” is a column containing information regarding the database name.
- “pg_database_size()” is a built-in function that retrieves the database sizes.
- “AS” represents a column alias, used to assign a temporary name to the resultant column.

The output snippet retrieves the databases’ size in bytes.

Getting Tables Size Using pgAdmin

This section demonstrates how to get tables size:

- Using pgAdmin’s Statistics
- Using pg_relation_size() Function
- Using pg_size_pretty() Function

Example 1: Getting Size of All Tables Using pgAdmin’s Statistics

Expand the “Schemas” option available under the "database” of your choice. Locate the “public” schema, select the “Tables” option, and click on the “statistics” tab to see the size of all tables:

The output shows the total size for each table available in the “postgres” database.

Example 2: Getting the Size of a Specific Table Using pgAdmin’s Statistics

To get the size of only a particular table, all you need to do is select the table of your choice and click on the “statistics” tab:

The output shows that the total size of the “department_information” table is “8KB”.

Example 3: Getting Table Size Using pg_relation_size() Function

Alternatively, you can specify the table name inside the “pg_relation_size()” function to get the size of the desired table:

SELECT pg_relation_size('department_information') AS table_size;

The output shows the total number of bytes that the “department_information” table takes.

Example 4: Getting Table Size Using pg_size_pretty() Function

Wrap the “pg_relation_size()” function within the “pg_size_pretty()” function to get the table’s size in human-understandable formats:

SELECT pg_size_pretty(pg_relation_size('department_information'));

The output proves that the pg_size_pretty() function returns the table’s size in an easily understandable format.

Conclusion

In PostgreSQL, built-in functions like pg_relation_size(), pg_database_size(), pg_size_pretty(), etc. are used to get the size of the table or database. The pgAdmin helps us get the size of database objects with or without using these built-in functions. To get the size of a database or table without using any function, you need to select the database or table of your choice and then click on pgAdmin’s statistics tab. This Postgres blog explained how to check the database size and table size using pgAdmin.