pg_size_pretty() in PostgreSQL

What is PG size pretty in Postgres?

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.

Why Use pg_size_pretty()?

When dealing with large datasets or database monitoring, knowing the exact size of a table, index, or database is crucial. However, raw sizes in bytes are difficult to interpret. For instance, seeing “120000000 bytes” doesn’t immediately give you a sense of scale. With pg_size_pretty(), you can convert this into “114 MB,” which is much easier to comprehend at a glance.

How to Use pg_size_pretty()

To demonstrate how pg_size_pretty() works, let’s walk through some practical examples.

Example 1: Checking Database Size

You can check the size of an entire database using the following query:

Syntax: SELECT pg_size_pretty(pg_database_size(‘your_database_name’)) AS db_size;

Query: SELECT pg_size_pretty(pg_database_size(‘dvdrental’)) AS db_size;

This will return the database size in a more readable format, like:

Example 2: Table Size

To check the size of a specific table in your database:

SELECT pg_size_pretty(pg_total_relation_size(‘public.employee’)) AS table_size;

This will output something like:

Example 3: Disk Usage for Multiple Tables

To list all tables in your database with their sizes:

SELECTrelname AS table_name,pg_size_pretty(pg_total_relation_size(relid)) AS table_sizeFROM pg_catalog.pg_statio_user_tablesORDER BY pg_total_relation_size(relid) DESC;

This query will provide a list of tables and their sizes in descending order, giving you an overview of your database’s largest tables.

Conclusion

pg_size_pretty() is a simple but powerful function for making size information easier to interpret. Whether you’re monitoring database growth, managing disk space, or analyzing performance, this function makes raw byte data more intuitive and useful. By using it in combination with other PostgreSQL functions like pg_database_size() and pg_total_relation_size(), you can gain a clearer picture of your database's structure and resource consumption.