In PostgreSQL, various built-in functions are used to get the size of database objects. For instance, the pg_relation_size() function retrieves the size of a particular table, the pg_database_size() function retrieves the size of a particular database, the pg_total_relation_size() function retrieves the total size of a table, etc.
All these functions return the size in bytes, which might be difficult to read/understand when the size of the object is too big. Therefore, Postgres provides a pg_size_pretty() function that retrieves the size of the database object in a more readable format, such as KB, MB, etc.
This post presents a comprehensive guide on how to use the “pg_size_pretty()” function to get the size of a database object in a human-readable format.
How to Get the Size of a Database Object Using pg_size_pretty() Function in Postgres?
To get the size of database objects in human-readable format use the pg_size_pretty() function as follows:
pg_size_pretty (function);
Where the “function” can be the pg_relation_size(), pg_database_size(), pg_tablespace_size(), etc.
Example 1: Getting Database and Table Size in Postgres
Let’s consider the following snippet to understand how the pg_relation_size() function and the pg_database_size() function work in Postgres:
SELECT pg_database_size ('postgres') AS db_size,
pg_relation_size('emp_data') AS table_size;
The output shows that the stated functions retrieve the size in bytes.
Example 2: Getting Database and Table Size in an Easily-Understandable Format
The below snippet demonstrates how to get the database and table size in a more readable format:
SELECT pg_size_pretty(pg_database_size ('postgres')) AS db_size,
pg_size_pretty (pg_relation_size('emp_data')) AS table_size;
The pg_size_pretty() returns the size in a well-understandable format.
Example 3: Getting Tablespace Size in Postgres
In the following code snippet, the pg_tablespace_size() function is used to fetch the size of the default tablespace:
SELECT pg_tablespace_size('pg_default') AS tablespace_size;
The output displays the size of the default tablespace.
Example 4: Getting Tablespace Size in Human-Readable Format
In the following snippet, the pg_tablespace_size() function is used along with the pg_size_pretty() function to fetch the size of the default tablespace in human-readable format:
SELECT pg_size_pretty(pg_tablespace_size('pg_default')) AS tablespace_size;
The output shows that the default tablespace takes 44MB size.
Example 5: Getting the Total Size of a Table in Postgres
The following snippet shows how to get the total size of a table using pg_total_realtion_size() and pg_size_pretty() functions:
SELECT pg_total_relation_size('emp_data') AS table_size,
pg_size_pretty(pg_total_relation_size('emp_data')) AS pretty_size;
The output shows the total size of a table named “emp_data”.
Conclusion
In PostgreSQL, the pg_size_pretty() function retrieves the size of the database object in a human-readable format, such as KB, MB, etc. The pg_size_pretty() function accepts the built-in functions like pg_relation_size(), pg_database_size(), pg_total_relation_size(), etc. as an argument and retrieves the object size in an easily understandable format. This post illustrates a thorough guide on how to use the “pg_size_pretty()” function to get the size of a database object in a well-understandable format.
