Get the information about the amount of storage used for tables (works for #mysql, #mariadb, #postgres)

get_tables_info.sql

-- Note that numbers are estimates, so the exact number of rows or index size might slightly differ from the number shown

-- For MySQL or MariaDB
select
    table_name as `table name`,
    table_rows as `row count`,
    round(data_length / (1024 * 1024 * 1024), 2) as `data size (gb)`,
    round(index_length / (1024 * 1024 * 1024), 2) as `index size (gb)`,
    round((data_length + index_length) / (1024 * 1024 * 1024), 2) as `total size (gb)`
from
    information_schema.tables
where
    -- table_schema = coalesce(:schema_name, 'services') -- Doesn't work in `mysql`
    table_schema = 'services'
order by
    `total size (gb)` desc;

--------------------------------------------------------------------------------------------------------------------

-- For PostgreSQL
select 
    c.relname as "table name",
    coalesce(nullif(c.reltuples, -1), 0)::bigint as "row count",
    round(pg_table_size(c.oid) / (1024.0 * 1024 * 1024), 2) as "data size (gb)",
    round(pg_indexes_size(c.oid) / (1024.0 * 1024 * 1024), 2) as "index size (gb)",
    round(pg_total_relation_size(c.oid) / (1024.0 * 1024 * 1024), 2) as "total size (gb)"
from 
    pg_class c
join 
    pg_namespace n on n.oid = c.relnamespace
where
    -- n.nspname = coalesce(:schema_name, 'services') -- Doesn't work in `psql`
    n.nspname = 'services' -- replace with your schema name
    and c.relkind = 'r' -- only regular tables
order by 
    "total size (gb)" desc;