Quickly Assess Your PostgreSQL Database Landscape by Size

Managing multiple PostgreSQL databases requires an understanding of their storage footprint. This article introduces a simple yet effective PostgreSQL code snippet that helps you quickly determine the size of each database in your system, ordered by their storage consumption (descending).

Sample Code from Command Line

1SELECT
2  datname,
3  pg_size_pretty(pg_database_size(datname))
4FROM
5  pg_database
6ORDER
7  BY pg_database_size(datname) DESC;

Notes: PostgreSQL databases by size descending. Tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, 13.0.

Quickly Assess Your PostgreSQL Database Landscape by Size

Managing multiple PostgreSQL databases requires an understanding of their storage footprint. This article introduces a simple yet effective PostgreSQL code snippet that helps you quickly determine the size of each database in your system, ordered by their storage consumption (descending).

Code Purpose:

The provided code retrieves information about all databases within your PostgreSQL server and calculates their sizes. It then presents the results in a user-friendly format, allowing you to identify the largest databases and optimize storage allocation if necessary.

Code Breakdown:

• Data Retrieval:
  • pg_database: This system catalog table stores information about all databases in the server.
• Selection:
  • datname: This column retrieves the name of each database.
  • pg_size_pretty(pg_database_size(datname)): This function calculates the size of each database in bytes and then converts it into a human-readable format (e.g., megabytes, gigabytes).
• Ordering:
  • ORDER BY pg_database_size(datname) DESC: This clause sorts the results in descending order based on the database size, with the largest database appearing first.

Key Points and Insights:

• This code provides a quick overview of your PostgreSQL database storage usage.
• Identifying the largest databases can help you prioritize optimization efforts, such as archiving old data or implementing data partitioning strategies.
• The human-readable size format (pg_size_pretty) makes it easier to interpret the results without needing to convert bytes to larger units manually.

Explanation of Important Parts:

• pg_database_size(datname): This function calculates the total size of a specific database (identified by datname) in bytes.
• pg_size_pretty(size): This function takes the database size in bytes (size) and converts it into a human-readable format with units like megabytes (MB) or gigabytes (GB).

Conclusion:

By incorporating this code snippet into your PostgreSQL management routine, you can gain valuable insights into your database storage utilization. This awareness empowers you to make informed decisions about storage allocation and database optimization strategies.

References:

pg_database system catalog: https://www.postgresql.org/docs/current/catalogs.html

pg_size_pretty function: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT

Source:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_databases_by_size.sql