Several common ways to view the size of database footprint in PostgreSQL

In PostgreSQL, viewing the current data usage of database and data tables can be achieved by executing specific SQL queries. Here are some commonly used methods:

1. Check the size of the footprint of a single data table:

Use the pg_size_pretty and pg_total_relation_size functions to get the footprint of a specific data table (including data and index). For example:

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

Replace table_name with the table name you want to query.

2. View the disk space occupied by a single database:

Also, using the pg_size_pretty and pg_database_size functions, you can get the footprint of a specific database. For example:

SELECT pg_size_pretty(pg_database_size('database_name'));

Replace database_name with the database name you want to query.

3. Statistics the disk sizes occupied by all databases:

If you need to view the size of all databases, you can use the following query:

SELECT  AS Name,  
       pg_catalog.pg_get_userbyid() AS Owner,  
       CASE  
           WHEN pg_catalog.has_database_privilege(, 'CONNECT') THEN  
               pg_catalog.pg_size_pretty(pg_catalog.pg_database_size())  
           ELSE 'No Access'  
       END AS SIZE  
FROM pg_catalog.pg_database d  
ORDER BY CASE  
            WHEN pg_catalog.has_database_privilege(, 'CONNECT') THEN  
                pg_catalog.pg_database_size()  
        END;

This query lists the names, owners, and their respective sizes of all databases.

4. View all table sizes:

If you need to view the size of all tables in the database, you can use the following query:

SELECT relname, pg_size_pretty(pg_relation_size(relid)) AS size  
FROM pg_stat_user_tables;

This is the end of this article about several commonly used methods for PostgreSQL to view the size of database space. For more related PostgreSQL to view the size of space, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!