List size of tables, materialized views and views in your PostgreSQL database.

check.sql

WITH
    tables AS (
        SELECT
            oid::REGCLASS::TEXT AS table_name,
            CASE relkind
                WHEN 'm' THEN 'materialized_view'
                WHEN 'r' THEN 'table'
                WHEN 'v' THEN 'view'
            END                 AS type
        FROM
            pg_class
        WHERE relkind IN ('m', 'r', 'v')
          AND NOT oid::REGCLASS::TEXT ILIKE 'pg%'
          AND NOT oid::REGCLASS::TEXT ILIKE 'information_schema.%'
          AND NOT oid::REGCLASS::TEXT ILIKE 'public.%'
    ),
    table_size AS (
        SELECT
            table_name,
            type,
            pg_relation_size(table_name)       AS table_size,
            pg_indexes_size(table_name)        AS index_size,
            pg_total_relation_size(table_name) AS total_size,
            (
                SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE oid = table_name::REGCLASS
            )                                  AS row_est
        FROM
            tables
        ORDER BY pg_total_relation_size(table_name) DESC
    ),
    total AS (
        SELECT *
        FROM
            table_size
        UNION ALL
        SELECT
            'total',
            '',
            sum(table_size) AS table_size,
            sum(index_size) AS index_size,
            sum(total_size) AS total_size,
            sum(row_est)    AS row_est
        FROM
            table_size

    )
SELECT
    table_name,
    type,
    pg_size_pretty(table_size),
    pg_size_pretty(index_size),
    pg_size_pretty(total_size),
    row_est
FROM
    total;