[How to find size of Database and Table in PostgreSQL] DBA Scripts #postgres

snippet.sql

# Resource: https://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/

#  Find all the table size in the current database.
SELECT
    table_schema || '.' || table_name AS TableName,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

# Find all the table and index size in the current database.
SELECT
    TableName
    ,pg_size_pretty(pg_table_size(TableName)) AS TableSize
    ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize
    ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM 
(
     SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName
     FROM information_schema.tables
) AS Tables
ORDER BY 4 DESC;