List Your PostgreSQL Databases by Size (With Access Check)

Keeping your PostgreSQL database server organized is crucial for optimal performance and manageability. A critical aspect of this organization is understanding the storage footprint of each database. This SQL code snippet empowers you to efficiently retrieve a list of all your PostgreSQL databases, sorted by their size in descending order, while also indicating whether you have access to connect to each database.

Sample Code from Command Line

 1SELECT
 2  d.datname AS Name,
 3  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
 4  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
 5    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
 6    ELSE 'No Access'
 7  END AS SIZE
 8FROM
 9  pg_catalog.pg_database d
10ORDER BY
11  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
12    THEN pg_catalog.pg_database_size(d.datname)
13    ELSE NULL
14  END
15  DESC;

Notes: PostgreSQL databases by size descending (only ones you can access). Tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, 13.0.

Understanding the Code Breakdown:

The provided PostgreSQL code achieves the desired functionality through the following steps:

1. Selecting Data:

   • d.datname AS Name: Selects the database name and assigns it the alias "Name".
   • pg_catalog.pg_get_userbyid(d.datdba) AS Owner: Retrieves the owner of the database and assigns it the alias "Owner".
   • CASE WHEN ... THEN ... ELSE ... END AS SIZE: This conditional expression determines the value displayed in the "SIZE" column:
     • If you have the privilege to connect (pg_catalog.has_database_privilege(d.datname, 'CONNECT') evaluates to TRUE), the database size is calculated using pg_catalog.pg_database_size(d.datname) and formatted for readability with pg_catalog.pg_size_pretty().
     • If you lack connection permission, the value displayed is simply "No Access".

2. Data Source:

   • FROM pg_catalog.pg_database d: Specifies that we're retrieving data from the pg_database catalog table, aliasing it as "d" for convenience.

3. Ordering the Results:

   • ORDER BY: Sorts the results based on the following logic:
     • If you have connection permission (pg_catalog.has_database_privilege(d.datname, 'CONNECT') is TRUE), the database size (pg_catalog.pg_database_size(d.datname)) is used for sorting in descending order (DESC).
     • If you don't have access, NULL is used for sorting, effectively placing those entries at the bottom of the list.

Key Points:

• This code snippet offers a quick and informative way to assess your PostgreSQL database server's storage usage.
• The size information is presented in a human-readable format using pg_size_pretty().
• The code also acts as a security check, indicating which databases you have access to connect to.

Putting it into Action:

1. Access your PostgreSQL server using a tool like pgAdmin or the command line with psql.
2. Copy and paste the provided code into the query window.
3. Execute the query.

The results will display a list of your databases, their owners, sizes (formatted for readability), and a clear indication of whether you have connection access to each one.

References:

PostgreSQL Documentation on pg_database_size(): https://stackoverflow.com/questions/18907047/postgres-db-size-command

PostgreSQL Documentation on has_database_privilege(): https://www.postgresql.org/docs/current/ddl-priv.html

Stack Overflow on Listing and Ordering PostgreSQL Tables by Size: https://stackoverflow.com/questions/77228730/how-to-list-tables-of-specific-database-and-sort-them-by-size-with-postgresql

Source:

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